[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

If statement for VBA Access

Posted on 2008-06-11
3
Medium Priority
?
1,044 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 600 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 600 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

834 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