Search code examples
sqldatabasems-accesssearch-form

How to make an if statement to stop access from running query?

I'm using Microsoft access and I want to make an if statement that basically does:

if the search criteria are all blank, then open a message box that says "something I write" and an OK button. The ok would throw you back to the search from and not run query.

My issue is that if all of the search criteria are left blank and someone hits search, it'll crash access. So I wanna make something that'll stop someone from running the query in blank. I am using a form with 6 different criteria's called Standards, Duds, ID, Desc1, Desc2, and Excel.

Just to let everyone know too, I am pretty stupid when it comes to coding and stuff so if you could spell it out in a way a 4 year old could understand that'd be great.

I would like something that reads:

If [Forms]![Search]![Standards] AND [Forms]![Search]![CADID] is blank then

MsgBox("You cant do this") - and then this would send you back to the form

ElseIf

Run the query normally.

Currently my code looks a bit like:

SELECT DISTINCT Standards.Name, Standards.[Catalog Id], - then a bunch of other tables and their respective columns

WHERE(((Standards.Name)Like"*"& [Forms]![Search]![Standards] & "*") AND ((Standards.[Catalog ID]) Like "*" & [Forms]![Search]![CADID] & "*"));

I dont know why it ends there. Where it reads [Standards] and [CADID] after WHERE are the text boxes a user and write in a form.let me know how much more detail you need.

Solution

  • Not sure what your search code is, but here's a template to work with. Put this in the On-click event of the button that executes your search and make sure you add whatever code executes your search in place of the comment I've left in the else part of the If statement below.

    If _
    (IsNull([Forms]![Search]![Standards]) Or _
    [Forms]![Search]![Standards] = "") And _
    (IsNull([Forms]![Search]![CADID]) Or _
    [Forms]![Search]![CADID]) = "" _
Then

    MsgBox "Please complete both Standards and CADID fields before searching", vbCritical Or vbOKOnly, "Search Error"

Else

    ' your search code here

End If