VBA code for "Not IsNull" is not working

[Xarkath]

Hello,

Can I get someone to tell me what I have I have done wrong with this peice of code?

ElseIf Not IsNull(Me.Subscriber_Name) And IsNull(Me.QE_Date) Or Not IsNull(Me.Subscriber_Name) And IsNull(Me.Term_Date) Or Not IsNull(Me.Subscriber_Name) And IsNull(Me.Batch_Date) Or Not IsNull(Me.Subscriber_Name) And IsNull(Me.Vol_Term) Then
MsgBox ("One or more fields in the COBRA Admin section need to be completed.")

I keep getting the message box even when the "Subscriber_Name" is null. I should only get the message box when the "Subscriber_Name" is populated but any one of the other fields was left blank.

Thanks

[aytee111]

Brackets. You can't mix AND's and OR's.

If (statement 1 AND statement 2) OR (statement 3 AND statement 4) ...

A better/cleaner way to do it would be:
IF Not IsNull(Me.Subscriber_Name) AND (A or B or C) Then

[burrina]

I use a different approach.

Dim msg As String, Style As Integer, Title As String
Dim nl As String, ctl As Control




nl = vbNewLine & vbNewLine


For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
If ctl.Tag = "*" And Trim(ctl & "") = "" Then
msg = "Data Required for '" & ctl.Name & "' field!" & nl & _
"You can't save this record until this data is provided!" & nl & _
"Enter the data and try again . . . "
Style = vbCritical + vbOKOnly
Title = "Required Data..."
MsgBox msg, Style, Title
ctl.SetFocus
Cancel = True
Exit For
End If
End If
Next


Ste Tag Property To "(*)"

HTH

[Xarkath]

aytee, I tried to shore it up but I get the same results. It is cleaner so I will be going back to similar code that I have in place.

Dave, That looks like handy peice of code but my VBA skill level is low to understand how to modify for my use. "Me.Controls" would contain 31 fields but in this instance I only need to validate 4 of them based on a 5th being populated. To my untrained eyes the code you posted looks like it would try to validate all 31 fields. That being said I am going to spend some time with it and see if I can't work out the logic it is using to better understand it.

Thank you both for the replies, I appreciate it.



Rob

[aytee111]

There is a difference between Null and blank (""). Your subscriber might be blank which is why the check on "not null" is failing.

Add
AND Me.Subscriber_Name<>""

[burrina]

As you wish, however you only have to add that code to the BeforeUpdateEvent for your form and also the "(*)" to the Tag Property of each field you want to be required.

Good Luck!

[Xarkath]

As you wish, however you only have to add that code to the BeforeUpdateEvent for your form and also the "(*)" to the Tag Property of each field you want to be required.

Good Luck!

That may be the peice I am missing or not understanding. I only want the fields to be required if a subscriber name has been entered. Other wise they should remain blank. I am currently looking at the code and just not understanding how that variable is applied.

I may be totally lost but after reading through the code a few times I think maybe I might need to added another IF statement so that it would like like this:

Dim msg As String, Style As Integer, Title As String
Dim nl As String, ctl As Control

nl = vbNewLine & vbNewLine

if Me.Subcriber_Name <>"" then (or maybe If Not IsNull(Me.Subscriber_Name) then )

For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
If ctl.Tag = "*" And Trim(ctl & "") = "" Then
msg = "Data Required for '" & ctl.Name & "' field!" & nl & _
"You can't save this record until this data is provided!" & nl & _
"Enter the data and try again . . . "
Style = vbCritical + vbOKOnly
Title = "Required Data..."
MsgBox msg, Style, Title
ctl.SetFocus
Cancel = True
Exit For
End If
End If
Next
End If


Am I completely off base here?

Thanks

[burrina]

YES. Can't use both! Did you try aytee111's suggestion? Simplify your criteria: Here is an example; Using the OnCurrentEvent
You could continue on and on.
Also another example;

If Me.Textbox="" or IsNull (Me.Textbox) Then

If Not Me.newrecord = True And IsNull([CustomerID]) Then 'Customer
MsgBox "A Customer is Required"
[CustomerID].SetFocus
Cancel = True
End If

HTH

[Xarkath]

I did try aytee111's suggestion and it worked. However, I have a few sections on the form that need validation to the code you supplied seems like it would be the more effecient option if I can understand how to apply correctly to each section.

In any case, thank you both for the help. It is much appreciated.

[burrina]

Please supply ALL of exact Names of the fields for validation.

[Xarkath]

Hi Dave,

I got it figured out. I had to make a few changes to your code to get what I needed; for example, I removed "If ctl.ControlType = acTextBox Then" because it turns out that the majority of the fields were dates not text and when used for a different kind of validation I had to add "On Error Resume Next" to avoid conflicts with a checkbox.

Anyhow, this is going to be a pretty handy peice of code.

Thanks again for the help.

[burrina]

Glad you got it sorted out. Good Luck With Your Project!