Separate SELECT and UPDATE queries

[burnsj]

Hello all, and thanks in advance for your time and advice!

Issue: Forms bounded to SELECT queries are placing the record in edit mode when a user makes a change, and no UPDATE query is required to update the data in the table. I would like to separate the processes and remain disconnected from the table except when these two queries are run. Is there any way to simply populate a form with data from a SELECT query, then run an UPDATE query after the form is updated?

Background: I am developing an Access 2003 database on Windows XP that is used by multiple users to track test parts. We have the occassional issue that two users update one part simultaneously. I would like to stay disconnected from the tables as much as possible to avoid problems. My plan was to populate a form using a SELECT query, allow the user to update the information on that form, then run some code to compare old values versus new values, and run an UPDATE query to make the needed changes.

Thanks! Let me know if you need more information and I'll try to post it.
Joe

 

[DCrake]

To do what you are suggesting means that you need to use an unbound form whereby the fields are not physically bound to a field in an underlying query or table. Then you give the user a button to Add/Edit/Delete and in response you run an update query or use recordsets to save the changes back to the table.

David

 

[Guus2005]

Hello all, and thanks in advance for your time and advice!

Issue: Forms bounded to SELECT queries are placing the record in edit mode when a user makes a change, and no UPDATE query is required to update the data in the table. I would like to separate the processes and remain disconnected from the table except when these two queries are run. Is there any way to simply populate a form with data from a SELECT query, then run an UPDATE query after the form is updated?

This can easily be accomplished when using unbound forms and an extra field in your tables.
Field: LastUpdated - date :to store when the record was last updated.

To populate form:
You start disconnected
Retrieve the information using a select statement
Store the information in a recordset.
Use the recodset to populate the controls on your form.
Put the recently added field in a hidden field ie me.txtLastUpdated
Keep the recordset for future reference (global variable)
Now your disconnected again.

To update:
To determine if a value was changed compare the recordset to the control values. ie

if me.txtName <> rst!Name then 'Value was changed.
    strsql = strsql & "Name ='" & me.txtName & "'"
end if

Build your update statement

You need the recently added field to determine if the record was changed by someone else while you where busy changing it.

In the end your update statement might look like this.

Update tablename set Name = 'Guus2005', OtherInfo = 'Unknown', Lastupdated = Now() Where Id = 123 and LastCreated = '02/09/2010 14:23:33'

Enjoy!

 

[burnsj]

Thanks for the replies! I'll work on that and let you know what I come up with. I don't have much experience with recordsets, but I'm a quick learner. Thanks again!