Do While Loop not producing expected results

[Rufusjeep]

Want to query table to get a list of all employees who have had a particular training then take their employee ID's and find them in the Worker table and change the field "Selected" to true. Was able to get it to mark the first record in the Worker table for the recordset but not the rest. Any ideas on what I need to change in my code to get it to modify all of the matching records in the Worker table and the recordset.

Dim CurDB As Database, Rs As Recordset, SQLStmt As String
Dim Rstbl As Recordset

Set CurDB = CurrentDb()
SQLStmt = "SELECT [EmpID], [EmpID] FROM [tblTRAINING] Where Criteria1 And Criteria2"
Set Rs = CurDB.OpenRecordset(SQLStmt, DB_OPEN_DYNASET)
Set Rstbl = CurDB.OpenRecordset("tblWORKER", DB_OPEN_DYNASET)
Do While Not Rs.EOF
Do While Not Rstbl.EOF
If Rs("EmpID&quot = Rstbl("EmpID&quot Then
Rstbl.Edit
Rstbl("SELECTED&quot = True
Rstbl.Update
End If
Rstbl.MoveNext
Loop
Rs.MoveNext
Loop
Rs.Close
Rstbl.Close
End Sub

Thanks for the help in advance,
Mike

 

[Craig0201]

Assuming that it is a Yes/No field

Rstbl("SELECTED&quot = -1

Craig

 

[jebry]

Hi Mike!

Try this little addition:

Do While Not Rs.EOF
Rstbl.MoveFirst
Do While Not Rstbl.EOF
If Rs("EmpID&quot = Rstbl("EmpID&quot Then
Rstbl.Edit
Rstbl("SELECTED&quot = True
Rstbl.Update
End If
Rstbl.MoveNext
Loop
Rs.MoveNext
Loop

hth
Jeff Bridgham
bridgham@purdue.edu

 

[TerpFan2001]

You should be able to use an UPDATE SQL Statement to get the same results.

Dim SQLStmt as String

SQLStmt = "UPDATE [tblWorker] SET [tblWorker].Selected = True WHERE [tblWorker].EmpID = [tblTraining].EmpID AND Criteria1 And Criteria2"

CurrentDB.Execute SQLStmt

 

[MichaelRed]

Better to follow the old MD grad A.K.A. TerpFan2001). He, at least, is not folllowing you off into clog-the-cpu swamp.


MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over