help with a multiple IF statement

[lorenambrose]

I have this multiple in the code for a form but it seems like it is ignoring the last if statement.

If IsNull(Me.Name_Cancel) Or IsNull(Me.[Date Cancelled]) Or IsNull(Me.Cancel_Reason) Then

Please help me figure this out.

 

[lorenambrose]

This is wierd. It is only messing up in record #1. All thers work just fine.

Any suggestions????

 

[jackd]

What is happening? What message/condition?
Please show the failing record(s)?

Need more info.

 

[lorenambrose]

THis is the whole code.

Also something is forcing it to open on a NewRecord and I can not figure out what.

Private Sub Command7_Click()
If Me.Status = "Review" Or Me.Status = "Assigned" Or Me.Status = "Abeyance" Or Me.Status = "Closed" Then
    MsgBox "Engineering Request can not be 'CANCELLED'." & vbCrLf & "Status must be 'Submitted' or 'Rejected'", vbOKOnly
    Exit Sub
Else
    If Me.Status = "CANCELLED" Then
        MsgBox "Engineering Request is already 'CANCELLED'.", vbOKOnly
        Exit Sub
    Else
        If IsNull(Me.Name_Cancel) Or IsNull(Me.[Date Cancelled]) Or IsNull(Me.Cancel_Reason) Then
            MsgBox "You must enter a Name, Date and Reason for the cancellation!", vbOKOnly
            Exit Sub
        Else
            If Me.Name_Cancel = Me.Originator Then
                If MsgBox("Are you sure you wish to cancel this Engineering Request?", vbYesNo, "Warning") = vbYes Then
                    Me.Status = "CANCELLED"
                    DoCmd.RunCommand acCmdRefresh
                    Me.Refresh
                Else
 
                End If
            Else
                If MsgBox("This is not the same person as the originator." & vbCrLf & "Do you wish to continue?", vbYesNo, "Warning") = vbYes Then
                    Me.Status = "CANCELLED"
                    DoCmd.RunCommand acCmdRefresh
                    Me.Refresh
                Else
 
                End If
            End If
        End If
    End If
End If
End Sub

 

[james_lankford]

put a breakpoint in the first line of code

If Me.Status = "Review" Or ...

then run he program and it will stop executing at that line, then use F8 to step through line by line and mouse highlight each form control and variable (Me.Status, for example). When its highlighted you will be able to see the value of each item and by stepping through you will be able to see exactly where it fails </pre>

 

[lorenambrose]

put a breakpoint in the first line of code

If Me.Status = "Review" Or ...


then run he program and it will stop executing at that line, then use F8 to step through line by line and mouse highlight each form control and variable (Me.Status, for example). When its highlighted you will be able to see the value of each item and by stepping through you will be able to see exactly where it fails
</PRE>

I really don't know what a break point is or exactly how this step through process works. Ant help would be great.

 

[lorenambrose]

Google helped me figure it out. So here is what I found

The hang up seems to be with the field or control Me.Cancel_Reason. when executed it acts like there is something in this cell but for record #1 only.

When I stepped through the code and hovered over IsNull Me.Cancel_Reason, it showed it as:

Me.Cancel_Reason = ""

Does "" mean the same as Null? if so the code should have worked fine. It works FLAWLESSLY on all the other records I have tried which is abour 25 random records.


Why is it acting like there is something in Me.Cancel_Reason for record #1?

 

[james_lankford]

ah, you figured it out as I was typing my reply

so for your next question
null is not the same as ""

null means undefined, nothing

"" is not undefined, it is an empty string

its confusing to a lot of people, but think of it as a "middle initial" field

there is a difference between null (nothing was ever entered, so we don't know if they have a middle initial or not) and a "" (no middle initial)

I'm not sure how your field became "", maybe somebody entered something and then backspaced it out
I'm not sure that would cause a "", but maybe ?

I usually do this
if Len(trim(Nz(Me.FirstName,""))) = 0 then

there may be better ways, but that's how I do it

 

[lorenambrose]

ah, you figured it out as I was typing my reply

so for your next question
null is not the same as ""

null means undefined, nothing

"" is not undefined, it is an empty string

its confusing to a lot of people, but think of it as a "middle initial" field

there is a difference between null (nothing was ever entered, so we don't know if they have a middle initial or not) and a "" (no middle initial)

I'm not sure how your field became "", maybe somebody entered something and then backspaced it out
I'm not sure that would cause a "", but maybe ?

I usually do this
if Len(trim(Nz(Me.FirstName,""))) = 0 then

there may be better ways, but that's how I do it

That is exactly what happened. I entered a bunch of random characters in the firld then deleated them to verify the code. I did the same in all the records and had no issues.

 

[lorenambrose]

Here is more

the three fields in question were populated then the data highlighted and deleated.

I stepped thru the code and this is the result

If IsNull(Me.Name_Cancel) OR shows Me.Name_Cancel = Null

IsNull(Me.[Date Cancelled]) Or shows Nothing I think this it is because it is a DATE field

IsNull(Me.Cancel_Reason) Then shows Me.Cancel_Reason = ""

Why is one Null and the other "" but only in record #1?