Why doesn't this code from the Access Help menu work??

[Edward]

I'm trying to improve my understanding of record locks and copied this code
from the Access help menu; also set a reference to DAO 3.6 and when trying to
run or compile the code it comes up with an error "Object or member not
found" at the .Edit line.

This is the code and now I'm more intrigued that it doesn't work than I am
to satisfy my understanding of locks in general:

Option Compare Database
Option Explicit

Sub LockEditsX()

Dim dbsNorthwind As Database
Dim rstCustomers As Recordset
Dim strOldName As String

Set dbsNorthwind = OpenDatabase("C:\Program Files\Microsoft
Office\Office10\Samples\Northwind.mdb")
Set rstCustomers = _
dbsNorthwind.OpenRecordset("Customers", _
dbOpenDynaset)

With rstCustomers
' Store original data.
strOldName = !CompanyName

If MsgBox("Pessimistic locking demonstration...", _
vbOKCancel) = vbOK Then

' Attempt to modify data with pessimistic locking
' in effect.
If PessimisticLock(rstCustomers, !CompanyName, _
"Acme Foods") Then
MsgBox "Record successfully edited."

' Restore original data...
.Edit
!CompanyName = strOldName
.Update
End If

End If

If MsgBox("Optimistic locking demonstration...", _
vbOKCancel) = vbOK Then

' Attempt to modify data with optimistic locking
' in effect.
If OptimisticLock(rstCustomers, !CompanyName, _
"Acme Foods") Then
MsgBox "Record successfully edited."

' Restore original data...
.Edit
!CompanyName = strOldName
.Update
End If

End If

.Close
End With

dbsNorthwind.Close

End Sub

Function PessimisticLock(rstTemp As Recordset, _
fldTemp As Field, strNew As String) As Boolean

Dim ErrLoop As Error

PessimisticLock = True

With rstTemp
.LockEdits = True

' When you set LockEdits to True, you trap for errors
' when you call the Edit method.
On Error GoTo Err_Lock
.Edit
On Error GoTo 0

' If the Edit is still in progress, then no errors
' were triggered; you may modify the data.
If .EditMode = dbEditInProgress Then
fldTemp = strNew
.Update
.Bookmark = .LastModified
Else
' Retrieve current record to see changes made by
' other user.
.Move 0
End If

End With

Exit Function

Err_Lock:

If DBEngine.Errors.Count > 0 Then
' Enumerate the Errors collection.
For Each ErrLoop In DBEngine.Errors
MsgBox "Error number: " & ErrLoop.Number & _
vbCr & ErrLoop.Description
Next ErrLoop
PessimisticLock = False
End If

Resume Next

End Function

Function OptimisticLock(rstTemp As Recordset, _
fldTemp As Field, strNew As String) As Boolean

Dim ErrLoop As Error

OptimisticLock = True

With rstTemp
.LockEdits = False
.Edit
fldTemp = strNew

' When you set LockEdits to False, you trap for errors
' when you call the Update method.
On Error GoTo Err_Lock
.Update
On Error GoTo 0

' If there is no Edit in progress, then no errors were
' triggered; you may modify the data.
If .EditMode = dbEditNone Then
' Move current record pointer to the most recently
' modified record.
.Bookmark = .LastModified
Else
.CancelUpdate
' Retrieve current record to see changes made by
' other user.
.Move 0
End If

End With

Exit Function

Err_Lock:

If DBEngine.Errors.Count > 0 Then
' Enumerate the Errors collection.
For Each ErrLoop In DBEngine.Errors
MsgBox "Error number: " & ErrLoop.Number & _
vbCr & ErrLoop.Description
Next ErrLoop
OptimisticLock = False
End If

Resume Next

End Function

 

[Dale Fye]

Edward,

did you remove the reference to ADO in your references when you added the
DAO reference? If not, you will need to explicitly define the type of
recordset. Try:

Dim rstCustomers as DAO.Recordset

--
HTH
Dale

Don't forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.