I have a form that has 3 tables in the data environment. These tables are remote views with the data existing on SQL Server 6.5. When the user clicks the 'action' button, the application is supposed to look for an available number in one of these tables (if the 'assigned' field has a value of 'N' then it can be used). Once it finds the first available number it is supposed to lock the record, change the 'assigned' value to 'Y' and update the table to reflect the changes, and then unlock the record.

The problem I am having is when multiple users are running this portion of the application they are pulling the same numbers (which is a BIG nono). I have tried 'requery' on each of the views each time the button is clicked but this does not seem to work.

So, how can I be sure that each workstation has the latest data in their views? I am missing something here and could sure use some input.

TIA,
George