Passing parameters to a query again (1 Viewer)

[AndyS48UK]

Hi

I've attached a little code...

"qryDespatch" has a parameter "p1".

When I run this code I get an error which is a data type conversion error - runtime error 3421 so the parameter is expected as a different type. However the query is fine when I run it based on the form so I'm obviously not passing the parameter correctly.

Anyone have any ideas where I'm going wrong?

The error comes up on the "set fred" line.

Private Sub Report_Open(Cancel As Integer)

Dim dbs As Database
Set dbs = CurrentDb

p1 = Forms("frmDespatchChoice").cmbOrderNoFilter.Value

Set fred = dbs.OpenRecordset("qryDespatch", "PARAMETERS ORDER_NUMBER" & p1)

DoCmd.GoToRecord acDataQuery, "qryDespatch", acFirst

Do
n = fred.QtyDespNoCarr + fred.qtyDelNoCarr
MsgBox n
DoCmd.GoToRecord acDataQuery, "qryDespatch", acNext
Loop


End Sub

Thanks

Andy

 

[Pat Hartman]

This is how you pass parameters to a query using DAO:

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qd As DAO.QueryDef
Set dbs = CurrentDb
Set qd = dbs.QueryDefs!qryQCMainSizeCheck
qd.Parameters![YourParm1] = "SomeValue"
qd.Parameters![YourParm2] = "SomeValue"
Set rst = qd.OpenRecordset

If you need to run an action query, you can use the Execute Method rather than the OpenRecordset Method.

 

[AndyS48UK]

????

I'm obviously being incredibly stupid. Having asked twice about passing parameters using VBA I've now spent a couple of days (yes lterally!) trying to figure out where I'm going wrong. I've used Pat's (thanks Pat) example religiously and also gone back to a previous reply using the Northwinds database.

To test it out I ran a test query based on a single table containing StockCatId and StockCat as fields.

so, adapting Pat's code...

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qd As DAO.QueryDef
Set dbs = CurrentDb
Set qd = dbs.QueryDefs!qryTest 'name of the query
qd.Parameters![StockCatID] = "1"
Set rst = qd.OpenRecordset

The StockCatID is an AutoID. There IS a "1" there - I checked by running the code without the parameter - no probs.

But...a msg comes up of Run time error 3265 - Item not found in this collection. Click "debug" and your pointed to the line with the parameter coding. Presumably we're talking about the QueryDefs colection here? But qryTest is a valid query.

I've read at length about parameters but it seems to be the norm to use sql to do this. I need to dynamically refresh the query based on user choices and the only way to do this effectively is by using VB.

Thanks

Andy

 

[Pat Hartman]

Since StockCatID is numeric, you need to remove the quotes:

qd.Parameters![StockCatID] = 1

 

[AndyS48UK]

Yeah I did try that, Pat. Thanks for the thought. I also changed the the parameter to "StockCat" and "Carriage" in case it was getting confused with the available records but I still get the same error.

I'll go and try and look back at the help files on all the key words and see if I can find out what I'm doing wrong.

Thanks for your time.

 

[Jon K]

"........
qd.Parameters![StockCatID] = "1"
.........
But...a msg comes up of Run time error 3265 - Item not found in this collection. Click "debug" and your pointed to the line with the parameter coding."

Access is telling you it can't find parameter StockCatID. Check its spelling in the query.

 

[AndyS48UK]

Thanks folks