"PJ" wrote in message
...
I have the following command button with an event procedure on click to
select all issuers by checking a check box:
private sub cmdselectall issuers click()
s = "Update [tbl Master comps] set [tbl Master comps].[issuer select check
box] = True"
"where [tbl master comps]. [issuer slecet check bo] = false"
currentdb.execute s
me.refresh
end sub
This command button is in a form that has a filter on it. It currently
selects all check boxes for all issuers and I want it to only check the
check
box for issuers that are in the filter.
Any ideas on how to do that?
There are a couple of possible approaches. One way is to get value of the
form's Filter property and append it to the criteria in the update query's
WHERE clause:
'------ start of example code ------
Private Sub cmdSelectAllIssuers_Click()
Dim strSQL As String
' Make surte current record has been saved.
If Me.Dirty Then Me.Dirty = False
' Set basic SQL statement for update.
strSQL = _
"UPDATE [tbl Master comps] " & _
"SET [issuer select check box] = True " & _
"WHERE [issuer select check box] = False"
' If the form is filtered, incorporate that filter in the WHERE clause.
If Me.FilterOn Then
strSQL = strSQL & " AND (" & Me.Filter & ")"
End If
' Execute the SQL statement
CurrentDb.Execute strSQL, dbFailOnError
' Refresh the form to show the updated check boxes.
Me.Refresh
End Sub
'------ end of example code ------
For most normal filters, that ought to work. If you find that it doesn't,
an alternative approach would be to loop through the form's RecordsetClone,
assembling a list of the primary keys of all the records currently included
in the recordset. Then you could use that list in a WHERE-clause condition.
--
Dirk Goldgar, MS Access MVP
Access tips:
www.datagnostics.com/tips.html
(please reply to the newsgroup)