[AccessD] VBA Unbound data entry / update form

Max Wanadoo max.wanadoo at gmail.com
Tue Jun 10 13:46:44 CDT 2008


 
>Golden rule with the app we purchased...first person to click
save...wins!!!

Mark, I think you have that back to front....  The LAST person to click save
wins.  Everybody before gets overwritten!

Max


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark A Matte
Sent: Tuesday, June 10, 2008 5:51 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] VBA Unbound data entry / update form


I can tell you how our PeopleSoft (web based) App handles it...it compares
the last_update_timestamp...if it is the same as when you pulled your
recordset...you get to save...if not...you lose your changes.

Golden rule with the app we purchased...first person to click save...wins!!!

Thanks,

Mark A. Matte




> Date: Tue, 10 Jun 2008 12:06:43 -0400
> From: jwcolby at colbyconsulting.com
> To: accessd at databaseadvisors.com
> Subject: Re: [AccessD] VBA Unbound data entry / update form
>
> Rocky,
>
> I hate to jump all over your method because I want a method to use, 
> but you are completely ignoring locking / update issues. What happened 
> if another user updated the record between the time you loaded yours 
> and the time you write it back?
>
> John W. Colby
> www.ColbyConsulting.com
>
>
> Rocky Smolin at Beach Access Software wrote:
>> I know how to make an unbound form but I haven't got enough nerve to 
>> present such heresy - it runs counter to strongly held religious beliefs.
>>
>> OK - briefly - but you didn't hear it from me.
>>
>> 1. Design your form as you would a bound form but no Control Source 
>> in the text boxes.
>> 2. Create a recordset in the Open event of the form (DAO or ADO - I 
>> prefer
>> DAO) using pretty much the same SQL or query as you would for your 
>> Record Source in a bound form.
>> 3. Add a module to put the fields from the current record of the 
>> recordset into the text boxes which you can call whenever you want to 
>> display the data on your form.
>> 4. Add a module to put the values in the text boxes into the fields 
>> of the current record (in DAO use .Edit or .Add and .Update) which 
>> you can call whenever you want to write the textbox values back to the
table.
>> 5. I always put my own navigation buttons on the unbound for - First, 
>> Last, Next, Previous with Click events that move the recordset point 
>> appropriately.
>>
>> HTH
>>
>> Rocky Smolin
>> Beach Access Software
>> 858-259-4334
>> www.e-z-mrp.com
>> www.bchacc.com
>>
>>
>>
>> -----Original Message-----
>> From: accessd-bounces at databaseadvisors.com
>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim 
>> Lawrence
>> Sent: Monday, June 09, 2008 5:38 PM
>> To: 'Access Developers discussion and problem solving'
>> Subject: Re: [AccessD] VBA Unbound data entry / update form
>>
>> Hi Jennifer:
>>
>> I have no idea how to create bound Access forms. ;-) The last bound 
>> database that I have worked with was back in '97.
>>
>> Jim
>>
>> -----Original Message-----
>> From: accessd-bounces at databaseadvisors.com
>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jennifer 
>> Gross
>> Sent: Tuesday, May 27, 2008 5:12 PM
>> To: 'Access Developers discussion and problem solving'
>> Subject: Re: [AccessD] VBA Unbound data entry / update form
>>
>> I have no idea how to create an unbound form in Access. I always use 
>> bound forms. Though I would be interested to know how it's done. 
>> Unless I've got it wrong, that seems to be the basic question here - 
>> For those of you who do it, how do you create an unbound form? How do 
>> you populate the textboxes initially and then how do you save the
information back to the tables?
>>
>> It's beginning to sound like nobody really does it.
>>
>> Jennifer
>>
>> -----Original Message-----
>> From: accessd-bounces at databaseadvisors.com
>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
>> Sent: Tuesday, May 27, 2008 2:49 PM
>> To: Access Developers discussion and problem solving
>> Subject: Re: [AccessD] VBA Unbound data entry / update form
>>
>>> 1) If your situation gives you the choice between Access and SQL 
>>> Server,
>> then you can use SQL Server Express. For your customer's benefit - 
>> it's free!
>>
>> I know that, and you know that, but they have used an MDB BE for most 
>> of a decade. SQL Server is the great unknown.
>>
>>> 2) How many concurrent users? Too Many?
>>
>> In the majority of cases, no. There ARE specific places where too 
>> many users cause mysterious issues with an MDB.
>> Memos are written in "pages", as are indexes. If you open a record 
>> and start to edit it, it "locks" an entire "page" of the index 
>> structure or the memo area, which locks not just your record but
potentially many others.
>>
>> This simply doesn't happen in SQL Server because SQL Server doesn't 
>> have this "page" system for storing memo fields etc.
>>
>>> 3) How many indexes on the tables?
>>
>> Not the point, the point is that ALL indexes are stored in Index 
>> pages, and entire pages of indexs can be locked by a single edit.
>>
>> Creating / using Indexes should be determined by need, not arcane 
>> locking issues within Jet.
>>
>>> 4) Are you starting up by setting a recordset to open and leaving it 
>>> that
>> way?
>>
>> If you are talking about creating and holding locks on the BE, yes I 
>> am, but that isn't the issue either.
>>
>> John W. Colby
>> www.ColbyConsulting.com
>>
>>
>> Dan Waters wrote:
>>> John - I've been reading this with interest:
>>>
>>> 1) If your situation gives you the choice between Access and SQL 
>>> Server, then you can use SQL Server Express. For your customer's 
>>> benefit - it's free!
>>>
>>> 2) How many concurrent users? Too Many?
>>>
>>> 3) How many indexes on the tables? I actually only use the primary 
>>> key as an index on every table. More indexes slows down writing
>> performance.
>>> 4) Are you starting up by setting a recordset to open and leaving it 
>>> that way? This greatly reduces 'churn' in the locking database. When 
>>> I set this up all my users reported a significant performance
improvement.
>> Code below:
>>> '-------------------------------------------------------------------
>>> -----
>>> Dim stgConnect As String
>>> Dim dbs As DAO.Database
>>> Dim tdf As DAO.TableDef
>>>
>>> stgConnect = BEFullPath
>>> Set dbs = DBEngine(0)(0)
>>> Set tdf = dbs.TableDefs("tblConnect") If tdf.Connect <> ";Database=" 
>>> & stgConnect Then tdf.Connect = ";Database=" & stgConnect 
>>> tdf.RefreshLink End If dbs.Close
>>>
>>> Set GrstConnect = DBEngine(0)(0).OpenRecordset("tblConnect",
>>> dbOpenSnapshot)
>>> '-------------------------------------------------------------------
>>> --
>>> ---
>>>
>>> Notes: BEFullPath is a function to return the full path to the BE.
>>> tblConnect is a one row one field table in the BE.
>>> The table link is refreshed because the table link is in a
>> library.
>>> GrstConnect is a globally defined DAO recordset.
>>> GrstConnect remains open until just before the database is closed.
>>>
>>> Good Luck!
>>> Dan
>> --
>> AccessD mailing list
>> AccessD at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
>>
>>
>> --
>> AccessD mailing list
>> AccessD at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
>>
>> --
>> AccessD mailing list
>> AccessD at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
>>
>> No virus found in this incoming message.
>> Checked by AVG.
>> Version: 8.0.100 / Virus Database: 270.1.0/1492 - Release Date: 
>> 9/6/2008
>> 10:29
>>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com

_________________________________________________________________
Search that pays you back! Introducing Live Search cashback.
http://search.live.com/cashback/?&pkw=form=MIJAAF/publ=HMTGL/crea=srchpaysyo
uback
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list