If two users click on same time – MultiUser Issue

[rpatil]

I have different teams ( say TeamA , TeamB , TeamC ) , each team need separate sequential Work Order Numbers ( if work order is created by TeamA member it should see the last order number and add one to it )

tblCurrentMaxIDFmly contains the last Id number used by the team and this is updated every time if Work order is created

RnDGroup CurrentMaxID
TeamA 4
TeamB 1
TeamC 2



I calculate the Next Rev number in form “Frmtreeview” and use query “"QryCreateWO"” that takes input from this form to created new work orders.

The database is silted in Front End and Back End ( Front end is on each users local machine and the back end is on the network)


The Following code works fine BUT if two users click the cmbCreateWO button on the EXACT SAME SECOND then it assigns the same WO number to both users.

Is there a Way to Prevent this from happening ( In Backend – Tools- Advance – Default record locking I have set it to “Edited Record” ) But still I am not able to LOCK THE RECORDSET if it is been used by a different User.

Please help

Thanks
Raj





Private Sub cmbCreateWO_Click()
Dim strSQL As String
Dim stDocName As String

'Dim rs As Recordset
Dim rs As DAO.Recordset
Dim db As Database
Dim Fmly As String
Dim REV As String
If (Forms![Frmtreeview]![CmbFmly] & "" = "") Then Exit Sub
Set db = CurrentDb()
Fmly = Forms![Frmtreeview]![CmbFmly]

Set rs = db.OpenRecordset("SELECT Max(CurrentMaxID)AS MaxOfRev FROM tblCurrentMaxIDFmly WHERE RnDGroup = '" & Fmly & "'")
If rs.RecordCount > 0 Then
'rs.MoveFirst
Forms![Frmtreeview]![cmbNexttRev] = rs!MaxofRev + 1
Me.CmbYourWONumberIs.Visible = True
Forms![Frmtreeview]![CmbYourWONumberIs] = "Work Order is" & " " & Fmly & "-" & Me.cmbNexttRev
Forms![Frmtreeview]![CmbWOsimple] = Fmly & "-" & Me.cmbNexttRev

Set rs = db.OpenRecordset("SELECT * FROM tblCurrentMaxIDFmly WHERE RnDGroup = '" & Fmly & "'")
If rs.RecordCount > 0 Then
rs.Edit
rs!CurrentMaxID = Forms![Frmtreeview]![cmbNexttRev]
rs.Update
End If
DoCmd.SetWarnings False
stDocName = "QryCreateWO"
DoCmd.OpenQuery stDocName, acNormal, acEdit

stDocName = "QryAppendDistinctWorkOrder"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Else

strSQL = "INSERT INTO tblCurrentMaxIDFmly ( RnDGroup, CurrentMaxID ) " & vbCrLf & _
"SELECT [Forms]![FrmTreeView]![CmbFmly] AS Fmly, ""1"" AS Rev " & vbCrLf & _
"FROM tblCurrentMaxIDFmly;"
DoCmd.RunSQL strSQL

DoCmd.SetWarnings False
stDocName = "QryCreateWO"
DoCmd.OpenQuery stDocName, acNormal, acEdit
stDocName = "QryAppendDistinctWorkOrder"
DoCmd.OpenQuery stDocName, acNormal, acEdit
End If

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

 

[Guus2005]

In really dificult situations you could try this:

Create a table "UniqueID" with two fields ID(Autonumber) and GUID(string)

If you create a GUID and you insert that GUID in this new table, it will create a new unique ID (because is was an autonumber field remember?) Retrieve this id by selecting it from the table using the GUID in the where clause.

The result is your own private unique *ID*

Below the code to create a GUID. Perhaps there are different ways to make one but this is the one i use when needed

'Type and API to create GUIDs
Public Type GUID
    Data1 As Long
    Data2 As Integer
    Data3 As Integer
    Data4(7) As Byte
End Type

Public Declare Function CoCreateGuid Lib "OLE32.DLL" (pGuid As GUID) As Long


Public Function GetGUID() As String

    Dim udtGUID As GUID
    
    If (CoCreateGuid(udtGUID) = 0) Then
        GetGUID = _
            "{" & _
            String(8 - Len(Hex$(udtGUID.Data1)), "0") & Hex$(udtGUID.Data1) & "-" & _
            String(4 - Len(Hex$(udtGUID.Data2)), "0") & Hex$(udtGUID.Data2) & "-" & _
            String(4 - Len(Hex$(udtGUID.Data3)), "0") & Hex$(udtGUID.Data3) & "-" & _
            IIf((udtGUID.Data4(0) < &H10), "0", "") & Hex$(udtGUID.Data4(0)) & _
            IIf((udtGUID.Data4(1) < &H10), "0", "") & Hex$(udtGUID.Data4(1)) & "-" & _
            IIf((udtGUID.Data4(2) < &H10), "0", "") & Hex$(udtGUID.Data4(2)) & _
            IIf((udtGUID.Data4(3) < &H10), "0", "") & Hex$(udtGUID.Data4(3)) & _
            IIf((udtGUID.Data4(4) < &H10), "0", "") & Hex$(udtGUID.Data4(4)) & _
            IIf((udtGUID.Data4(5) < &H10), "0", "") & Hex$(udtGUID.Data4(5)) & _
            IIf((udtGUID.Data4(6) < &H10), "0", "") & Hex$(udtGUID.Data4(6)) & _
            IIf((udtGUID.Data4(7) < &H10), "0", "") & Hex$(udtGUID.Data4(7)) & _
            "}"
    End If

End Function

HTH