Error 3022 Trapping Module Not Working Properly

[rnutts]

Hallo

I have a form which is used to create job sheets, where the control JobNumber is the primary key in tblnapswork
As more than one user can be entering at one time I need to prevent duplicate job numbers being given out by the system. I have a module which should trap the Error 3022 and effectively pause one of the users until the other user has finished running their bit of the code. My problem is that this does not appear to be working.
Below is a portion of the code for the Save button on the form and also the code within the Module.
Save Button Code:-

If Me.JobNumber = 0 Then
Me.inputdate.Value = Date
NextAssistFMJobNo
Me.CreatedByUser = fOSUserName
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

When the save button is clicked the second user gets a job number and then the error 3022 message appears stating that duplicate values will be created etc.
The debugger shows the code has fallen over at the Docmd.Domenuitem line.
This should have been handled by the module code below:-

On Error Resume Next
If Forms![frmnapswork]![txtUKASSelect] = -1 Then
Forms![frmnapswork]![JobNumber] = Nz(DMax("[UKASjobnumber]", "tblUkasjobnumbers"), 0) + 1
Forms![frmnapswork].Refresh
Else
Forms![frmnapswork]![JobNumber] = Nz(DMax("[NUjobnumber]", "tblNUjobnumbers"), 0) + 1
Forms![frmnapswork]!frmNUJobNumberssubform.Form!NUJobNumber = Forms![frmnapswork]!JobNumber
Forms![frmNUJobNumberssubform].Form.Dirty = False
Forms![frmnapswork].Refresh
End If
If Err = 3022 Then
Forms![frmnapswork]![JobNumber] = ""
Forms![frmnapswork].Refresh
NextAssistFMJobNo
End If
End Function

If anyone has any ideas/thoughts then they would be gratefully received.

Thanks

Richard Nuttall

 

[pr2-eugin]

Hi Richard,
This code is wrong in many levels..

* What is NextAssistFMJobNo
* You have 'On Error Resume Next', which means you are asking the error handler to just continue with the next line as if there was no error.
* Your If statement has Err.. but what object of error are you referring to? It should be Err.Number
* Inside your If you should ask it to Resume to somewhere..

To know more information on Error Handling and the proper way to do it.. go here

 

[vbaInet]

... plus DoMenuItem is an old way of doing things. What version of Access are you using?

 

[jdraw]

You may wish to review Allen Browne's thoughts re Error Handling at
http://allenbrowne.com/ser-23a.html