Running Update Query in VBA fails

[Peter Hibbs]

Access 2003.

I have this Update query called qryTopSalesUpdate :-

UPDATE (tblCompany INNER JOIN tblContacts ON tblCompany.ID =
tblContacts.ID) INNER JOIN tblOrders ON tblCompany.ID = tblOrders.ID
SET tblCompany.Category = "3"
WHERE (((tblCompany.Category) Like
[Forms]![frmMailShot]![txtTopCategory]) AND
((tblContacts.TeleCalls)<>"Invalid") AND ((tblOrders.InvoiceNum) Is
Not Null) AND ((tblOrders.OrderDate) Between
[Forms]![frmMailShot]![txtStartDate] And
[Forms]![frmMailShot]![txtEndDate]));

which sets the Category Text field to '3' for the given criteria. If I
open the form and set up the criteria fields (i.e. txtTopCategory,
txtStartDate and txtEndDate) and then run the update query from the
database window it runs correctly (with the usual warning messages).

If, however, I use the following code :-

CurrentDb.Execute "qryTopSalesUpdate"

to run the query from the form with the Text fields (which is my aim)
I get this error message :-

Run time error '3061'.
Too few parameters. Expected 3.

Anyone know why and how to fix it?

Peter Hibbs.

 

[Douglas J. Steele]

Unfortunately, when you use the Execute method, you need to explicitly
resolve your parameters.

Try the following:


Dim qdfUpdate As DAO.QueryDef
Dim prmCurr As DAO.Parameter

Set qdfUpdate = CurrentDb("qryTopSalesUpdate")
For Each prmCurr in qdfUpdate.Parameters
prmCurr.Value = Eval(prmCurr.Name)
Next prmCurr
qdfUpdate.Execute

 

[Peter Hibbs]

Hi Doug,

Sorry, but when I run your code I get this error on the line :-
Set qdfUpdate....

Run-time error '3265'.
Item not found in this collections.

Any other suggestions?

Peter Hibbs.

 

[pietlinden]

Hi Doug,

Sorry, but when I run your code I get this error on the line :-
    Set qdfUpdate....

Run-time error '3265'.
Item not found in this collections.

Any other suggestions?

Peter Hibbs.

How about ...

Set qdfUpdate = CurrentDb.Querydefs("qryTopSalesUpdate")

You have to specify the collection the object is in, otherwise the
database engine can't find it.

 

[Peter Hibbs]

Piet,

Thank you, that works OK now.

Peter Hibbs.

 

[Douglas J. Steele]

Oops. Typed too quickly. Thanks, Piet.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Doug,

Sorry, but when I run your code I get this error on the line :-
Set qdfUpdate....

Run-time error '3265'.
Item not found in this collections.

Any other suggestions?

Peter Hibbs.

How about ...

Set qdfUpdate = CurrentDb.Querydefs("qryTopSalesUpdate")

You have to specify the collection the object is in, otherwise the
database engine can't find it.