Solved

If statement for VBA Access

Posted on 2008-06-11
3
1,023 Views
Last Modified: 2013-11-28
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!
0
Comment
Question by:Dingo212
3 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 150 total points
ID: 21762737
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
 
LVL 10

Expert Comment

by:therealmongoose
ID: 21762753
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
 
LVL 14

Assisted Solution

by:JohnK813
JohnK813 earned 150 total points
ID: 21762758
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now