Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2006
    Posts
    4

    Unanswered: If two users click on same time – MultiUser Issue

    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

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    First step, make that field UNIQUE within your table.
    Then the duplicate insert will fail.
    Trap the error in your error handling and ask the user to try again.

    Bish, bash, bosh.
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •