Grab the 1st available record

[fmm]

I'm trying to set up a queue for a form:
The table has a field called AccessStatus that is "0" if the record is available, "1" if it is not.

I'm trying to set the AccessStatus to "1" as soon as the form is opened, so that no one else can get it.

Here's the code:

Private Sub Form_Open(Cancel As Integer)

Dim dbs As Database
Dim rst As Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("qryAvailable", dbOpenDynaset, dbSeeChanges, dbPessimistic)

rst.MoveFirst
With rst
.Edit
!AccessStatus = "1"
.Update
End With


rst.Close
dbs.Close

End Sub

I can still get 2 users to see the same record. Any ideas?


thanks!

 

[Jack Cowley]

This is what Autonumbers are for in the Primary Key of a record. No two people can work on the same record at the same time. Just change your ID to an Autonumber Data Type and that should be the end of your problem.

 

[fmm]

Jack Cowley:

I think you misunderstand the problem, or I don't understand your comment.

Once person A has grabbed the top record (call ir Rec01), I don't want person B to even be able to see it; they should get Rec02).

I don't see how having the ID as an Autonumber accomplishes this.

Something that I should have stated in my original post: the query qryAvailable returnes records where AccessStatus = 0. THe difficulty is in changing AccessStatus to 1 as quicky as possible, so that qryAvailable no longer sees it.

I hope this better defines my problem.


Thanks!

[This message has been edited by fmm (edited 01-14-2002).]

 

[fmm]

I haven't solved this yet, and this message will keep the topic on today's list.


Thanks.

 

[fmm]

I'll try one more day...