Multi user problem

[Jan Ten]

I have created a multi user access database across 3 computers with a back end and front end.
My problem is that if 1 user is creating a new record at the same time as another user 1 of them will lose their invoice no.
I believe there is a way of locking records while they are being worked on.
How do I approach this problem of 2 users not being able to create a new record at the same time?
Thanks in advance.

[pbaldy]

I assume you're creating a custom invoice number? And creating it early on in the process? One solution is to get that number right at the end, as the record is saved (before update event of the form). Another is to save the incomplete record immediately after generating the number, but allow the user to continue entering. Either reduces your chance of a clash to milliseconds. The second is vulnerable to the user changing their mind. There are more robust solutions that are a little more complicated to implement.

[Jan Ten]

Paul.
I have already the save function a soon as the invoice No has been created and it doesn't appear to fix this problem.
I would be inserted in more complicated one you mentioned.
I am conversant with coding and use vba, if that helps.
Kind Regards


Jan T Queensland Australia

[Micron]

Paul, excuse me for chiming in!

I have already the save function a soon as the invoice No has been created

That wasn't the suggestion. The suggestion was to create the number at the END of the process. This is usually the best method. If it cannot be so, (you might need the number to create child records on a sub form) then enforce a save as soon as the record is started while still allowing further data entry/edits. If this also cannot be, say because a user might change their mind and cancel and you cannot allow gaps in the numbers, then trap the error when you are saving the record. What you should do then I can't say because we don't know enough about the process. That is, do you update the number to the next available one?

One of the more complicated methods inferred might have been to create a temporary value (because child records are required) in temp tables (1 temp table for the main record, 1 for the child records) then update these to the actual value (invoice number) at the time of records saving or form closing and writing the real value to the proper tables. by now, you can probably see why creating the number at the last possible moment is the best method.

Does each user have their own front end, or do you have only one that is being shared?

[Jan Ten]

each user has their own front end and the back end is on the server

[Micron]

OK. Otherwise, I would have wanted to add a cautionary note about using temp tables in a single shared FE. Most people don't design that way, but if you had, you need a way to deal with one user's temp records without affecting everyone else's.

[pbaldy]

I have already the save function a soon as the invoice No has been created and it doesn't appear to fix this problem.

I'd be curious what that code looks like, as if you get the new invoice number and immediately save the record, clashes should be very minimal. Another option discussed here, using a table:

http://www.baldyweb.com/CustomAutonumber.htm

But I've never needed to do it. I've got a commercial reservation program that does.