G
Guest
Hello
i have a form with a subform. the mainform is unbound, the subform is bound
to a query that lists information from a few sources. i want the user to be
able to filter the subform, type in a text box on the main form and then
update a field in all the records showing in the subform with a press of the
button.
i tried to create a second update query that would use the same filters on
my form and the update value is the unboudn text box the user would type in
on the main form, but it came up saying:
"operation must use an updateable query."
so i tried to use the sql to do the same thing. that came up with a type
mismatch.
is there any other way i should try this?
here is the sql:
sql = "UPDATE tblServiceReps RIGHT JOIN (subtblWarrantyStatus RIGHT JOIN
((((tblWarrantyClaim LEFT JOIN qryWarrantyLabourTotals ON
tblWarrantyClaim.WarrantyClaimID = qryWarrantyLabourTotals.WarrantyClaimID)
LEFT JOIN qryWarrantyOtherCreditsTotal ON tblWarrantyClaim.WarrantyClaimID =
qryWarrantyOtherCreditsTotal.WarrantyClaimID) LEFT JOIN
qryWarrantyReplacedPartsTotal ON tblWarrantyClaim.WarrantyClaimID =
qryWarrantyReplacedPartsTotal.WarrantyClaimID) LEFT JOIN
qryWarrantyTravelTotal ON tblWarrantyClaim.WarrantyClaimID =
qryWarrantyTravelTotal.WarrantyClaimID) ON subtblWarrantyStatus.StatusID =
tblWarrantyClaim.StatusID) ON tblServiceReps.ServiceRepID =
tblWarrantyClaim.ServiceRepID SET tblWarrantyClaim.CreditNumber =
Val([Forms]![zzMAINFORM]![txtUpdate]) WHERE (((tblWarrantyClaim.CreditNumber)
Is Null) AND
((IIf([tblWarrantyClaim]![WarrantyClaimID]<7000,[ClaimNumber],[tblWarrantyClaim]![WarrantyClaimID]))
Like [Forms]![zzMAINFORM]![txtClaimNumberHidden] & " * " Or
(IIf([tblWarrantyClaim]![WarrantyClaimID]<7000,[ClaimNumber],[tblWarrantyClaim]![WarrantyClaimID]))
Is Null) AND ((tblWarrantyClaim.DateofClaim) Is Null Or
(tblWarrantyClaim.DateofClaim) Between
[Forms]![zzMAINFORM]![txtDateofClaimFromHidden] And
[Forms]![zzMAINFORM]![txtDateofClaimToHidden]) AND
((tblWarrantyClaim.DateApproved) Is Not Null) AND
((tblWarrantyClaim.DateRejected) Is Null) AND
((tblWarrantyClaim.WCDateDeleted) Is Null) AND ((tblServiceReps.Name) Like
[Forms]![zzMAINFORM]![txtRepHidden] & " * ") AND
((subtblWarrantyStatus.Status) Like [Forms]![zzMAINFORM]![txtStatusHidden] &
" * "));
if anyone has any thoughts please help.
i have a form with a subform. the mainform is unbound, the subform is bound
to a query that lists information from a few sources. i want the user to be
able to filter the subform, type in a text box on the main form and then
update a field in all the records showing in the subform with a press of the
button.
i tried to create a second update query that would use the same filters on
my form and the update value is the unboudn text box the user would type in
on the main form, but it came up saying:
"operation must use an updateable query."
so i tried to use the sql to do the same thing. that came up with a type
mismatch.
is there any other way i should try this?
here is the sql:
sql = "UPDATE tblServiceReps RIGHT JOIN (subtblWarrantyStatus RIGHT JOIN
((((tblWarrantyClaim LEFT JOIN qryWarrantyLabourTotals ON
tblWarrantyClaim.WarrantyClaimID = qryWarrantyLabourTotals.WarrantyClaimID)
LEFT JOIN qryWarrantyOtherCreditsTotal ON tblWarrantyClaim.WarrantyClaimID =
qryWarrantyOtherCreditsTotal.WarrantyClaimID) LEFT JOIN
qryWarrantyReplacedPartsTotal ON tblWarrantyClaim.WarrantyClaimID =
qryWarrantyReplacedPartsTotal.WarrantyClaimID) LEFT JOIN
qryWarrantyTravelTotal ON tblWarrantyClaim.WarrantyClaimID =
qryWarrantyTravelTotal.WarrantyClaimID) ON subtblWarrantyStatus.StatusID =
tblWarrantyClaim.StatusID) ON tblServiceReps.ServiceRepID =
tblWarrantyClaim.ServiceRepID SET tblWarrantyClaim.CreditNumber =
Val([Forms]![zzMAINFORM]![txtUpdate]) WHERE (((tblWarrantyClaim.CreditNumber)
Is Null) AND
((IIf([tblWarrantyClaim]![WarrantyClaimID]<7000,[ClaimNumber],[tblWarrantyClaim]![WarrantyClaimID]))
Like [Forms]![zzMAINFORM]![txtClaimNumberHidden] & " * " Or
(IIf([tblWarrantyClaim]![WarrantyClaimID]<7000,[ClaimNumber],[tblWarrantyClaim]![WarrantyClaimID]))
Is Null) AND ((tblWarrantyClaim.DateofClaim) Is Null Or
(tblWarrantyClaim.DateofClaim) Between
[Forms]![zzMAINFORM]![txtDateofClaimFromHidden] And
[Forms]![zzMAINFORM]![txtDateofClaimToHidden]) AND
((tblWarrantyClaim.DateApproved) Is Not Null) AND
((tblWarrantyClaim.DateRejected) Is Null) AND
((tblWarrantyClaim.WCDateDeleted) Is Null) AND ((tblServiceReps.Name) Like
[Forms]![zzMAINFORM]![txtRepHidden] & " * ") AND
((subtblWarrantyStatus.Status) Like [Forms]![zzMAINFORM]![txtStatusHidden] &
" * "));
if anyone has any thoughts please help.