@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 :)
124
