Trying to detect when a query has no records using a Macro (1 Viewer)

Umpire

Member
Local time
Yesterday, 21:12
Joined
Mar 24, 2020
Messages
101
NOOB question here.
I have an equipment testing log database that uses a Short Text field for Serial Number (Some serial numbers have characters in them).
I have a query that uses the Criteria of [Enter Serial Number] for the Serial Number field.
If the Serial number exists it display a form with every time that serial number was tested etc.
If the entered serial number is NOT in the database it just opens a blank form.

What I am trying to do is use the DCount function in an IF statement to prevent the blank form from opening and instead display a message box saying there were no records.
The problem is I cannot get DCount to work. When I type is as shown on the attached picture, I get an unable to parse error.
Serial Number is the field I am looking at. QRY-Serial Number Look Up is the query that I use to find the records associated with a particular serial number.
 

Attachments

  • Dcount.PNG
    Dcount.PNG
    17.7 KB · Views: 54

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:12
Joined
Oct 29, 2018
Messages
15,548
Hi. Check out the syntax for DCount(). It goes something like this.

If DCount("*", "QueryName") > 0 Then
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 23:12
Joined
Feb 28, 2001
Messages
20,224
The problem is that macros represent actions from the ribbon. There is no ribbon-based action that would do a DCount. You need VBA to do this testing rather than a macro. This next is "air code" to show you generally what the VBA might look like.

Code:
If DCOUNT( fill-in-the-blanks-here) > 0 THEN
    open the form
Else
    MSGBOX "No records match the serial number", vbOKOnly, "Cannot open form."
End If
 

Umpire

Member
Local time
Yesterday, 21:12
Joined
Mar 24, 2020
Messages
101
Hi. Check out the syntax for DCount(). It goes something like this.

If DCount("*", "QueryName") > 0 Then
Lets see if I am reading this syntax correctly.

Count all records found in the results of the named query, regardless of which field contains data. (The "*" wildcard.) If that count is greater than 0 then open the form. If that count is not greater than 0, then follow whatever the "Else" statement says to do.

So rather than checking for information in a single field, like I was trying to do, I rely on the query to do that. All I care about is if the query returns anything and if it does, open the form to display what it returned.

Does that sound right?
 

Umpire

Member
Local time
Yesterday, 21:12
Joined
Mar 24, 2020
Messages
101
The problem is that macros represent actions from the ribbon. There is no ribbon-based action that would do a DCount. You need VBA to do this testing rather than a macro. This next is "air code" to show you generally what the VBA might look like.

Code:
If DCOUNT( fill-in-the-blanks-here) > 0 THEN
    open the form
Else
    MSGBOX "No records match the serial number", vbOKOnly, "Cannot open form."
End If
OK. Then why does the Macro allow me to insert the DCount command? Is that just Access being Access? It seems strange to me that they would allow a command to be listed that can not run.

I would need to write a Module (?) with the code you listed, (with pertinent information filled in) and then on the Click On event function for my button, run the module?

Can you point me to an explanation of the vbOKOnly, "Cannot open form." section of the code?
 

Umpire

Member
Local time
Yesterday, 21:12
Joined
Mar 24, 2020
Messages
101
Sounds right to me.
Except I found another problem. I wrote code and it tried to work but failed because I am using a parameter Query and apparently DCount can not be used for a parameter query.

So I am starting to understand what need to do. I just am having a hard time figuring it out with only Access help files, weak Google searches (because don't know what to search for) and this site.

I sure wish there was a good book on how to do this. Heck, at this point even a weak one would help
 

Isaac

Lifelong Learner
Local time
Yesterday, 21:12
Joined
Mar 14, 2017
Messages
5,380
Except I found another problem. I wrote code and it tried to work but failed because I am using a parameter Query and apparently DCount can not be used for a parameter query.

So I am starting to understand what need to do. I just am having a hard time figuring it out with only Access help files, weak Google searches (because don't know what to search for) and this site.

I sure wish there was a good book on how to do this. Heck, at this point even a weak one would help
One thing you can do is get rid of the parameter query. Leave only the "bare bones" of the query - whatever it is without the parameters.
Let's say there were 2 parameters.

Then do a Dcount on the query as it is now, and specify those criteria inside the Dcount.

If the columns were text:
Dcount("*","QueryName","Column1='" & SomeVariable & "' and Column2='" & SomeVariable2 & "'")
If the columns were date:
Dcount("*","QueryName","Column1=#" & format(SomeDateVariable,"mm/dd/yyyy") & "# and Column2=#" & format(SomeDateVariable2,"mm/dd/yyyy") & "#")
If the columns were number:
Dcount("*","QueryName","Column1=" & SomeVariable & " and Column2=" & SomeVariable2)
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:12
Joined
Oct 29, 2018
Messages
15,548
Hi. A parameter query prompts the user for input. Since you want to use code, that would bypass the prompt for the query. So, if you need user input, you will have to do it another way. For example, create your own prompt and leave the query out of that business.
 

Users who are viewing this thread

Top Bottom