proper use of querydefs

[ryan_eng]

Hi all,

I'm trying to open a query in VB with some criteria. I tried setting the sql
statement as follows:

"SELECT * FROM qry_Hours WHERE [Emp_ID] = " & Employee & " AND [Date] > #" &
start_date & "# AND [Date] < #" & end_date & "#"

Employee, start_date and end_date already have values based on an open form.
(yes I know I shouldn't be using a field called "date")

When I use this sql statement in db.openrecordset(qry_sql,dbopenforwardonly)
I get the dreaded 3061 'too few parameters, expected 1' error.

After reading a lot of other posts and other stuff on msdn, It seems that I
can't have a WHERE clause in the sql because the parameters need to be
resolved before being passed to Jet. Apparently I need to set the parameters
in querydefs but I haven't found a clear syntax for that. This is the current
code being used, if you could tear it apart and tell me whats wrong that
would be awesome.

Dim db As DAO.Database
Dim rst_qry As DAO.Recordset
Dim qry_sql as string

qry_sql = "**see above**"
Set rst_qry = db.openrecordset(qry_sql,dbopenforwardonly)

Thanks!
RY

 

[ryan_eng]

OK, no need to panic, I got it working with the following code:

Dim rst_qry As DAO.Recordset
Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = CurrentDb
qry_sql = "SELECT * FROM qry_Hours WHERE [EmpID] = " & Employee & " AND
[Date] >= #" & start_date & "# AND [Date] <= #" & end_date & "#"
Set qdf = db.CreateQueryDef("", qry_sql)
Set rst_qry = qdf.OpenRecordset

But I'm still interested in those parameter query things, if anyone would
like to educate me.

RY

 

['69 Camaro]

Hi, Ryan.

But I'm still interested in those parameter query things, if anyone would
like to educate me.

Your code needs to instantiate a QueryDef object and a Parameter object for
each parameter, then assign a value to each Parameter before opening the
record set. For example, say you have a parameter query named qry_Hours,
with a parameter named MyParam, such as the following:

PARAMETERS MyParam Text ( 255 );
SELECT *
FROM tblEmpHrs
WHERE (Dept = MyParam);

.. . . and you want to use it in VBA. Then you could use a button on a form
to initiate the process, like this:

Private Sub EmpHrsBtn_Click()

On Error GoTo ErrHandler

Dim rst_qry As DAO.Recordset
Dim param As DAO.Parameter
Dim qry As QueryDef
Dim qry_sql As String
Dim fOpenedRecSet As Boolean

Set qry = CurrentDb().QueryDefs("qry_Hours")
Set param = qry.Parameters!MyParam
qry.Parameters("MyParam") = "Sales"

qry_sql = "SELECT * FROM qry_Hours WHERE [Emp_ID] = " & _
Employee & " AND [Date] > #" & start_date & _
"# AND [Date] < #" & end_date & "#"

Set rst_qry = qry.OpenRecordset
fOpenedRecSet = True

' Do whatever with the rec set.

CleanUp:

If (fOpenedRecSet) Then
rst_qry.Close
fOpenedRecSet = False
End If

Set rst_qry = Nothing
Set param = Nothing
Set qry = Nothing

Exit Sub

ErrHandler:

MsgBox "Error in EmpHrsBtn_Click( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
GoTo CleanUp

End Sub ' EmpHrsBtn_Click( )

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
See http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

OK, no need to panic, I got it working with the following code:

Dim rst_qry As DAO.Recordset
Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = CurrentDb
qry_sql = "SELECT * FROM qry_Hours WHERE [EmpID] = " & Employee & " AND
[Date] >= #" & start_date & "# AND [Date] <= #" & end_date & "#"
Set qdf = db.CreateQueryDef("", qry_sql)
Set rst_qry = qdf.OpenRecordset

But I'm still interested in those parameter query things, if anyone would
like to educate me.

RY