Use of AND in a IF ... THEN sentence (1 Viewer)

[anb001]

When I use below code, the data in the table is updated, but the msgbox after the FOR ... NEXT is not showing:

...

For counter = 1 To 10

If Not Me("chkSJABody" & counter).Value = False [COLOR="Red"]And Me("chkCanEdit" & counter).Value = False[/COLOR] Then

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM tblSJABody WHERE SJA_Step = '" & Me("txtSJA_Step" & counter) & "'")

With rs
        .Edit
        .Fields("BasicStep") = Me("txtBasicStep" & counter).Value
        .Fields("Hazard") = Me("txtHazard" & counter).Value
        .Fields("Consequence") = Me("txtConsequence" & counter).Value
        .Fields("Measures") = Me("txtMeasures" & counter).Value
        .Fields("PersonMeasures") = Me("txtPersonMeasures" & counter).Value
        .Update
        
End With

Set db = Nothing
Set rs = Nothing

End If

Next counter

MsgBox("SJA is now waiting for OIM's approval.", vbOKOnly, "SJA updated")

...

But when I use it without the "AND", it works fine:

...

For counter = 1 To 10

If Not Me("chkSJABody" & counter).Value = False Then

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM tblSJABody WHERE SJA_Step = '" & Me("txtSJA_Step" & counter) & "'")

With rs
        .Edit
        .Fields("BasicStep") = Me("txtBasicStep" & counter).Value
        .Fields("Hazard") = Me("txtHazard" & counter).Value
        .Fields("Consequence") = Me("txtConsequence" & counter).Value
        .Fields("Measures") = Me("txtMeasures" & counter).Value
        .Fields("PersonMeasures") = Me("txtPersonMeasures" & counter).Value
        .Update
        
End With

Set db = Nothing
Set rs = Nothing

End If

Next counter

MsgBox("SJA is now waiting for OIM's approval.", vbOKOnly, "SJA updated")

...

Can anyone explain why, and what should I do to be able to include the "AND" part?

Thanks,
Anders

 

[VilaRestal]

It looks like it should work. I've got wonder about code before it though. Is there error handling? (Are you sure it doesn't bug out before the msgbox? Are you sure there is chkCanEdit10 control?) Have you stepped through it?

 

[anb001]

Thank a lot.

Your sentence

Are you sure there is chkCanEdit10 control?

made me check the names of the checkboxes (which I probably should have done in the first place), and sure enough, chkCanEdit4 was missing the "4". It works now.

Thanks again

 

[Pat Hartman]

If Not Me("chkSJABody" & counter).Value = False And Me("chkCanEdit" & counter).Value = False Then

The problem is when you combine AND/OR/NOT in the same expression, you almost always need parentheses to enforce your desired order of operation. I think you meant this:

If (Not Me("chkSJABody" & counter).Value = False) And (Me("chkCanEdit" & counter).Value = False) Then

but are getting this:

If Not (Me("chkSJABody" & counter).Value = False And Me("chkCanEdit" & counter).Value = False) Then

 

[VilaRestal]

Hmmm, any statement:

If Not something = False

is a bit wtf.com

Why not just

If something

?

(I know there are rare cases where Not False is not the same as True but here I think it should be.)

And there's a couple of other ways the code could be improved if I may be so bold:

For counter = 1 To 10

    If Me("chkSJABody" & counter).Value And Not Me("chkCanEdit" & counter).Value Then
        Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblSJABody WHERE SJA_Step = '" & Me("txtSJA_Step" & counter).Value & "'")
        With rs
            .Edit
            !BasicStep = Me("txtBasicStep" & counter).Value
            !Hazard = Me("txtHazard" & counter).Value
            !Consequence = Me("txtConsequence" & counter).Value
            !Measures = Me("txtMeasures" & counter).Value
            !PersonMeasures = Me("txtPersonMeasures" & counter).Value
            .Update
            .Close
        End With
    End If

Next counter
Set rs = Nothing

MsgBox("SJA is now waiting for OIM's approval.", vbOKOnly, "SJA updated")

I've learnt you can do Me("ControlName") instead of Me.Controls("ControlName"), which is handy. I wonder if you need any of the .Values

 

[Pat Hartman]

No. The Value property is the default for Access form/report controls and so it is normally omitted.

 

[anb001]

Pat,
Yep, I had some issues with the parentheses, but finally got it solved. And thanks for the ".value"hint.

VilaRestal,
After reading through the code, I came to the same conclusion about the IF NOT = FALSE, and just used IF = TRUE. Thanks anyway