updating a field in a filtered datasheet subform

  • Thread starter Thread starter Guest
  • Start date Start date
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 assume your subform is bound to an updateable query. It probably has a
more meaningful name, but let's call it "qrySubform". (If is just a SQL
statement in the RecordSource and not a saved query, then save it and name
it.)

Now, the filter on the subform is available from:
Me.SubformControlName.Form.Filter

Also, if you have Link Master/Child Fields defined, you will have an
additional filter. So, you can construct a filter string as follows:

strFilter = "(CreditCardNumber is Null)"
' omit the following line if the subform is not linked
strFilter = strFilter & " AND ([LinkChildFieldName]=" &
Me![LinkMasterFieldName] & ")"
With Me.SubformControl.Form
If .FilterOn Then
strFilter = strFilter & " AND (" & .Filter & ")"
End If
End With

You can then construct a very simple SQL statement to do the update:

strSQL = "UPDATE qrySubform SET CreditNumber = " & Me!txtUpdate _
& " WHERE " & strFilter
CurrentDb.Execute strSQL, dbFailOnError
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

DawnTreader said:
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.
 
Hello

actually i mentioned in my explaination that i was getting a message telling
me that i didnt have an updatable query. but your suggestion got me thinking
about the update query that i created based on the list query and i realised
that both were a little more complex then they needed to be.

i simplified the update query a lot more than the list, but when i was done
all i really needed was one table in the update query and that made it work.
i have found that sometimes a query with more than one table is updateable
and sometimes not. until i simplified the update query wouldnt work.

in the end all i needed was an update query that i built from in design view
based on the table that i needed to update 2 fields in, and all the criteria
was on that one table as well.

although i think your suggestion was way over my head and way more than i
was needing, thanks for jogging my thoughts into the right answer. :)

sometimes the simplest tricks are the best tricks. :)

Graham Mandeno said:
I assume your subform is bound to an updateable query. It probably has a
more meaningful name, but let's call it "qrySubform". (If is just a SQL
statement in the RecordSource and not a saved query, then save it and name
it.)

Now, the filter on the subform is available from:
Me.SubformControlName.Form.Filter

Also, if you have Link Master/Child Fields defined, you will have an
additional filter. So, you can construct a filter string as follows:

strFilter = "(CreditCardNumber is Null)"
' omit the following line if the subform is not linked
strFilter = strFilter & " AND ([LinkChildFieldName]=" &
Me![LinkMasterFieldName] & ")"
With Me.SubformControl.Form
If .FilterOn Then
strFilter = strFilter & " AND (" & .Filter & ")"
End If
End With

You can then construct a very simple SQL statement to do the update:

strSQL = "UPDATE qrySubform SET CreditNumber = " & Me!txtUpdate _
& " WHERE " & strFilter
CurrentDb.Execute strSQL, dbFailOnError
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

DawnTreader said:
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.
 
Back
Top