If statement for VBA Access

Hello,

I have 3 boxes/fields in a search form. I can perform a search by filling out 1 field, 2 fields or all 3 of them.

The fields are

1 combo box (I can select a country, say Canada and get the search filtering all Canadian records)
2 text boxes (I can type in a keyword to get the search boxes brings in results too)

Now if I don't type/choose in anything at all from these 3 fields, my program kinda crashes/goes in to debug.

What I want is few lines of code to check if anything is entered (using AND) and if not I want a message displayed saying something like "you must have at least 1 criteria to search"

And then stop there.

what I have so far is this but I think I am wrong here considering I did little VB coding way back.

If IsNull(txtSearchField1) AND isNull(txtSearchField2) AND IsNull(cboSearchField1)  Then
MsgBox "you must have at least 1 criteria to search"
Else
<<I believe my rest of the search code goes here?>>
End If

Thanks for the help!
Dingo212Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
mbizupConnect With a Mentor Commented:
Your code looks okay, assuming nothing has been entered at all (typing and then back spacing will result in an empty string, not Null).  Try this, which checks for both nulls and empty strings:

If NZ(txtSearchField1,"") = "" AND NZ(txtSearchField2,"") = ""  AND NZ(txtSearchField3,"") = ""   Then

If that fails, post the exact line of code that the debugger highlights, and the exact error message you get.
0
 
therealmongooseCommented:
Your code looks fine... Doe's this give a debug error? If so what is it.

You can force a default for a combo box by using the following on the on open event of your form:

cboSearchField1 = cboSearchField1.column(0,0)

This sets the combo box to the first value in it's list and therefore prevents your user doing an invalid search....



0
 
JohnK813Connect With a Mentor Commented:
This should work for you.

1. You have to take into account those times when the fields are left blank but aren't null.  I replaced your IsNull's with the Nz.  Nz works in this way:
Nz(if this is null, replace it with this)
Here, you would replace Null values with "", which is basically a blank string (and the same thing as the field being blank but not null).  So that will catch both null and blank values.

2.  I removed the Else clause and added the Exit Sub.  If you're using a Function instead of a Sub, you'll need to say Exit Function.  Exit Sub completely exits out of the Sub, meaning that Access will ignore any code below the Exit Sub line.  That way, the user can go back and enter their search criteria and try the search again.
Of course, if they entered search criteria initially, the If statement will be false.  That means it will skip the Exit Sub statement and go right into your search code.
If Nz(txtSearchField1,"")="" AND Nz(txtSearchField2,"")="" AND NZ(cboSearchField1,"")=)  Then
  MsgBox "you must have at least 1 criteria to search"
  Exit Sub
End If
<<rest of the search code goes here>>

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.