Checking record/query locks on save in multi-user environment.

[Ascentient]

Sorry for the length.

Background
I have a ticketing database that contains all of my forms, queries and a couple of non-linked tables and many linked tables. The primary tables are in a separate database.
The "forms" database is compiled into MDE's and placed on two workstations, where the linked files are set.

Over the last two weeks (since installation) they have managed to skip ticket numbers. Well, I found one instance where it could happen and the code is listed below. If two users are entering tickets at the same time, AND click "Print/Save" at the same timeI get the following error:

"The expression you entered as the event property setting produced the following error: The Microsoft Jet database engine could not find the object 'tblBMtkP'. Make sure the object exists and that you spell its name correctly."

The result is a missing record for the user who hit the routine first.

My guess is the record locks are not set correct or I need to find a way to stall one users "Print/Save" event so the other users doesn't override them. I have the forms Record Locks set to Edited Record, but that does not seem to be helping.

Is there a way to check for this information and stall one of the users?

   Dim db As DAO.Database, rs As DAO.Recordset
   Set db = CurrentDb
   Set rs = db.OpenRecordset("tblLastk", dbOpenDynaset)
   Me![TICKET-NO] = rs![LAST TICKET NUMBER] + 1
   With rs
      .Edit
      ![LAST TICKET NUMBER] = Me![TICKET-NO]
      .Update
   End With
    
   Set rs = Nothing
   Set db = Nothing

   Call CALC_Click
   DoCmd.SetWarnings False
   OK2Close = True
   Dim strSQLRpt, strSQLUpdInvQtys, L1, L2, L3, l4, l5, l6,
     l7, l8 As String
   'Updates onhand inventory quantities
   For Lineno = 1 To 10
      L1 = Me("[MATERIAL-CODE-" & Lineno & "]") <> ""
      If Me("[MATERIAL-CODE-" & Lineno & "]") <> "" Then
         strSQLUpdInvQtys = "UPDATE tblPrdct SET 
            tblPrdct.INVENTORY = [tblprdct]![inventory] - " 
            & Me("[QTY-SHIP-" & Lineno & "]") & " WHERE 
            (((tblPrdct.MATERIAL)=""" & Me("[MATERIAL-CODE-"
            & Lineno & "]") & """));"
         DoCmd.RunSQL strSQLUpdInvQtys
      End If
   Next
   L2 = [Forms]![frmBmtic]![PLANT-NO]
   l4 = [Forms]![frmBmtic]![TICKET-NO]
   l6 = [Forms]![frmBmtic]![cust-job-id]
   l8 = [Forms]![frmBmtic]![ORDER-DATE]

   DoCmd.Close acForm, "frmBmtic", acSaveYes
   strSQLRpt = " SELECT tblBMtk.* INTO tblBMtkP FROM tblBMtk
      WHERE (((tblBMtk.[PLANT-NO])='" & L2 & "') AND 
      ((tblBMtk.[TICKET-NO])=" & l4 & ") AND 
      ((tblBMtk.[CUST-JOB-ID])='" & l6 & "') AND 
      ((tblBMtk.[ORDER-DATE])=#" & l8 & "#));"
   DoCmd.RunSQL strSQLRpt
   DoCmd.OpenReport "rptBmtk"
   DoCmd.Close acForm, "frmBmtic", acSaveYes
   DoCmd.OpenForm "frmBmtic", acNormal
   DoCmd.SetWarnings True

Thanks in advance.
Ascent

 

[Ascentient]

Does anyone think that this would work?

   lblSaveinProgress.Visible = True
   Do While rs.EditMode = dbEditInProgress
     DoEvents
   Loop
   lblSaveinProgress.Visible = False

Ascent

 

[MichaelRed]

see faq181-291

Ms. A. doesnot support true record locking. The only way you can get a true "LOCK" is to lock the table. Locking a 'active' table is, of course, also verbotten, see the faq ref above. Its' purpose is somewaht different, but you may be able to adapt it to suit your purpose.





MichaelRed

 

[Ascentient]

Thank you. I will research and implement as needed. I will let you know how it turns out.