Full Version: Prevent duplicate reference number
UtterAccess Forums > Microsoft® Access > Access Forms
Hi all,
I've developed a system which the users are using to keep track of tasks. Every task that comes in gets loaded onto the system and a reference number is automatically assigned when they start entering the task.
Then, as soon as the user navigates to another record or hits "Save Record", the reference number is checked against the max in the system, and if the reference has been taken, the user is prompted, and the next available reference is allocated against that task.
This seems to work fine most of the time, however I've been advised that there are a few instances where we're getting duplicate numbers.
Here's the code that checks - can someone take a look and let me know where I'm going wrong? The code is on the Before_Update event of the form (and also on a "save record" button) - perhaps this is the wrong place?
Many thanks in advance,
Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.NewRecord And Me.Dirty Then
        'Changes to make
        If MsgBox("Do you want to save this job?", vbYesNo + vbQuestion, "Save Job Details?") = vbYes Then
            'First, we need to find out what the job number should be...  we add this in because the job number is "stored" in the form, but if there
            'is a delay of entering the form details in for any reason, it may have been taken
            Dim lngJobNo As Long, strNextJobNo As String
            lngJobNo = CLng(Right(tMax("JobNo", "tblJobs"), 5))
            strNextJobNo = strJobStart & Format(lngJobNo + 1, "00000")
            'Check to see if the current job no is still the highest one available
            If lngJobNo > CLng(Right(Me.JOBNO, 5)) Then
                'No, it's not
                If MsgBox("Sorry, the job number entered has been used since you started recording this job" & vbCrLf & _
                            "The next available Job Number is " & strNextJobNo & ", do you want to use this?", vbYesNo + vbQuestion, "Change Job Number?") = vbYes Then
                    'We've got the next available job number already, so get that.
                    Me.JOBNO = strNextJobNo
                    'Customer doesn't want to use the new job number, so return focus back to the form
                    'we cannot overwrite the job number if one already exists!!
                    Exit Sub
                End If
            End If
            'All ok, save the details
            'DoCmd.RunCommand acCmdSaveRecord
            Cancel = True
            DoCmd.RunCommand acCmdUndo
        End If
    End If
End Sub
Have you checked out this example in the code archive. It demonstrate the method you are using and has a confirmation routine on the form's before update.
There is also another method demonstrated which may be more suited to your needs.
Thanks William - your code was very similar to mine in function, but I made a couple of minor adjustments to my version based on yours. Will see whether it's worked during the next few days - cheers!

Edited by: adaytay on Thu Sep 4 9:32:34 EDT 2008.
Glad I could help.
You may want to look into moving to the counter method. I think it may work better for you.
I did think about that initially to be fair, as that's the method I've adopted in the past. However, I'll see how we get on with this. It's a relatively small system with only 6 users adding stuff concurrently.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.