Sharing PO number on network

[Kyle]

My database require user to enter next available PO number to create a
record. If I put my database on the network, how the MS Access grand which
user get the next PO number first.
For instant, user 1 open the database and see the next number S-he can use
is 1900. User 2 is happen to access the database at the same time and see
the next PO number is 1900. Which user will get the PO number 1900. What
error message the other user will see if S-he not get the number 1900.

Please help or explain how the MS access work when use on line with
multipler user access to same database and try to assign next available PO
number.

Thanks in advance.

 

[Allen Browne]

If you just want a unique number (not necessarily sequential order numbers),
use an AutoNumber field. Access will assign a number as soon as you start an
entry. If someone else starts another entry, they get the next number. If
anyone aborts an entry, that number is not reused (which is why you don't
get a completel sequence.)

If you want a sequence, the simplest thing is to assign the number at the
last possible moment. The BeforeUpdate event procedure of the *form* is the
last event fired before the record is saved, so use this to find the DMax(),
and add 1. Since this is only a millisecond before the record is saved, the
chance of 2 users being given the same number is minimized. If it does
occur, the 2nd one won't be able to save their record (assuming this is the
primary key field), but when they try again they will probably succeed.

There are more complex ways of coding special tables that you lock until the
number is assigned and the record saved, with random timeouts and limited
retries.

 

[Kyle]

I want the PO number in sequence, no gap. The PO number is a primary key. I
am yet try on the network.

I am not very clear of this statement, don't mind clarify "The BeforeUpdate
event procedure of the *form* is the last event fired before the record is
saved, so use this to find the DMax(), and add 1.

Is there a way the MS Access will prompt 2nd user if the PO number is in use
by 1st user?

 

[Allen Browne]

The idea is this:

Private Sub Form_BeforeUpate(Cancel As Integer)
If Me.NewRecord Then
Me.POID = Nz(DMax("POID", "POtable"),0) + 1
End If
End Sub

Since this happens at the last possible instant before the record is saved,
the chance of a duplicte is small.

You will get an error message if it fails, and then you can try to save
again. The second time, it gives a new number, and you would be very unlucky
to hit the same problem with another user saving at the same milliseond a
2nd time.

If you will have scores of users banging in new records all the time, you do
need a more comprehensive solution.