Filtered Records

[Guest]

How do I check yes in yes/no box for filtered records? I filter records in a
form and when I am done I would like to check yes for each record in the
current set of filtered records.

I normally start with 7,000 records and then filter down to 400 depending on
current needs. Of those 400 I need to check a yes or no box. Currently I am
hand checking each one. I haven’t figured out how to check the first one and
hold shift to check the last one (like other programs). I can not build a
standard query to check them because my filter criteria changes each time.

So, I want to take the “current filtered records†and run them through an
update query to change no to yes. How do I get the current filtered records?

 

[Guest]

Hi, Bryan.

Of those 400 I need to check a yes or no box. Currently I am
hand checking each one.

Please tell me you get paid by the hour. ;-)

I haven’t figured out how to check the first one and
hold shift to check the last one (like other programs).

It doesn't work that way. Just use an UPDATE query.

I can not build a
standard query to check them because my filter criteria changes each time.

Perhaps you can change your mind about this. Try:

Private Sub UpdChkBoxBtn_Click()

On Error GoTo ErrHandler

Dim ans As Integer

If (Len(Me.Filter) > 0) Then
CurrentDb().Execute "UPDATE " & Me.RecordSource & _
" SET SomeValue = " & True & _
" WHERE " & Me.Filter, dbFailOnError
Else
ans = MsgBox("No filter has been applied to this form." & vbCrLf & _
"Would you like to update all records?", vbInformation +
vbYesNo, _
"Update All Records?")

If (ans = vbYes) Then
CurrentDb().Execute "UPDATE " & Me.RecordSource & _
" SET SomeValue = " & True, dbFailOnError
End If
End If

Exit Sub

ErrHandler:

MsgBox "Error in UpdChkBoxBtn_Click( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear

End Sub

.... where UpdChkBoxBtn is the name of the button you will select any time
you want to set the check box field to Yes in all of the filtered records,
and SomeValue is the name of the field that the check box is bound to. The
form, of course, must be bound to the table where the field is to be updated.
If the form is bound to a query that uses multiple tables, then
Me.RecordSource above must be changed to the actual table name where the
field to be updated is located.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Guest]

What does UPDATE stand for??? I understand the rest.

 

['69 Camaro]

Hi, Bryan.

What does UPDATE stand for???

UPDATE is a SQL command to make a change to records in a table. It's
followed by the name of the table that needs the change, then the SET
command and the list of fields and the values that each of these fields will
get. If there's a WHERE clause in the UPDATE query, then only certain
records that meet the criteria will be updated, and all other records in the
table will be left as is.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.