Updating Filter by selection

[Clddleopard]

I would like to run an update query on only the records shown in a form where
I have used Access 2003's built in Filter by selection option (the one where
you right mouse click or choose from the menu), but I'm not sure how to
specify that in the criteria for the update query. Is it possible?

 

[fredg]

I would like to run an update query on only the records shown in a form where
I have used Access 2003's built in Filter by selection option (the one where
you right mouse click or choose from the menu), but I'm not sure how to
specify that in the criteria for the update query. Is it possible?

Add a command button to the form.
Code it's Click event:

Dim strSQL As String
Dim strFilter As String
strFilter = "Where " & Me.Filter
strSQL = "Update YourTable Set YourTable.[FieldName] = 'John' "
strSQL = strSQL & strFilter
CurrentDb.Execute strSQL, dbFailOnError

After applying the filter by form, click the command button.

Change YourTable and FieldName to whatever the actual name of the
table and the field you sient to update are. Note there must be a
space after the second ' and the following ". Also a space after the
word "Where ".

Note: The above assumes the field's datatype that you wish to update
is Text. Remove the single quotes if the field is Number datatype,
i.e. [FieldName] = 123 ", or surround the data with #'s if the field
is a date datatype, i.e. [FieldName] = #5/3/2008# ".

 

[Clddleopard]

Hi Fredg!
I pasted your code into the OnClick Event of a button on the form, changing
to the appropriate field and table names, then applied a filter and clicked
the button. I got the error: Run-time error '3061': Too few parameters.
Expected 1.

When I clicked debug, it highlighted the line "CurrentDb.Execute strSQL,
dbFailOnError"

My form is based on an updateable query with more than one table. The field
I want to update is a text field.
Any ideas?
Thanks for your time!

I would like to run an update query on only the records shown in a form where
I have used Access 2003's built in Filter by selection option (the one where
you right mouse click or choose from the menu), but I'm not sure how to
specify that in the criteria for the update query. Is it possible?

Add a command button to the form.
Code it's Click event:

Dim strSQL As String
Dim strFilter As String
strFilter = "Where " & Me.Filter
strSQL = "Update YourTable Set YourTable.[FieldName] = 'John' "
strSQL = strSQL & strFilter
CurrentDb.Execute strSQL, dbFailOnError

After applying the filter by form, click the command button.

Change YourTable and FieldName to whatever the actual name of the
table and the field you sient to update are. Note there must be a
space after the second ' and the following ". Also a space after the
word "Where ".

Note: The above assumes the field's datatype that you wish to update
is Text. Remove the single quotes if the field is Number datatype,
i.e. [FieldName] = 123 ", or surround the data with #'s if the field
is a date datatype, i.e. [FieldName] = #5/3/2008# ".

 

[Clddleopard]

BTW, here's my exact code:
Private Sub Command47_Click()
Dim strSQL As String
Dim strFilter As String
strFilter = "Where " & Me.Filter
strSQL = "Update EnrichmentAssignments Set EnrichmentAssignments.[KIGiven =
'John' "
strSQL = strSQL & strFilter
CurrentDb.Execute strSQL, dbFailOnError
End Sub

Hi Fredg!
I pasted your code into the OnClick Event of a button on the form, changing
to the appropriate field and table names, then applied a filter and clicked
the button. I got the error: Run-time error '3061': Too few parameters.
Expected 1.

When I clicked debug, it highlighted the line "CurrentDb.Execute strSQL,
dbFailOnError"

My form is based on an updateable query with more than one table. The field
I want to update is a text field.
Any ideas?
Thanks for your time!

I would like to run an update query on only the records shown in a form where
I have used Access 2003's built in Filter by selection option (the one where
you right mouse click or choose from the menu), but I'm not sure how to
specify that in the criteria for the update query. Is it possible?

Add a command button to the form.
Code it's Click event:

Dim strSQL As String
Dim strFilter As String
strFilter = "Where " & Me.Filter
strSQL = "Update YourTable Set YourTable.[FieldName] = 'John' "
strSQL = strSQL & strFilter
CurrentDb.Execute strSQL, dbFailOnError

After applying the filter by form, click the command button.

Change YourTable and FieldName to whatever the actual name of the
table and the field you sient to update are. Note there must be a
space after the second ' and the following ". Also a space after the
word "Where ".

Note: The above assumes the field's datatype that you wish to update
is Text. Remove the single quotes if the field is Number datatype,
i.e. [FieldName] = 123 ", or surround the data with #'s if the field
is a date datatype, i.e. [FieldName] = #5/3/2008# ".

 

[Clddleopard]

I changed the Table Name to the Query Name that the form was based on and it
worked. Thanks fredg!