Update table based on matching critera from looping recordsets (1 Viewer)

[kit_sune]

Keep in mind this code is based off my notes and memory because I don't have access to the computer that this code is at right now. (there may be errors)

If there's a better way, please let me know!!

So the code works right now to do what I want it to do, with one last thing to figure out.

Dim Date1 As Date
Dim Date2 As Date
Dim Tool As String

Dim rst As DAO.Recordset
Set rst = CurrentDB.Openrecordset("ToolCheckout_tbl")
Dim rs2 As DAO.Recordset


Do Until rst.EOF
	Date1 = rst.Fields("CheckoutDate")
	Date2 = rst.Fields("ReturnDate")
	Tool = rst.Fields("Tool")
	Set rs2 = CurrentDB.Openrecordset("WorkOrder_tbl")
	Do Until rs2.EOF
		If rs2.Fields("Tool") = Tool And _
		rs2.Fields("JobDate") >= Date1 And _
		rs2.Fields("JobDate") <= Date2 Then _
		'Code goes here to update record of the second table so that Operator = Operator from table 1.
		'the associated record from "WorkOrder_tbl" should have the "Operator" field updated from an
		'empty value to the name of the operator from table 1.
		'Basically, we identify that the job in table 2 was being worked on a date that fell on or
		'between our date range that the tool was checked out to someone, and so we update that record to reflect this.
		rs2.MoveNext
	Loop
	rs2.close
	rst.MoveNext
Loop

rst.Close

So the question is this:
When the criteria from record set one matches the criteria from record set two, how do I then go back to table 2 to update the "Operator" field?

Also, is there any way I could do all this without using DAO.recordset?

Thanks..
~Kit

 

[Ranman256]

you shouldnt have to loop...
a query running a query should do it all at once.

 

[isladogs]

Agree with ranman

Use an update query in place of the recordset code
It will be far faster than looping through a recordset as the query will effectively update all records 'at once'

Create a select query linking the 2 tables and join/filter using your criteria
Make sure the query isn't read only

Change it to an update query and set the criteria for updating the Operator field in table 2.
Run the query

That's it

 

[kit_sune]

I should have mentioned that the tables cannot be linked. The only field that they share is the tool field, but multiple records in each table can have the same tool. That’s why I didn’t do a query to start with.Imagine this scenario:I have 4 records in table 1:Tool | Operator | CheckoutDate | ReturnDateTool A | Bob | 1 July | 15 JulyTool A | Joe | 16 July | 25 JulyTool B | Joe | 1 July | 11 JulyTool B |Joe | 17 July | 22 JulyI have 10 records in table 2. They start off with the Operator being blank, but with my code I want them to be filled in with the name of the person who had the tool at the time of the job. Blank values are ok if there is no record (Since that helps me to isolate errors). Job ID | Hours | Tool | Date | Operator1 | 13 | Tool A | 1 Jul| | (Bob)2 | 17 | Tool A | 3 Jul | (Bob)3 | 3 | Tool A | 5 Jul | (Bob)4 | 5 | Tool B | 6 Jul | (Joe)5 | 7 | Tool A | 15 Jul | (Bob)6 | 6 | Tool B | 13 Jul | (blank)7 | 8 | Tool B | 19 Jul | (Joe)8 | 12 | Tool A | 22 Jul | (Joe)9 | 9 | Tool B | 27 Jul | (blank)10 | 12 | Tool A | 30 Jul | (blank)The parenthesis were added as emphasis, that the values were added after the vba function was run.

 

[arnelgp]

UPDATE WorkOrder_tbl AS T1, ToolCheckOut_tbl AS T2 Set T1.Operator=T2.Operator WHERE T1.Tool=T2.Tool AND T1.JobDate Between T2.CheckOutDate And T2.ReturnDate

This is equivalent to your code.