Capture empty query

[Lightenup]

How can I run a query but stop it from opening if it is empty/has no records?


Dim stDocName As String
stDocName = "qryDesc"
DoCmd.OpenQuery stDocName, acViewNormal, acReadOnly

Thanks for the help.
Lightenup

 

[JoanneM]

hello there

This may help - fldname should actually be field that will be returned should any records be available

dim recnum as integer
recnum = dcount("fldname","qryname&quot
if recnum = 0 then
' skipquery
else
' put alternative action here
endif


just checked it out to make sure - s'OK

regards

JO

 

[Lightenup]

Thanks JO. Here's what I did (see code below). The problem I ran into is that "qryDesc" is a parameter query so no value is being passed to it and I get this error: "The expression you entered as a query parameter produced this error: "The object doesn't contain the Automation object 'Enter Description or *."

I looked up the DCount help reference and see that it has a criteria argument. How can I put the criteria from my query into this line of code? I deleted the criteria from qryDesc and tried: recnum = DCount("description", "qryDesc", Description Like "*" & [Enter Description or *] & "*&quot in the code but the system didn't recognize it.

Since I deleted the criteria from the qryDesc how can I pass it back to it after the check?

Sure appreciate your help.

L

Dim recnum As Integer
recnum = DCount("description", "qryDesc&quot
If recnum = 0 Then
MsgBox "There is nothing here for you."
Exit Sub
' skipquery
Else
Dim stDocName As String
stDocName = "qryDesc"
DoCmd.OpenQuery stDocName, acViewNormal, acReadOnly
' put alternative action here
End If

 

[JoanneM]

I see what you are getting at now

I'll go ponder another method & get back to you


Jo

 

[Lightenup]

JO!
The code below works to pass criteria into the check but I still don't know how to pass that same criteria to the query using the DoCmd.OpenQuery when records are > 0.

One step at a time gets you there.

Have a wonderful weekend. Thanks again.
L

Dim sHold As String
Dim recnum As Integer
sHold = InputBox("Enter a key word.&quot

recnum = DCount("description", "qryDesc", "description like '" & "*" & sHold & "*" & "'&quot

If recnum = 0 Then
MsgBox "There is nothing here for you."
Exit Sub
Else: Dim stDocName As String
stDocName = "qryDesc"
DoCmd.OpenQuery stDocName, acViewNormal, acReadOnly
End If

 

[JoanneM]

Well done

I'm tippling nicely for Friday so I think I will take your advice and have a nice weekend but first the final solution to your dilemma

copy your present query and rename it qryDesc2
in the field where you had the parameter put

= ReturnedValue()
create a new module
in the declarations section of a new module type

public ParamToQuery as string


function returnedvalue()as variant
returnedvalue = paramtoquery
end function






Your code

Dim sHold As String
Dim recnum As Integer
sHold = InputBox("Enter a key word.&quot

recnum = DCount("description", "qryDesc", "description like '" & "*" & sHold & "*" & "'&quot

If recnum = 0 Then
MsgBox "There is nothing here for you."
Exit Sub
Else: Dim stDocName As String
ParamToQuery = "*" & sHold & "*"
stDocName = "qryDesc2"
DoCmd.OpenQuery stDocName, acViewNormal, acReadOnly
End If

regards and nice working with you


jo

 

[Lightenup]

Good Morning, Jo

I tried the code as is and the function picks up the variable but I have an empty query (should be 476 records according to recnum.) When I put in a stop I found that the docmd.openquery runs THEN it goes to the function so I get an empty query. I tried putting a call to returnedvalue between 'stDocName = "qryDesc2"' and DoCmd.OpenQuery and then it went to the function before and after the OpenQuery but the query is still empty. I also tried opening the query in design mode, called the function then opened the query in normal mode and still got an empty query. The recnum shows 476 records should be coming in.

Where to next, I know we're in the right direction.
L

 

[JoanneM]

lets go a little deeper may be my logic was lubricated with my wine last friday

copy the function below over your old function
this way you can see what is being passed


function returnedvalue()as variant
debug.print paramtoquery
returnedvalue = paramtoquery
debug.print "RV: ",returnedvalue
end function


step into the function and read the values in the immediate window CTRL+G

Thanks for your patience.

regards
JO

 

[Lightenup]

Hey Jo!

Hallelujah! I figured it out. My paramtoquery value was absolute correct but I was still getting an empty query back. I went back and played with the original query and realized in the qryDesc2 we weren't passing a "Like" criteria definer as we were in the code 'recnum' criteria to the original query.

I tried passing it in code in the ParamToQuery = "*" & sHold & "*" string. It produced "Like "*search*" as the string. What the query wants to see is Like "*search*" when it's all done. No " in front of the word Like. I couldn't strip off the " in front in code. However, by changing my criteria in qryDesc2 itself to: Like ReturnedValue(), the rest of the code works like a champ.

I just love it when a plan comes together. It's been a pleasure working this through with you. I don't understand 'Functions' at all. Is there a book you would recommend that would help me better understand them and other advanced code? Or perhaps I could just do a down load from your sharp mind.

L

 

[JoanneM]

My silly way of learning subs & functions was a little childish but has never failed me

sub routines are like a bag of mixed sweets

It can show you the value of each sweet,
its properties,
soft centre, chocolate coated

but it can't share them around ( meanie)
a public variable can be a representation of them but only for the liftime of the routine ( you gotta be quick)

where as a function
can share anything about them - provided you have made sure its the right type of function
a flavour type function can pass the value coffee cream but not chocolate coated, alternatively if you declared a user defined type of
'Sweetie'
with a coating property
flavour property
centre property

that function could have you munching all night.

Quirky allegory I know but how else do you explain functions to a chocaholic

for the more professional guys out there this is not an in depth working of subs & functions but it made me remember if I wanted to share something it had to be functional otherwise you call on the SUBS[\COLOR]

 

[Lightenup]

For anyone interested in using this solution here's a wrap up with the code simplified.

In the module:
Public ParamToQuery As String

Function returnedvalue() As Variant
returnedvalue = ParamToQuery
End Function

In the query: (Place the code below in the criteria line under the field you're using to refine the search)

Like ReturnedValue()

In the form: (I simplified this so it uses only one query)
Dim sHold As String
Dim recnum As Integer
sHold = InputBox("Enter a key word.&quot
ParamToQuery = "*" & sHold & "*"
recnum = DCount("description", "qryDesc&quot

If recnum = 0 Then ' skipquery
MsgBox "There is nothing here for you."
Exit Sub
Else: Dim stDocName As String ' put alternative action here
stDocName = "qryDesc"
DoCmd.OpenQuery stDocName, acViewNormal, acReadOnly
End If

Thanks again, Jo.
L