Error 3265. Item not found in this collection

[Mike Collard]

I have a query with a parameter that references a form; I
need to update the underlying table on which the query is
based and have been trying to create some code to do so.

First of all I got an error 'Too few parameters. Expected
1' which is apparently caused because the query references
a form. I modified the query temporarily so that it
didn't reference the form and it worked but that is not an
acceptable solution so I have modified my code as below
but I now get an error 'Item not found in this collection'

Sub Test()

Dim dbsA As Database, rstA As Recordset, qdfA As QueryDef

Set dbsA = CurrentDb
Set qdfA = dbsA.QueryDefs("Pricing Test")
qdfA![Forms]![Pricing Screen]![Site_id] = [Forms]![Pricing
Screen]![Site_id]
Set rstA = dbsA.OpenRecordset("Pricing Test",
dbOpenDynaset)

End Sub

Any help appreciated.

Mike Collard

 

[TC]

I have a query with a parameter that references a form; I
need to update the underlying table on which the query is
based and have been trying to create some code to do so.

First of all I got an error 'Too few parameters. Expected
1' which is apparently caused because the query references
a form. I modified the query temporarily so that it
didn't reference the form and it worked but that is not an
acceptable solution

That's not the cause of that error. The cause is, you SQL statement had a
syntax error. In changing the statement, you removed the source of error.

so I have modified my code as below
but I now get an error 'Item not found in this collection'
(snip)

Set dbsA = CurrentDb Fine.

Set qdfA = dbsA.QueryDefs("Pricing Test") Fine.

qdfA![Forms]![Pricing Screen]![Site_id] = [Forms]![Pricing

Screen]![Site_id]
No. The only format that looks like: qdfA![xxx] = ..., is where the
specified querydef deines a parameter called "xxx", and you are assigning a
value to that parameter. Check online help for the PARAMETER(S?) statement.

Set rstA = dbsA.OpenRecordset("Pricing Test", dbOpenDynaset)

That's ok - but it is nothing to do with the querydef! (So it will work, or
not work, regardless of qdfA).

If you want to open a recordset for the actual data returned by the
querydef:

set rstA = qdfA.openrecordset

HTH,
TC

 

[TC]

No. The only format that looks like: qdfA![xxx] = ..., is where

I meant:

The only format that looks like qdfA dot anything is:

qdfA.Parameters![xxx] = ...,

This is where ...