Duplicating Sequential numeric form numbers
Hello everyone,
I created DB for item check in/out. Everytime item is issued, it prints the form with sequential form #. I couldn't use the Autonumber as it skips the number so used DMax to assign next number. It worked great until few days ago. Now we have over 20K records, access somehow creating a new record with the same form number. How is it possible? Did I make mistake anywhere in the code(see below)? I new to both DB and VBA. Please help.
Hello everyone,
I created DB for item check in/out. Everytime item is issued, it prints the form with sequential form #. I couldn't use the Autonumber as it skips the number so used DMax to assign next number. It worked great until few days ago. Now we have over 20K records, access somehow creating a new record with the same form number. How is it possible? Did I make mistake anywhere in the code(see below)? I new to both DB and VBA. Please help.
Code:
Private Sub BtnSubmit_Click()
On Error GoTo Problem
' chekcing all fields are filled
If IsNull(Me.Shift) Or (Me.Shift = "") Then
MsgBox "Please select the Shift.", vbOKOnly, "Required Data"
Me.Shift.SetFocus
Exit Sub
End If
If IsNull(Me.Location) Or (Me.Location = "") Then
MsgBox "Please select the Location.", vbOKOnly, "Required Data"
Me.Location.SetFocus
Exit Sub
End If
If (Me.CboAmount = 0) Then
MsgBox "Please select the Amount.", vbOKOnly, "Required Data"
Me.CboAmount.SetFocus
Exit Sub
End If
If IsNull(Me.CboNGEmp) Or (Me.CboNGEmp = "") Then
MsgBox "Please enter employee's Lic# .", vbOKOnly, "Required Data"
Me.CboNGEmp.SetFocus
Exit Sub
End If
If IsNull(Me.CboCOEmp) Or (Me.CboCOEmp = "") Then
MsgBox "Please enter Cashier's Lic#", vbOKOnly, "Required Data"
Me.CboCOEmp.SetFocus
Exit Sub
End If
'Assigns a form number
Me.TxtFormNo.Value = Nz(DMax("[IssFormNo]", "TblTransactions"), 0) + 1
DoCmd.RunCommand acCmdSaveRecord
'error handling if form number is already used
Problem:
If Err.Number = 3022 Then
Me.TxtFormNo.Value = Nz(DMax("[IssFormNo]", "TblTransactions"), 0) + 1
DoCmd.RunCommand acCmdSaveRecord
Resume Next
End If
'Printing a form Issue
DoCmd.OpenReport "RptBankFrm", acViewNormal, "", "[IssFormNo]=[Forms]![NavigationForm]![NavigationSubform]![IssFormNo]", acNormal
'YES?NO for printout options to reprint document if didn't print
Dim LResponse As Integer
LResponse = MsgBox("Did you receive a good printout?", vbYesNo, "Print Out")
While (LResponse = vbNo)
DoCmd.OpenReport "RptBankFrm", acViewNormal, "", "[IssFormNo]=[Forms]![NavigationForm]![NavigationSubform]![IssFormNo]", acNormal
LResponse = MsgBox("Did you receive a good printout?", vbYesNo, "Print Out")
Wend
'Going to new record to issue another bank.
DoCmd.RunCommand acCmdRecordsGoToNew
Call EnableTab
End Sub
Last edited: