Update Syntax

[jrbmagoo]

Good Day - I'm working on a form to assign work in Access. I have all of the other pieces set up besides this last part. So the first part of the form the supervisor would select the type of work they want to assign from a drop down. I linked the form to my query to pull the work (Priority) that does not have any attempted calls made. That works fine. Next the sup selects who they want to assign the work to (CID) from a drop down. The last part is where it gets a little tricky. The last option on the form for the sup to select is the number of records they want assigned to the CID they have selected based on the priority. What I would like the update query to do is assign the CID to the records that were pulled by Priority based off what ever number the sup enters into the box on the form. So if they have selected TOU as the priority and CID 01234 as the rep they want to assign work to and 20 in the quantity box, I want the update to take 20 records that fit the criteria and assign to that rep. Any assistance you guys can provide would be greatly appreciated.

[Bulzie]

Maybe something like this: (AfterUpdate on the form

rs1 = Select * From AssignmentTable Where Priority = '" & Me.Priority & "' And Attempted Calles = null"

rs1.MoveFirst
For x = 1 to me!Quantity

rs1.Edit

rs1!CID = me!CID

rs1.Update

Next x

[John_G]

You have to account for the fact that there might not be enough records to assign, i.e. the quantity requested might be too large. A few changes to the code can look after that:

rs1 = Select * From AssignmentTable Where Priority = '" & Me.Priority & "' And nz([Attempted Calls],0) = 0"   ' because it might be Null or 0

rs1.MoveFirst
x = 1
While x <= me!Quantity and not rs1.EOF
rs1.Edit
rs1!CID = me!CID
rs1.Update
x = x + 1
rs1.Movenext  ' advance to the next record
Wend

This assumes that you don't really care which records are assigned; Access will not put the results into rs1 in any particular order.

[jrbmagoo]

I'm thinking of making this action execute from a button, so would be On Click for the event. Will the code above execute this based off the options selected in the form?

[John_G]

As long as the Command Button is on the same form as the Quantity and CID options, it should work. If the record source for the form is AssignmentTable and one of the records the code tries to update is the one currently on the screen, you might get an error about another user trying to update the record.