Data Access Problems

[soundmangav]

Hi there

I'm using VBA with Access '97 to develop a simple account updating interface, but seem to be encounter problems where multiple users can access the same account at the exact same time.

Basically all I'm doing is using a Form to retrieve record information from an Accounts table, but if two people request a valid Account at exactly the same time, then they are both presented with the record details, even though as soon as one individual gets an Account, the Locked field in the Account table should be set to true (ie checkbox is ticked).

Is there any way I can prevent this from happening, I've thought about trying to use a Pessimistic Lock, so as soon as the record is put into edit mode its Locked, but I'm not really sure?

Here's the bare bones of my code, cheers in advance for any help!

Option Compare Database
Option Explicit
Dim RecFound As Boolean
Dim rst As Recordset
Dim db As Database

Private Sub cmdNext_Click()

Call Clear_All 'Clears all form fields

If Not RecFound Then 'If RecFound is False then call Find_Next
Call Find_Next
Exit Sub
End Sub

Private Sub Find_Next()

Dim iLoopCount As Integer 'Loop Counter
Dim sSearch As String 'SQL String

iLoopCount = 0 'Initialise Counter

RetryLoop:

Set db = CurrentDb

'SQL Criteria = Locked field & Updated field in Accounts table are False

sSearch = "SELECT TOP 1 * FROM tbl_Accounts "
sSearch = sSearch & "WHERE tbl_Accounts.Locked = False AND"
sSearch = sSearch & "tbl_Accounts.Updated = False"

Set rst = db.OpenRecordset(sSearch, dbOpenDynaset)

If rst.RecordCount < 1 Then
MsgBox &quot;No more Accounts to work!&quot;, vbInformation, &quot;Accounts&quot;
RecFound = False
Exit Sub
Else
If rst!Locked = False Then 'If record field &quot;Locked&quot; is unchecked
rst.Edit 'Put into edit mode
rst!Locked = True 'Lock or Check the &quot;Locked&quot; field
rst.Update 'Update the Accounts Table
RecFound = True 'Set RecFound to True
Call Pop_All 'Populate the Form fields with the Record information
Else
If iLoopCount < 3 Then 'Try 3 times
iLoopCount = iLoopCount + 1 'Increment counter
GoTo RetryLoop 'Loop
Else
MsgBox &quot;Unable to retrieve record, please click 'Next Account'&quot;, vbInformation, &quot;Accounts&quot;

RecFound = False 'Set RecFound to False
Exit Sub

End If
End If
End If

Exit Sub

End Sub