[AccessD] VBA Unbound data entry / update form

Won't work John.
If you have User 1 making changes, you need to ensure that User 2 is not
making simultaneous changes to the same data albeit via a temp table.
Until you do that, nothing else makes any sense.
I would:
1. Put a field on the table "I am locked By UserName".
2. Lock it as soon as record is accessed/edited.
3. Warn other users that records is being edited by UserName if they try to
access it.  Ask them do they want to be notified when record becomes
available.  If so, record PK and User in a temp table or in variable array
and check it via an OnTimer event.
4. You can also put a Time-Out default if UserName left a record Locked when
he/she went off to luch.  Time-out and lock/leave the record thus unlockign
it for others.
5. KIS works for me.
Max
Ps. Also be aware of what you need to do if the record is locked by being
reported on via a Report/update batch run, etc.  Remember record will be
incomplete.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Wednesday, May 28, 2008 11:31 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] VBA Unbound data entry / update form

I am actually thinking about using a temp table in the FE. 
Copy the record to edit to the temp table.  Bind the entire form to the temp
table.  Make the changes.  When done, look up the original record in the
real table, compare data fields from temp to real.  If there are any changes
THEN make the appropriate decisions etc.  This still requires a fair amount
of logic to determine "old values" and the like, but does allow the form to
be bound, can use the data events and such per normal etc.  Any "locks" are
applied to the local Temp table until such time as the final write lock to
get the data back to the live data table.

Once saved back out, delete the data from the temp table in preparation for
the next edit.

John W. Colby
www.ColbyConsulting.com


Gustav Brock wrote:
> Hi John
> 
> Another option is to follow my advice #3 here:
> 
> http://blogs.techrepublic.com.com/10things/?p=331
> 
> Leave your form bound but move the memo field off the current source table
to a separate table with a one-to-one relationship and set your memo TextBox
unbound.
> OnCurrent of the form, read the memo field into the memo TextBox.
> Store the initial content in a variable, say, strMemoLoad.
> 
> OnExit of the memo TextBox, compare the content with strMemoLoad. If
different, save the edited content to the memo field and fill strMemoLoad
again (in case the user will make a later edit.
> Or set a flag at OnChange and save the content to the memo field at
BeforeUpdate or AfterUpdate of the form.
> 
> When adding a new record to the main table, add a new record to the memo
table as well.
> 
> /gustav
> 
> 
>>>> jwcolby at colbyconsulting.com 28-05-2008 00:34 >>>
> 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.
> 
> 
> 
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com