Multiple if statment (1 Viewer)

[Tor_Fey]

Good Afternoon;

I need to check that two fields on a form contain data before closing as they are linked to a report and can't have data in one field and not the other, I have tried the following:

Dim varwhite, varyellow

varwhite = RGB(255, 255, 255)
varyellow = RGB(255, 255, 0)

If Me.TF_Ben_B_S.Value > 0 Then
If (IsNull(Me.Ben_B_S_Date)) Then
MsgBox "Benefit Saving Date Required", vbExclamation, "Benefit Saving Date Required"
Me.TF_Ben_B_S_Date.SetFocus
Me.TF_Ben_B_S_Date.BackColor = varyellow
Exit Sub

But it isn’t working, please can someone give me a pointer as to how I check two criteria with one iff statement?
Your help as always is most appreciated.

Kind Regards
Tor Fey

 

[Minty]

For each If .. Then you will need an End If unless you process the result on the same line.

    varwhite = RGB(255, 255, 255)
    varyellow = RGB(255, 255, 0)

    If Me.TF_Ben_B_S.Value > 0 Then
        If (IsNull(Me.Ben_B_S_Date)) Then
            MsgBox "Benefit Saving Date Required", vbExclamation, "Benefit Saving Date Required"
            Me.TF_Ben_B_S_Date.SetFocus
            Me.TF_Ben_B_S_Date.BackColor = varyellow
        
        End If
    End If

 

[Tor_Fey]

Thanks Minty;

Unfortunately now the code seems to be getting ignored, the full code on my close button is as follows:

Private Sub closebutton_Click()

Dim varwhite, varyellow


varwhite = RGB(255, 255, 255)
varyellow = RGB(255, 255, 0)

If (IsNull(TF_Ben_Date_Taken)) Then
MsgBox "Taken Date Required", vbExclamation, "Taken Date Required"
Me.TF_Ben_Date_Taken.SetFocus
Me.TF_Ben_Date_Taken.BackColor = varyellow
Exit Sub
End If

'New Code for validation
If Me.TF_Ben_B_S.Value > 0 Then
    If (IsNull(Me.Ben_B_S_Date)) Then
        MsgBox "Benefit Saving Date Required", vbExclamation, "Benefit Saving Date Required"
            Me.TF_Ben_B_S_Date.SetFocus
                Me.TF_Ben_B_S_Date.BackColor = varyellow
            End If
        End If
        
        


        If Me.TF_Ben_Status = 5 Then
        If MsgBox("This record has a status of 'UNALLOCATED', you need to change the status to make this record live! Would you like to change the record status now?", vbQuestion + vbYesNo, "RECORD UNALLOCATED") = vbYes Then
            Exit Sub
        End If
    End If
        DoCmd.CLOSE acForm, "frm_TF_Ben_Main"
        'Forms!frm_tf_menu!mycaseis.Requery
        DoCmd.OpenForm "Frm_TF_Menu", acNormal, "", "", , acNormal
        DoCmd.Maximize


End Sub

I just need to check that the money value field and the date value field are not empty before closing the form.

Kind Regards
Tor Fey

 

[Minty]

So what happens ? Nothing at all?

Normally you do form validation on the before update event to prevent the form closing.

Also check that your values are actually Null and not ZLS.

Add some Debug.Prints before all comparisons start.

 

[Gasman]

If you indented the code properly, any errors are easier to spot.?

On errors like this I always fall back to line by line debugging.

Private Sub closebutton_Click()

    Dim varwhite, varyellow

    varwhite = RGB(255, 255, 255)
    varyellow = RGB(255, 255, 0)

    If (IsNull(TF_Ben_Date_Taken)) Then
        MsgBox "Taken Date Required", vbExclamation, "Taken Date Required"
        Me.TF_Ben_Date_Taken.SetFocus
        Me.TF_Ben_Date_Taken.BackColor = varyellow
        Exit Sub
    End If

    'New Code for validation
    If Me.TF_Ben_B_S.Value > 0 Then
        If (IsNull(Me.Ben_B_S_Date)) Then
            MsgBox "Benefit Saving Date Required", vbExclamation, "Benefit Saving Date Required"
            Me.TF_Ben_B_S_Date.SetFocus
            Me.TF_Ben_B_S_Date.BackColor = varyellow
        End If
    End If

    If Me.TF_Ben_Status = 5 Then
        If MsgBox("This record has a status of 'UNALLOCATED', you need to change the status to make this record live! Would you like to change the record status now?", vbQuestion + vbYesNo, "RECORD UNALLOCATED") = vbYes Then
            Exit Sub
        End If
    End If
    DoCmd.Close acForm, "frm_TF_Ben_Main"
    'Forms!frm_tf_menu!mycaseis.Requery
    DoCmd.OpenForm "Frm_TF_Menu", acNormal, "", "", , acNormal
    DoCmd.Maximize


End Sub

 

[Micron]

When checking only that a control has data, I use a function in a standard module so that any form in a db can use it

Function IsNullEmpty(ctl As Control) As Boolean
   IsNullEmpty = False 'maybe not req'd but guarantees it won't return True if it shouldn't
   If IsNull(ctl) Or Len(ctl & vbNullString) = 0 Then IsNullEmpty = True
End Function

and would call it as in If IsNullEmpty(Me.Ben_B_S_Date) Then


While probably rare, a test for Null will fail if the control contains an empty string, so the function tests for zls as well. Also, your validation may be pointless if the user can still close the form via the form control box X or context menu (right click). Depending on the situation, validating before leaving a record (form BeforeUpdate) or in the Unload event (which can be cancelled) or in some other BeforeUpdate event may be less prone to undesirable results. I have no real issue with doing it in a click event like this, but would remove the control box and prevent a right click on the form if I was truly concerned about validation. You can also put validation at the table field level, which I'd say is more foolproof in some cases. Access will give you the option to cancel the record.

 

[Acropolis]

For things like this, I normally add a tag to fields that need checking of “required” then in the code loop through each object in the form and if it has the tag “required” it then checks for if len(nz(ctl.value, vbnullstring)) = 0 then tell it what to do if it finds one that is null.

For fields that need to be a date or must be a number, I also perform a check to make sure the data is the correct type, check for a date or a integer value etc.

 

[isladogs]

Hi Micron
Here's a slighlty more concise version of your function which also checks for nulls and ZLS:

Function IsNullEmpty(ctl As Control) As Boolean
   IsNullEmpty = False 'maybe not req'd but guarantees it won't return True if it shouldn't
   If Nz(ctl, "") = "" Then IsNullEmpty = True
End Function

 

[Frothingslosh]

Or the even more concise

Function IsNullEmpty(ctl As Control) As Boolean
    IsNullEmpty = (Nz(ctl, "") = "")
End Function

 

[isladogs]

LOL! Damn too short....OK you win

 

[Tor_Fey]

Thanks for this, but i'm unsure how to call this to check my required fields?

Regards
Tor Fey

Or the even more concise

Function IsNullEmpty(ctl As Control) As Boolean
    IsNullEmpty = (Nz(ctl, "") = "")
End Function

 

[isladogs]

Micron explained how to use it in post 6. All 3 versions work in the same way

 

[Micron]

Are you sure that nz on a field with a zero length string will be affected by nz? Not sure but think not. "" is not null. My intention was to check for zls and null.

 

[Frothingslosh]

Nz literally checks to see if the first parameter is null, and replaces it with the second parameter if that is the case. (If there is no second parameter, it actually returns a 0 for numeric data types and a ZLS for the rest.) It does nothing if the first parameter has any value other than null.

So my code does SPECIFICALLY what you're looking for with your function.

Remember that "" *IS* a zero-length string. So Nz checks the default property of ctl (which is Value) and if its value is null, it replaces it with the second parameter, which in this case is a zero-length string. Then the code checks the updated value against "", aka the zero-length string. If they match - meaning that the control's value was EITHER null or a zero length string - it returns TRUE. If they don't, it returns FALSE.

 

[isladogs]

EDIT: I see Frothy replied faster.

That is indeed precisely the point of Nz.
This is from an MS article:

The Nz function is useful for expressions that may include Null values. To force an expression to evaluate to a non-Null value even when it contains a Null value, use the Nz function to return zero, a zero-length string, or a custom return value.

So Nz(somevalue,"") ="" covers both somevalue being null (due to the Nz) and it being a zls

 

[Frothingslosh]

It really is a ridiculously useful function.

If you're used to T-SQL, it has basically the same functionality as T-SQL's ISNULL function.

 

[isladogs]

Its also faster than using alternatives such as Len or Trim
http://www.mendipdatasystems.co.uk/speed-comparison-tests/4594424200

 

[Micron]

What I wrote in last post is true if you think of it, yet there seems to be some disagreement. Perhaps my statement was misunderstood. Basically, I was saying that Nz against a zls will return a zls i.e. Nz("",0) = "" not 0. The part I was missing is that you can simply compare the result of the function to = "" as a shortcut (as was done in the latter part of the expression). No more posting using my phone as soon as I get out of bed! Impressive, Frothingslosh.

 

[isladogs]

Yes of course Nz("",0) will return "" not 0 because the Nz function only acts when an expression is null. As a ZLS is not null it returns a ZLS

Whereas Nz(Null,0) will return 0

 

[Tor_Fey]

I couldn't really get your solutions to work, so instead added the date when the "On click" events of the fields I was trying to validate was selected.

Private Sub TF_Ben_B_S_Click()
If IsNull(TF_Ben_B_S_Date) Then
Me.TF_Ben_B_S_Date.Value = Now()
End If
End Sub

Private Sub TF_Ben_O_P_Click()
If IsNull(TF_Ben_B_S_Date) Then
Me.TF_Ben_B_S_Date.Value = Now()
End If
End Sub

Works a treat, no need for any fancy modules, add the date so the field isn't empty if an amount is entered or the field is selected.

Regards
Tor Fey