[AccessD] VBA Unbound data entry / update form

jwcolby jwcolby at colbyconsulting.com
Tue May 27 16:54:06 CDT 2008


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




More information about the AccessD mailing list