I am using nested If-Then and For Next statements to compare data from one table to data from another table. I am also starting it all out with a DLookup that gets the value of a check box and if it's Yes(-1), I loop through both tables to find the matching record and update the weekdays accordingly. Everything goes through, but the word "Timed" ends up in all the weekdays and should only show up when there is a value in the matching weekday of the other table.
Here is my code and if anyone has a better way of doing this, I am open to suggestions.
Here is my code and if anyone has a better way of doing this, I am open to suggestions.
Code:
Dim i As Integer
Dim j As Integer
Dim db As DAO.Database
Dim wc As DAO.Recordset
Dim stc As DAO.Recordset
Set db = CurrentDb
Set wc = db.OpenRecordset("Weekly_Challenges")
Set stc = db.OpenRecordset("Weekly_StartTime_Challenges")
amRoutine = DLookup("AMRoutineStartTime", "Standard_Actions", "UserId=" & Me.UserID.Value & " And WeekNumber=" & Me.WeekNumber)
If Form_Weekly_Challenges_subform.StandardAction.Value = "AM Routine" Then
If amRoutine = -1 Then
For i = 0 To stc.RecordCount - 1
If stc.Fields("StartTimeAction") = "AM Routine" Then
For j = 0 To wc.RecordCount - 1
If wc.Fields("StandardAction") = "AM Routine" Then
If stc.Fields("Monday") = Null Then
wc.Edit
wc.Fields("Monday") = ""
wc.Update
Else
wc.Edit
wc.Fields("Monday") = "Timed"
wc.Update
End If
If stc.Fields("Tuesday") = Null Then
wc.Edit
wc.Fields("Tuesday") = ""
wc.Update
Else
wc.Edit
wc.Fields("Tuesday") = "Timed"
wc.Update
End If
If stc.Fields("Wednesday") = Null Then
wc.Edit
wc.Fields("Wednesday") = ""
wc.Update
Else
wc.Edit
wc.Fields("Wednesday") = "Timed"
wc.Update
End If
If stc.Fields("Thursday") = Null Then
wc.Edit
wc.Fields("Thursday") = ""
wc.Update
Else
wc.Edit
wc.Fields("Thursday") = "Timed"
wc.Update
End If
If stc.Fields("Friday") = Null Then
wc.Edit
wc.Fields("Friday") = ""
wc.Update
Else
wc.Edit
wc.Fields("Friday") = "Timed"
wc.Update
End If
If stc.Fields("Saturday") = Null Then
wc.Edit
wc.Fields("Saturday") = ""
wc.Update
Else
wc.Edit
wc.Fields("Saturday") = "Timed"
wc.Update
End If
If stc.Fields("Sunday") = Null Then
wc.Edit
wc.Fields("Sunday") = ""
wc.Update
Else
wc.Edit
wc.Fields("Sunday") = "Timed"
wc.Update
End If
End If
wc.MoveNext
Next j
End If
stc.MoveNext
Next i
End If
Else
End If