Search Check Boxes on a Form Using Check boxes on a search Form

[rsw1984]

I currently have a Form called frmMain which uses all the information taken
from tblMain. This form contains check boxes with a yes/no answer. What I
want to do is set up a new form with the very same check boxes on, which has
the ability to search and filter the frmMain to only bring up records that
match those on the new form using the check boxes

 

[Klatuu]

You don't really need a different form, you can do it with your current form.
It would be faster and save you from having to maintain two identical forms.
You can filter the form by building a string that is like an SQL Where
statment without the word WHERE. You include the name of the field in the
form's recordset and what control it should compare to, then the form's
filter and filteron properties. Then use a command button to filter or
unfilter the form:

Private Sub cmdSetFilter_Click()
Dim strFitler As String

If Me.cmdSetFilter.Caption = "Set Filter" Then
strFilter = "[Field1] = " & Me.chkOne & " AND [Field2] = " &
Me.chkTwo & " AND [Field3] = " & Me.chkThree
Me.Filter = strFilter
Me.FilterOn = True
Me.cmdSetFilter.Caption = "Clear Filter"
Else
Me.FilterOn = False
Me.cmdSetFilter.Caption = "Set Filter"
End If
End Sub

 

[rsw1984]

Please could you explain this in more detail?

You don't really need a different form, you can do it with your current form.
It would be faster and save you from having to maintain two identical forms.
You can filter the form by building a string that is like an SQL Where
statment without the word WHERE. You include the name of the field in the
form's recordset and what control it should compare to, then the form's
filter and filteron properties. Then use a command button to filter or
unfilter the form:

Private Sub cmdSetFilter_Click()
Dim strFitler As String

If Me.cmdSetFilter.Caption = "Set Filter" Then
strFilter = "[Field1] = " & Me.chkOne & " AND [Field2] = " &
Me.chkTwo & " AND [Field3] = " & Me.chkThree
Me.Filter = strFilter
Me.FilterOn = True
Me.cmdSetFilter.Caption = "Clear Filter"
Else
Me.FilterOn = False
Me.cmdSetFilter.Caption = "Set Filter"
End If
End Sub
--
Dave Hargis, Microsoft Access MVP

I currently have a Form called frmMain which uses all the information taken
from tblMain. This form contains check boxes with a yes/no answer. What I
want to do is set up a new form with the very same check boxes on, which has
the ability to search and filter the frmMain to only bring up records that
match those on the new form using the check boxes

 

[Klatuu]

Can you tell me what you need explained? I thought the example I provided
described it, but if it is unclear, let me know what you don't understand and
I will happy to provide more detail.
--
Dave Hargis, Microsoft Access MVP

Please could you explain this in more detail?

You don't really need a different form, you can do it with your current form.
It would be faster and save you from having to maintain two identical forms.
You can filter the form by building a string that is like an SQL Where
statment without the word WHERE. You include the name of the field in the
form's recordset and what control it should compare to, then the form's
filter and filteron properties. Then use a command button to filter or
unfilter the form:

Private Sub cmdSetFilter_Click()
Dim strFitler As String

If Me.cmdSetFilter.Caption = "Set Filter" Then
strFilter = "[Field1] = " & Me.chkOne & " AND [Field2] = " &
Me.chkTwo & " AND [Field3] = " & Me.chkThree
Me.Filter = strFilter
Me.FilterOn = True
Me.cmdSetFilter.Caption = "Clear Filter"
Else
Me.FilterOn = False
Me.cmdSetFilter.Caption = "Set Filter"
End If
End Sub
--
Dave Hargis, Microsoft Access MVP

I currently have a Form called frmMain which uses all the information taken
from tblMain. This form contains check boxes with a yes/no answer. What I
want to do is set up a new form with the very same check boxes on, which has
the ability to search and filter the frmMain to only bring up records that
match those on the new form using the check boxes

 

[rsw1984]

I don't understand where the code goes?

Could you also give me an example the field is called Business and
Enterprise could you use this as an example for one of the check boxes so I
know where this goes in the code.

Thanks

Can you tell me what you need explained? I thought the example I provided
described it, but if it is unclear, let me know what you don't understand and
I will happy to provide more detail.
--
Dave Hargis, Microsoft Access MVP

Please could you explain this in more detail?

You don't really need a different form, you can do it with your current form.
It would be faster and save you from having to maintain two identical forms.
You can filter the form by building a string that is like an SQL Where
statment without the word WHERE. You include the name of the field in the
form's recordset and what control it should compare to, then the form's
filter and filteron properties. Then use a command button to filter or
unfilter the form:

Private Sub cmdSetFilter_Click()
Dim strFitler As String

If Me.cmdSetFilter.Caption = "Set Filter" Then
strFilter = "[Field1] = " & Me.chkOne & " AND [Field2] = " &
Me.chkTwo & " AND [Field3] = " & Me.chkThree
Me.Filter = strFilter
Me.FilterOn = True
Me.cmdSetFilter.Caption = "Clear Filter"
Else
Me.FilterOn = False
Me.cmdSetFilter.Caption = "Set Filter"
End If
End Sub
--
Dave Hargis, Microsoft Access MVP


:

I currently have a Form called frmMain which uses all the information taken
from tblMain. This form contains check boxes with a yes/no answer. What I
want to do is set up a new form with the very same check boxes on, which has
the ability to search and filter the frmMain to only bring up records that
match those on the new form using the check boxes

 

[Klatuu]

Sure, no problem.

First, add a command button to your form. Give it the name cmdSetFilter.
Set the Caption to Set Filter
Now, to add the code, select the Events tab of the properties dialog. Find
the text box labled On Click.
Click on the small command button just to the right of the text box.
Choose Code Builder from the list that will pop up.
The Visual Basic Editor (VBE) will open and you will see:
Paste the code into the editor where the cursor is.

Private Sub cmdSetFilter_Click()
|
End Sub

Now, to use you check box name as an example. I put it in as the first
field name. Now let's get clear on terminology. When you say Field, that
means a field in a table or query. When you refer to a check box on a form,
that is a Control. So based on your statement, I am assuming [Business and
Enterprise] is the name of a field in the form's record source. Each of your
Check Boxes is probably bound to a field in the form's record source, so you
use the name of the field and the name of the control in the comparison. You
do it for each check box you want to include in th your filter. So:
v----Name of Field---------v v--Name of Control bound to field
"[Business and Enterprise] = " & Me.chkOne


Dim strFitler As String

If Me.cmdSetFilter.Caption = "Set Filter" Then
strFilter = "[Business and Enterprise] = " & Me.chkOne & " AND
[Field2] = " &
Me.chkTwo & " AND [Field3] = " & Me.chkThree
Me.Filter = strFilter
Me.FilterOn = True
Me.cmdSetFilter.Caption = "Clear Filter"
Else
Me.FilterOn = False
Me.cmdSetFilter.Caption = "Set Filter"
End If

--
Dave Hargis, Microsoft Access MVP

I don't understand where the code goes?

Could you also give me an example the field is called Business and
Enterprise could you use this as an example for one of the check boxes so I
know where this goes in the code.

Thanks

Can you tell me what you need explained? I thought the example I provided
described it, but if it is unclear, let me know what you don't understand and
I will happy to provide more detail.
--
Dave Hargis, Microsoft Access MVP

Please could you explain this in more detail?

:

You don't really need a different form, you can do it with your current form.
It would be faster and save you from having to maintain two identical forms.
You can filter the form by building a string that is like an SQL Where
statment without the word WHERE. You include the name of the field in the
form's recordset and what control it should compare to, then the form's
filter and filteron properties. Then use a command button to filter or
unfilter the form:

Private Sub cmdSetFilter_Click()
Dim strFitler As String

If Me.cmdSetFilter.Caption = "Set Filter" Then
strFilter = "[Field1] = " & Me.chkOne & " AND [Field2] = " &
Me.chkTwo & " AND [Field3] = " & Me.chkThree
Me.Filter = strFilter
Me.FilterOn = True
Me.cmdSetFilter.Caption = "Clear Filter"
Else
Me.FilterOn = False
Me.cmdSetFilter.Caption = "Set Filter"
End If
End Sub
--
Dave Hargis, Microsoft Access MVP


:

I currently have a Form called frmMain which uses all the information taken
from tblMain. This form contains check boxes with a yes/no answer. What I
want to do is set up a new form with the very same check boxes on, which has
the ability to search and filter the frmMain to only bring up records that
match those on the new form using the check boxes

 

[BIS-SISA]

Hi,

Happy to find this thread and I was successful in getting the code to work
in my form but am puzzled by the filtered results.

I have 1276 records that when I filter them it shows 1265 records. My goal
is to see the 11 records filtered out. Any suggestions??

Thanks!

 

[Klatuu]

Well, you obviously have the logic working correctly, it would just be how
you are identify the filtering values.

If you can give me some details on what you are doing, perhaps we can make
it work for you.
--
Dave Hargis, Microsoft Access MVP

Hi,

Happy to find this thread and I was successful in getting the code to work
in my form but am puzzled by the filtered results.

I have 1276 records that when I filter them it shows 1265 records. My goal
is to see the 11 records filtered out. Any suggestions??

Thanks!

Sure, no problem.

First, add a command button to your form. Give it the name cmdSetFilter.
Set the Caption to Set Filter
Now, to add the code, select the Events tab of the properties dialog. Find
the text box labled On Click.
Click on the small command button just to the right of the text box.
Choose Code Builder from the list that will pop up.
The Visual Basic Editor (VBE) will open and you will see:
Paste the code into the editor where the cursor is.

Private Sub cmdSetFilter_Click()
|
End Sub

Now, to use you check box name as an example. I put it in as the first
field name. Now let's get clear on terminology. When you say Field, that
means a field in a table or query. When you refer to a check box on a form,
that is a Control. So based on your statement, I am assuming [Business and
Enterprise] is the name of a field in the form's record source. Each of your
Check Boxes is probably bound to a field in the form's record source, so you
use the name of the field and the name of the control in the comparison. You
do it for each check box you want to include in th your filter. So:
v----Name of Field---------v v--Name of Control bound to field
"[Business and Enterprise] = " & Me.chkOne


Dim strFitler As String

If Me.cmdSetFilter.Caption = "Set Filter" Then
strFilter = "[Business and Enterprise] = " & Me.chkOne & " AND
[Field2] = " &
Me.chkTwo & " AND [Field3] = " & Me.chkThree
Me.Filter = strFilter
Me.FilterOn = True
Me.cmdSetFilter.Caption = "Clear Filter"
Else
Me.FilterOn = False
Me.cmdSetFilter.Caption = "Set Filter"
End If

 

[BIS-SISA]

Well first let me say that you are dealing with the coding challenged here
(hence my joy at finding your thread laying out all the steps). Now that I
have disclosed that grievous fact let me try and provide some info that will
be what you need to help me

I have inherited an AC 2000 database that is used to track IT assets. There
are multiple Yes/No controls to identify location/hardware/use. Also the form
is multi-tabbed to breakout information by type (i.e. hardware, software,
maintenance, etc.).

Here is what is working in my dyslexic world, and yes I am dyslexic):

Private Sub cmdFilterBIS_Click()
Dim strFilter As String

If Me.cmdFilterBIS.Caption = "Set BIS Filter" Then
strFilter = "[BIS PC] = " & Me.ysnBIS_PC
Me.Filter = strFilter
Me.FilterOn = True
Me.cmdFilterBIS.Caption = "Clear BIS Filter"
Else
Me.FilterOn = False
Me.cmdFilterBIS.Caption = "Set BIS Filter"
End If

End Sub

The reason I have inserted “BIS†is due to the need that once I have this
filter working I need to create two additional filters for “Education†and
“Health Careâ€.

Any help will be appreciated and your mentoring is very much valued!

Uriel

Well, you obviously have the logic working correctly, it would just be how
you are identify the filtering values.

If you can give me some details on what you are doing, perhaps we can make
it work for you.
--
Dave Hargis, Microsoft Access MVP

Hi,

Happy to find this thread and I was successful in getting the code to work
in my form but am puzzled by the filtered results.

I have 1276 records that when I filter them it shows 1265 records. My goal
is to see the 11 records filtered out. Any suggestions??

Thanks!

Sure, no problem.

First, add a command button to your form. Give it the name cmdSetFilter.
Set the Caption to Set Filter
Now, to add the code, select the Events tab of the properties dialog. Find
the text box labled On Click.
Click on the small command button just to the right of the text box.
Choose Code Builder from the list that will pop up.
The Visual Basic Editor (VBE) will open and you will see:
Paste the code into the editor where the cursor is.

Private Sub cmdSetFilter_Click()
|
End Sub

Now, to use you check box name as an example. I put it in as the first
field name. Now let's get clear on terminology. When you say Field, that
means a field in a table or query. When you refer to a check box on a form,
that is a Control. So based on your statement, I am assuming [Business and
Enterprise] is the name of a field in the form's record source. Each of your
Check Boxes is probably bound to a field in the form's record source, so you
use the name of the field and the name of the control in the comparison. You
do it for each check box you want to include in th your filter. So:
v----Name of Field---------v v--Name of Control bound to field
"[Business and Enterprise] = " & Me.chkOne


Dim strFitler As String

If Me.cmdSetFilter.Caption = "Set Filter" Then
strFilter = "[Business and Enterprise] = " & Me.chkOne & " AND
[Field2] = " &
Me.chkTwo & " AND [Field3] = " & Me.chkThree
Me.Filter = strFilter
Me.FilterOn = True
Me.cmdSetFilter.Caption = "Clear Filter"
Else
Me.FilterOn = False
Me.cmdSetFilter.Caption = "Set Filter"
End If

 

[Klatuu]

With this line:
strFilter = "[BIS PC] = " & Me.ysnBIS_PC

What you are doing is filtering your form's recordset based on a field in
the recodset named [BIS PC] being equal to a control on your form name
ysnBIS_PC.

I am guessing from the name of the control it is a check box and that [BIS
PC] is a Yes/No field in your form's recordset. If all of this is correct
then here is what would happen.
If ysnBIS_PC is checked and you apply the filter, then only records where
the field [BIS PC] is True (the value is actually -1) will be included. If,
when you click the button to set the filter and ysnBIS_PC is not checked, you
would have only rows where [BIS PC] is False (0).

If the field and control are not as I assume, please let me know what data
types they are and how you want the filtering to work.
--
Dave Hargis, Microsoft Access MVP

Well first let me say that you are dealing with the coding challenged here
(hence my joy at finding your thread laying out all the steps). Now that I
have disclosed that grievous fact let me try and provide some info that will
be what you need to help me

I have inherited an AC 2000 database that is used to track IT assets. There
are multiple Yes/No controls to identify location/hardware/use. Also the form
is multi-tabbed to breakout information by type (i.e. hardware, software,
maintenance, etc.).

Here is what is working in my dyslexic world, and yes I am dyslexic):

Private Sub cmdFilterBIS_Click()
Dim strFilter As String

If Me.cmdFilterBIS.Caption = "Set BIS Filter" Then
strFilter = "[BIS PC] = " & Me.ysnBIS_PC
Me.Filter = strFilter
Me.FilterOn = True
Me.cmdFilterBIS.Caption = "Clear BIS Filter"
Else
Me.FilterOn = False
Me.cmdFilterBIS.Caption = "Set BIS Filter"
End If

End Sub

The reason I have inserted “BIS†is due to the need that once I have this
filter working I need to create two additional filters for “Education†and
“Health Careâ€.

Any help will be appreciated and your mentoring is very much valued!

Uriel

Well, you obviously have the logic working correctly, it would just be how
you are identify the filtering values.

If you can give me some details on what you are doing, perhaps we can make
it work for you.
--
Dave Hargis, Microsoft Access MVP

Hi,

Happy to find this thread and I was successful in getting the code to work
in my form but am puzzled by the filtered results.

I have 1276 records that when I filter them it shows 1265 records. My goal
is to see the 11 records filtered out. Any suggestions??

Thanks!

:

Sure, no problem.

First, add a command button to your form. Give it the name cmdSetFilter.
Set the Caption to Set Filter
Now, to add the code, select the Events tab of the properties dialog. Find
the text box labled On Click.
Click on the small command button just to the right of the text box.
Choose Code Builder from the list that will pop up.
The Visual Basic Editor (VBE) will open and you will see:
Paste the code into the editor where the cursor is.

Private Sub cmdSetFilter_Click()
|
End Sub

Now, to use you check box name as an example. I put it in as the first
field name. Now let's get clear on terminology. When you say Field, that
means a field in a table or query. When you refer to a check box on a form,
that is a Control. So based on your statement, I am assuming [Business and
Enterprise] is the name of a field in the form's record source. Each of your
Check Boxes is probably bound to a field in the form's record source, so you
use the name of the field and the name of the control in the comparison. You
do it for each check box you want to include in th your filter. So:
v----Name of Field---------v v--Name of Control bound to field
"[Business and Enterprise] = " & Me.chkOne


Dim strFitler As String

If Me.cmdSetFilter.Caption = "Set Filter" Then
strFilter = "[Business and Enterprise] = " & Me.chkOne & " AND
[Field2] = " &
Me.chkTwo & " AND [Field3] = " & Me.chkThree
Me.Filter = strFilter
Me.FilterOn = True
Me.cmdSetFilter.Caption = "Clear Filter"
Else
Me.FilterOn = False
Me.cmdSetFilter.Caption = "Set Filter"
End If

 

[BIS-SISA]

Yes I think that is right. In the table “System Computer†the field name “BIS
PC†has a data type of “Yes/Noâ€. In the form “frmSystemComputerBIS†the
control name is “ysnBIS_PC†and the control source is “BIS PCâ€.

So is that right? I know it’s awful that I don’t know for sure but there you
have it, the coding challenged live on!

Thanks for the on-going help!!

Uriel

With this line:
strFilter = "[BIS PC] = " & Me.ysnBIS_PC

What you are doing is filtering your form's recordset based on a field in
the recodset named [BIS PC] being equal to a control on your form name
ysnBIS_PC.

I am guessing from the name of the control it is a check box and that [BIS
PC] is a Yes/No field in your form's recordset. If all of this is correct
then here is what would happen.
If ysnBIS_PC is checked and you apply the filter, then only records where
the field [BIS PC] is True (the value is actually -1) will be included. If,
when you click the button to set the filter and ysnBIS_PC is not checked, you
would have only rows where [BIS PC] is False (0).

If the field and control are not as I assume, please let me know what data
types they are and how you want the filtering to work.
--
Dave Hargis, Microsoft Access MVP

Well first let me say that you are dealing with the coding challenged here
(hence my joy at finding your thread laying out all the steps). Now that I
have disclosed that grievous fact let me try and provide some info that will
be what you need to help me

I have inherited an AC 2000 database that is used to track IT assets. There
are multiple Yes/No controls to identify location/hardware/use. Also the form
is multi-tabbed to breakout information by type (i.e. hardware, software,
maintenance, etc.).

Here is what is working in my dyslexic world, and yes I am dyslexic):

Private Sub cmdFilterBIS_Click()
Dim strFilter As String

If Me.cmdFilterBIS.Caption = "Set BIS Filter" Then
strFilter = "[BIS PC] = " & Me.ysnBIS_PC
Me.Filter = strFilter
Me.FilterOn = True
Me.cmdFilterBIS.Caption = "Clear BIS Filter"
Else
Me.FilterOn = False
Me.cmdFilterBIS.Caption = "Set BIS Filter"
End If

End Sub

The reason I have inserted “BIS†is due to the need that once I have this
filter working I need to create two additional filters for “Education†and
“Health Careâ€.

Any help will be appreciated and your mentoring is very much valued!

Uriel

Well, you obviously have the logic working correctly, it would just be how
you are identify the filtering values.

If you can give me some details on what you are doing, perhaps we can make
it work for you.
--
Dave Hargis, Microsoft Access MVP


:

Hi,

Happy to find this thread and I was successful in getting the code to work
in my form but am puzzled by the filtered results.

I have 1276 records that when I filter them it shows 1265 records. My goal
is to see the 11 records filtered out. Any suggestions??

Thanks!

:

Sure, no problem.

First, add a command button to your form. Give it the name cmdSetFilter.
Set the Caption to Set Filter
Now, to add the code, select the Events tab of the properties dialog. Find
the text box labled On Click.
Click on the small command button just to the right of the text box.
Choose Code Builder from the list that will pop up.
The Visual Basic Editor (VBE) will open and you will see:
Paste the code into the editor where the cursor is.

Private Sub cmdSetFilter_Click()
|
End Sub

Now, to use you check box name as an example. I put it in as the first
field name. Now let's get clear on terminology. When you say Field, that
means a field in a table or query. When you refer to a check box on a form,
that is a Control. So based on your statement, I am assuming [Business and
Enterprise] is the name of a field in the form's record source. Each of your
Check Boxes is probably bound to a field in the form's record source, so you
use the name of the field and the name of the control in the comparison. You
do it for each check box you want to include in th your filter. So:
v----Name of Field---------v v--Name of Control bound to field
"[Business and Enterprise] = " & Me.chkOne


Dim strFitler As String

If Me.cmdSetFilter.Caption = "Set Filter" Then
strFilter = "[Business and Enterprise] = " & Me.chkOne & " AND
[Field2] = " &
Me.chkTwo & " AND [Field3] = " & Me.chkThree
Me.Filter = strFilter
Me.FilterOn = True
Me.cmdSetFilter.Caption = "Clear Filter"
Else
Me.FilterOn = False
Me.cmdSetFilter.Caption = "Set Filter"
End If

 

[Klatuu]

I just set up a test for for this situation using a yes/no field and a check
box on a form. It worked just fine for me. I don't see a problem with the
code you posted. Here is my version. Note, I would not use default Access
names normally, this was just a test:

Private Sub Command10_Click()
With Me.Command10
If .Caption = "Filter" Then
.Caption = "Filter Off"
Me.Filter = "[iscorporate] = " & Me.chkIsCorporate
Me.FilterOn = True
Else
.Caption = "Filter"
Me.FilterOn = False
End If
End With
End Sub

Functionally, I don't see a difference, but I thought I would send this
along so you can compare to see if I am missing something.
--
Dave Hargis, Microsoft Access MVP

Yes I think that is right. In the table “System Computer†the field name “BIS
PC†has a data type of “Yes/Noâ€. In the form “frmSystemComputerBIS†the
control name is “ysnBIS_PC†and the control source is “BIS PCâ€.

So is that right? I know it’s awful that I don’t know for sure but there you
have it, the coding challenged live on!

Thanks for the on-going help!!

Uriel

With this line:
strFilter = "[BIS PC] = " & Me.ysnBIS_PC

What you are doing is filtering your form's recordset based on a field in
the recodset named [BIS PC] being equal to a control on your form name
ysnBIS_PC.

I am guessing from the name of the control it is a check box and that [BIS
PC] is a Yes/No field in your form's recordset. If all of this is correct
then here is what would happen.
If ysnBIS_PC is checked and you apply the filter, then only records where
the field [BIS PC] is True (the value is actually -1) will be included. If,
when you click the button to set the filter and ysnBIS_PC is not checked, you
would have only rows where [BIS PC] is False (0).

If the field and control are not as I assume, please let me know what data
types they are and how you want the filtering to work.
--
Dave Hargis, Microsoft Access MVP

Well first let me say that you are dealing with the coding challenged here
(hence my joy at finding your thread laying out all the steps). Now that I
have disclosed that grievous fact let me try and provide some info that will
be what you need to help me

I have inherited an AC 2000 database that is used to track IT assets. There
are multiple Yes/No controls to identify location/hardware/use. Also the form
is multi-tabbed to breakout information by type (i.e. hardware, software,
maintenance, etc.).

Here is what is working in my dyslexic world, and yes I am dyslexic):

Private Sub cmdFilterBIS_Click()
Dim strFilter As String

If Me.cmdFilterBIS.Caption = "Set BIS Filter" Then
strFilter = "[BIS PC] = " & Me.ysnBIS_PC
Me.Filter = strFilter
Me.FilterOn = True
Me.cmdFilterBIS.Caption = "Clear BIS Filter"
Else
Me.FilterOn = False
Me.cmdFilterBIS.Caption = "Set BIS Filter"
End If

End Sub

The reason I have inserted “BIS†is due to the need that once I have this
filter working I need to create two additional filters for “Education†and
“Health Careâ€.

Any help will be appreciated and your mentoring is very much valued!

Uriel

:

Well, you obviously have the logic working correctly, it would just be how
you are identify the filtering values.

If you can give me some details on what you are doing, perhaps we can make
it work for you.
--
Dave Hargis, Microsoft Access MVP


:

Hi,

Happy to find this thread and I was successful in getting the code to work
in my form but am puzzled by the filtered results.

I have 1276 records that when I filter them it shows 1265 records. My goal
is to see the 11 records filtered out. Any suggestions??

Thanks!

:

Sure, no problem.

First, add a command button to your form. Give it the name cmdSetFilter.
Set the Caption to Set Filter
Now, to add the code, select the Events tab of the properties dialog. Find
the text box labled On Click.
Click on the small command button just to the right of the text box.
Choose Code Builder from the list that will pop up.
The Visual Basic Editor (VBE) will open and you will see:
Paste the code into the editor where the cursor is.

Private Sub cmdSetFilter_Click()
|
End Sub

Now, to use you check box name as an example. I put it in as the first
field name. Now let's get clear on terminology. When you say Field, that
means a field in a table or query. When you refer to a check box on a form,
that is a Control. So based on your statement, I am assuming [Business and
Enterprise] is the name of a field in the form's record source. Each of your
Check Boxes is probably bound to a field in the form's record source, so you
use the name of the field and the name of the control in the comparison. You
do it for each check box you want to include in th your filter. So:
v----Name of Field---------v v--Name of Control bound to field
"[Business and Enterprise] = " & Me.chkOne


Dim strFitler As String

If Me.cmdSetFilter.Caption = "Set Filter" Then
strFilter = "[Business and Enterprise] = " & Me.chkOne & " AND
[Field2] = " &
Me.chkTwo & " AND [Field3] = " & Me.chkThree
Me.Filter = strFilter
Me.FilterOn = True
Me.cmdSetFilter.Caption = "Clear Filter"
Else
Me.FilterOn = False
Me.cmdSetFilter.Caption = "Set Filter"
End If

 

[BIS-SISA]

Back Again

Been trying to make it work and discovered that if I have a record that is
True then the filter shows the 11 True records. However, if I have a record
that is False then the filter shows the 1265 False records.

Puzzled I am!!

I just set up a test for for this situation using a yes/no field and a check
box on a form. It worked just fine for me. I don't see a problem with the
code you posted. Here is my version. Note, I would not use default Access
names normally, this was just a test:

Private Sub Command10_Click()
With Me.Command10
If .Caption = "Filter" Then
.Caption = "Filter Off"
Me.Filter = "[iscorporate] = " & Me.chkIsCorporate
Me.FilterOn = True
Else
.Caption = "Filter"
Me.FilterOn = False
End If
End With
End Sub

Functionally, I don't see a difference, but I thought I would send this
along so you can compare to see if I am missing something.
--
Dave Hargis, Microsoft Access MVP

Yes I think that is right. In the table “System Computer†the field name “BIS
PC†has a data type of “Yes/Noâ€. In the form “frmSystemComputerBIS†the
control name is “ysnBIS_PC†and the control source is “BIS PCâ€.

So is that right? I know it’s awful that I don’t know for sure but there you
have it, the coding challenged live on!

Thanks for the on-going help!!

Uriel

With this line:
strFilter = "[BIS PC] = " & Me.ysnBIS_PC

What you are doing is filtering your form's recordset based on a field in
the recodset named [BIS PC] being equal to a control on your form name
ysnBIS_PC.

I am guessing from the name of the control it is a check box and that [BIS
PC] is a Yes/No field in your form's recordset. If all of this is correct
then here is what would happen.
If ysnBIS_PC is checked and you apply the filter, then only records where
the field [BIS PC] is True (the value is actually -1) will be included. If,
when you click the button to set the filter and ysnBIS_PC is not checked, you
would have only rows where [BIS PC] is False (0).

If the field and control are not as I assume, please let me know what data
types they are and how you want the filtering to work.
--
Dave Hargis, Microsoft Access MVP


:

Well first let me say that you are dealing with the coding challenged here
(hence my joy at finding your thread laying out all the steps). Now that I
have disclosed that grievous fact let me try and provide some info that will
be what you need to help me

I have inherited an AC 2000 database that is used to track IT assets. There
are multiple Yes/No controls to identify location/hardware/use. Also the form
is multi-tabbed to breakout information by type (i.e. hardware, software,
maintenance, etc.).

Here is what is working in my dyslexic world, and yes I am dyslexic):

Private Sub cmdFilterBIS_Click()
Dim strFilter As String

If Me.cmdFilterBIS.Caption = "Set BIS Filter" Then
strFilter = "[BIS PC] = " & Me.ysnBIS_PC
Me.Filter = strFilter
Me.FilterOn = True
Me.cmdFilterBIS.Caption = "Clear BIS Filter"
Else
Me.FilterOn = False
Me.cmdFilterBIS.Caption = "Set BIS Filter"
End If

End Sub

The reason I have inserted “BIS†is due to the need that once I have this
filter working I need to create two additional filters for “Education†and
“Health Careâ€.

Any help will be appreciated and your mentoring is very much valued!

Uriel

:

Well, you obviously have the logic working correctly, it would just be how
you are identify the filtering values.

If you can give me some details on what you are doing, perhaps we can make
it work for you.
--
Dave Hargis, Microsoft Access MVP


:

Hi,

Happy to find this thread and I was successful in getting the code to work
in my form but am puzzled by the filtered results.

I have 1276 records that when I filter them it shows 1265 records. My goal
is to see the 11 records filtered out. Any suggestions??

Thanks!

:

Sure, no problem.

First, add a command button to your form. Give it the name cmdSetFilter.
Set the Caption to Set Filter
Now, to add the code, select the Events tab of the properties dialog. Find
the text box labled On Click.
Click on the small command button just to the right of the text box.
Choose Code Builder from the list that will pop up.
The Visual Basic Editor (VBE) will open and you will see:
Paste the code into the editor where the cursor is.

Private Sub cmdSetFilter_Click()
|
End Sub

Now, to use you check box name as an example. I put it in as the first
field name. Now let's get clear on terminology. When you say Field, that
means a field in a table or query. When you refer to a check box on a form,
that is a Control. So based on your statement, I am assuming [Business and
Enterprise] is the name of a field in the form's record source. Each of your
Check Boxes is probably bound to a field in the form's record source, so you
use the name of the field and the name of the control in the comparison. You
do it for each check box you want to include in th your filter. So:
v----Name of Field---------v v--Name of Control bound to field
"[Business and Enterprise] = " & Me.chkOne


Dim strFitler As String

If Me.cmdSetFilter.Caption = "Set Filter" Then
strFilter = "[Business and Enterprise] = " & Me.chkOne & " AND
[Field2] = " &
Me.chkTwo & " AND [Field3] = " & Me.chkThree
Me.Filter = strFilter
Me.FilterOn = True
Me.cmdSetFilter.Caption = "Clear Filter"
Else
Me.FilterOn = False
Me.cmdSetFilter.Caption = "Set Filter"
End If

 

[Klatuu]

That is exactly what you are telling it to do. Describe in not technical
detail what you want to do.
--
Dave Hargis, Microsoft Access MVP

Back Again

Been trying to make it work and discovered that if I have a record that is
True then the filter shows the 11 True records. However, if I have a record
that is False then the filter shows the 1265 False records.

Puzzled I am!!

I just set up a test for for this situation using a yes/no field and a check
box on a form. It worked just fine for me. I don't see a problem with the
code you posted. Here is my version. Note, I would not use default Access
names normally, this was just a test:

Private Sub Command10_Click()
With Me.Command10
If .Caption = "Filter" Then
.Caption = "Filter Off"
Me.Filter = "[iscorporate] = " & Me.chkIsCorporate
Me.FilterOn = True
Else
.Caption = "Filter"
Me.FilterOn = False
End If
End With
End Sub

Functionally, I don't see a difference, but I thought I would send this
along so you can compare to see if I am missing something.
--
Dave Hargis, Microsoft Access MVP

Yes I think that is right. In the table “System Computer†the field name “BIS
PC†has a data type of “Yes/Noâ€. In the form “frmSystemComputerBIS†the
control name is “ysnBIS_PC†and the control source is “BIS PCâ€.

So is that right? I know it’s awful that I don’t know for sure but there you
have it, the coding challenged live on!

Thanks for the on-going help!!

Uriel


:

With this line:
strFilter = "[BIS PC] = " & Me.ysnBIS_PC

What you are doing is filtering your form's recordset based on a field in
the recodset named [BIS PC] being equal to a control on your form name
ysnBIS_PC.

I am guessing from the name of the control it is a check box and that [BIS
PC] is a Yes/No field in your form's recordset. If all of this is correct
then here is what would happen.
If ysnBIS_PC is checked and you apply the filter, then only records where
the field [BIS PC] is True (the value is actually -1) will be included. If,
when you click the button to set the filter and ysnBIS_PC is not checked, you
would have only rows where [BIS PC] is False (0).

If the field and control are not as I assume, please let me know what data
types they are and how you want the filtering to work.
--
Dave Hargis, Microsoft Access MVP


:

Well first let me say that you are dealing with the coding challenged here
(hence my joy at finding your thread laying out all the steps). Now that I
have disclosed that grievous fact let me try and provide some info that will
be what you need to help me

I have inherited an AC 2000 database that is used to track IT assets. There
are multiple Yes/No controls to identify location/hardware/use. Also the form
is multi-tabbed to breakout information by type (i.e. hardware, software,
maintenance, etc.).

Here is what is working in my dyslexic world, and yes I am dyslexic):

Private Sub cmdFilterBIS_Click()
Dim strFilter As String

If Me.cmdFilterBIS.Caption = "Set BIS Filter" Then
strFilter = "[BIS PC] = " & Me.ysnBIS_PC
Me.Filter = strFilter
Me.FilterOn = True
Me.cmdFilterBIS.Caption = "Clear BIS Filter"
Else
Me.FilterOn = False
Me.cmdFilterBIS.Caption = "Set BIS Filter"
End If

End Sub

The reason I have inserted “BIS†is due to the need that once I have this
filter working I need to create two additional filters for “Education†and
“Health Careâ€.

Any help will be appreciated and your mentoring is very much valued!

Uriel

:

Well, you obviously have the logic working correctly, it would just be how
you are identify the filtering values.

If you can give me some details on what you are doing, perhaps we can make
it work for you.
--
Dave Hargis, Microsoft Access MVP


:

Hi,

Happy to find this thread and I was successful in getting the code to work
in my form but am puzzled by the filtered results.

I have 1276 records that when I filter them it shows 1265 records. My goal
is to see the 11 records filtered out. Any suggestions??

Thanks!

:

Sure, no problem.

First, add a command button to your form. Give it the name cmdSetFilter.
Set the Caption to Set Filter
Now, to add the code, select the Events tab of the properties dialog. Find
the text box labled On Click.
Click on the small command button just to the right of the text box.
Choose Code Builder from the list that will pop up.
The Visual Basic Editor (VBE) will open and you will see:
Paste the code into the editor where the cursor is.

Private Sub cmdSetFilter_Click()
|
End Sub

Now, to use you check box name as an example. I put it in as the first
field name. Now let's get clear on terminology. When you say Field, that
means a field in a table or query. When you refer to a check box on a form,
that is a Control. So based on your statement, I am assuming [Business and
Enterprise] is the name of a field in the form's record source. Each of your
Check Boxes is probably bound to a field in the form's record source, so you
use the name of the field and the name of the control in the comparison. You
do it for each check box you want to include in th your filter. So:
v----Name of Field---------v v--Name of Control bound to field
"[Business and Enterprise] = " & Me.chkOne


Dim strFitler As String

If Me.cmdSetFilter.Caption = "Set Filter" Then
strFilter = "[Business and Enterprise] = " & Me.chkOne & " AND
[Field2] = " &
Me.chkTwo & " AND [Field3] = " & Me.chkThree
Me.Filter = strFilter
Me.FilterOn = True
Me.cmdSetFilter.Caption = "Clear Filter"
Else
Me.FilterOn = False
Me.cmdSetFilter.Caption = "Set Filter"
End If

 

[BIS-SISA]

Ok. I thought that if you had all the records and are anywhere in the
recordset that if you apply the filter it would return only the records that
meet the criteria. In this instance, I have to have all the records available
but actually be in a record that meets the criteria before the filter will
work correctly.

So if I am on a record that does not meet the criteria and apply the filter
instead of the 11 records I want I get the 1265 that I don't want.

That is exactly what you are telling it to do. Describe in not technical
detail what you want to do.
--
Dave Hargis, Microsoft Access MVP

Back Again

Been trying to make it work and discovered that if I have a record that is
True then the filter shows the 11 True records. However, if I have a record
that is False then the filter shows the 1265 False records.

Puzzled I am!!

I just set up a test for for this situation using a yes/no field and a check
box on a form. It worked just fine for me. I don't see a problem with the
code you posted. Here is my version. Note, I would not use default Access
names normally, this was just a test:

Private Sub Command10_Click()
With Me.Command10
If .Caption = "Filter" Then
.Caption = "Filter Off"
Me.Filter = "[iscorporate] = " & Me.chkIsCorporate
Me.FilterOn = True
Else
.Caption = "Filter"
Me.FilterOn = False
End If
End With
End Sub

Functionally, I don't see a difference, but I thought I would send this
along so you can compare to see if I am missing something.
--
Dave Hargis, Microsoft Access MVP


:

Yes I think that is right. In the table “System Computer†the field name “BIS
PC†has a data type of “Yes/Noâ€. In the form “frmSystemComputerBIS†the
control name is “ysnBIS_PC†and the control source is “BIS PCâ€.

So is that right? I know it’s awful that I don’t know for sure but there you
have it, the coding challenged live on!

Thanks for the on-going help!!

Uriel


:

With this line:
strFilter = "[BIS PC] = " & Me.ysnBIS_PC

What you are doing is filtering your form's recordset based on a field in
the recodset named [BIS PC] being equal to a control on your form name
ysnBIS_PC.

I am guessing from the name of the control it is a check box and that [BIS
PC] is a Yes/No field in your form's recordset. If all of this is correct
then here is what would happen.
If ysnBIS_PC is checked and you apply the filter, then only records where
the field [BIS PC] is True (the value is actually -1) will be included. If,
when you click the button to set the filter and ysnBIS_PC is not checked, you
would have only rows where [BIS PC] is False (0).

If the field and control are not as I assume, please let me know what data
types they are and how you want the filtering to work.
--
Dave Hargis, Microsoft Access MVP


:

Well first let me say that you are dealing with the coding challenged here
(hence my joy at finding your thread laying out all the steps). Now that I
have disclosed that grievous fact let me try and provide some info that will
be what you need to help me

I have inherited an AC 2000 database that is used to track IT assets. There
are multiple Yes/No controls to identify location/hardware/use. Also the form
is multi-tabbed to breakout information by type (i.e. hardware, software,
maintenance, etc.).

Here is what is working in my dyslexic world, and yes I am dyslexic):

Private Sub cmdFilterBIS_Click()
Dim strFilter As String

If Me.cmdFilterBIS.Caption = "Set BIS Filter" Then
strFilter = "[BIS PC] = " & Me.ysnBIS_PC
Me.Filter = strFilter
Me.FilterOn = True
Me.cmdFilterBIS.Caption = "Clear BIS Filter"
Else
Me.FilterOn = False
Me.cmdFilterBIS.Caption = "Set BIS Filter"
End If

End Sub

The reason I have inserted “BIS†is due to the need that once I have this
filter working I need to create two additional filters for “Education†and
“Health Careâ€.

Any help will be appreciated and your mentoring is very much valued!

Uriel

:

Well, you obviously have the logic working correctly, it would just be how
you are identify the filtering values.

If you can give me some details on what you are doing, perhaps we can make
it work for you.
--
Dave Hargis, Microsoft Access MVP


:

Hi,

Happy to find this thread and I was successful in getting the code to work
in my form but am puzzled by the filtered results.

I have 1276 records that when I filter them it shows 1265 records. My goal
is to see the 11 records filtered out. Any suggestions??

Thanks!

:

Sure, no problem.

First, add a command button to your form. Give it the name cmdSetFilter.
Set the Caption to Set Filter
Now, to add the code, select the Events tab of the properties dialog. Find
the text box labled On Click.
Click on the small command button just to the right of the text box.
Choose Code Builder from the list that will pop up.
The Visual Basic Editor (VBE) will open and you will see:
Paste the code into the editor where the cursor is.

Private Sub cmdSetFilter_Click()
|
End Sub

Now, to use you check box name as an example. I put it in as the first
field name. Now let's get clear on terminology. When you say Field, that
means a field in a table or query. When you refer to a check box on a form,
that is a Control. So based on your statement, I am assuming [Business and
Enterprise] is the name of a field in the form's record source. Each of your
Check Boxes is probably bound to a field in the form's record source, so you
use the name of the field and the name of the control in the comparison. You
do it for each check box you want to include in th your filter. So:
v----Name of Field---------v v--Name of Control bound to field
"[Business and Enterprise] = " & Me.chkOne


Dim strFitler As String

If Me.cmdSetFilter.Caption = "Set Filter" Then
strFilter = "[Business and Enterprise] = " & Me.chkOne & " AND
[Field2] = " &
Me.chkTwo & " AND [Field3] = " & Me.chkThree
Me.Filter = strFilter
Me.FilterOn = True
Me.cmdSetFilter.Caption = "Clear Filter"
Else
Me.FilterOn = False
Me.cmdSetFilter.Caption = "Set Filter"
End If

 

[Klatuu]

Okay, I see what you are saying. If you want to be able to choose without
going to a record that meets the criteria, you need to use a different
checkbox. On that is not bound and filter use that control in your filter
logic as well. Or, here is an alternative. Create an option group with 3
options All, True, False
Then do the filtering in the After Update event of the option group.
--
Dave Hargis, Microsoft Access MVP

Ok. I thought that if you had all the records and are anywhere in the
recordset that if you apply the filter it would return only the records that
meet the criteria. In this instance, I have to have all the records available
but actually be in a record that meets the criteria before the filter will
work correctly.

So if I am on a record that does not meet the criteria and apply the filter
instead of the 11 records I want I get the 1265 that I don't want.

That is exactly what you are telling it to do. Describe in not technical
detail what you want to do.
--
Dave Hargis, Microsoft Access MVP

Back Again

Been trying to make it work and discovered that if I have a record that is
True then the filter shows the 11 True records. However, if I have a record
that is False then the filter shows the 1265 False records.

Puzzled I am!!

:

I just set up a test for for this situation using a yes/no field and a check
box on a form. It worked just fine for me. I don't see a problem with the
code you posted. Here is my version. Note, I would not use default Access
names normally, this was just a test:

Private Sub Command10_Click()
With Me.Command10
If .Caption = "Filter" Then
.Caption = "Filter Off"
Me.Filter = "[iscorporate] = " & Me.chkIsCorporate
Me.FilterOn = True
Else
.Caption = "Filter"
Me.FilterOn = False
End If
End With
End Sub

Functionally, I don't see a difference, but I thought I would send this
along so you can compare to see if I am missing something.
--
Dave Hargis, Microsoft Access MVP


:

Yes I think that is right. In the table “System Computer†the field name “BIS
PC†has a data type of “Yes/Noâ€. In the form “frmSystemComputerBIS†the
control name is “ysnBIS_PC†and the control source is “BIS PCâ€.

So is that right? I know it’s awful that I don’t know for sure but there you
have it, the coding challenged live on!

Thanks for the on-going help!!

Uriel


:

With this line:
strFilter = "[BIS PC] = " & Me.ysnBIS_PC

What you are doing is filtering your form's recordset based on a field in
the recodset named [BIS PC] being equal to a control on your form name
ysnBIS_PC.

I am guessing from the name of the control it is a check box and that [BIS
PC] is a Yes/No field in your form's recordset. If all of this is correct
then here is what would happen.
If ysnBIS_PC is checked and you apply the filter, then only records where
the field [BIS PC] is True (the value is actually -1) will be included. If,
when you click the button to set the filter and ysnBIS_PC is not checked, you
would have only rows where [BIS PC] is False (0).

If the field and control are not as I assume, please let me know what data
types they are and how you want the filtering to work.
--
Dave Hargis, Microsoft Access MVP


:

Well first let me say that you are dealing with the coding challenged here
(hence my joy at finding your thread laying out all the steps). Now that I
have disclosed that grievous fact let me try and provide some info that will
be what you need to help me

I have inherited an AC 2000 database that is used to track IT assets. There
are multiple Yes/No controls to identify location/hardware/use. Also the form
is multi-tabbed to breakout information by type (i.e. hardware, software,
maintenance, etc.).

Here is what is working in my dyslexic world, and yes I am dyslexic):

Private Sub cmdFilterBIS_Click()
Dim strFilter As String

If Me.cmdFilterBIS.Caption = "Set BIS Filter" Then
strFilter = "[BIS PC] = " & Me.ysnBIS_PC
Me.Filter = strFilter
Me.FilterOn = True
Me.cmdFilterBIS.Caption = "Clear BIS Filter"
Else
Me.FilterOn = False
Me.cmdFilterBIS.Caption = "Set BIS Filter"
End If

End Sub

The reason I have inserted “BIS†is due to the need that once I have this
filter working I need to create two additional filters for “Education†and
“Health Careâ€.

Any help will be appreciated and your mentoring is very much valued!

Uriel

:

Well, you obviously have the logic working correctly, it would just be how
you are identify the filtering values.

If you can give me some details on what you are doing, perhaps we can make
it work for you.
--
Dave Hargis, Microsoft Access MVP


:

Hi,

Happy to find this thread and I was successful in getting the code to work
in my form but am puzzled by the filtered results.

I have 1276 records that when I filter them it shows 1265 records. My goal
is to see the 11 records filtered out. Any suggestions??

Thanks!

:

Sure, no problem.

First, add a command button to your form. Give it the name cmdSetFilter.
Set the Caption to Set Filter
Now, to add the code, select the Events tab of the properties dialog. Find
the text box labled On Click.
Click on the small command button just to the right of the text box.
Choose Code Builder from the list that will pop up.
The Visual Basic Editor (VBE) will open and you will see:
Paste the code into the editor where the cursor is.

Private Sub cmdSetFilter_Click()
|
End Sub

Now, to use you check box name as an example. I put it in as the first
field name. Now let's get clear on terminology. When you say Field, that
means a field in a table or query. When you refer to a check box on a form,
that is a Control. So based on your statement, I am assuming [Business and
Enterprise] is the name of a field in the form's record source. Each of your
Check Boxes is probably bound to a field in the form's record source, so you
use the name of the field and the name of the control in the comparison. You
do it for each check box you want to include in th your filter. So:
v----Name of Field---------v v--Name of Control bound to field
"[Business and Enterprise] = " & Me.chkOne


Dim strFitler As String

If Me.cmdSetFilter.Caption = "Set Filter" Then
strFilter = "[Business and Enterprise] = " & Me.chkOne & " AND
[Field2] = " &
Me.chkTwo & " AND [Field3] = " & Me.chkThree
Me.Filter = strFilter
Me.FilterOn = True
Me.cmdSetFilter.Caption = "Clear Filter"
Else
Me.FilterOn = False
Me.cmdSetFilter.Caption = "Set Filter"
End If

 

[Klatuu]

Here is how it looks:
Private Sub opgFilter_AfterUpdate()
Dim strFilter As String

Select Case Me.opgFilter
Case 1
Me.FilterOn = False
Case 2
strFilter = "[iscorporate] = -1"
Case 3
strFilter = "[iscorporate] = 0"
End Select
If Len(strFilter) > 0 Then
Me.Filter = strFilter
Me.FilterOn = True
End If
End Sub

Change iscorpororate to your field name.

--
Dave Hargis, Microsoft Access MVP

Ok. I thought that if you had all the records and are anywhere in the
recordset that if you apply the filter it would return only the records that
meet the criteria. In this instance, I have to have all the records available
but actually be in a record that meets the criteria before the filter will
work correctly.

So if I am on a record that does not meet the criteria and apply the filter
instead of the 11 records I want I get the 1265 that I don't want.

That is exactly what you are telling it to do. Describe in not technical
detail what you want to do.
--
Dave Hargis, Microsoft Access MVP

Back Again

Been trying to make it work and discovered that if I have a record that is
True then the filter shows the 11 True records. However, if I have a record
that is False then the filter shows the 1265 False records.

Puzzled I am!!

:

I just set up a test for for this situation using a yes/no field and a check
box on a form. It worked just fine for me. I don't see a problem with the
code you posted. Here is my version. Note, I would not use default Access
names normally, this was just a test:

Private Sub Command10_Click()
With Me.Command10
If .Caption = "Filter" Then
.Caption = "Filter Off"
Me.Filter = "[iscorporate] = " & Me.chkIsCorporate
Me.FilterOn = True
Else
.Caption = "Filter"
Me.FilterOn = False
End If
End With
End Sub

Functionally, I don't see a difference, but I thought I would send this
along so you can compare to see if I am missing something.
--
Dave Hargis, Microsoft Access MVP


:

Yes I think that is right. In the table “System Computer†the field name “BIS
PC†has a data type of “Yes/Noâ€. In the form “frmSystemComputerBIS†the
control name is “ysnBIS_PC†and the control source is “BIS PCâ€.

So is that right? I know it’s awful that I don’t know for sure but there you
have it, the coding challenged live on!

Thanks for the on-going help!!

Uriel


:

With this line:
strFilter = "[BIS PC] = " & Me.ysnBIS_PC

What you are doing is filtering your form's recordset based on a field in
the recodset named [BIS PC] being equal to a control on your form name
ysnBIS_PC.

I am guessing from the name of the control it is a check box and that [BIS
PC] is a Yes/No field in your form's recordset. If all of this is correct
then here is what would happen.
If ysnBIS_PC is checked and you apply the filter, then only records where
the field [BIS PC] is True (the value is actually -1) will be included. If,
when you click the button to set the filter and ysnBIS_PC is not checked, you
would have only rows where [BIS PC] is False (0).

If the field and control are not as I assume, please let me know what data
types they are and how you want the filtering to work.
--
Dave Hargis, Microsoft Access MVP


:

Well first let me say that you are dealing with the coding challenged here
(hence my joy at finding your thread laying out all the steps). Now that I
have disclosed that grievous fact let me try and provide some info that will
be what you need to help me

I have inherited an AC 2000 database that is used to track IT assets. There
are multiple Yes/No controls to identify location/hardware/use. Also the form
is multi-tabbed to breakout information by type (i.e. hardware, software,
maintenance, etc.).

Here is what is working in my dyslexic world, and yes I am dyslexic):

Private Sub cmdFilterBIS_Click()
Dim strFilter As String

If Me.cmdFilterBIS.Caption = "Set BIS Filter" Then
strFilter = "[BIS PC] = " & Me.ysnBIS_PC
Me.Filter = strFilter
Me.FilterOn = True
Me.cmdFilterBIS.Caption = "Clear BIS Filter"
Else
Me.FilterOn = False
Me.cmdFilterBIS.Caption = "Set BIS Filter"
End If

End Sub

The reason I have inserted “BIS†is due to the need that once I have this
filter working I need to create two additional filters for “Education†and
“Health Careâ€.

Any help will be appreciated and your mentoring is very much valued!

Uriel

:

Well, you obviously have the logic working correctly, it would just be how
you are identify the filtering values.

If you can give me some details on what you are doing, perhaps we can make
it work for you.
--
Dave Hargis, Microsoft Access MVP


:

Hi,

Happy to find this thread and I was successful in getting the code to work
in my form but am puzzled by the filtered results.

I have 1276 records that when I filter them it shows 1265 records. My goal
is to see the 11 records filtered out. Any suggestions??

Thanks!

:

Sure, no problem.

First, add a command button to your form. Give it the name cmdSetFilter.
Set the Caption to Set Filter
Now, to add the code, select the Events tab of the properties dialog. Find
the text box labled On Click.
Click on the small command button just to the right of the text box.
Choose Code Builder from the list that will pop up.
The Visual Basic Editor (VBE) will open and you will see:
Paste the code into the editor where the cursor is.

Private Sub cmdSetFilter_Click()
|
End Sub

Now, to use you check box name as an example. I put it in as the first
field name. Now let's get clear on terminology. When you say Field, that
means a field in a table or query. When you refer to a check box on a form,
that is a Control. So based on your statement, I am assuming [Business and
Enterprise] is the name of a field in the form's record source. Each of your
Check Boxes is probably bound to a field in the form's record source, so you
use the name of the field and the name of the control in the comparison. You
do it for each check box you want to include in th your filter. So:
v----Name of Field---------v v--Name of Control bound to field
"[Business and Enterprise] = " & Me.chkOne


Dim strFitler As String

If Me.cmdSetFilter.Caption = "Set Filter" Then
strFilter = "[Business and Enterprise] = " & Me.chkOne & " AND
[Field2] = " &
Me.chkTwo & " AND [Field3] = " & Me.chkThree
Me.Filter = strFilter
Me.FilterOn = True
Me.cmdSetFilter.Caption = "Clear Filter"
Else
Me.FilterOn = False
Me.cmdSetFilter.Caption = "Set Filter"
End If