User types number in a box, then gets assigned that many records 2

[firebirdta84]

I have a table named tblMaster with two fields - AssignedTo and CaseNumber. The CaseNumber is imported daily but the AssignedTo is left blank. I have a dozen users and they need the ability to "assign" CaseNumbers to themselves without going into the table. The main form, frmMain, has a textbox (txtNum) and a textbox pre-filled with their name (txtUser). I need to make it so the user can enter a number in txtNum, press a button, and then have that many CaseNumbers assigned to them by taking the value in txtUser and placing it in the AssignedTo field of tblMaster. The CaseNumbers should be assigned in the order they are in tblMaster (or the order of the AutoNumber field called ID)

Can someone point me in the right direction?

Thanks!
Joe

 

[JerryKlmns]

You cannot lock the table for assigning x number of records to a user, and have a second user wait until the first one grabs them. Unless assignment happens in a different mdb that can be opened exclusively. This reminds me of FAQ on generating Unique ID numbers in a Multi-User database.

You 'll have to find a way for saying that the table is available for assigning. Maybe a dummy file (existing or missing) could be a flag for that purpose.
Check if it exists
Delete it
Assign
Create ii back
If it doesnt exist wait and try again a little later.

For assigning use an updatable recordset based on this SQL statement
SELECT TOP " & txtNum.Value " FROM tblMaster WHERE AssignedTo IsNull;"
and loop until EOF updating the field AssignedTo

 

[PWise]

docmd.runsql "Update (Select top " & txtNum & " *
From tblMaster 
where txtUser is null) dt Set AssignedTo ='" & txtUser & "'"

 

[PWise]

sorry sb

docmd.runsql "Update (Select top " & txtNum & " * From tblMaster where AssignedTo is null order by id) dt Set AssignedTo ='" & txtUser & "'"