Check ALL fields for null

S

Shel

Scenario: The code below checks all the fields in an unbound form for null
values. If all the fields are null then a message is given "please enter
search criteria or click cancel." Otherwise it runs the rest of my code
which opens another form based on the search criteria given.

Problem: The “If†statement below is too long. VBA won’t let the statement
wrap. This means that the last few fields won’t be checked for null.

What I want: Code to check that search criteria has been entered into AT
LEAST one field. If that is not the case (If all fields are null) then
display the message. Otherwise run the rest of the code. I am guessing that
there is probably another cleaner/shorter way of checking all of the fields
for null or data. I have tried *If Me.Dirty Then* but that does not give me
the results I am looking for either. Please HELP!

**********CODE************

If (Eval("[Forms]![empfrmLocateTaxPayer]![txtTxPrID] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtSSN] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtFName] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtMidInit] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtLName] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtAddress] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtCity] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtState] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtZip] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtEmployerID] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtRoutingNo] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtAccountNo] Is Null And
[Forms]![empfrmLocateTaxPayer]![chkProtester] Is Null And
[Forms]![empfrmLocateTaxPayer]![chkDeceased] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtComments] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtAddUser] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtAddDateBegin] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtAddDateEnd] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtUpdateUser] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtUpdateDateBegin] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtUpdateDateEnd] Is Null")) Then
Beep
MsgBox "Please enter search criteria or click Cancel", vbInformation, "No
Search Criteria"
DoCmd.CancelEvent
Else
*run other code*
End If

**********END CODE*********
 
D

Dirk Goldgar

Shel said:
Scenario: The code below checks all the fields in an unbound form for
null values. If all the fields are null then a message is given
"please enter search criteria or click cancel." Otherwise it runs
the rest of my code which opens another form based on the search
criteria given.

Problem: The "If" statement below is too long. VBA won't let the
statement wrap. This means that the last few fields won't be checked
for null.

What I want: Code to check that search criteria has been entered into
AT LEAST one field. If that is not the case (If all fields are null)
then display the message. Otherwise run the rest of the code. I am
guessing that there is probably another cleaner/shorter way of
checking all of the fields for null or data. I have tried *If
Me.Dirty Then* but that does not give me the results I am looking for
either. Please HELP!

**********CODE************

If (Eval("[Forms]![empfrmLocateTaxPayer]![txtTxPrID] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtSSN] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtFName] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtMidInit] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtLName] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtAddress] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtCity] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtState] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtZip] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtEmployerID] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtRoutingNo] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtAccountNo] Is Null And
[Forms]![empfrmLocateTaxPayer]![chkProtester] Is Null And
[Forms]![empfrmLocateTaxPayer]![chkDeceased] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtComments] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtAddUser] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtAddDateBegin] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtAddDateEnd] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtUpdateUser] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtUpdateDateBegin] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtUpdateDateEnd] Is Null")) Then
Beep
MsgBox "Please enter search criteria or click Cancel", vbInformation,
"No Search Criteria"
DoCmd.CancelEvent
Else
*run other code*
End If

**********END CODE*********
Click to expand...

Air code:

Dim ctl As Access.Control
Dim fGotOne As Boolean

For Each ctl In Me.Controls

If ctl.ControlType = acTextBox _
Or ctl.ControlType = acCheckBox _
Or ctl.ControlType = acComboBox _
Or ctl.ControlType = acListBox _
Then
If Not IsNull(ctl.Value) Then
fGotOne = True
Exit For
End If
End If

Next ctl

If fGotOne Then
' ... run code ...
Else
DoCmd.Beep
MsgBox _
"Please enter search criteria or click Cancel", _
vbInformation, _
"No Search Criteria"
DoCmd.CancelEvent
End If
 
S

Shel

Thank you so VERY VERY much!

Dirk Goldgar said:
Shel said:
Scenario: The code below checks all the fields in an unbound form for
null values. If all the fields are null then a message is given
"please enter search criteria or click cancel." Otherwise it runs
the rest of my code which opens another form based on the search
criteria given.

Problem: The "If" statement below is too long. VBA won't let the
statement wrap. This means that the last few fields won't be checked
for null.

What I want: Code to check that search criteria has been entered into
AT LEAST one field. If that is not the case (If all fields are null)
then display the message. Otherwise run the rest of the code. I am
guessing that there is probably another cleaner/shorter way of
checking all of the fields for null or data. I have tried *If
Me.Dirty Then* but that does not give me the results I am looking for
either. Please HELP!

**********CODE************

If (Eval("[Forms]![empfrmLocateTaxPayer]![txtTxPrID] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtSSN] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtFName] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtMidInit] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtLName] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtAddress] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtCity] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtState] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtZip] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtEmployerID] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtRoutingNo] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtAccountNo] Is Null And
[Forms]![empfrmLocateTaxPayer]![chkProtester] Is Null And
[Forms]![empfrmLocateTaxPayer]![chkDeceased] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtComments] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtAddUser] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtAddDateBegin] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtAddDateEnd] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtUpdateUser] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtUpdateDateBegin] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtUpdateDateEnd] Is Null")) Then
Beep
MsgBox "Please enter search criteria or click Cancel", vbInformation,
"No Search Criteria"
DoCmd.CancelEvent
Else
*run other code*
End If

**********END CODE*********
Click to expand...

Air code:

Dim ctl As Access.Control
Dim fGotOne As Boolean

For Each ctl In Me.Controls

If ctl.ControlType = acTextBox _
Or ctl.ControlType = acCheckBox _
Or ctl.ControlType = acComboBox _
Or ctl.ControlType = acListBox _
Then
If Not IsNull(ctl.Value) Then
fGotOne = True
Exit For
End If
End If

Next ctl

If fGotOne Then
' ... run code ...
Else
DoCmd.Beep
MsgBox _
"Please enter search criteria or click Cancel", _
vbInformation, _
"No Search Criteria"
DoCmd.CancelEvent
End If


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Code Problem 6
Multi fields search form 1
Calculations with possible null value fields 1
Validate visible fields only. 3
Flexible query 1
How to check continuous form fields 3
counting Null fields 3
Finding null 4

Top