Field locking (1 Viewer)

[temacc]

Hi,

I have a database being used by multiple users which was working fine. Recently two users entered new records into the database at the same time, the data saved however the code generates a license # and both users happened to get the same license number. Is there a way to lock a field/table until one user has gotten the last license number and added to it so the next record entered will get the next sequential number??

Thanks

 

[cogent1]

Read Help on Pessimistic Locking

 

[temacc]

would you have any code examples of the pessimistic locking for a field?

 

[cogent1]

No code is required. The Record Locks property of the Form determines how records are handled. Go into Design View of your form, select Properties, highlight the Records Lock item and press F1 for help on setting the correct choice.

 

[Pat Hartman]

I take it that license is a user generated number. If it must be unique, then a unique index should be defined for it. Your sequence number generator needs to take into account the possibility that two users are simultaneously trying to create a new license and deal with it. If you were using autonumbers, you would not have this problem.

 

[temacc]

I'm not so much concerned with record locks, rather with locking a field! These are new records being entered so the data is fine, however when the user clicks a picklist to approve a license, code is triggered to get the max number in the database and add to it to produce a new license number but if too people are doing this at the same time and don't save immediatley after they get their new number then they'll end up with the same license number.

Any ideas how to keep the license numbers unique?

thanks

 

[Pat Hartman]

You confuse yourself and waste our time by posting the same question multiple times. Stick with a single thread until you have resolved your issue.

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=34908

What I will add to my response in the other thread is that you should NOT assign the license number until you are ready to save. This will minimize the potential for conflicts.

I also repeat my statment that Access handles all of the multi-user issues for you when you use autonumbers. What you give up is the ability to guarantee no gaps in the license number sequence. Since the license number's purpose is to provide a unique ID, it should be defined as the primary key.