Running Update Query in VBA fails

  • Thread starter Thread starter Peter Hibbs
  • Start date Start date
P

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.
 
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
 
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.
 
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.
Click to expand...

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.
 
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.
Click to expand...

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.
 
Back
Top