Incress a PO number by one in a multiuser DB

  • Thread starter Thread starter Gary.
  • Start date Start date
G

Gary.

I am setting up a purchase order form using the code
PO_Number = Nz(DMax("Po_Number", "tblPONum")) +1
I am using the code in the BeforeInsert of the Form

I have heard that this is a problem in a multiuser database
What would be a better option in a multiuser situation
 
Use an AutoNumber instead of a number.
Access will give you a unique number (not necessarily sequential.)

If you can't do that, put this code into the BeforeUpdate event of the
*form*. This is the last event to run before the record is saved, so reduces
the chance of 2 users being given the same number. After any other
code/message boxes you need to run in Form_BeforeUpdate:
If me.NewRecord Then
Me.PO_Number = Nz(DMax("Po_Number", "tblPONum"), 0) +1
End If

A full-blown solution is considerably more complex. You need another table
just to hold the highest number used so far. In your code, open this table
with an exclusive lock, increment the number, assign it, save the record,
and only then release the exclusive lock. Then add code to give a fixed
number of random pauses and retries if the table is found to be locked when
you attempt the update.
 
There are a number of solutions to that. Roger Carlson has a simple
one at
the link which I gave in my reply to the OP. A slightly more complex
one of
mine which also allows for the next number to be used to be 'seeded'
is at:


http://community.netscape.com/n/pfx/forum.aspx?nav=libraryMessages&ts…


Ken Sheridan
Stafford, England

Hi Ken. Long time no 'speak'.

I'm interested in taking a squint at your code but unfortunately that url
just leads to the netscape forums home page.
 
Stuart,

Here is the code that I use and got from that location when it was
available.

I have a separate mdb with the only table in it being the counter
table.
In this example I happen to keep the location of the table as a data
item in a table so that multiple applications can use this same table.
It was necessary because of the way two applications had to keep the
number in sync.
It loops until the table is available to be opened in case another
user happens to be in it momentarily.

========================
Public Function GetNextTDDNumber() As Long

Dim dbsCounter As DAO.Database, rstCounter As DAO.Recordset
Dim strCounterDb As String
strCounterDb = Forms![HiddenKey]![HKCounterDBDirectory]
Const NOCURRENTRECORD As Integer = 3021
'OpenCounterDb Forms![HiddenKey]![HKCounterDBDirectory]
Dim n As Integer, I As Integer, intInterval As Integer

' make 10 attempts to open external database exclusively
DoCmd.Hourglass True
SysCmd acSysCmdSetStatus, "Attempting to get new number"
On Error Resume Next
For n = 1 To 10
err.Clear
Set dbsCounter = OpenDatabase(strCounterDb, True)
If err = 0 Then
Exit For
Else
intInterval = Int(Rnd(Time()) * 100)
For I = 1 To intInterval
DoEvents
Next I
End If
Next n
SysCmd acSysCmdClearStatus

' If err = 0 Then
' OpenCounterDb = True
' End If
If err <> 0 Then
MsgBox "Cannot open Counter Tables."
GoTo skipcloseTDD
End If

Set rstCounter = dbsCounter.OpenRecordset("TDDCounter")

On Error Resume Next
With rstCounter
.Edit
' insert new row if table is empty
If err = NOCURRENTRECORD Then
.AddNew
!NextNumber = 1
.update
GetNextTDDNumber = 1
Else
' update row and get next number in sequence
!NextNumber = !NextNumber + 1
.update
GetNextTDDNumber = rstCounter!NextNumber
End If
End With
rstCounter.Close
dbsCounter.Close

Set rstCounter = Nothing
Set dbsCounter = Nothing

skipcloseTDD:
DoCmd.Hourglass False
End Function
========================

Ron
 
Back
Top