Check Box as filter

S

sebastico

Hello

In a Continuous form in Access 2003, I have been using a txtbox for filter
with the following code:

If Not IsNull(Me.txtComID) Then
strWhere = strWhere & "([ComID] Like """ & Me.txtComID & """) AND "
End If

I would like to use Check Boxes instead of textbox, but I have no idea how
to do it.
Already in the form I have these check boxes: chkA, ChkB, chkC, chkD and
chkE.

Any help is greatly appreciated

Thanks in advance.
 
B

Barry A&P

Im new so take this for what its worth
I use radio buttions and this kind of code

If chkA Then
strWhere = strWhere & "([ComID] Like """ & Me.txtComID & """) AND "
Else If chkb Then
strWhere = strWhere & "([ComID] Like """ & Me.txtComID & """) AND "
End If

if this helps Please check Yes
Thanks
Barry
 
S

sebastico

Hi Barry.

Thanks very much for your help.
I tried your code with checkbox but nil.
I need the check box control due to user may need to check one, two, three
or all boxes.


Barry A&P said:
Im new so take this for what its worth
I use radio buttions and this kind of code

If chkA Then
strWhere = strWhere & "([ComID] Like """ & Me.txtComID & """) AND "
Else If chkb Then
strWhere = strWhere & "([ComID] Like """ & Me.txtComID & """) AND "
End If

if this helps Please check Yes
Thanks
Barry
sebastico said:
Hello

In a Continuous form in Access 2003, I have been using a txtbox for filter
with the following code:

If Not IsNull(Me.txtComID) Then
strWhere = strWhere & "([ComID] Like """ & Me.txtComID & """) AND "
End If

I would like to use Check Boxes instead of textbox, but I have no idea how
to do it.
Already in the form I have these check boxes: chkA, ChkB, chkC, chkD and
chkE.

Any help is greatly appreciated

Thanks in advance.
Click to expand...
Click to expand...
 
B

Barry A&P

Sebastico
Sorry each IF needs to be seperate
This worked for me

Private Sub Button1_Click()
Dim StrWhere As String
StrWhere = "1=1"

If Check1 Then
StrWhere = StrWhere & " check1 AND "
End If

If Check2 Then
StrWhere = StrWhere & " Check2 AND "
End If

If Check3 Then
StrWhere = StrWhere & " check3 AND "
End If

Me!ResultTxt = StrWhere 'view my string for testing in my ResultTxt Textbox
End Sub


I am not very good with building strings so i removed your where criteria
for my testing. are you sure your filter string is being properly
concatenated..

Please check as answered if this helps
Thanks
Barry

sebastico said:
Hi Barry.

Thanks very much for your help.
I tried your code with checkbox but nil.
I need the check box control due to user may need to check one, two, three
or all boxes.


Barry A&P said:
Im new so take this for what its worth
I use radio buttions and this kind of code

If chkA Then
strWhere = strWhere & "([ComID] Like """ & Me.txtComID & """) AND "
Else If chkb Then
strWhere = strWhere & "([ComID] Like """ & Me.txtComID & """) AND "
End If

if this helps Please check Yes
Thanks
Barry
sebastico said:
Hello

In a Continuous form in Access 2003, I have been using a txtbox for filter
with the following code:

If Not IsNull(Me.txtComID) Then
strWhere = strWhere & "([ComID] Like """ & Me.txtComID & """) AND "
End If

I would like to use Check Boxes instead of textbox, but I have no idea how
to do it.
Already in the form I have these check boxes: chkA, ChkB, chkC, chkD and
chkE.

Any help is greatly appreciated

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
S

sebastico

Hi Barry
Let me give you more details what I'm doing and What I need.I have working
with a modified Allen's Brownw search form.
In a form I have with txt controls to search records. If records are finded
the form will show those records in txtboxes in Detail section. The control
source name in the form is MyQry

Now the first 5 instruction are workin well, nevertheles, when I try the
check box control theres no records in controls of detail section.

What I need is to apply the check box control with 5th instruction where you
see M.txtFilterFacID.

I also I include the code I have been testing following your code. Could
help me to find what i', doing wrong?

Thank you very much indeed

Here is the code
Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long

'Look at each search box, and build up the criteria string from the
non-blank ones.
'***********************************************************************
'Text field example. Use quotes around the value in the string.

If Not IsNull(Me.txtFilterObID) Then
strWhere = strWhere & "([ObID] = """ & Me.txtFilterObID & """) AND "
End If

If Not IsNull(Me.txtFilterYr) Then
strWhere = strWhere & "([Yr] = """ & Me.txtFilterYr & """) AND "
End If

If Not IsNull(Me.txtFilterAtNb) Then
strWhere = strWhere & "([AtNb] Like ""*" & Me.txtFilterAtNb & """) AND "
End If

If Not IsNull(Me.txtFilterTit) Then
strWhere = strWhere & "([Tit] Like """ & Me.txtFilterTit & """) AND "
End If

If Not IsNull(Me.txtFilterFacID) Then
strWhere = strWhere & "([FacID] Like """ & Me.txtFilterFacID & """) AND "
End If

‘With this code Check box is not working
'If chkA Then 'Selected check box
'strWhere = strWhere & " chkA AND "
'End If

'If chkB Then 'Selected check box
'strWhere = strWhere & " chkB AND "
'End If

'If chkC Then 'Selected check box
'strWhere = strWhere & " chkC AND "
'End If

'If chkD Then 'Selected
'strWhere = strWhere & " chkD AND "
'End If

'If chkE Then 'Selected check box
'strWhere = strWhere & " chkE AND "
'End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to
Inmediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
End If
Me.Filter = strWhere
Me.FilterOn = True
End Sub



Barry A&P said:
Sebastico
Sorry each IF needs to be seperate
This worked for me

Private Sub Button1_Click()
Dim StrWhere As String
StrWhere = "1=1"

If Check1 Then
StrWhere = StrWhere & " check1 AND "
End If

If Check2 Then
StrWhere = StrWhere & " Check2 AND "
End If

If Check3 Then
StrWhere = StrWhere & " check3 AND "
End If

Me!ResultTxt = StrWhere 'view my string for testing in my ResultTxt Textbox
End Sub


I am not very good with building strings so i removed your where criteria
for my testing. are you sure your filter string is being properly
concatenated..

Please check as answered if this helps
Thanks
Barry

sebastico said:
Hi Barry.

Thanks very much for your help.
I tried your code with checkbox but nil.
I need the check box control due to user may need to check one, two, three
or all boxes.


Barry A&P said:
Im new so take this for what its worth
I use radio buttions and this kind of code

If chkA Then
strWhere = strWhere & "([ComID] Like """ & Me.txtComID & """) AND "
Else If chkb Then
strWhere = strWhere & "([ComID] Like """ & Me.txtComID & """) AND "
End If

if this helps Please check Yes
Thanks
Barry
:

Hello

In a Continuous form in Access 2003, I have been using a txtbox for filter
with the following code:

If Not IsNull(Me.txtComID) Then
strWhere = strWhere & "([ComID] Like """ & Me.txtComID & """) AND "
End If

I would like to use Check Boxes instead of textbox, but I have no idea how
to do it.
Already in the form I have these check boxes: chkA, ChkB, chkC, chkD and
chkE.

Any help is greatly appreciated

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
B

Barry A&P

Im sorry I thought you wanted to search by a specific criteria if the check
box was checked
I see you want to add a yes/no field to your search results
This will do it

If chkA Then 'Selected check box
strWhere = strWhere & "([YourCheckFieldA] = True) AND "
End If

If chkB Then 'Selected check box
strWhere = strWhere & "([YourCheckFieldA] = True) AND "
End If

Barry

sebastico said:
Hi Barry
Let me give you more details what I'm doing and What I need.I have working
with a modified Allen's Brownw search form.
In a form I have with txt controls to search records. If records are finded
the form will show those records in txtboxes in Detail section. The control
source name in the form is MyQry

Now the first 5 instruction are workin well, nevertheles, when I try the
check box control theres no records in controls of detail section.

What I need is to apply the check box control with 5th instruction where you
see M.txtFilterFacID.

I also I include the code I have been testing following your code. Could
help me to find what i', doing wrong?

Thank you very much indeed

Here is the code
Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long

'Look at each search box, and build up the criteria string from the
non-blank ones.
'***********************************************************************
'Text field example. Use quotes around the value in the string.

If Not IsNull(Me.txtFilterObID) Then
strWhere = strWhere & "([ObID] = """ & Me.txtFilterObID & """) AND "
End If

If Not IsNull(Me.txtFilterYr) Then
strWhere = strWhere & "([Yr] = """ & Me.txtFilterYr & """) AND "
End If

If Not IsNull(Me.txtFilterAtNb) Then
strWhere = strWhere & "([AtNb] Like ""*" & Me.txtFilterAtNb & """) AND "
End If

If Not IsNull(Me.txtFilterTit) Then
strWhere = strWhere & "([Tit] Like """ & Me.txtFilterTit & """) AND "
End If

If Not IsNull(Me.txtFilterFacID) Then
strWhere = strWhere & "([FacID] Like """ & Me.txtFilterFacID & """) AND "
End If

‘With this code Check box is not working
'If chkA Then 'Selected check box
'strWhere = strWhere & " chkA AND "
'End If

'If chkB Then 'Selected check box
'strWhere = strWhere & " chkB AND "
'End If

'If chkC Then 'Selected check box
'strWhere = strWhere & " chkC AND "
'End If

'If chkD Then 'Selected
'strWhere = strWhere & " chkD AND "
'End If

'If chkE Then 'Selected check box
'strWhere = strWhere & " chkE AND "
'End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to
Inmediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
End If
Me.Filter = strWhere
Me.FilterOn = True
End Sub



Barry A&P said:
Sebastico
Sorry each IF needs to be seperate
This worked for me

Private Sub Button1_Click()
Dim StrWhere As String
StrWhere = "1=1"

If Check1 Then
StrWhere = StrWhere & " check1 AND "
End If

If Check2 Then
StrWhere = StrWhere & " Check2 AND "
End If

If Check3 Then
StrWhere = StrWhere & " check3 AND "
End If

Me!ResultTxt = StrWhere 'view my string for testing in my ResultTxt Textbox
End Sub


I am not very good with building strings so i removed your where criteria
for my testing. are you sure your filter string is being properly
concatenated..

Please check as answered if this helps
Thanks
Barry

sebastico said:
Hi Barry.

Thanks very much for your help.
I tried your code with checkbox but nil.
I need the check box control due to user may need to check one, two, three
or all boxes.


:


Im new so take this for what its worth
I use radio buttions and this kind of code

If chkA Then
strWhere = strWhere & "([ComID] Like """ & Me.txtComID & """) AND "
Else If chkb Then
strWhere = strWhere & "([ComID] Like """ & Me.txtComID & """) AND "
End If

if this helps Please check Yes
Thanks
Barry
:

Hello

In a Continuous form in Access 2003, I have been using a txtbox for filter
with the following code:

If Not IsNull(Me.txtComID) Then
strWhere = strWhere & "([ComID] Like """ & Me.txtComID & """) AND "
End If

I would like to use Check Boxes instead of textbox, but I have no idea how
to do it.
Already in the form I have these check boxes: chkA, ChkB, chkC, chkD and
chkE.

Any help is greatly appreciated

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
S

sebastico

Hello Barry

The code works in the way you said. Nevertheless in the Form Header section
of my form, I have 6 txtlabels to search and after clicking a button for
filter, the records must be showed in 6 txtboxes in Detail section of the
form.
The reason is I'm learning to program in vba. I hope to find the way to use
correctly your code.

Thank you very much indeed fork your time.
Barry A&P said:
Im sorry I thought you wanted to search by a specific criteria if the check
box was checked
I see you want to add a yes/no field to your search results
This will do it

If chkA Then 'Selected check box
strWhere = strWhere & "([YourCheckFieldA] = True) AND "
End If

If chkB Then 'Selected check box
strWhere = strWhere & "([YourCheckFieldA] = True) AND "
End If

Barry

sebastico said:
Hi Barry
Let me give you more details what I'm doing and What I need.I have working
with a modified Allen's Brownw search form.
In a form I have with txt controls to search records. If records are finded
the form will show those records in txtboxes in Detail section. The control
source name in the form is MyQry

Now the first 5 instruction are workin well, nevertheles, when I try the
check box control theres no records in controls of detail section.

What I need is to apply the check box control with 5th instruction where you
see M.txtFilterFacID.

I also I include the code I have been testing following your code. Could
help me to find what i', doing wrong?

Thank you very much indeed

Here is the code
Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long

'Look at each search box, and build up the criteria string from the
non-blank ones.
'***********************************************************************
'Text field example. Use quotes around the value in the string.

If Not IsNull(Me.txtFilterObID) Then
strWhere = strWhere & "([ObID] = """ & Me.txtFilterObID & """) AND "
End If

If Not IsNull(Me.txtFilterYr) Then
strWhere = strWhere & "([Yr] = """ & Me.txtFilterYr & """) AND "
End If

If Not IsNull(Me.txtFilterAtNb) Then
strWhere = strWhere & "([AtNb] Like ""*" & Me.txtFilterAtNb & """) AND "
End If

If Not IsNull(Me.txtFilterTit) Then
strWhere = strWhere & "([Tit] Like """ & Me.txtFilterTit & """) AND "
End If

If Not IsNull(Me.txtFilterFacID) Then
strWhere = strWhere & "([FacID] Like """ & Me.txtFilterFacID & """) AND "
End If

‘With this code Check box is not working
'If chkA Then 'Selected check box
'strWhere = strWhere & " chkA AND "
'End If

'If chkB Then 'Selected check box
'strWhere = strWhere & " chkB AND "
'End If

'If chkC Then 'Selected check box
'strWhere = strWhere & " chkC AND "
'End If

'If chkD Then 'Selected
'strWhere = strWhere & " chkD AND "
'End If

'If chkE Then 'Selected check box
'strWhere = strWhere & " chkE AND "
'End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to
Inmediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
End If
Me.Filter = strWhere
Me.FilterOn = True
End Sub



Barry A&P said:
Sebastico
Sorry each IF needs to be seperate
This worked for me

Private Sub Button1_Click()
Dim StrWhere As String
StrWhere = "1=1"

If Check1 Then
StrWhere = StrWhere & " check1 AND "
End If

If Check2 Then
StrWhere = StrWhere & " Check2 AND "
End If

If Check3 Then
StrWhere = StrWhere & " check3 AND "
End If

Me!ResultTxt = StrWhere 'view my string for testing in my ResultTxt Textbox
End Sub


I am not very good with building strings so i removed your where criteria
for my testing. are you sure your filter string is being properly
concatenated..

Please check as answered if this helps
Thanks
Barry

:


Hi Barry.

Thanks very much for your help.
I tried your code with checkbox but nil.
I need the check box control due to user may need to check one, two, three
or all boxes.


:


Im new so take this for what its worth
I use radio buttions and this kind of code

If chkA Then
strWhere = strWhere & "([ComID] Like """ & Me.txtComID & """) AND "
Else If chkb Then
strWhere = strWhere & "([ComID] Like """ & Me.txtComID & """) AND "
End If

if this helps Please check Yes
Thanks
Barry
:

Hello

In a Continuous form in Access 2003, I have been using a txtbox for filter
with the following code:

If Not IsNull(Me.txtComID) Then
strWhere = strWhere & "([ComID] Like """ & Me.txtComID & """) AND "
End If

I would like to use Check Boxes instead of textbox, but I have no idea how
to do it.
Already in the form I have these check boxes: chkA, ChkB, chkC, chkD and
chkE.

Any help is greatly appreciated

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
S

sebastico

Barry
Sorry for delay in writing

After hundred hours of testing your code and the help from this forum, I
already have what I have been locking for with check boxes.Many thanks

Unfortunately I have not been able to run the filter when I add more check
boxes bounded to another table, even though I added such table to the query.
The problem: filter displays double of records.

sebastico said:
Hello Barry

The code works in the way you said. Nevertheless in the Form Header section
of my form, I have 6 txtlabels to search and after clicking a button for
filter, the records must be showed in 6 txtboxes in Detail section of the
form.
The reason is I'm learning to program in vba. I hope to find the way to use
correctly your code.

Thank you very much indeed fork your time.
Barry A&P said:
Im sorry I thought you wanted to search by a specific criteria if the check
box was checked
I see you want to add a yes/no field to your search results
This will do it

If chkA Then 'Selected check box
strWhere = strWhere & "([YourCheckFieldA] = True) AND "
End If

If chkB Then 'Selected check box
strWhere = strWhere & "([YourCheckFieldA] = True) AND "
End If

Barry

sebastico said:
Hi Barry
Let me give you more details what I'm doing and What I need.I have working
with a modified Allen's Brownw search form.
In a form I have with txt controls to search records. If records are finded
the form will show those records in txtboxes in Detail section. The control
source name in the form is MyQry

Now the first 5 instruction are workin well, nevertheles, when I try the
check box control theres no records in controls of detail section.

What I need is to apply the check box control with 5th instruction where you
see M.txtFilterFacID.

I also I include the code I have been testing following your code. Could
help me to find what i', doing wrong?

Thank you very much indeed

Here is the code
Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long

'Look at each search box, and build up the criteria string from the
non-blank ones.
'***********************************************************************
'Text field example. Use quotes around the value in the string.

If Not IsNull(Me.txtFilterObID) Then
strWhere = strWhere & "([ObID] = """ & Me.txtFilterObID & """) AND "
End If

If Not IsNull(Me.txtFilterYr) Then
strWhere = strWhere & "([Yr] = """ & Me.txtFilterYr & """) AND "
End If

If Not IsNull(Me.txtFilterAtNb) Then
strWhere = strWhere & "([AtNb] Like ""*" & Me.txtFilterAtNb & """) AND "
End If

If Not IsNull(Me.txtFilterTit) Then
strWhere = strWhere & "([Tit] Like """ & Me.txtFilterTit & """) AND "
End If

If Not IsNull(Me.txtFilterFacID) Then
strWhere = strWhere & "([FacID] Like """ & Me.txtFilterFacID & """) AND "
End If

‘With this code Check box is not working
'If chkA Then 'Selected check box
'strWhere = strWhere & " chkA AND "
'End If

'If chkB Then 'Selected check box
'strWhere = strWhere & " chkB AND "
'End If

'If chkC Then 'Selected check box
'strWhere = strWhere & " chkC AND "
'End If

'If chkD Then 'Selected
'strWhere = strWhere & " chkD AND "
'End If

'If chkE Then 'Selected check box
'strWhere = strWhere & " chkE AND "
'End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to
Inmediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
End If
Me.Filter = strWhere
Me.FilterOn = True
End Sub



:

Sebastico
Sorry each IF needs to be seperate
This worked for me

Private Sub Button1_Click()
Dim StrWhere As String
StrWhere = "1=1"

If Check1 Then
StrWhere = StrWhere & " check1 AND "
End If

If Check2 Then
StrWhere = StrWhere & " Check2 AND "
End If

If Check3 Then
StrWhere = StrWhere & " check3 AND "
End If

Me!ResultTxt = StrWhere 'view my string for testing in my ResultTxt Textbox
End Sub


I am not very good with building strings so i removed your where criteria
for my testing. are you sure your filter string is being properly
concatenated..

Please check as answered if this helps
Thanks
Barry

:


Hi Barry.

Thanks very much for your help.
I tried your code with checkbox but nil.
I need the check box control due to user may need to check one, two, three
or all boxes.


:


Im new so take this for what its worth
I use radio buttions and this kind of code

If chkA Then
strWhere = strWhere & "([ComID] Like """ & Me.txtComID & """) AND "
Else If chkb Then
strWhere = strWhere & "([ComID] Like """ & Me.txtComID & """) AND "
End If

if this helps Please check Yes
Thanks
Barry
:

Hello

In a Continuous form in Access 2003, I have been using a txtbox for filter
with the following code:

If Not IsNull(Me.txtComID) Then
strWhere = strWhere & "([ComID] Like """ & Me.txtComID & """) AND "
End If

I would like to use Check Boxes instead of textbox, but I have no idea how
to do it.
Already in the form I have these check boxes: chkA, ChkB, chkC, chkD and
chkE.

Any help is greatly appreciated

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Can I still use check boxes? 21
Boolean expressions in the where statement 5
Filter for years and keywords 6
Allen Brownes ECount() 5
Search Form with a SubForm 3
Compile error: Argument not optional 3
Report Menu - Multi select Listbox Filter? 0
VBA filter not working in form 2

Top