Passing parameters in a QueryDef by using prgram code

[mor]

Hi everyone,

A bit stuck on how to execute some code. I have a query that searches for records that are between two dates using the WHERE clause. The two dates are referenced to two respective text boxes on a form. On the same form I have a button that will launch the query in VBA using querydefs. I get the error 3061 saying I need to input the parameters (makes sense). Therefore I am a bit unsure how to set the parameters in VBA. So far I have done this:


CODE \\\

Dim Db As DAO.Database
Set Db = CurrentDb

Dim QDef As DAO.QueryDef
Dim rst As DAO.Recordset

Set QDef = Db.QueryDefs("Rqt_F_BrokerageMandate_MF3_TEST")

' Define the parameters

Dim Param As DAO.Parameter
For Each Param In QDef.Parameters
Debug.Print Param
Next

'QDef.Parameters("Date_VL") >= [Formulaires]![F_BrokerageMandate_Main_Formulaire_TEST]![Fees_Start_Date]
'QDef.Parameters("Date_VL") <= [Formulaires]![F_BrokerageMandate_Main_Formulaire_TEST]![Fees_End_Date]

Set rst = QDef.OpenRecordset(dbOpenDynaset) /// CODE

Where Date_VL is the field to be filtered. I know this is wrong but all examples I have seen have equated the parameter to a fixed value i.e 30/12/2012 for example, but I want this to be at the users discression. The only way I know of to get around this at the moment would be to write a temp query in VBA with PARAMETERS in the SQL code instead using the method above/

Any help would be greatly appreciated.


After this I'm going to assign the recordset to a matrix but that's a different story!

Regards,
MOR

 

[pr2-eugin]

mor, Parameters are the ones you pass into the Query.. The Field names form the left hand side of the Condition to be tested.. In other words Date_VL is the field name not the parameter name..

Let me give an example.. If I have a Query to select all Employees who started between a specific date range.. This specific date range forms the parameter.. So the Query looks like..

SELECT empTbl.empName FROM empTbl
WHERE empTbl.[COLOR=Red][B]startDate[/B][/COLOR] BETWEEN [COLOR=Blue][B][EnterTheStartDate:][/B][/COLOR] AND [COLOR=Blue][B][EnterTheEndDate:][/B][/COLOR];

So when you try to run the Query it will pop up asking you to enter the parameter for [EnterTheStartDate:] and [EnterTheEndDate:].. Make sense? So if you are using VBA to edit Parametrized Queries, you use the name of the parameter..[EnterTheStartDate:] and [EnterTheEndDate:] not startDate

 

[mor]

"The Field names form the left hand side of the Condition to be tested.. In other words Date_VL is the field name not the parameter name". Correct, Date_VL is the field name I want to apply parameters to. These parameters are defined in the query.

I'm not sure I explained myself very well. I have a query called "Rqt_F_BrokerageMandate_MF3_TEST" with SQL as follows..

CODE////

SELECT tblVL.Id_VL, tblVL.Id_Product, tblVL.Date_VL, tblVL.VL
FROM tblVL
WHERE (((tblVL.Date_VL)>=[Formulaires]![F_BrokerageMandate_Main_Formulaire_TEST]![Fees_Start_Date] And (tblVL.Date_VL)<=[Formulaires]![F_BrokerageMandate_Main_Formulaire_TEST]![Fees_End_Date]));

\\\\Code

All I want to do is execute this code, I don''t want to edit it. I take the results from the query and put into a matrix in VBA. The code springs an error asking for parameter values obviously for the fees start date and fees end date. I just want to know how to pass these values from the controls on the form to the vba code so the SQL executes without error.


"So if you are using VBA to edit Parametrized Queries, you use the name of the parameter..[EnterTheStartDate:] and [EnterTheEndDate:] not startDate"

I don't want to edit the parametrized query, and I dont see how to "name" the parameter without as I said using the CreateQuerydef("") and writing the SQL code within VBA. i.e

PARAMETERS ....
SELECT....
WHERE....

then define the parameter this way.

Sorry if I misinterpreted your post but I'm not sure if your reply answers my question. :/

But thank you anyway and if you can clarify how I name the parameters

 

[pr2-eugin]

If you are referencing the parameters from the Form.. Just make sure the Form is kept open.. If the Form is open and you run the Query, you should not have the Enter Parameter Value pop up..

Since you are new to the Site, please have a look at how to use CODE tags..

 

[mor]

If I run the query from a button with an on click event with the code above without parameters, I get the Error 3061, too few parameters: 2 expected.

Dim Db As DAO.Database
Set Db = CurrentDb

Dim QDef As DAO.QueryDef
Dim rst As DAO.Recordset

Set QDef = Db.QueryDefs("Rqt_F_BrokerageMandate_MF3_TEST")

' Define the parameters

Dim Param As DAO.Parameter
For Each Param In QDef.Parameters
Debug.Print Param
Next

Set rst = QDef.OpenRecordset(dbOpenDynaset)

The code defaults on the last line. The form is definitely open with the controls filled in.

 

[pr2-eugin]

This is all that you need..

Dim Db As DAO.Database
Dim rst As DAO.Recordset

Set Db = CurrentDb

Set rst = Db.OpenRecordset("Rqt_F_BrokerageMandate_MF3_TEST")

PLUS make sure the Form which the Query uses i.e. F_BrokerageMandate_Main_Formulaire_TEST is kept OPEN..

 

[mor]

Agreed but generally speaking you need to set warnings off and turn them back on again. Is there not a solution to using the querydefs method?

 

[pr2-eugin]

but generally speaking you need to set warnings off and turn them back on again.

Not unless the Query is a INSERT/DELETE/SELECT INTO/UPDATE Statement.. If it is a normal SELECT Query there would be no need to Turn off the Warnings..

Is there not a solution to using the querydefs method?

I can help you with QueryDefs, but at this point it is quiet unclear of why you wish to use it? As you also seem to mention you do not want to change the Query..

 

[mor]

Not unless the Query is a INSERT/DELETE/SELECT INTO/UPDATE Statement.. If it is a normal SELECT Query there would be no need to Turn off the Warnings.. [\Quote]

Thats why I said generally speaking If I were trying to do the same thing with an INSERT/DELETE/SELECT INTO/UPDATE query I would still have the same problem. I would still get the same error (3061) even if the form is open. So how to I get round it?

I can help you with QueryDefs, but at this point it is quiet unclear of why you wish to use it? As you also seem to mention you do not want to change the Query..

I want to use it as when I do come across the same problem in the future albeit more complex in all likelihood, I won't have to ask around again. Therefore, is it possible to do with the querydefs method by calling the query or will I have to write the sql code in VBA?

Thanks! MOR

 

[PeterF]

You can't run a parameter query from VBA. Construct the query on the fly like:

Dim strSQL as String
Dim Db As DAO.Database
Dim rst As DAO.Recordset

strSQL = "SELECT tblVL.Id_VL, tblVL.Id_Product, tblVL.Date_VL, tblVL.VL FROM tblVL " & _
"WHERE (((tblVL.Date_VL)>=  #" & me.[COLOR="Red"][B]Fees_Start_Date[/B][/COLOR] " & # And (tblVL.Date_VL)<= #" & me.[COLOR="Red"][B]Fees_End_Date[/B][/COLOR] & "#));"

Set Db = CurrentDb
Set rst = Db.OpenRecordset(strSQL)

This is assuming you run your code from the form where the parameters are. The parts in red need to refer to the control on the form.
SQL started from VBA needs the US date format so maybe you need to format the date.

 

[mor]

Thanks Peter Just the answer I wanted!!!