[AccessD] VBA Unbound data entry / update form

Susan,

 > John, are you asking how to use disconnected recordsets

No, I am trying to discover how to use unbound forms for 
data entry editing.  It is a similar but not identical thing.

For example if you create a new record in an unbound form, 
there is no recordset to be disconnected.

If you edit an existing record, then yes, kind of.  You 
would (I assume) open a record, grab the field values, old 
values, names and such, and then close the recordset.  That 
assumes a DAO recordset.  You would disconnect the recordset 
(I assume) if you tried to do this in ADO.

 >or are you trying to talk yourself into writing a class 
instead?

No, I am trying to discover the best way to emulate the 
functionality of a bound form.  I do assume that this will 
involve custom written classes, but never having done this I 
am not certain of that.

I have actually thought of taking a bound form, opening it 
to a record (bound forms use DAO recordsets, at least back 
in 2K and previous which this client still uses), then 
grabbing all the field values and "disconnecting" the form 
(and controls).  This would give me a snapshot of the data 
as it existed as the form opened, and a method of 
determining if the data changed.

And no, I do not have a TechRepublic membership.

John W. Colby
www.ColbyConsulting.com


Susan Harkins wrote:
> 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