Auto Saving a Record in VBA

[Duncs]

I have a database that is used by several people. As part of the
operation, three fields are set when the form opens:

Date modified - Today's Date
User ID - Network logon of the user
Status - Initially set to "In Progress"

When the user completes their work, they click a button which sets the
Satus to "Completed" and then closes the form, which in turn saves the
updated record. So, what's my problem?

The query behind the form gets all records where the Status field is
null. If three people access the form, it presents them with the same
record. So, I wanted to save the record so that the Status would be
set to "In Progress", and no one else would be able to work it, as the
query looks for the Status field being null. However, I can't work out
how to save the record from within the Form Open event.

What am I doing wrong? Is there an easier way to do this?

TIA for your help.

Duncs

 

[John W. Vinson]

What am I doing wrong? Is there an easier way to do this?

One thing you're CERTAINLY doing wrong is having three people on a network log
in to the same unitary database. You'll have much better luck if you "split"
the database into a frontend (containing forms, reports, queries and code) and
a backend (containing only the tables). Each user will get their own copy of
the frontend, linked to the tables in the backend. The database splitter
wizard under Tools... Database Utilities will set this up for you.

To prevent contention for the same record, you may want to set the status to
"In Progress" and immediately save the record to disk in the Form's
BeforeInsert event. This assumes that there are no other required fields
(which would prevent saving the record unless they are also assigned a value).

 

[tcarter06]

I have a database that is used by several people. As part of the
operation, three fields are set when the form opens:

Date modified - Today's Date
User ID - Network logon of the user
Status - Initially set to "In Progress"

When the user completes their work, they click a button which sets the
Satus to "Completed" and then closes the form, which in turn saves the
updated record. So, what's my problem?

The query behind the form gets all records where the Status field is
null. If three people access the form, it presents them with the same
record. So, I wanted to save the record so that the Status would be
set to "In Progress", and no one else would be able to work it, as the
query looks for the Status field being null. However, I can't work out
how to save the record from within the Form Open event.

What am I doing wrong? Is there an easier way to do this?

TIA for your help.

Duncs