How to pass Parameter Value in Query via VBA

[bullfrog83]

I have a saved query (q_StdDegReqAll) that has one parameter (WHERE StdDegReq.StdDegReqId = lngStdDegReqId). I want to pass a value into this parameter in vba. I have the following code:

Dim lngStdDegReqId As Long
Dim qd1 As DAO.QueryDef
 
Set qd1 = CurrentDb.QueryDefs("q_StdDegReqAll")
qd1.Parameters(lngStdDegReqId) = lngStdDegReqId
 
DoCmd.OpenQuery "q_StdDegReqAll", , "acReadOnly"
 

When I execute the code I get the error: "Item not found in this collection". It's referring to my 4th line. I do not know what I am doing wrong.

[Jim Doherty]

@bullfrog83
Yes.

qd1.Parameters(lngStdDegReqId) = lngStdDegReqId

Passing lngStdDegReqId unto itself? where is lngStdDegReqId defined value

[bullfrog83]

Sorry. It's defined at the beginning of the routine where a value is passed into it:

Public Sub procEvaluateDegReq(lngStdDegReqId As Long)

[NeoPa]

This is a tricky area. Parameters in a Recordset are transitory. They do not get saved with any QueryDef you may be accessing in your Recordset.

Your line #5 (I assume to be the one that fails) would only set the parameter for use by qd1. The subsequent call to open the QueryDef would not have any reference to the parameter in qd1. Does that make sense?

[NeoPa]

Sorry. It's defined at the beginning of the routine where a value is passed into it:

Public Sub procEvaluateDegReq(lngStdDegReqId As Long)

I believe Jim was referring to the name of the QueryDef's parameter.

Do you have the SQL for that to view?

[bullfrog83]

@NeoPa
I'm assuming you mean the SQL to the query? If so, here it is:

SELECT StdDegReq.StdDegReqId, Cl.StdDegReqDiscId AS DiscId, D.SortOrder AS DiscSortOrder, D.CreditMin AS DiscCreditMin, D.CourseMin AS DiscCourseMin, Cr.StdDegReqClassId AS ClassId, Cl.SortOrder AS ClassSortOrder, Cl.CreditMin AS ClassCreditMin, Cl.CourseMin AS ClassCourseMin, Cr.StdDegReqCourseId AS CourseId, Cr.Course, Cr.SortOrder AS CourseSortOrder, T.Event_Id AS TakenCourse, T.Credit_Grade
FROM (((StdDegReq INNER JOIN StdDegReqDisc AS D ON StdDegReq.StdDegReqId=D.StdDegReqId) INNER JOIN StdDegReqClass AS Cl ON D.StdDegReqDiscId=Cl.StdDegReqDiscId) INNER JOIN StdDegReqCourse AS Cr ON Cl.StdDegReqClassId=Cr.StdDegReqClassId) LEFT JOIN t_TranscriptDetail AS T ON Cr.TakenCourseId=T.TranscriptDetailId
WHERE StdDegReq.StdDegReqId=lngStdDegReqId
ORDER BY D.SortOrder, Cl.SortOrder, Cr.SortOrder;

[bullfrog83]

@NeoPa
I think so. So, is there not a way to pass a parameter value into a query via vba?

[NeoPa]

I see no parameters in your query at all. This would certainly explain the error message.

[NeoPa]

I think so. So, is there not a way to pass a parameter value into a query via vba?

If I read you aright (Query here referring to a QueryDef opened in data view on the screen as opposed to into a recordset in your code), then no. i don't believe there is. Something I always thought was a strange omission if I'm honest.

[bullfrog83]

@NeoPa
The parameter is lngStdDegReqId in the WHERE clause. When I execute the query it prompts me to enter a value for it.

[NeoPa]

You need to use the PARAMETERS declaration. You can find details in the help system under Microsoft Jet SQL Reference.

PARAMETERS name datatype [, name datatype [, ...]]

[Jim Doherty]

@bullfrog83
Sorry....... have been absent and very busy last couple of days.....

OK you are using the DoCmd.OpenQuery statement. The arguments for that action do not include a capability ie an 'argument' in itself to pass parameters (as would appear logical, given parameters do tend to go hand in hand with queries) I agree with Neopa in that, it is somewhat lacking in that area. It is not like the OpenForm method for instance where you can pass an sql string as a WHERE clause or a filter or openargs for a form.

The DoCmd.OpenQuery command is designed to open a saved query but as you know many queries have parameters attached to them. The general rule of thumb if you want to open a query using the parameters AS IS is to open a recordset in code by reference to the QUERYDEFS collection. You then manipulate any dataset using recordset code. An example of this would be as follows:

Dim MyDb as DAO.Database
Dim Myqdf as DAO.QueryDef
Dim rst as DAO.Recordset
Dim MyQueryName As String
Dim strSurname as String
 
MyQueryName = "qryPeople"
strSurname="Lennon"
 
set MyDb =CurrentDb()
Set Myqdf = MyDb.QueryDefs(MyQueryName)
qdf.parameters("Surname")=strSurname
'Open a recordset on this query
set rst=Myqdf.OpenRecordset()
Do While Not rst.EOF
 
'your code sequence goes here
Loop
 
rst.Close
Myqdf.Close
MyDB.Close
rst=Nothing
Myqdf=Nothing
Mydb=Nothing

The other method is to build your SQL on the fly in your form code assigning elements for any WHERE clause and then use this to set the recordsource of a form.

If you merely want to use and open the the query itself you might want to consider using a function to pass back values to your parameter query. Your form code could set the criteria and pass this to a global variable. A Function could pick up on that global variable and pass the value to the query. The below function can be pasted into a new module

Global MY_INTEGER As Integer
Global MY_LONG As Long
Global MY_STRING As String
Global MY_BOOLEAN As Boolean
 
Function GetMyInteger() As Integer
GetMyInteger = MY_INTEGER
End Function
Function GetMyString() As Integer
GetMyString = MY_STRING
End Function
Function GetMyBoolean() As Integer
GetMyBoolean = MY_BOOLEAN
End Function

To implement the above functions you would merely replace your 'square bracketed' parameters in your query in the query grid and instead use a function call ie:

GetMyLongInteger()

(Remember you are explicitly setting the value of your global variable from your form code) so in your particular specific example case, your current DoCmd.Openquery command program flow could still be used

MY_LONG=7
DoCmd.OpenQuery "q_StdDegReqAll", , "acReadOnly"

So your current query amended SQL would look like this...

SELECT StdDegReq.StdDegReqId, Cl.StdDegReqDiscId AS DiscId, D.SortOrder AS DiscSortOrder, D.CreditMin AS DiscCreditMin, D.CourseMin AS DiscCourseMin, Cr.StdDegReqClassId AS ClassId, Cl.SortOrder AS ClassSortOrder, Cl.CreditMin AS ClassCreditMin, Cl.CourseMin AS ClassCourseMin, Cr.StdDegReqCourseId AS CourseId, Cr.Course, Cr.SortOrder AS CourseSortOrder, T.Event_Id AS TakenCourse, T.Credit_Grade 
FROM (((StdDegReq INNER JOIN StdDegReqDisc AS D ON StdDegReq.StdDegReqId=D.StdDegReqId) INNER JOIN StdDegReqClass AS Cl ON D.StdDegReqDiscId=Cl.StdDegReqDiscId) INNER JOIN StdDegReqCourse AS Cr ON Cl.StdDegReqClassId=Cr.StdDegReqClassId) LEFT JOIN t_TranscriptDetail AS T ON Cr.TakenCourseId=T.TranscriptDetailId 
WHERE StdDegReq.StdDegReqId=GetMyLongInteger()
ORDER BY D.SortOrder, Cl.SortOrder, Cr.SortOrder; 

Hope this helps you a little and yes I know this is workaround material :)

[DataAnalyzer]

When assigning parameters, you need to refer to the parameter name

I think your your line:

qd1.Parameters(lngStdDegReqId) = lngStdDegReqId

should be

qd1.Parameters("StdDegReqId") = lngStdDegReqId

Here's a good resource for queries including VBA code for parameters:
http://www.fmsinc.com/tpapers/querie...ningParameters

[bullfrog83]

@Jim Doherty
Thanks for the workarounds! I decided to just type my sql in code. Hopefully, Microsoft will improve on the .OpenQuery command in a future release and allow an argument to pass parameters. Definitely a serious omission.