Database Engine error when attempting to use a form control in a query

[atrium]

I have included the form control [Forms]![AddMatterFrm]![MatterIdFld] in the criteria of the first of three queries.
1st Query - with the form control isolates transactions relative to the contents of the form variable (coming from an open Form)

The second query is a XTAB query further processing and summarising the transaction.

The 3rd one adds another expression field.

I execute the 3rd Query using the command below

Set rstDebtTrans3Qry = db.OpenRecordset("DebtTrans3Qry")

I then get the ....Database Engine does not recognise [Forms]![AddMatterFrm]![MatterIdFld] as a valid field name or expression.

If I take the control reference out and hard wire the value I want - there is no problem.
I'm sure it has something to do with the XTAB query but I need a solution.

Can anyone help please

Thank you

 

[JHB]

In the below link it is a descriptor how to do it.
https://support.microsoft.com/en-us/kb/209778?wa=wsignin1.0

 

[atrium]

I have had a look at your link and
I have 4 queries, it's the first query where I have the variable

[Forms]![AddMatterFrm]![MatterIdFld]

Used to isolate all transactions relative to the [MatterIdFld]

The 2nd query manipulates some numeric columns
The 3rd query is the XTAB which is were the problem seems to be.

I have placed the PARAMETERS [Forms]![AddMatterFrm]![MatterIdFld] Short; line in the first query.


Still get a run time error - this time it is 3061 Too few parameters, Expected 0.

?????

 

[CJ_London]

your AddMatterFrm form does need to be open and MatterIdFld needs to have a value.

I'm also pretty sure with Xtabs, you need to have your criteria as a separate 'where' column rather than against one of the row/column/value columns

 

[atrium]

The AddMatterFrm is open and the control MatterIdFld has the right value.

I tried having the AddMatterFrm open and the criteria set [Forms]![AddMatterFrm]![MatterIdFld] in the 1st query and ran the 4 queries individually

The 1st query ran with out a problem, so did the 2nd, so did the 3rd (XTAB) and so did the 4th.
But when I hang them all together and refer to them as

 Dim db As DAO.Database
 Set db = CurrentDb
 Dim rstDebtTrans3Qry As DAO.Recordset
 Set rstDebtTrans3Qry = db.OpenRecordset("DebtTrans3Qry")

I get the errors and the debug stopps on the last line above.

Run Time error - 3061
Too Few parameters. Expected 0.


?????

 

[atrium]

I have reduced the queries down to two

One - the Cross Tab - where I need to insert the form control variable [Forms].[AddMatterId].[MatterIdFld] to isolate the transactions relative to the Matter displayed on the open form [AddMatterFrm] and

Two - Another query that uses the two columns created by the CrossTab to make a summary column.

The second query DebtTrans3Qry uses the output of the Cross Tab query DebtTrans2XTABQry to create the desired result.

The DebtTrans2XTABQry has the parameter set up and I still get the error in the previous reply.

I have been looking at QueryDefs
When I open the recordset etc I include DebtTrans3Qry and not sure how I can put in the query def because the variable being defined is related to the source of DebtTrans3Qry.

I must be as thick as two planks - I just can't see the light

 

[spikepl]

I have been looking at QueryDefs
When I open the recordset etc I include DebtTrans3Qry and not sure how I can put in the query def because the variable being defined is related to the source of DebtTrans3Qry.

The above is just a lot of not unambiguous digital arm waving.

You need to show the actual code. The composite query has parameters, and it does not care in which specific query the parameter or parameters were buried, the setting of them is the same. You set the value of a parameter like this (replace parameter name by yours, note you could just at weel call it @Param1 or whatever, in the query and here):

qdfMyQueryDef.Parameters("[Forms]![frmLogTraining]![txtStatusDate]") = Me.txtStatusDate

 

[atrium]

I'm assuming from your comment "..The above is just a lot of not unambiguous digital arm waving..." That I'm not explaining my problem clearly. If so - point taken and I apologise for that.

This is where I set up the record set

     Dim db As DAO.Database
    Dim rstDebtTrans3Qry As DAO.Recordset
    Set db = CurrentDb
    Set rstDebtTrans3Qry = db.OpenRecordset("DebtTrans3Qry")

do I now add the code below to what I have (above)

 rstDebtTrans3Qry.Parameters("[Forms]![AddMatterFrm]![MatterIdFld]") = Me.MatterIdFld

Thanks

 

[Galaxiom]

Dim qdf As QueryDef
Dim rs As DAO.Recordset

    Set qdf = CurrentDb.QueryDefs("queryname")
    qdf.Parameters("parameter") = "whatever"
    Set rs = qdf.OpenRecordset

 

[atrium]

Thank you everyone for giving me your advise.

I solved the problem with

     Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    
    Dim rstDebtTrans3Qry As DAO.Recordset
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("DebtTrans3Qry")
    qdf.Parameters("[Forms]![AddMatterFrm]![MatterIdFld]") = Me.MatterIdFld
    Set rstDebtTrans3Qry = qdf.OpenRecordset()

Thanks again