Not opening empty queries

[funwithaccess]

Hi,

I am creating a "knowledgebase" for my company. Each table will contain records with hyperlinks that point to a PDF or other file. Currently, I have 1 search field on a form that also contains 5 combo boxes and 16 hyperlink buttons.

My question is regarding the search field. I have it set up to query multiple tables using: Like "*" & [Forms]![Start Here]!

& "*" in the corresponding queries. The search field works, however, it opens all of the queries, not just the queries with searched records. Is there a way to prevent opening queries that return no search results? A macro or VBA code?

The macro for the search field to run the queries is as follows:
Open Query
Query Name HD Manual Query
View Datasheet
Data Mode Read Only

Thank you in advance for your assistance!

Nate​

 

[GinaWhipp]

Hmm, with some VBA, yes, (I don't use Macros), something like...

If DCount("FieldThatWouldHaveData", "YourQuery") > 0 Then
   DoCmd.OpenQuery "YourQuery"
End If

 

[funwithaccess]

Hi Gina,

Thank you for your prompt reply! The code for the macro is as follows (not sure if it will show up correctly or not):

Private Sub Find_Click()
On Error GoTo Find_Click_Err

DoCmd.OpenQuery "HD Manual Query", acViewNormal, acReadOnly
DoCmd.OpenQuery "SDDocumentation Query", acViewNormal, acReadOnly
DoCmd.OpenQuery "Xerox Assets Query", acViewNormal, acReadOnly


Find_Click_Exit:
Exit Sub

Find_Click_Err:
MsgBox Error$
Resume Find_Click_Exit

End Sub

I need to follow a better naming convention for not only the tables and queries but also the commands. The button for the text box is "Find" and the text box is called "Heading". For "FieldThatWouldHaveData" what field is that referring to? The textbox on the form? Or the field in the query that is being searched?

Thank you so much for your help!!

 

[funwithaccess]

Ok, so I figured out what field you are referring to, and it is working except for when I query the Xerox table. I believe that the DCount is throwing an error because the field that it is searching is IP numbers that have 4 octets separated by decimal points. Any idea as to how I can make it work? This is a vital part of the database.

Thanks again!

Edit: The IP field is set as Short Text in the table.

 

[funwithaccess]

I wrapped the text for the IP field in the DCount and it worked perfectly!

I couldn't have done it without you, Gina! Thank you!

Edit: This is the resulted code:

If DCount("[IP Address]", "Xerox Assets Query") > 0 Then

 

[GinaWhipp]

WELL, I finally get back to this thread and I see you figured it out! Glad to help!

 

[funwithaccess]

I'm back!! I would like to expand my previous question to include having multiple field names. In other words, how do I have the following DCount include multiple (short text) field names, not just [IP Address]?

If DCount("[IP Address]", "Xerox Assets Query") > 0 Then
DoCmd.OpenQuery "Xerox Assets Query", acViewNormal, acReadOnly
End If

 

[GinaWhipp]

If I am understanding you correctly... You don't you write seperate queries.

 

[funwithaccess]

I created 2 more queries, it works, however, all 3 queries pop up even though only one of the fields was queried. Not sure if that makes sense... I am hoping to be able to have only one query pop up. The other queries that have nothing to with the Xerox tables do not open if the text in the text box has nothing to do with them, which is exactly what I want and was my original question in my post. Now I'd like it if the Xerox queries reacted the same way. I'm apologize if this comes across as convoluted haha.

If DCount("[Asset Number]", "Xerox Assets Query") > 0 Then
DoCmd.OpenQuery "Xerox Assets Query", acViewNormal, acReadOnly
End If

If DCount("[IP Address]", "Xerox IP Query") > 0 Then
DoCmd.OpenQuery "Xerox IP Query", acViewNormal, acReadOnly
End If

If DCount("[Serial Number]", "Xerox Serial Query") > 0 Then
DoCmd.OpenQuery "Xerox Serial Query", acViewNormal, acReadOnly
End If

EDIT: It did not work, I stand corrected. It only works when I query the IP, it does not work when I query the Asset Number or the Serial Number. Nothing happens.

 

[GinaWhipp]

They would if they are all in the same Event Procedure AND they all have data. You would need to put them in the Event Procedure that pertains to what you are asking for.

 

[funwithaccess]

Yes!!! I totally forgot to set the Event Procedure!! Gina, you are a machine! The queries are working!

 

[GinaWhipp]

No problem... Glad to help!