VBA For Each Loop

[Saved]

Good Evening,



Please forgive me for such a rudimentary question, but I am very new to coding.

I have an access database that schedules job tasks based on scheduled work orders. I need to write a loop that will look at each work order and based on status and assigned team, execute an append query. The append query moves a predetermined set of job tasks to a master daily work schedule.

Below are two separate loops that I have been trying to use. Both look at one work order and then exits. Can someone help me, please?


Dim lastrow
lastrow = rs.EOF

For i = lastrow To 0 Step -1
If order_status.Value = "1" _
And assigned_team.Value = 1 Then
db.Execute "qry-append-team-1"
ElseIf order_status.Value = "1" _
And assigned_team.Value = 2 Then
db.Execute "qry-append-team-2"
ElseIf order_status.Value = "1" _
And assigned_team.Value = 3 Then
db.Execute "qry-append-team-3"
ElseIf order_status.Value = "1" _
And assigned_team.Value = 4 Then
db.Execute "qry-append-team-4"
End If
Next I







With rs
Do Until .EOF
If !order_status = "1" Then
Select Case !assigned_team
Case "1": db.Execute "qry-append-team-1"
Case "2": db.Execute "qry-append-team-2"
Case "3": db.Execute "qry-append-team-3"
Case "4": db.Execute "qry-append-team-4"
End Select
End If
.MoveNext
Loop
.Close
End With

[pbaldy]

The second is more common syntax, but you don't show what the recordset is based on. Also, your queries don't refer any value from the recordset, so they just execute the same values. They may be failing silently as well. Add:

db.Execute "qry-append-team-1", dbFailOnError

[Saved]

The second is more common syntax, but you don't show what the recordset is based on. Also, your queries don't refer any value from the recordset, so they just execute the same values. They may be failing silently as well. Add:

db.Execute "qry-append-team-1", dbFailOnError

pbaldy,

Thanks for a hasty reply. I added the dbFailOnError. And.....now i run into a completely different unintended result. When I try to add a second assignment to Team 1, ("qry-append-team-1") it gives the first team with this assignment a second identical work assignment and gives the second team with this assignment two identical work assignments. Both should only have one.

The recordset is based on a table that contains the discreet work orders.

Private Sub btn_build_work_schedule_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset


Set db = CurrentDb
Set rs = db.OpenRecordset("tbl_work_orders")
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE FROM tbl_work_assignments"

With rs
Do Until .EOF
If !order_status = "1" Then
Select Case !assigned_team
Case "1": db.Execute "qry-append-team-1", dbFailOnError
Case "2": db.Execute "qry-append-team-1", dbFailOnError
Case "3": db.Execute "qry-append-team-1", dbFailOnError
Case "4": db.Execute "qry-append-team-1", dbFailOnError
End Select
End If
.MoveNext
Loop
.Close
End With

[pbaldy]

Well, you're looping the entire table, so every time you do this you'll add records for previously worked on records, won't you? It sounds like your queries need to take the work order from the loop into account somehow. Options include executing SQL from code, or populating a form control and having the queries get the value from there. Can you attach a sample db here and describe the intended result?

[davegri]

Quit picking on team 1.

With rs
Do Until .EOF
If !order_status = "1" Then
Select Case !assigned_team
Case "1": db.Execute "qry-append-team-1", dbFailOnError
Case "2": db.Execute "qry-append-team-2", dbFailOnError
Case "3": db.Execute "qry-append-team-3", dbFailOnError
Case "4": db.Execute "qry-append-team-4", dbFailOnError
End Select
End If
.MoveNext
Loop
.Close
End With

[moke123]

DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE FROM tbl_work_assignments"

Also note that when you set warnings to false you should also explicitly turn them back on. And be careful that they get turned back on before any error occurs.
Better to use the .execute method.

Dim strSql as string
strSql = "Delete * from  tbl_work_assignments"
db.Execute strSql,dbFailOnError