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

Umpire

Member
Local time
Today, 12:14
Joined
Mar 24, 2020
Messages
120
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: 401

theDBguy

I’m here to help
Staff member
Local time
Today, 12:14
Joined
Oct 29, 2018
Messages
21,358
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
Today, 14:14
Joined
Feb 28, 2001
Messages
26,999
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
Today, 12:14
Joined
Mar 24, 2020
Messages
120
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
Today, 12:14
Joined
Mar 24, 2020
Messages
120
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
Today, 12:14
Joined
Mar 24, 2020
Messages
120
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
Today, 12:14
Joined
Mar 14, 2017
Messages
8,738
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
Today, 12:14
Joined
Oct 29, 2018
Messages
21,358
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.
 

bikermo

Registered User.
Local time
Today, 12:14
Joined
Jan 8, 2013
Messages
38
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?
a little late to the party here but I just found a solution to this. As I typed the DCount function into my macro it prompted me for its parts: DCount(expression, domain, [criteria]) so I searched for more information on the function and found the format necessary to get it to work (DCount Function).
My macro is checking whether the count of Field1 in my table is >0. the syntax is:
DCount("[Field1]","Table1","Field1>0")
 

Users who are viewing this thread

Top Bottom