A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Command button in a form



 
 
Thread Tools Display Modes
  #1  
Old April 25th, 2010, 06:00 PM posted to microsoft.public.access.forms
PJ
external usenet poster
 
Posts: 265
Default Command button in a form

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
  #2  
Old April 25th, 2010, 06:13 PM posted to microsoft.public.access.forms
Dirk Goldgar
external usenet poster
 
Posts: 2,529
Default Command button in a form

"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)

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:34 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.