Nested If and For Next statements. (1 Viewer)

Lkwdmntr · Jul 10, 2019

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.

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

The_Doc_Man · Jul 10, 2019

First, I would be totally remiss if I didn't point out that your structure is denormalized, which usually leads to issues. Having separate fields for each day of the week is an example of what is called a "repeating group" and is one of the common normalization errors. May I respectfully suggest that you read up on the topic "normalization" using this forum's SEARCH facility (in the thin menu ribbon near the top of the page, 3rd from right in that ribbon). Further, if you choose a wider search of the web, be sure to qualify that as "database normalization" because without the qualifier, you will find other types of normalization from math, politics, chemistry, and several other fields. If you then can normalize your DB, some of the scans involved become simple queries.

Second, the statement IF xxx = Null will NEVER work because in Access and VBA, Null never equals anything (including another null!) You can use IF IsNull(xxx) but even that might not work as you wish. The best option is usually If NZ(xxx,"") = "" as a way of testing for a null OR EMPTY field. That is because some things that you think are null really aren't; they are just empty. And to Access, that means they are very different.

theDBguy · Jul 10, 2019

Hi. If I knew exactly what you're trying to do, I might be able to suggest using an UPDATE query rather than VBA to do this. Just a thought...

Nested If and For Next statements. (1 Viewer)

Lkwdmntr

Registered User.

The_Doc_Man

Immoderate Moderator

theDBguy

I’m here to help

Similar threads

Users who are viewing this thread