Use a SELECT Query result in VBA

[deweysanchez]

Hi
I am running a select query from VBA (i think) and would like to use the select query result as a variable for something else. This is what I have atm. FYI STAFFDept is a numerical value relating to the ID number in the DEPARTMENTTable.


Dim STAFFDept
Dim stDocName As String
Dim db As Database
Dim rs As Recordset
Dim qdf As QueryDef
STAFFDept = Me!Department

Set db = CurrentDb
Set qdf = db.QueryDefs("STAFFFormDEPTQuery")

qdf.Parameters("WHERE DEPARTMENTTable!ID") = STAFFDept

Set rs = qdf.OpenRecordset

So is there a way of checking if this is working? and a way of using the result for example to show it in a message box, or use it as a definition for something else.

 

[namliam]

For starters..
Dim STAFFDept

Allways declare AS something... dont just leave it like this...

Second
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef

Allways disambiguate, to make sure the database doesnt by mistake make it ADO instead.

Lastly
qdf.Parameters("WHERE DEPARTMENTTable!ID") = STAFFDept

This doesnt work this way... Can you post the SQL as generated in your
STAFFFormDEPTQuery ??

To answer your question...
rs.Movenext will move to the next record (lookup MoveNext in the access help for details)
Also there is movefirst movelast etc...
MsgBox rs!ColumnName
Will show the value of the column in a messagebox... I think that is what your looking for

P.S. Welcome to AWF

 

[deweysanchez]

Thanks for the welcome and the speedy reply

The SQL view for the query shows:

SELECT DEPARTMENTTable.Department AS DEPARTMENTTable_Department
FROM DEPARTMENTTable INNER JOIN STAFFLISTTable ON DEPARTMENTTable.ID = STAFFLISTTable.Department
GROUP BY DEPARTMENTTable.Department, DEPARTMENTTable.ID, STAFFLISTTable.Department
HAVING ((([WHERE DEPARTMENTTable]![ID])=[STAFFLISTTable]![Department]));

When you run the query seperatly you're asked to Enter Parameter value WHERE DEPARTMENTTable!ID.

 

[deweysanchez]

Scratch my last reply, using the suggestions you made it is all working fine. Below is the working code for anyone following the thread.

Thanks Again

Private Sub STAFFDeptCommand_Click()

Dim STAFFDept As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
STAFFDept = Me!Department

Set db = CurrentDb
Set qdf = db.QueryDefs("STAFFFormDEPTQuery")
qdf.Parameters("WHERE DEPARTMENTTable!ID") = STAFFDept
Set rs = qdf.OpenRecordset

MsgBox rs!DEPARTMENTTable_Department

End Sub

 

[Banana]

Well, still, I do not think the Parameter is working the way it should be. It may give you the results but quite inefficiently because it's not used in its intended fashion.

Normally, a parameter query looks like this:

PARAMETERS lSTAFFDept INT;
SELECT DEPARTMENTTable.Department AS DEPARTMENTTable_Department
FROM DEPARTMENTTable INNER JOIN STAFFLISTTable ON DEPARTMENTTable.ID = STAFFLISTTable.Department
WHERE [STAFFLISTTable]![Department] = [lSTAFFDept]
GROUP BY DEPARTMENTTable.Department, DEPARTMENTTable.ID, STAFFLISTTable.Department;

.Parameters("lSTAFFDept") = STAFFDept

 

[namliam]

Banana is right... Your query looks a little odd...
In the query design, please dont use "Where"

Also common acronyms for tables: tbl
tblDepartment
Queries: qry
qryDepartment
Forms: frm
frmDepartment

IF you get used to that you will save yourselve some headaches ...