Solved

If statement for VBA Access

Posted on 2008-06-11
3
1,036 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
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…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

749 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