Retrieving 'first' record in table

  • Thread starter Thread starter mscertified
  • Start date Start date
M

mscertified

I have a table which is in effect a FIFO queue. I need to retrieve then
delete the first record added. I'm assuming I cannot rely on the autonumber
key to do this and will need a timestamp column.
How can I ensure that different users don't grab the same record since this
is a multi-user system?
 
I've done this by adding an "InUse" (Y/N) field to the table.

I then have a function something like the following that will get the ID
value of the next record and set its InUse value to True, preventing the next
person from getting that record. Depending on the number of users, it is
highly unlikely that two users will click the button to get the next ID at
the exact same time. Once you have the fnNextID value, you can retrieve that
data, then can delete the record using the ID value. You could also provide
a mechanism to "Cancel" which would just reset the InUse flag to False, if
you need to.

Public Function fnNextID() as Long

Dim strSQL as string
Dim rs as dao.recordset

strSQL = "SELECT Top 1 * FROM tbl_YourTableName " _
& "WHERE InUse = 0 " _
& "ORDER BY DateTimeAdded "
Set rs = currentdb.openrecordset(strSQL)

if rs.eof Then
fnNextID = 0
else
rs.edit
rs("InUse") = True
fnNextID = rs("ID")
rs.update
endif

fnNextID_Exit:
rs.close
set rs = nothing

End Function
 
Hi mscertified

I can't see any reason not to use the AutoNumber field - the lowest numbered
record should always be the first one that was entered. So you could use the
DMin() function to find the lowest value for your key field (or for your
datestamp field if you prefer). Then use that in the WHERE clause of a
"DELETE * FROM..." sql statement.

Just a thought. It might be simpler!

All the best
 
that would be unrreliable since autonumbers can be reused and thus the latest
is not always the lowest.
 
Back
Top