help with auto number - new thread (1 Viewer)

Started a new thread: Rebuilt database and still need help with auto number for multi user form. The code I have works for single user but not multi user. Also if someone opens the form and closes without adding anything to the form the number is used with blank record.

This is what I have thus far: [FONT=&quot] [/FONT]
[FONT=&quot]Private Sub Form_Current()[/FONT] [FONT=&quot]Me.[PO] = Nz(DMax("[PO]", "tblPurchaseOrder"), 0) + 1[/FONT]
[FONT=&quot]End Sub[/FONT]

[FONT=&quot][/FONT]

gizmogeek, I can see you are trying to use DMax+1, that will work for single user, but if you go to the Demo DB, by RainLover there is also another sample for MultiUser environment.. whihc you might be interested to look into..

I do have that but I can't open it in design view just get the form. I do remember getting instructions from RainLover but deleted the thread.
Thanks!

Hold Shift Key down while you open it.. If that does not work, change some of the privacy settings..

gizmogeek said:

I do remember getting instructions from RainLover but deleted the thread.

Click to expand...

Please, please do not delete any threads, you might think you have it resolved, but in future someone in the exact same situation would find the answers/discussion very useful..

If people start deleting then this forum will prove pointless.. Let moderators be the deciders of the life of a thread on this forum..

Here's the code I used. I'm sure it's an error on my part. RainLover knows what he's doing so it surely has to be me.
Thanks

Private Sub txttblPO_AfterUpdate()

If (conHandleErrors) Then On Error GoTo ErrorHandler

' If we have some data
If Len(Me!txtlblPO) Then

' Find the highest number and increase it by ONE.
Me!tblPO = Nz(DMax("[PO]", "tblPO", "[PO] = '" & Me!txtlblPO & "'"), 0) + 1
Else
MsgBox "A String is Required", vbInformation, "Missing Information"
Screen.PreviousControl.SetFocus
Me.Undo
End If

' Added 03/02/2011 by Rainlover.
Me.txtConcatenated = Me.txttblPO & Me.txtlblPO
Me.Refresh

ExitProcedure:
Exit Sub

ErrorHandler:
DisplayError "txttlbPO_AfterUpdate", Me.Name
Resume ExitProcedure

End Sub


Private Sub Form_Error(ByRef intDataErr As Integer, ByRef intResponse As Integer)

' Designed by ChrisO.

' If in a multiuser environment it is quite possible for more that one
' machine to attempt to write the same value which will cause a Form Error.
' Handle the error and exit the Sub.

' Is it a duplicate index value error?
If intDataErr = 3022 Then
' Ignore the data error.
intResponse = acDataErrContinue

' Set the timer handler and interval.
Me.OnTimer = "=BumpTheNumber()"
Me.TimerInterval = 1
End If

End Sub


Private Function BumpTheNumber()

' Designed by ChrisO.

' This Error trap is required, do not turn it off.
On Error GoTo ErrorHandler

' Cancell the timer until next required.
Me.TimerInterval = 0

' Create a VBA error and stay in loop until fixed.
Me.Dirty = False

ExitProcedure:
Exit Function

ErrorHandler:
' Bump the number and resume to try again.
Me!PO = Me!PO + 1
Resume

End Function


Private Sub CmdNew_Click()

If (conHandleErrors) Then On Error GoTo ErrorHandler

DoCmd.GoToRecord , , acNewRec

ExitProcedure:
Exit Sub

ErrorHandler:
DisplayError "CmdNew_Click", Me.Name
Resume ExitProcedure

End Sub

I've worked on this all day and just keep coming back with what I posted above. Any insight?