Runtime 3061: Too Few Parameters on nested query (1 Viewer)

[beeawwb]

Hi all,

Having an issue with runtime error 3061 when creating SQL in VBA. I've run
into this previously, where I need to pass parameters to my queries through
VBA and have overcome it in the past by recreating the SQL in VBA itself, so
that I can say "this is the value you want" and then use db.Execute to run
the query.

The problem is, now I need to run a nested query. The query itself doesn't
actually have any specific parameters, they're in the 3 nested ones. It's
only 1 parameter, shared by the 3 queries to make sure they're all loading
the same review.

What I'm trying to do is update values in the first nested query to match
values in the other 2. But only where the values in query 2 are equal (agreed)
to the values in query 3. This will leave all unmatched values as null.

So in short hand... Update query1, inner join query1, query2, query3. Update
query1 value to query3, where query2 value = query3 value.

When I execute the sql though, it advises me (to be fair, I expected it would
do so) that there are too few parameters. Expected 1.

Thanks for your time and assistance!

Bob

Code pasted below (apologies for the mess, I'm still learning some of this):

Parent query:

strsql = "UPDATE ([qry_AgreedRatingFilter] INNER JOIN
[qry_AgreedRating_SubSelf] ON ([qry_AgreedRatingFilter].[txt_CapabilityLink]
= [qry_AgreedRating_SubSelf].[txt_CapabilityLink]) AND (
[qry_AgreedRatingFilter].[txt_CapabilityGroupLink] =
[qry_AgreedRating_SubSelf].[txt_CapabilityGroupLink])) INNER JOIN
[qry_AgreedRating_SubMgr] ON ([qry_AgreedRating_SubSelf].[txt_CapabilityLink]
= [qry_AgreedRating_SubMgr].[txt_CapabilityLink]) AND (
[qry_AgreedRating_SubSelf].[txt_CapabilityGroupLink] =
[qry_AgreedRating_SubMgr].[txt_CapabilityGroupLink]) SET
[qry_AgreedRatingFilter].[txt_RatedLevel] = [qry_AgreedRating_SubMgr].
[txt_RatedLevel] WHERE ((([qry_AgreedRating_SubSelf].[txt_RatedLevel]) =
[qry_AgreedRating_SubMgr].[txt_RatedLevel]))"

Nested Queries: These are all the same query, but with the explicitly set
parameter changed for each one. There are 3 possible values: "Agreed" (AgreedRatingFilter)
"Manager" (AgreedRating_SubMgr) and "Self" (AgreedRating_SubSelf), which I've tried to show below.

SELECT tbl_CapReview.id_Review, tbl_CapSequence.id_CapSequence,
tbl_CapSequence.txt_SequenceType, tbl_Capability.txt_CapabilityGroupLink,
tbl_CapabilityListing.txt_CapabilityLink, tbl_CapRatings.txt_RatedLevel
FROM ((tbl_User INNER JOIN tbl_CapReview ON tbl_User.id_User = tbl_CapReview.
txt_ReviewUser) INNER JOIN tbl_CapSequence ON tbl_CapReview.id_Review =
tbl_CapSequence.txt_ReviewID) INNER JOIN (tbl_Capability INNER JOIN
(tbl_CapabilityListing INNER JOIN tbl_CapRatings ON tbl_CapabilityListing.
id_CapabilityListing = tbl_CapRatings.txt_RatingCapability) ON tbl_Capability.
id_Capability = tbl_CapabilityListing.txt_CapabilityLink) ON tbl_CapSequence.
id_CapSequence = tbl_CapRatings.txt_RatingReview
WHERE (((tbl_CapReview.id_Review)=[Forms]![frm_Dynamic]![sub_Detail].[Form]!
[ReviewFilter]) AND ((tbl_CapSequence.txt_SequenceType)
="Agreed"/"Manager"/"Self"));

 

[beeawwb]

Hi again everybody,
Thanks to a bit more Googling I was able to find a solution to this on The Access Web.
http://www.mvps.org/access/queries/qry0013.htm

In short my code now runs the Update query directly, instead of setting an SQL string in VBA.

Set qdf = CurrentDb.QueryDefs("qry_AgreedRatingFilter_Update")
 
For Each prm In qdf.Parameters
        prm.Value = Eval(prm.Name)
Next prm
 
qdf.Execute dbFailOnError

Cheers,
Bob