Record locking problem

[Spelthorne1]

I have a table with a yes/no value for Bob, Sue and Geoff (as these are the only users for the db.
If Bob enters data i want his column in the table to say yes (-1).

So to automate this process i have them find their name on a list box on a form, which then loads up the data entry form i created and places their name in a text box on the form ([userid]).

I have 3 chkboxes one for each name with the control source linked to the appropriate column. On the afterinsert() I have:

Select Case userid
Case "Bob": chkbob = -1
Case "Sue": chksue = -1
Case "Geoff": chgef = -1
End Select

But when Bob trys to enter a new record the debugger appears with the chkbob = -1 statement above highlighted in yellow. The only way he can continue is if Geoff closes his form down and reopens it.

This problem is managable but gets irritating .
Does anyone know how i can get round this? Ive used If statements and combo boxes but to no avail. If all else fails i will remove the automation, but i would like a solution somehow. Thanks.

 

[ghudson]

I assume that the db is split and the users are not sharing the same front end. If not, then that is part of your problem.

I also suggest that you have only one field in your table to store who modified each record. Normalization is a must and you are breaking it by creating a field for each user in your table just to store which records they are modifying.

You should use the forms BeforeUpdate event to update the "User" field with the user name and also a date and time stamp.

 

[Pat Hartman]

You should use the forms AfterUpdate event to update the "User" field with the user name and also a date and time stamp.

-NO, NO, NO!!! You need to use the form's BEFOREUPDATE event. You want to add the changeby and date BEFORE the current record is saved. If you do it after, you simply dirty the record again and force Access to re-save it. This effectively puts the form into a loop because each time Access saves the form, you dirty it again.

 

[ghudson]

My mistake! You definitely want to make any record changes before the record is saved. I modified my original post above to correct my error.

 

[Spelthorne1]

Thanks for the prompt replies.
I didnt realise when we first created the database i should design it with the future in mind. - ie more users and larger data.
The db is not spilt as of yet, but that will be looked into in due course. - i wasnt aware that it would be this causeing the problem!!
Your replies are all greatfully received.

The users are now all in one column and with just a slight alteration of the code i should have it working again tomorrow.