Prev: HELP WITH BUTTON
Next: Fields Positioned At Bottom of the Form
From: PJ on 25 Apr 2010 13:00
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
From: Dirk Goldgar on 25 Apr 2010 13:13
"PJ" <PJ(a)discussions.microsoft.com> wrote in message
news:2AE84D27-FBE8-44F7-B945-D0DD0021E2C4(a)microsoft.com...
>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)

 | 
Pages: 1
Prev: HELP WITH BUTTON
Next: Fields Positioned At Bottom of the Form