Data Entry in Multi-User Environment 1

[dots]

Hi,

I am new to using Access in a multiple user environment. I have a database (inherited) that will soon be shared by about 8 people for entering work orders.

My question is regarding the data entry form. The Work Order Number is not an auto-number field. It is based on another table (WO Master) that only has 1 entry -- which would be the next work order number. Apparently, when a new work order is saved in the first form, a query runs which adds 1 to the number in WO Master. If the add record process is aborted, the original number in the WO Master table is maintained.

So, I'm assuming that if a user is in the process of entering a new record and a second user opens the data entry form, they will both have the same Work Order Number.

Can anyone explain how to use record locking in this situation. I'm afraid I'll lock everyone out of using anything at all in the database. I really can't change the Work Order Number field to an auto-number at this point -- the database already has over 4,000 records in it.

Any help/pointers would be greatly appreciated.

Thank you

 

[danvlas]

Use the BeforeUpdate event of the form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim rst As DAO.Recordset
Dim rst As New ADODB.Recordset
Const conRetries = 10
Dim strSQL As String
Dim i As Integer
On Error GoTo ErrHandler
strSQL = "Select IDField From [WO Master]"
start:
set rst = Nothing
Set rst = CurrentDB.OpenRecordset(strSQL, dbOpenDynaSet, dbPessimistic)
rst.Edit 'This will raise an error for DAO if conflict

rst.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockPessimistic

rst.Fields("IDField&quot = rst.Fields("IDField&quot + 1
Me("IDOnForm&quot = rst.Fields("IDField&quot
rst.Update 'This will raise an error for ADO if conflict

ExitHere:
Set rst = Nothing
Exit Sub

ErrHandler:
i = i + 1
If i >= conRetries Then
If MsgBox("Retry?",vbYesNo) = vbYes Then
i = 0
resume start
Else
Cancel = True
resume ExitHere
End If
Else
Resume start
End If
End Sub

Green part is DAO specific. Red part is ADO specific.
You should use either, but NOT both, so delete what's not necessary.

Code will attempt 10 times (conRetries) to set next ID, if unsuccessful you'll be prompted.

You can also use the BeforeInsert event, but if user aborts the record you'll get a gap in numbering.

HTH




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html