How to test if parameter select query has returnd records?

[easyit]

The query is rather big so I would like to keep it in the query designpane instead of building it up in VBA. SoI have a existing query with parameters and I need to test in VBA (pref. ADO) if it has any records. Seems simple, but I don't know how to solve this. Anyone with ideas?

EasyIT

 

[JerryKlmns]

Open a recordset based on that query and check for .EOF & .BOF property of the rscordset. When both true there is nothing there....

 

[easyit]

Jerry, thanks, but I know how to test. The problem is that I get an eror because it is a parameter query.

The error is

-2147217900 Er is een ongeldige SQL-instructie gevonden. DELETE, INSERT, PROCEDURE, SELECT of UPDATE werd verwacht.

(This roughly translates to "not valid sql instruction expected DELETE etc.")

EasyIT

 

[RoyVidar]

It's a bit hard giving suggestions on specific methods with this lack of information/code.

You will need to resolve the parameters, and probably also specify the correct commandtypeenum (adCmdStoredProc?).

Perhapsh you will find some information here thread709-819033, thread705-941731, faq709-1526.

Roy-Vidar

 

[easyit]

Roy,

Thank you for the response. Sorry for not giving more info, thought the subject would say it all...

From thread

http://www.tek-tips.com/viewthread.cfm?qid=941731

I took:

dim cmd as adodb.command
dim rs as adodb.recordset
MillSection = "WDUS"
set cmd=new adodb.command
with cmd
  .activeconnection=currentproject.connection
  .properties("Jet OLEDB:Stored Query")=true
  .commandtext="bbk_MainQueryByState"
  .parameters("[Mill Section]") = MillSection
  set rs = .execute
end with
do while not rs.eof
  debug.print "FacilityID=" & rs("FacilityID").value
  rs.movenext
loop

Which works very well for me. I was actually hoping to solve this without using the parameters in VBA, but it doesn't seem possible.

EasyIT

 

[abaldwin]

I dont see how you plan on executing a Query that requires a Parameter to be entered without using the parameters in VBA. Am I loosing my mind or are you hard coding the mill section in the query?

Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[easyit]

..I have a form that has three combo boxes (currently 7, 3 and 2 items). The form spills out a maximum of 7x3x2 = 42 excel sheets. The combo's are the parameters of the query. I don't want empty sheets hence the test. I can skip the test of course will work also.