[AccessD] VBA Unbound data entry / update form

Susan Harkins ssharkins at gmail.com
Tue May 27 17:02:22 CDT 2008


John, are you asking how to use disconnected recordsets or are you trying to 
talk yourself into writing a class instead? I've got a good basic article on 
using disconnected recordsets on techrepublic.com, if you're interested. 
There's also a much more involved article on the subject in an old issue of 
IMA. I could look up the issue, but unless you have it... none of their 
stuff's assessable online.

Susan H.


> Rocky,
>
> The whole point of the unbound exercise is to open a single
> record, populate the form with the values, then close the
> recordset.  If I am going to hold the record open then I
> might as well just bind the form and be done with it.
>
> When the user saves the data in the unbound form, the form
> needs to check if anything is dirty, if so then it needs to
> grab the record again, if the record no longer exists then
> the record has been deleted and then what...?  Inform the
> record that the record was deleted?  Refuse to save the changes?
>
> Assuming the record has not been deleted, then the updated
> fields have to check the same fields in the underlying
> record to see if the "old data" from the form (the data at
> the time the form opened) matches the data in the
> corresponding field in the record.  If so then it can just
> be written back.  If not then the user has to be told that
> the data changed and...  Ask the user if they want to
> overwrite the changes in the table with the changes they
> just made?  Refuse to save their changes?
>
> As you can see, disconnected records cause a lot of
> headaches that bound forms "just take care of".
> "Disconnected" means that no lock is placed, no recordset is
> held open, and dbPessimistic and dbSeeChanges are irrelevant.
>
> I am not an "unbound" kinda guy so I have not answered these
> questions, I am attempting to get answers for "best
> practices" on how to handle these and any other similar issues.
>
> The only reason I am even getting into this is that I have a
> couple of "choke points" where for some unknown, unexplained
> reason, I am getting "record locked" issues as the user
> tries to edit records.  AFAICT these are not even records
> that other users are editing or even viewing.  In fact I get
> "locked" records when trying to ADD A NEW RECORD... which
> indicates that the issue is way down in the guts of Access /
> JET in things like Index or memo pages.  My THEORY is that
> if I make these forms unbound then these memo and index page
> locks will go away since there are no longer dozens of users
> trying to lock memo pages or index pages, except very
> briefly as they save new / changed records.
>
> Unfortunately, the "art of unboundedness" is arcane (at
> least to me) and I am not finding good solid references as
> to how to handle these things, nor what the results will be
> at the "jet guts" level.
>
> I tried to convince my clients to just move to SQL Server,
> which doesn't have these memo and index page lock issues,
> and let me go back to my peaceful bound form world.  They
> are not doing that so here we are.
>
> John W. Colby
> www.ColbyConsulting.com
>
>
> Rocky Smolin at Beach Access Software wrote:
>> John:
>>
>> Won't dbPessimistic in the LockEdits parameter of the OpenRecordset do 
>> what
>> you need to have done with regard to multiple users attempting to edit 
>> the
>> same record?
>>
>> dbSeeChanges may also bee what you need.  From the help:
>>
>> Use the dbSeeChanges constant in a Microsoft Jet workspace if you want to
>> trap changes while two or more users are editing or deleting the same
>> record. For example, if two users start editing the same record, the 
>> first
>> user to execute the Update method succeeds. When the second user invokes 
>> the
>> Update method, a run-time error occurs. Similarly, if the second user 
>> tries
>> to use the Delete method to delete the record, and the first user has
>> already changed it, a run-time error occurs.
>>
>> Typically, if the user gets this error while updating a record, your code
>> should refresh the contents of the fields and retrieve the newly modified
>> values. If the error occurs while deleting a record, your code could 
>> display
>> the new record data to the user and a message indicating that the data 
>> has
>> recently changed. At this point, your code can request a confirmation 
>> that
>> the user still wants to delete the record.
>>
>> 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 jwcolby
>> Sent: Tuesday, May 27, 2008 1:05 PM
>> To: Access Developers discussion and problem solving
>> Subject: Re: [AccessD] VBA Unbound data entry / update form
>>
>> LOL, I do want to do a lot with an unbound form.  It should open a 
>> record,
>> display that record, allow it to be edited, and save the record back 
>> again
>> once edited, all without corrupting data, honoring data saves before it
>> saves etc.
>> Bound forms do all of that, and the unholy alliance of unbounders have
>> always indicated that they could also do all of this stuff.
>>
>> Now I need to do all that stuff and just thought those UAU members would
>> have their expertise to share.
>>
>> 8-0
>>
>> John W. Colby
>> www.ColbyConsulting.com
>
> -- 
> 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