Filter check box

  • Thread starter Thread starter rob p
  • Start date Start date
R

rob p

Table has a field - text box for Y/N inactive. I have this field on a form
for enter edit. I would only want to display all or active clients. I have 8
records. 2 are inactive (checked)

So far, I tried putting (([tblcompany Query].[Client Inactive]=0)) and
adding a command button, form operation and apply form filter. I press it
and the filter works - showing 6 unchecked records.

Now, I can undo the filter from the toolbar (and see 8 records) but IS THERE
A WAY I can make another command button that turns off the filter so all
controls are on the form and not on the toolbar.

thanks.
 
Why not just create an unbound combo box
Name = cboFilter
Row Source Type = Value List
Row Source = "Active";"Inactive";"All"

In the after update event of the combo box modify the form's record source
to something like
SELECT Case me.cboFilter
CASE "Active"
me.RecordSource = "SELECT * FROM tblCompany WHERE tblCompany.Active
= True"
CASE "Inactive"
me.RecordSource = "SELECT * FROM tblCompany WHERE tblCompany.Active
= False"
CASE ELSE
me.RecordSource = "SELECT * FROM tblCompany"
END SELECT
 
You may want to try a toggle button, and use this as the onClick event:

Me.FilterOn = Not Me.FilterOn
Me.Toggle0.Value = Me.FilterOn

This should toggle the filter with every click, and appear pressed when
filter is on.

Hope this helps,

Mike
 
rob said:
Table has a field - text box for Y/N inactive. I have this field on a form
for enter edit. I would only want to display all or active clients. I have 8
records. 2 are inactive (checked)

So far, I tried putting (([tblcompany Query].[Client Inactive]=0)) and
adding a command button, form operation and apply form filter. I press it
and the filter works - showing 6 unchecked records.

Now, I can undo the filter from the toolbar (and see 8 records) but IS THERE
A WAY I can make another command button that turns off the filter so all
controls are on the form and not on the toolbar.

thanks.
Click to expand...

I think you can save your filter as a Query (use Filter --> Advanced
Filter/Sort, then right-click on that and choose "Save as Query").

Edit the Query, if you wish, to specify the sorting order, the order in
which the fields should appear in a record, etc., and give it a suitable
name (such as [Q_ActiveClients]).

For the list of all clients, you could copy [Q_ActiveClients] and give
the copy a new name, such as [Q_AllClients]; this copy will retain your
choices of sort order, etc. But in Query Design View, remove the
Criteria specification of ([Client Inactive]=0).

Having defined the two Queries, you can display either or both, or
attach them to command buttons, or use them as bases for Forms or
Reports, or basically do most of what you could do with Tables.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
This works great for record selection. How do I take the me.RecordSource and
translate into that amount of records printing. I can take a command button,
put it on a form and select a report I want to print if it is pushed but I
don't know how I can tell it to only use the records the combo box selected.
thanks.

Bill Edwards said:
Why not just create an unbound combo box
Name = cboFilter
Row Source Type = Value List
Row Source = "Active";"Inactive";"All"

In the after update event of the combo box modify the form's record source
to something like
SELECT Case me.cboFilter
CASE "Active"
me.RecordSource = "SELECT * FROM tblCompany WHERE tblCompany.Active
= True"
CASE "Inactive"
me.RecordSource = "SELECT * FROM tblCompany WHERE tblCompany.Active
= False"
CASE ELSE
me.RecordSource = "SELECT * FROM tblCompany"
END SELECT


rob p said:
Table has a field - text box for Y/N inactive. I have this field on a form
for enter edit. I would only want to display all or active clients. I have
8
records. 2 are inactive (checked)

So far, I tried putting (([tblcompany Query].[Client Inactive]=0)) and
adding a command button, form operation and apply form filter. I press it
and the filter works - showing 6 unchecked records.

Now, I can undo the filter from the toolbar (and see 8 records) but IS
THERE
A WAY I can make another command button that turns off the filter so all
controls are on the form and not on the toolbar.

thanks.
Click to expand...
Click to expand...
 
rob said:
This works great for record selection. How do I take the me.RecordSource and
translate into that amount of records printing. I can take a command button,
put it on a form and select a report I want to print if it is pushed but I
don't know how I can tell it to only use the records the combo box selected.
Click to expand...


As Mike said you can use the form's Filter and FiltrOn
properties to control the records displayed on the form. To
open a report that's filtered to the same records as the
form, use the OpenReport method's WhereCondition argument:

Dim strCriteria As String
If Me.FilterOn Then strCriteria = Me.Filter
DoCmd.OpenReport "reportname", , , strCriteria
 
Basically use Docmd.OpenReport with a WHERE clause

In the command button that prints the report place the following code:

dim strWhere as string
dim strREportName as string
strReportName = "Some Report Name"
' Check to see what the user selected from the combo box
SELECT Case me.cboFilter
Case "Active"
strWhere = "Active = True"
Case "Inactive"
strWHERE = "Active =False"
End Select

DoCmd.OpenReport strReportname, acViewNormal, , strWhere

rob p said:
This works great for record selection. How do I take the me.RecordSource
and
translate into that amount of records printing. I can take a command
button,
put it on a form and select a report I want to print if it is pushed but I
don't know how I can tell it to only use the records the combo box
selected.
thanks.

Bill Edwards said:
Why not just create an unbound combo box
Name = cboFilter
Row Source Type = Value List
Row Source = "Active";"Inactive";"All"

In the after update event of the combo box modify the form's record
source
to something like
SELECT Case me.cboFilter
CASE "Active"
me.RecordSource = "SELECT * FROM tblCompany WHERE tblCompany.Active
= True"
CASE "Inactive"
me.RecordSource = "SELECT * FROM tblCompany WHERE tblCompany.Active
= False"
CASE ELSE
me.RecordSource = "SELECT * FROM tblCompany"
END SELECT


rob p said:
Table has a field - text box for Y/N inactive. I have this field on a form
for enter edit. I would only want to display all or active clients. I have
8
records. 2 are inactive (checked)

So far, I tried putting (([tblcompany Query].[Client Inactive]=0)) and
adding a command button, form operation and apply form filter. I press it
and the filter works - showing 6 unchecked records.

Now, I can undo the filter from the toolbar (and see 8 records) but IS
THERE
A WAY I can make another command button that turns off the filter so
all
controls are on the form and not on the toolbar.

thanks.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top