Problem with Dmax Code

[rnutts]

Hi

I have a form which has upto three users entering at the same time using Front Ends on their desktops linked to a back end on a server.
I have a field on the form which is updated using a Dmax piece of code. The number is unique. I am having a problem with the users being given the same number by the table and then getting a violation because of the duplication.
I have tried to enter a save record line straight after the Dmax code but this has not cured the problem.
Could someone look at the code posted below and give me some suggestions.
The code is quite long so I have highlighted the particular DMax line.
The users are activating the code at the same time.

Private Sub txtComments_LostFocus()
On Error GoTo Err_handler_lostfocus

retvalue = MsgBox("Do You Want To Add Another Sample To This Batch", vbYesNo)
If retvalue = vbNo Then
If Me.Our_Sample_Number = 0 Then
Me.Our_Sample_Number = Nz(DMax("[Our Sample Number]", "Sample Details"), 0) + 1
Me.Dirty = False
Me.CreatedBy = fOSUserName
Me.CreatedByDate = Date
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Me.txtComments.LimitToList = False
Me.frmAssistFMSampleEntrySubform.Requery
retvalue = MsgBox("Does The Sample Count Match To Data Collection Sheets?", vbYesNo)
If retvalue = vbNo Then
cmdCloseForm.SetFocus
Else
DoCmd.OpenReport "rptAssistFMLabSheet", acViewPreview
End If
Else
Me.Dirty = False
Me.CreatedBy = fOSUserName
Me.CreatedByDate = Date
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Me.txtComments.LimitToList = False
Me.frmAssistFMSampleEntrySubform.Requery
retvalue = MsgBox("Does The Sample Count Match To Data Collection Sheets?", vbYesNo)
If retvalue = vbNo Then
cmdCloseForm.SetFocus
Else
DoCmd.OpenReport "rptAssistFMLabSheet", acViewPreview
End If
End If
Else
If Me.Our_Sample_Number = 0 Then
Me.Our_Sample_Number = Nz(DMax("[Our Sample Number]", "Sample Details"), 0) + 1
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Me.txtComments.LimitToList = False
Me.CreatedBy = fOSUserName
Me.CreatedByDate = Date
Me.Dirty = False
DoCmd.GoToRecord , , acNewRec
Me.txtSeperator.Locked = False
Me.txtComments.Locked = False
Me.Check42.Locked = False
Me.Clients_Sample_Number.Locked = False
Me.Floor_Level.Locked = False
Me.Room_Location.Locked = False
Me.ItemLocation.Locked = False
Me.Item.Locked = False
Me.Component.Locked = False
Me.PrevSampleNo.Locked = False
Me.DrwgNo.Locked = False
Me.PhotoNo.Locked = False
Me.ProductType.Locked = False
Me.ExtentofDamage.Locked = False
Me.SurfaceTreatment.Locked = False
Me.Identified.Locked = False
Me.QuantityOfMaterial.Locked = False
Me.Action.Locked = False
Me.Access_Position.Locked = False
Me.OccupantNormal.Locked = False
Me.OccupantSecond.Locked = False
Me.LikeliLocation.Locked = False
Me.LikeliAccessibility.Locked = False
Me.LikeliQtyExtent.Locked = False
Me.HEFreqUse.Locked = False
Me.HEOccup.Locked = False
Me.HETimeInUse.Locked = False
Me.MaintenanceType.Locked = False
Me.MaintenanceFrequency.Locked = False
Me.Batch_Number = Forms![frmAssistFMBatchDetails]![Batch Number]
Me.txtSeperator.SetFocus
Else
Me.txtComments.LimitToList = False
Me.CreatedBy = fOSUserName
Me.CreatedByDate = Date
Me.Dirty = False
DoCmd.GoToRecord , , acNewRec
Me.txtSeperator.Locked = False
Me.txtComments.Locked = False
Me.Check42.Locked = False
Me.Clients_Sample_Number.Locked = False
Me.Floor_Level.Locked = False
Me.Room_Location.Locked = False
Me.ItemLocation.Locked = False
Me.Item.Locked = False
Me.Component.Locked = False
Me.PrevSampleNo.Locked = False
Me.DrwgNo.Locked = False
Me.PhotoNo.Locked = False
Me.ProductType.Locked = False
Me.ExtentofDamage.Locked = False
Me.SurfaceTreatment.Locked = False
Me.Identified.Locked = False
Me.QuantityOfMaterial.Locked = False
Me.Action.Locked = False
Me.Access_Position.Locked = False
Me.OccupantNormal.Locked = False
Me.OccupantSecond.Locked = False
Me.LikeliLocation.Locked = False
Me.LikeliAccessibility.Locked = False
Me.LikeliQtyExtent.Locked = False
Me.HEFreqUse.Locked = False
Me.HEOccup.Locked = False
Me.HETimeInUse.Locked = False
Me.MaintenanceType.Locked = False
Me.MaintenanceFrequency.Locked = False
Me.Batch_Number = Forms![frmAssistFMBatchDetails]![Batch Number]
Me.txtSeperator.SetFocus
End If
End If

Exit_handler_lostfocus:
Exit Sub
Err_handler_lostfocus:
MsgBox Err.description
Resume Exit_handler_lostfocus
End Sub

Many Thanks

Richard

 

[DCrake]

Simple Software Solutions

Hear is a alternative approach to what you are trying to achieve.

Create a new table that has one field in it Called FldNextSampleIID as Long
and set it to the next sequential number in the main table. Make the field an autonumber field.

When the user selects Yes to add another sample instead of getting the DMax from the destination table. To do this add a record to the GUID table to get the next ID then delete the previous one. So you will only ever have one record in it.

So if you have 3 or 33 users they will always get a different number from the GUID table. Use this number to create the unique sample number in the main table. If the user does not complete the addition of the new sample it does not matter, you may have "holes" in the sequence but at least you won't have the duplicates issue.

CodeMaster:

 

[Dennisk]

I don't why people keep posting this DMAX solution as it is just wrong. It is quite easy for people to generate the same next ID.

If you require a incremental number that will be exposed to the users, then create a table containing the last number generated and use a function with the correct locking to increment it.

Here is such a function

Declare Sub Sleep Lib "kernel32" (ByVal dwMS As Long)
Public Const TABLE_LOCKED = 3262

Public Function GetNextAutoNumber() As String
' Function to Emulate Autonumber Generation
' Generates err 3262 if table is locked
On Error GoTo Error_Handler

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    
    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblAutoNumber", dbOpenDynaset, dbDenyRead)
    
    rst.Edit
        rst!AutoNumber = rst!AutoNumber + 1
    
        GetNextAutoNumber = rst!AutoNumber
    rst.Update
      
Clean_Exit:
    rst.Close
    db.Close
    Exit Function
    
Error_Handler:
    If Err = TABLE_LOCKED Then
        ' Pause and try again
        Sleep 20
        Resume ' try to open the table again
    Else
        MsgBox "Error " & Err & " " & Err.Description
        Resume Clean_Exit
    End If

End Function

 

[John.Woody]

I have created the following function

Public Sub NextInvoiceNo()
On Error Resume Next

If IsNull(DMax("InvoiceNo", "Invoice")) Then
Forms!F_Invoice!InvoiceNo = 1
Else
Forms!F_Invoice!InvoiceNo = DMax("InvoiceNo", "Invoice") + 1
End If

Forms!F_Invoice.Refresh
If Err = 3022 Then
Forms!F_Invoice!InvoiceNo = ""
Forms!F_Invoice.Refresh
NextInvoiceNo
End If

End Sub

This way you will always get a sequencial number as the code will cycle through its self again until the error clears. 3022 being the error code if requested changes would create duplicate values in the field (which I take it you have already set up hence your post.

It is important to save or refresh the form as soon as possible which you are doing in your second line of highlighted code so that the next number can be selected.

HTH

John

 

[rnutts]

Thanks both for your answers

The funny or amusing thing is that I moved away from Autonumber to put in the Dmax code!!!!!!!!, albeit the autonumber was for the actual field and would have created more of the problem I am having.

With regards to the function from Dennisk. What would I put in my code to call the public function you have created.

Thanks

Richard

 

[boblarson]

Thanks both for your answers

The funny or amusing thing is that I moved away from Autonumber to put in the Dmax code!!!!!!!!,

Actually Dennisk's answer really is not an autonumber. He has a field named autonumber in the table (not sure I would use that exact word because of the confusion it might cause but it isn't a real problem) and the table really only stores one datum. It is the last number generated.

The one thing that is missing that Dennisk should provide here is the Sleep function as that doesn't exist as a part of Access (at least not in any version up to 2003 - I didn't look in 2007).

As for implementing, you just put his code in a standard (not form) module and then call GetNextAutoNumber in the same place you used DMax. So,

Me.Our_Sample_Number = GetNextAutoNumber()