Hi all, how can i prevent two users on editing the same recordset on the subform that i have placed in my form?
I tried setting the record lock tO "EDITED RECORDS" under the property sheet in the form. But it still does not work on my side. Can somebody help?
Thanks
In the strictest sense, you can't do this easily. What you have to do is have a single field that corresponds to your user ID in each lockable record. You can best do this easily via a DAO operation. I'm going to gloss this over a bit, but here goes...
You have to be able to select the record using two steps, one of which uses whatever search criteria you would have used if you were doing this standalone.
Code:
DECLARE DAODB AS DAO.Database
DECLARE LockSQL AS String
DECLARE NumAff AS Long
...
SET DAODB = CurrentDB
...
LockSQL = "UPDATE MyTable SET OWNERID = " & {some source of ID} & _
" WHERE OWNERID = '' AND {insert other selection criteria here} ;"
NumAff = 0
On Error Resume Next
DAODB.Execute LockSQL, dbFailOnError
NumAff = DAODB.RecordsAffected
{do something about error trapping if needed}
IF NumAff = 1 THEN
GoTo GOT_THE_RECORD
ELSE
GoTo NOT_MINE_YET
END IF
If you got the record, then your ID will be on it and you can work on it. If you didn't get the record, someone else's ID will be there and you have to wait for it. You will GET the record any time that it's lock field OWNERID is blank, null, zero, whatever is appropriate for your ID. Don't forget to change the UPDATE statement's OWNERID criteria based on the data type you are using for the lock field. I was using a text ID. You could use something numeric equally well if that is the kind of ID you are using.
You can tell right away because no matter what happens, you will have either 0 or 1 in the NumAff variable because the count tells you whether you succeeded in updating ANY record. If you updated the record then your UPDATE put your ID in it and you now own it. If you got back zero records then the record was owned by someone else and so NO records were selected by the WHERE criteria. This UPDATE done this is essentially a monolithic operation so should be close to bulletproof - but as we know, all is relative. So as a final test, you could verify that it is YOUR ID in the lock field that I'm calling OWNERID.
I said this was in two steps. Step one is the UPDATE. Step two is asking the DB engine whether anything was updated.
Incumbent on using this approach is that you remember to UNLOCK the record by clearing out the OWNERID when you are done. Also, unless something really screwy is going on, you should never have more than one such record locked at a time.
Hi all, how can i prevent two users on editing the same recordset on the subform that i have placed in my form?
I tried setting the record lock tO "EDITED RECORDS" under the property sheet in the form. But it still does not work on my side. Can somebody help?
Thanks
Hi, are you sure you do the "edited records" lock on the subform ? You need to set it outside the form where it is dropped as a subform. Go to the original form you created to act as a subform and make the lock change there. It has always worked for me. The form record locks were first implemented in A2007, I believe.
Hi, are you sure you do the "edited records" lock on the subform ? You need to set it outside the form where it is dropped as a subform. Go to the original form you created to act as a subform and make the lock change there. It has always worked for me. The form record locks were first implemented in A2007, I believe.
Yes, I did. I am now having error with my code. The error shows"Object variable or with block variable not set.
Below is my code:
Code:
'check whether there is exists data in list
If Not (Me.Form1.Form.Recordset.EOF And Me.Form1.Form.Recordset.BOF) Then
'get data to text box control
With Me.Form1.Form.Recordset
Me.txtGLGPO = .Fields("PO")
Me.txtFabricDelivery = .Fields("Date")
Me.txtStyleNo = .Fields("Style NO")
Me.txtGLA = .Fields("GL Lot")
Me.txtFabrication = .Fields("Description2")
Me.txtWidth = .Fields("Fabric Cuttable Width")
Me.txtColour = .Fields("Color")
Me.txtLbs = .Fields("SumOfOurQty")
Me.txtYds = .Fields("SumOfSupplierQty")
Me.txtFabricDelivery = .Fields("Date")
Me.txtFinishedGoods = .Fields("GSMBeforeWash")
Me.txtGSMsq = .Fields("GMS Per SqYD")
Me.txtPrintedRemarks = .Fields("Remark")
Me.txtFabricWeight = .Fields("Fabric Weight")
Me.TXTuNITpRICE = .Fields("Unit Price")
Me.txtName1 = .Fields("Name1")
Me.txtGarmentDelDate = .Fields("Garment Delivery Date")
Me.txtLine = .Fields("Line")
Me.Image97.Picture = Me.txtGarmentSketch & ""
'store GLGPO in Tag of in case GLGPO is modified
Me.txtGLGPO.Tag = .Fields("PO")
'disable button edit
Me.cmdEdit.Enabled = True
End With
End If
