equential alphanumeric order numbers (1 Viewer)

donsi

Registered User.
Local time
Today, 14:57
Joined
Sep 1, 2016
Messages
73
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.


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:

isladogs

MVP / VIP
Local time
Today, 21:57
Joined
Jan 14, 2017
Messages
18,186
Sorry to be critical, but there are several issues with this code:

1. The initial section can be simplified using Nz statements
2. The following line is incorrect:
Code:
If (Me.CboAmount = 0) Then
3. If err 3022 occurs , I think it will keep creating new records in a loop
4. Error handling should normally go at the end of the routine
5. The print section seems over complex (though a separate issue)

Addressing points 1-4, try this:

Code:
Private Sub BtnSubmit_Click()

On Error GoTo Err_Handler

' checking all fields are filled

 If Nz(Me.Shift,"") = "" Then
        MsgBox "Please select the Shift.", vbOKOnly, "Required Data"
            Me.Shift.SetFocus
        Exit Sub
 End If

 If Nz(Me.Location,"") = "" Then
        MsgBox "Please select the Location.", vbOKOnly, "Required Data"
            Me.Location.SetFocus
        Exit Sub
 End If

 If Nz(Me.CboAmount,0) = 0 Then
        MsgBox "Please select the Amount.", vbOKOnly, "Required Data"
            Me.CboAmount.SetFocus
        Exit Sub
 End If

 If Nz((Me.CboNGEmp,"") = "" Then
        MsgBox "Please enter employee's Lic# .", vbOKOnly, "Required Data"
            Me.CboNGEmp.SetFocus
        Exit Sub
 End If

 If Nz(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

'=================================
'This section seems over complicated
'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
'=================================
Exit_Handler:
    Exit Sub

Err_Handler:   
    'error handling if form number is already used

If Err.Number = 3022 Then
    Me.TxtFormNo.Value = Nz(DMax("[IssFormNo]", "TblTransactions"), 0) + 1
    DoCmd.RunCommand acCmdSaveRecord
    'Resume Next <== NO - this may cause a repeated loop?
End If
    
   GoTo Exit_Handler <==replacement

End Sub

I expect this can be improved further, but see if that fixes the problem you described
 

MarkK

bit cruncher
Local time
Today, 14:57
Joined
Mar 17, 2004
Messages
8,178
DMax() + 1 does not guarantee uniqueness. Two users might calculate DMax() + 1 at the same time, get the same result, and then save them as dupes. The mistake is not in the code so much as in the table, which should enforce uniqueness on the field, in which case the second user's attempt to save the dupe would have failed.
hth
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:57
Joined
Feb 28, 2001
Messages
27,001
Normally that DMax should do the trick. However, your error code is suspicious. Normally when you have an On Error GoTo situation, it jumps completely out of the line and does a Resume to a label. In this case, I could see a problem if you get error 3022 (which I presume is "Duplicate Key"), take the trap, and attempt to update the record number with another DMax+1. However, the first acSaveRecord didn't work (because you got a trap, didn't you?) so the DMax inside the trap will come up with the same answer as the DMax outside the trap.

Further, I'm speculating that there might even be a flow problem. What happens if you take a trap at the first acSaveRecord and the code does a Resume Next afterwards? I wonder if you go through that code a second time NOT as a trap but because it is in-line as the "Next" statement that would be chosen by the Resume Next. I'm not exactly sure when the Err object resets so I don't know if testing Err.Number will get the same number in-line that it got as a trap. I would expect it at least to be possible that you would see an error about "Resume outside of trap" or something similar.

I'm not saying your code is the culprit for the duplicated key, but it might obfuscate your ability to find the true problem.
 

donsi

Registered User.
Local time
Today, 14:57
Joined
Sep 1, 2016
Messages
73
Apologies for reviving the old thread and never able to respond. I was transferred to different location and many other accounting projects was assigned to me so never got back to my pet project. Now I am back and trying to resolve this. I applied your code, but it now doesn't produce the paper receipt at the end of the transaction for the user who encountered the error. I believe, it is due to "Exit_Handler" ending the sub, so it never runs the code to print the job. As far as complicated printout code, I was trying to give an option to the use to reprint if any printer malfunction arises. Maybe it is not the best way to do it, but with my skills, that is all I was able to do. Any suggestion?


Sorry to be critical, but there are several issues with this code:

1. The initial section can be simplified using Nz statements
2. The following line is incorrect:
Code:
If (Me.CboAmount = 0) Then
3. If err 3022 occurs , I think it will keep creating new records in a loop
4. Error handling should normally go at the end of the routine
5. The print section seems over complex (though a separate issue)

Addressing points 1-4, try this:

Code:
Private Sub BtnSubmit_Click()

On Error GoTo Err_Handler

' checking all fields are filled

 If Nz(Me.Shift,"") = "" Then
        MsgBox "Please select the Shift.", vbOKOnly, "Required Data"
            Me.Shift.SetFocus
        Exit Sub
 End If

 If Nz(Me.Location,"") = "" Then
        MsgBox "Please select the Location.", vbOKOnly, "Required Data"
            Me.Location.SetFocus
        Exit Sub
 End If

 If Nz(Me.CboAmount,0) = 0 Then
        MsgBox "Please select the Amount.", vbOKOnly, "Required Data"
            Me.CboAmount.SetFocus
        Exit Sub
 End If

 If Nz((Me.CboNGEmp,"") = "" Then
        MsgBox "Please enter employee's Lic# .", vbOKOnly, "Required Data"
            Me.CboNGEmp.SetFocus
        Exit Sub
 End If

 If Nz(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

'=================================
'This section seems over complicated
'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
'=================================
Exit_Handler:
    Exit Sub

Err_Handler:   
    'error handling if form number is already used

If Err.Number = 3022 Then
    Me.TxtFormNo.Value = Nz(DMax("[IssFormNo]", "TblTransactions"), 0) + 1
    DoCmd.RunCommand acCmdSaveRecord
    'Resume Next <== NO - this may cause a repeated loop?
End If
    
   GoTo Exit_Handler <==replacement

End Sub

I expect this can be improved further, but see if that fixes the problem you described
 

donsi

Registered User.
Local time
Today, 14:57
Joined
Sep 1, 2016
Messages
73
DMax() + 1 does not guarantee uniqueness. Two users might calculate DMax() + 1 at the same time, get the same result, and then save them as dupes. The mistake is not in the code so much as in the table, which should enforce uniqueness on the field, in which case the second user's attempt to save the dupe would have failed.
hth

Field is set to have unique record thus the error when duplicate number is assigned.
 

MarkK

bit cruncher
Local time
Today, 14:57
Joined
Mar 17, 2004
Messages
8,178
So handle the error, re-run the DMax() + 1 operation, get a new--unique this time--ID, and re-run the save. :)
Mark
 

donsi

Registered User.
Local time
Today, 14:57
Joined
Sep 1, 2016
Messages
73
Normally that DMax should do the trick. However, your error code is suspicious. Normally when you have an On Error GoTo situation, it jumps completely out of the line and does a Resume to a label. In this case, I could see a problem if you get error 3022 (which I presume is "Duplicate Key"), take the trap, and attempt to update the record number with another DMax+1. However, the first acSaveRecord didn't work (because you got a trap, didn't you?) so the DMax inside the trap will come up with the same answer as the DMax outside the trap.

Further, I'm speculating that there might even be a flow problem. What happens if you take a trap at the first acSaveRecord and the code does a Resume Next afterwards? I wonder if you go through that code a second time NOT as a trap but because it is in-line as the "Next" statement that would be chosen by the Resume Next. I'm not exactly sure when the Err object resets so I don't know if testing Err.Number will get the same number in-line that it got as a trap. I would expect it at least to be possible that you would see an error about "Resume outside of trap" or something similar.

I'm not saying your code is the culprit for the duplicated key, but it might obfuscate your ability to find the true problem.


The_Doc_Man, you are right about getting trap for a duplicate Key. I thought putting a trap right after error handling would catch it first before running the rest of the code, and "Resume Next" will resume the running code next in line after the trap (which is a Printout).
 

donsi

Registered User.
Local time
Today, 14:57
Joined
Sep 1, 2016
Messages
73
So handle the error, re-run the DMax() + 1 operation, get a new--unique this time--ID, and re-run the save. :)
Mark

That's what I am trying to accomplish by error handler "Problem". But somehow, either its not saving the record correctly or it's duplicating the record.
 

isladogs

MVP / VIP
Local time
Today, 21:57
Joined
Jan 14, 2017
Messages
18,186
Apologies for reviving the old thread and never able to respond. I was transferred to different location and many other accounting projects was assigned to me so never got back to my pet project. Now I am back and trying to resolve this. I applied your code, but it now doesn't produce the paper receipt at the end of the transaction for the user who encountered the error. I believe, it is due to "Exit_Handler" ending the sub, so it never runs the code to print the job. As far as complicated printout code, I was trying to give an option to the use to reprint if any printer malfunction arises. Maybe it is not the best way to do it, but with my skills, that is all I was able to do. Any suggestion?

Thanks for the speedy reply. ... :)
Good to have you back.
What happened to this over the past year whilst you were away?

I haven't read this again in detail but will do so later.
The Exit_Handler code is correct.
Basically if there are no errors the code reaches that point and exits the sub 'gracefully'
However if code errors occur then the Err_Handler section kicks in first
Suggest you clarify what the complex print section is trying to do and i'll look for an easier way of doing it

BUT in the meantime you have other issues to work on that my colleagues have picked up.
 

donsi

Registered User.
Local time
Today, 14:57
Joined
Sep 1, 2016
Messages
73
Thanks for the speedy reply. ... :)
Good to have you back.
What happened to this over the past year whilst you were away?

I haven't read this again in detail but will do so later.
The Exit_Handler code is correct.
Basically if there are no errors the code reaches that point and exits the sub 'gracefully'
However if code errors occur then the Err_Handler section kicks in first
Suggest you clarify what the complex print section is trying to do and i'll look for an easier way of doing it

BUT in the meantime you have other issues to work on that my colleagues have picked up.

Since last year everything went back to manual recording with manual sequential forms and logs.
So I'm still trying to follow and learn VBA. So would I enter GoTo Exit_Handler in the If statement or outside as it is displayed on your code.
Print is noting but a receipt that prints at the end of the transaction for the issuer and receiver to sign. I just want to ensure that receipt is printed and user acknowledges by clicking YES. If it hasn't printed for any reason, user will click NO to reprint the receipt. While loop runs until user clicks YES for "Good Printout". Hope this helps
 

isladogs

MVP / VIP
Local time
Today, 21:57
Joined
Jan 14, 2017
Messages
18,186
The code below shows standard error handling in use on a button
It goes where I showed you - at the end

Code:
Private Sub cmdBackup_Click()

On Error GoTo Err_Handler

  'all code goes here 

Exit_Handler:
    Exit Sub

Err_Handler:
  'optional line to treat certain errors differently
  '94=invalid use of null, 7874 = table doesn't exist
   If Err = 94 Or Err = 7874 Then Resume Next 
   'otherwise show a message
    MsgBox "Error " & Err.Number & " in cmdBackup_Click procedure: " & Err.Description
    Resume Exit_Handler

End Sub

Not sure its a good idea to loop around repeatedly printing a report.
If it fails once, why should it work the next time or a third time?

However if you want to do it, here's a simpler way:

Code:
PrintReport:
	DoCmd.OpenReport "RptBankFrm", acViewNormal, "", "[COLOR="DarkRed"][B][IssFormNo]=[Forms]![NavigationForm]![NavigationSubform]![IssFormNo][/B][/COLOR]", acNormal
    
      'option to reprint document if didn't print
       Dim LResponse As String
       LResponse = MsgBox("Did you receive a good printout?", vbYesNo, "Print Out")
       If LResponse = vbNo Then GoTo PrintReport

I've highlighted the filter criteria in RED. Does it work?
Navigation forms & subforms have their own rules and can be tricky to work with.
I never use them - hence the question
 

donsi

Registered User.
Local time
Today, 14:57
Joined
Sep 1, 2016
Messages
73
Ok I think I understood the logic now. Few reasons to have loop, there one to have user foolproof. Anyways, just to make sure, PrintReort will go between "Exit_Handler" and " Exit Sub", correct??

Where criteria works just fine. That took a long time to figure out, but it is perfectly working.

Thanks for all the help
 

isladogs

MVP / VIP
Local time
Today, 21:57
Joined
Jan 14, 2017
Messages
18,186
No
Place the PrintReport code above Exit Handler
It is used in place of the section between 2 sets of broken lines =========
 

donsi

Registered User.
Local time
Today, 14:57
Joined
Sep 1, 2016
Messages
73
Ok. So the flow would be

Code ran without error (number generated )=> Form Printed => Exit
Code ran => error => error handler ( number generated) =>Exit Handler => Exit
On a second scenario, will it go to PrintReopt or would it skip?
 

isladogs

MVP / VIP
Local time
Today, 21:57
Joined
Jan 14, 2017
Messages
18,186
If a code error occurs, it would jump to the error handler, show a message and exit. ALL code after the error would be skipped.

With no error handler, it would just crash if a code error occurs.

The error handler helps the developer identify and fix the error.

To test what happens, add this line somewhere in your code
Code:
Err.raise 94

This will trigger error 94 and a message will be shown

Make sure you remove it afterwards!
 
Last edited:

donsi

Registered User.
Local time
Today, 14:57
Joined
Sep 1, 2016
Messages
73
This is where I was getting confused. I was under impression that with err handler, you catch the error, fix it, then resume with the rest of the code. Thanks for clarifying.
Now the question is, how do I permanently fix the error for duplicate form number so it won't need to have an handler.
This DB will be used by multiple users throughout the day, and there might be several occasions when two users may click Submit button at the same time.
 

Mark_

Longboard on the internet
Local time
Today, 14:57
Joined
Sep 12, 2017
Messages
2,111
To simplify your work and to make your code easier to maintain, I would suggest breaking it down into pieces that are easy to work with.

I would make the following functions/subs to do the actual work;
Function Val_Print ‘Function that has all of your field checking. Each of your checks that now exits would simply have this return “FALSE”, otherwise it returns “TRUE”
Sub Assign_Number ‘Sub that assigns the number, saves the record and does error checking
Sub Print_Form ‘Code to actually print the form and prompt if they need to reprint.

Each of these would be the existing piece of code you have move to the function or sub as appropriate. Your commented '================================= looks like you were already trying to get to this result.

This would allow you to concentrate on each piece without having the other code get in the way. Your code for your button would then look like
Code:
IF NOT Val_Print then exit sub	‘If not ready to print, exit out.
Assign_Number
Print_Form
Often by breaking larger pieces of code down into smaller pieces it becomes not only more manageable but also easier to troubleshoot.
 

Users who are viewing this thread

Top Bottom