DoCmd.OpenQuery to Test Search Results

[dusterb]

I have a database of magazine articles and I use a "Search" form for the user to build a custom sql select query, kind of a complicated one with lots of joins. The results get passed to a "Search Results" form.

DoCmd.OpenForm "ArticleSearchResultsForm", , , , , , varSQL

Before I pass the results and open the results form, I'd like to first test for any results. If there isn't anything, throw up a MsgBox and exit sub. I was thinking something like this:

If IsNothing(DoCmd.OpenQuery(varSQL)) Then
MsgBox "Sorry, no Articles were found", vbInformation, gstrAppTitle
Exit Sub
End If

varSQL is the complete select query as a string built by the module out of the user's form selections.

Obviously this isn't right though. I guess DoCmd.OpenQuery is looking for an actual saved query. I read one post about having the module write out the dynamic query into the database, run it, and then delete it. Is that the route to go? Any other maybe simpler solutions?

 

[Darrylles]

Hi duster,

Have a look at DCount.

I use :
[tt]
If DCount("Dupes", "qryGetDuplicatedUpdateParts") > 0 Then
msg user
else
carry on
endif
[/tt]
This passes the number of records into "Dupes" that the query returns.
The following may work for you...
[tt]
If DCount("Recs", DoCmd.OpenQuery(varSQL)) = 0 Then
msgbox "Sorry ..........."
endif
[/tt]

ATB

Darrylle


Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk

 

[dusterb]

Hi Darrylle,

Thanks, good tip. Wrapping the query with DCount seems much cleaner than my IsNothing function (which probably wouldn't have worked anyway), but I still have the basic problem of DoCmd.OpenQuery(varSQL) not being the correct syntax where varSQL is a string variable, an sql select query.

Duster

 

[Darrylles]

Hi duster,

Sorry, I can't see a mention in your post that you HAD a problem with syntax.

However, whenever replacing a string constant (with quotes) with a variable, then you need only use the variable name, forget the quotes and/or brackets.....

Use:
DoCmd.OpenQuery varSQL
instead of:
DoCmd.OpenQuery (varSQL)
or instead of:
DoCmd.OpenQuery "select * from mytable;"

Also use:
If DCount("Recs", DoCmd.OpenQuery varSQL) = 0 Then
instead of
If DCount("Recs", DoCmd.OpenQuery(varSQL)) = 0 Then

If you still have syntax problems, then try pasting the SQL into the query builder and seeing if it interprets it correctly there. (With complex dynamic SQL, I get hold of the actual SQL string by displaying the variable content in a textbox on a form and copying it at run-time).

Hope this helps,

ATB

Darrylle






Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk