Run query from code

[jfrizelle]

Hi. I want to do a query as part of validation, and am not sure how to do it. To explain - I have a Training_Hdr form, and a Training_Dtl subform. The Training_Hdr record shows, among other things, Course_Id and Course_Date. The related Training_Dtl record shows Emp_Id, Test_Score, etc.

Basically, when I am adding a Training_Dtl record, I want to query previous records to see if that employee passed that course in the previous 2 years. I have created a query using Emp_Id from the Training_Dtl record, and Course_Id and Course_Date from the Training_Hdr record. However, when I use the Docmd.RunSQL command to run this query - DoCmd.RunSQL ("qry_Employee_Passed_Course&quot - I get an error msg : Runtime error 3129 - Invalid SQL statement; expected Insert, Select... etc.

Having looked around for help, I've also tried the following :

Dim cnn As ADODB.Connection
Dim rs As New ADODB.Recordset
Set cnn = CurrentProject.Connection
rs.Open "qry_Employee_Passed_Course", cnn

but then I get Runtime error -2147217900 - Invalid SQL statement; expected Insert, Select... etc.

All I want to do is see are there any qualifying records - I don't want to see the result of the query, just a simple yes/no to 'Were there any records selected based on these criteria?'

Thanks in advance for any pointers.
Jennie.

 

[vbajock]

DoCmd.RunSQL "qryName" is an invalid command, should be docmd.openquery "qryName".

 

[MakeItSo]

You could use DCount instead:

Dim NumOfRecs as Long

NumOfRecs=DCount("[RecID]","qry_Employee_Passed_Course&quot

This will only return the number of results of the query. No records.

Is that what you need?

MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[MakeItSo]

P.S: replace RecID with a valid field name from that query.

 

[jfrizelle]

MakeItSo - that's exactly what I need. I've been trying all sorts of complicated stuff and all the time the answer was *so* simple.

Thank you so much!

Jennie.

 

[MakeItSo]

Anytime again. ;-)