B
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"
"Manager" and "Self", 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"));
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"
"Manager" and "Self", 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"));