Command button in a form

  • Thread starter Thread starter PJ
  • Start date Start date
P

PJ

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?

Thanks in advance
 
PJ said:
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?
Click to expand...


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.
 
Back
Top