Hello – After splitting db into front and backend, I have the Public Function below in frontend that creates a new counter in table tblFlexAutoNum which is located in the backend. I am using linked table manager to link to this table from the frontend. The BeforeUpdate event for my form inserts the new counter into the form – code also below. Issue: get “could not get a counter” message. I need help to change the function and/or BeforeUpdate event for the form. Thank you!
Code:
Public Function acbGetCounter() As Long
'Get a value from the counters table and increment it
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim blnLocked As Boolean
Dim intRetries As Integer
Dim lngTime As Long
Dim lngCnt As Long
Dim lngCounter As Long
'set number of retries
Const conMaxRetries = 5
Const conMinDelay = 1
Const conMaxDelay = 10
On Error GoTo HandleErr
Set db = CurrentDb()
blnLocked = False
Do While True
For intRetries = 0 To conMaxRetries
On Error Resume Next
Set rst = db.OpenRecordset("tblFlexAutoNum", _
dbOpenTable, dbDenyWrite + dbDenyRead)
If Err.Number = 0 Then
blnLocked = True
Exit For
Else
lngTime = intRetries ^ 2 * _
Int((conMaxDelay - conMinDelay + 1) * Rnd + conMinDelay)
For lngCnt = 1 To lngTime
DoEvents
Next lngCnt
End If
Next intRetries
On Error GoTo HandleErr
If Not blnLocked Then
If MsgBox("Could not get a counter: Try again?", _
vbQuestion + vbYesNo) = vbYes Then
intRetries = 0
Else
Exit Do
End If
Else
Exit Do
End If
Loop
If blnLocked Then
lngCounter = rst("CounterValue")
acbGetCounter = lngCounter
rst.Edit
rst("CounterValue") = lngCounter + 1
rst.Update
rst.Close
Else
acbGetCounter = -1
End If
Set rst = Nothing
Set db = Nothing
ExitHere:
Exit Function
HandleErr:
MsgBox Err.Number & ": " & Err.Description, , "acbGetCounter"
Resume ExitHere
End Function
BeforeUpdate event for form inserts the new counter into the form:
Private Sub Form_BeforeUpdate(Cancel As Integer)
'Try to get a unique counter and write it to the CourseID field
Code:
Dim lngCounter As Long
If IsNull(Me.txtCourseID) Then
lngCounter = acbGetCounter()
'If no counter is available...
If lngCounter < 1 Then
'cancel the update event.
Cancel = True
Else
'write the key field.
Me.txtCourseID = lngCounter
End If
End If