QueryDef Parameters

[LB79]

Hi All,

Id like to ask a couple of questions about QueryDef parameters. Im looking blankly at a few help sheets online…

The first is how do I set the parameter? The below is what I've been working with but its not working

[SIZE=3][COLOR=#000080][FONT=Arial]Dim db As DAO.Database[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Set db = CurrentDb[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Dim QDF As DAO.QueryDef[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Set QDF = db.QueryDefs("qry_MyQRY")[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Dim SAISQL As String[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]QDF.Parameters("MyPara") = [Forms]![frm_Main]![M_subfrm].[Form].[S_subfrm].[Form].[lst1].Value[/FONT][/COLOR][/SIZE]

The second is how di I refer to the parameter in the SQL? I have the below SQL for example, but how do I include the parameter?

[SIZE=3][COLOR=#000080][FONT=Arial]SELECT tbl_MyTbl.Field1[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]FROM tbl_MyTbl[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]WHERE (((tbl_MyTbl.Field1)=[Forms]![frm_MyForm]![subfrm].[Form]![cbo2])); [/FONT][FONT=Wingdings][FONT=Wingdings]ß[/FONT][/FONT][FONT=Arial]How do I set this to read the parameter?[/FONT][/COLOR][/SIZE]

I hope ive made sense here – Thanks for looking!

 

[MarkK]

What does it mean that it is "not working?" Do you get a wrong result, error, ???

If I had SQL like ...

SELECT * FROM SomeTable WHERE ID = [prmID]

...then the [prmID] is a parameter. If you execute this query without supplying that parameter it will open a prompt requersting a value for [prmID].
In code you can create the query and supply the parameter as follows ...

dim qdf as dao.querydef
set qdf = currentdb.creatquerydef( _
  "qTestQuery", _
  "SELECT * FROM SomeTable WHERE ID = [prmID]")
qdf.parameters("prmID") = Forms!SomeForm.SomeControl

Finally, you might want to open a recordset ...

dim rst as dao.recordset
set rst = qdf.openrecordset

But for simplicity I far more commonly construct the SQL on the fly and open a recordset--or execute an action query--directly without creating the querydef. Code would look like ...

dim rst as dao.recordset
set rst = currentdb.openrecordset( _
  "SELECT * FROM SomeTable WHERE ID = " & _
  Forms!SomeForm.SomeControl)

So just the two lines of code.
Does that help clarify?
Cheers,

 

[LB79]

Thanks for the advice... but Ive fallen at the first hurdle.
When I run the code it debugs at Set QDF "Run time 3265 - Item not found in this collection". The lstbox is there. Should this be obvious?

Thanks

Dim db As DAO.Database
Set db = CurrentDb
Dim QDF As DAO.QueryDef
Set QDF = db.QueryDefs("MyQry")
QDF.Parameters("MyPara") = [Forms]![frm_Main]![MN_subfrm].[Form].[SB_subfrm].[Form].[lst2]

 

[namliam]

Set QDF = db.QueryDefs("MyQry")

That line rigth? that isnt about any listbox, it is about your "MyQry" not existing

 

[LB79]

Yeah - Just checked. The query exists... If I take out the parameter it works. Just when I add the parameter it debugs on the QDF.Parameter line...

 

[DCrake]

The problem you have got is that even if you get it to work this query will be reliant on the form being open and the field being populated. However if you tackle the problem as outlined Here you will be able to make the query dynamic to all forms.

 

[LB79]

Thanks DCrake… This query can only run when the form is open and a selection is made so I think that will be OK.
It seems lots of people on the net are having this same problem… I just cant find one with a solution yet…

 

[DCrake]

QDF.Parameters("MyPara") = [Forms]![frm_Main]![MN_subfrm].[Form].[SB_subfrm].[Form].[lst2]

Don't know what is in the control lst2 however this should read

QDF.Parameters("[" & MyPara & "]") = [Forms]![frm_Main]![MN_subfrm].[Form].[SB_subfrm].[Form].[lst2]

What are you attempting to achieve?

 

[LB79]

Hi DCrake,
Just tried the revision and still debugs in the same place.
The goal is that I want to export data directly to an Excel template (ive done this with a few other things and it work as I want).
I have several listbox that a user selects options from. Then when the click a cmd button, it does its things.
This is setup as a querydef and I want a querydef parameter for each listbox value, which I can then include in my SQL that transfers to the query.
But it debugs on the QDF.Parameters line with that run time error.
Thanks

 

[DCrake]

What is the expected SQL suposed to look like once complete?

 

[DCrake]

Looking at a query that I have that uses hardcoded parameters the sql looks like this

PARAMETERS [Select Period] Text ( 255 );
SELECT ......

 

[LB79]

The expected SQL will look like

SELECT IIf([SUB_AGENT]='120GA','120GA',[REPORTING_OFFICE]) AS Agent, tbl_REF_RMOPIC.[Agency Name] AS Agency, Right([REPORTING_MONTH],4) & Left([REPORTING_MONTH],2) AS RepMnth, Right([COST_MONTH],4) & Left([COST_MONTH],2) AS CstMnth, tbl_REF_Month2.FY, tbl_REF_Month2.[FY_QTR (QQ)] AS QTR, tbl_REF_EstWorld.Item_Code AS Item, tbl_PLD_PLDefine.PL_DEFINE AS [Item Type], tbl_REF_EstWorld.LINE_NAME AS Trade, tbl_REF_EstWorld.LINE AS Line, tbl_REF_EstWorld.PORT AS Port, tbl_REF_EstWorld.VESSEL AS VSL, tbl_REF_EstWorld.VOYAGE AS VOY, Format([SAILING_DATE],'DD/MM/YYYY') AS [Date], tbl_REF_EstWorld.CURRENCY AS CURR, IIf([Current_Est]=[Pre_est],[Current_est],0) AS [Current]
FROM ((tbl_REF_EstWorld LEFT JOIN tbl_PLD_PLDefine ON tbl_REF_EstWorld.Item_Code = tbl_PLD_PLDefine.ItemCode) LEFT JOIN tbl_REF_RMOPIC ON tbl_REF_EstWorld.REPORTING_OFFICE = tbl_REF_RMOPIC.[AG Code]) LEFT JOIN tbl_REF_Month2 ON tbl_REF_EstWorld.COST_MONTH = tbl_REF_Month2.MonthMMCCYY;

With a WHERE clause in showing WHERE ID = [Reporting_Office] but I havent added that in yet cause I cant get past the Debug yet...

 

[DCrake]

SELECT IIf([SUB_AGENT]='120GA','120GA',[REPORTING_OFFICE]) AS Agent, tbl_REF_RMOPIC.[Agency Name] AS Agency, Right([REPORTING_MONTH],4) & Left([REPORTING_MONTH],2) AS RepMnth, Right([COST_MONTH],4) & Left([COST_MONTH],2) AS CstMnth, tbl_REF_Month2.FY, tbl_REF_Month2.[FY_QTR (QQ)] AS QTR, tbl_REF_EstWorld.Item_Code AS Item, tbl_PLD_PLDefine.PL_DEFINE AS [Item Type], tbl_REF_EstWorld.LINE_NAME AS Trade, tbl_REF_EstWorld.LINE AS Line, tbl_REF_EstWorld.PORT AS Port, tbl_REF_EstWorld.VESSEL AS VSL, tbl_REF_EstWorld.VOYAGE AS VOY, Format([SAILING_DATE],'DD/MM/YYYY') AS [Date], tbl_REF_EstWorld.CURRENCY AS CURR, IIf([Current_Est]=[Pre_est],[Current_est],0) AS [Current]
FROM ((tbl_REF_EstWorld LEFT JOIN tbl_PLD_PLDefine ON tbl_REF_EstWorld.Item_Code = tbl_PLD_PLDefine.ItemCode) LEFT JOIN tbl_REF_RMOPIC ON tbl_REF_EstWorld.REPORTING_OFFICE = tbl_REF_RMOPIC.[AG Code]) LEFT JOIN tbl_REF_Month2 ON tbl_REF_EstWorld.COST_MONTH = tbl_REF_Month2.MonthMMCCYY;

Firstly using words such as Currency and Date will cause problems as they are Access reserved words.

What error are you encountering so far?
If you copy this into the SQL pane of a query and then change the view to datasheet, do it throw an error?

 

[LB79]

Its not that SQL thats causing the error. This works. The problem I have is in VBA where the querydef is created. When I try to set the parameter it debugs with Run time 3265 - Item not found in this collection

 

[DCrake]

Have you got a field called ID or a field called REPORTING_OFFICE in your query def?

The item not in collection is saying that you are trying to apply a condition on a non existant field. My feeling it has to do the IIF() in the select statement.

Shoud it not be where ID = Agent

 

[MarkK]

If you get an 'item not found in this collection' error on this line ...

Set qdf = db.QueryDefs("MyQry")

... then 'MyQry' doesn't exist in the database 'db'. The collection being referenced is the QueryDefs collection of the Database object 'db' and the item 'MyQry' is not present. If this query was recently created you may need to do ...

db.QueryDefs.Refresh

... which forces DAO to rebuild the collection. This is a costly operation so DAO doesn't do it regularly.

 

[LB79]

Thanks for all the advice guys.
The query isnt a new one but I added the refresh anyway and still get the same error.
The field is reporting_office not ID. and as there are 2 offices with the same code (but a differnt sub code), I added the iif statement to deal with it.