Solved

If statement for VBA Access

Posted on 2008-06-11
3
1,027 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Create tables in access db (2016)  using vba 13 42
Exporting Access Tables as CSV 3 24
Filter a form 8 15
Opening forms with an input box 5 22
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

803 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