troubleshooting Question

Executing an update query using VBA code

Avatar of BugHugger
BugHuggerFlag for United States of America asked on
Microsoft Access
5 Comments1 Solution198 ViewsLast Modified:
I'm a bit of a novice and I'm having a hard time getting something to work.

I have a form with dropdowns.  Once the dropdowns are selected I need an update query to use the selections in the dropdowns to update some cells in some tables.  Some of the dropdowns on the form feed the WHERE criteria, and some feed the "Update To" part.

I've built a separate update query that works great.  But trying to embed the query or trying to execute the query in VBA code (via an On Click sub for a button on the form) results in a slew of different errors.  The most common is a 3601:

"Run-time error '3061'. Too few parameters. Expected 1."

This didn't work:

CurrentDb.Execute "MyUpdateQuery"

Also didn't work:  

 DoCmd.RunSQL  "UPDATE Results INNER JOIN SampleDetails ON (Results.Test = SampleDetails.Test) AND (Results.SampleNumber = SampleDetails.SampleNumber) AND (Results.OrderID = SampleDetails.OrderID) SET SampleDetails.SampleDetails_User4 = Date() & "--" & BEUser(), Results.Results_User1 = [Forms]![Rework]![ReworkCombo], Results.Results_User2 = [Forms]![Rework]![InitiatorCombo], Results.Results_User3 = [Forms]![Rework]![ReasonTextBox]" _
& "WHERE (((SampleDetails.SampleNumber)=[Forms]![Rework]![SampleNumCombo]) AND ((SampleDetails.Test)=[Forms]![Rework]![TestCombo]) AND ((Results.Param)=[Forms]![Rework]![ParamCombo]));"

My update query works great.  It's:

UPDATE Results INNER JOIN SampleDetails ON (Results.Test = SampleDetails.Test) AND (Results.SampleNumber = SampleDetails.SampleNumber) AND (Results.OrderID = SampleDetails.OrderID) SET SampleDetails.SampleDetails_User4 = Date() & "--" & BEUser(), Results.Results_User1 = [Forms]![Rework]![ReworkCombo], Results.Results_User2 = [Forms]![Rework]![InitiatorCombo], Results.Results_User3 = [Forms]![Rework]![ReasonTextBox]
WHERE (((SampleDetails.SampleNumber)=[Forms]![Rework]![SampleNumCombo]) AND ((SampleDetails.Test)=[Forms]![Rework]![TestCombo]) AND ((Results.Param)=[Forms]![Rework]![ParamCombo]));

So how do I get a button on a form to execute an existing update query without these maddening error messages?
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros