extract next available control num

[jwmott]

Access 2010 64 bit on Windows 7.

Two tables: controlNum and requisiteInfo. When saving a new requisiteInfo record, I need to pull the next available controlNum from the controlNum table. A controlNum is available if it hasn't yet been assigned to a requisiteInfo record, or opened but not yet assigned (opened means selected but not yet assigned). When saving the requisiteInfo record I also need to update the assigned status and opened status of the selected controlNum.

Database is multiuser so I need to account for potential simultaneous access. controlNum table has opened field and assigned field - both boolean flags.

Been playing around with this for a couple of days without success

 

[MazeWorX]

a simple query of the controlNum table should give you available numbers( <> True) then assigning it with append/update queries should do the trick. if the number is taken - 2 people trying to assign the same number - then error trapping can be used to notify the update loser that they need to try again

thoughts?

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>