Best way to filter data

I have a form [Filters] that is based on a table [Filters]. On the [Filters]
form there is a subform [Find a Batch Cover Sheet subform] that has the same
values as the [Filters] form. I would like to choose 1, or all 5 of the
combo box's on the [Filters] form and then hit a command button to filter the
[Find a Batch Cover Sheet subform] with the values that I have selected. Is
this possible, and if so, can anyone help me please?? Thank you in
advance....!

Cascading Combo Boxes.
Not that hard to do. The basic part of this filtering the row source of
each combo box on the value in the previous combo box. That is, combo 5's
row source should filter on the value in Combo 4. Combo 4's row source
should filter on the value in Combo 3, etc.

Now, starting with Combo 1. Use the After Update event to requery Combo2.
Use the After Update event of Combo 2 to requery Combo 3, etc.

As an example of the row source filtering, here is an example of how Combo 2
would be set up:

SELECT SomeField FROM SomeTable WHERE PickAField = Combo1

Then when the button is pressed, you will have to build a Where statement
based on the values in the combos.

Dim strWhere As String

strWhere = ""
If Not IsNull(Me.Combo1) Then
strWhere = "[Field1] = '" & Me.Combo1 & "'"
End If

If Not IsNull(Me.Combo2) Then
strWhere = AddAnd(strWhere)
strWhere = "[Field2] = '" & Me.Combo2 & "'"
End If

If Not IsNull(Me.Combo3) Then
strWhere = AddAnd(strWhere)
strWhere = "[Field23] = '" & Me.Combo3 & "'"
End If

If Not IsNull(Me.Combo4) Then
strWhere = AddAnd(strWhere)
strWhere = "[Field4] = '" & Me.Combo4 & "'"
End If

If Not IsNull(Me.Combo5) Then
strWhere = AddAnd(strWhere)
strWhere = "[Field5] = '" & Me.Combo5 & "'"
End If

At this point, strWhere will contain a string that is a Where condition
without the word WHERE. You can use that in an OpenReport or to set a Filter
Property. If you need to inlcude it in an SQL statement, just put the word
WHERE in front of it:

strSQL = strSQL & " WHERE " & strWhere & ";"


Public Function AddAnd(strWhereNext As String) As String
If Len(strWhereNext) 0 Then
strWhereNext = strWhereNext & " AND "
End If
AddAnd = strWhereNext
End Function

"Ryan" wrote:

I have a form [Filters] that is based on a table [Filters]. On the [Filters]
form there is a subform [Find a Batch Cover Sheet subform] that has the same
values as the [Filters] form. I would like to choose 1, or all 5 of the
combo box's on the [Filters] form and then hit a command button to filter the
[Find a Batch Cover Sheet subform] with the values that I have selected. Is
this possible, and if so, can anyone help me please?? Thank you in
advance....!

What if I wanted to use combo box 2 and 4 only leaving combo box 1,3,& 4
null? Im trying to not be restricted to updating all preceding combo boxes.
My [Filters] table and my [Find a Batch Cover Sheet subform] are not related
in any way other than the field names. I'm not sure if this is possible, but
if field two is employee and field 4 is date and I only put values in those
field, could I then hit a button to just filter on field 2 and 4? Is your
solution still the answer to this kind of filter? Thank you so much for your
response and help.

Cascading Combo Boxes.
Not that hard to do. The basic part of this filtering the row source of
each combo box on the value in the previous combo box. That is, combo 5's
row source should filter on the value in Combo 4. Combo 4's row source
should filter on the value in Combo 3, etc.

Now, starting with Combo 1. Use the After Update event to requery Combo2.
Use the After Update event of Combo 2 to requery Combo 3, etc.

As an example of the row source filtering, here is an example of how Combo 2
would be set up:

SELECT SomeField FROM SomeTable WHERE PickAField = Combo1

Then when the button is pressed, you will have to build a Where statement
based on the values in the combos.

Dim strWhere As String

strWhere = ""
If Not IsNull(Me.Combo1) Then
strWhere = "[Field1] = '" & Me.Combo1 & "'"
End If

If Not IsNull(Me.Combo2) Then
strWhere = AddAnd(strWhere)
strWhere = "[Field2] = '" & Me.Combo2 & "'"
End If

If Not IsNull(Me.Combo3) Then
strWhere = AddAnd(strWhere)
strWhere = "[Field23] = '" & Me.Combo3 & "'"
End If

If Not IsNull(Me.Combo4) Then
strWhere = AddAnd(strWhere)
strWhere = "[Field4] = '" & Me.Combo4 & "'"
End If

If Not IsNull(Me.Combo5) Then
strWhere = AddAnd(strWhere)
strWhere = "[Field5] = '" & Me.Combo5 & "'"
End If

At this point, strWhere will contain a string that is a Where condition
without the word WHERE. You can use that in an OpenReport or to set a Filter
Property. If you need to inlcude it in an SQL statement, just put the word
WHERE in front of it:

strSQL = strSQL & " WHERE " & strWhere & ";"


Public Function AddAnd(strWhereNext As String) As String
If Len(strWhereNext) 0 Then
strWhereNext = strWhereNext & " AND "
End If
AddAnd = strWhereNext
End Function

"Ryan" wrote:

I have a form [Filters] that is based on a table [Filters]. On the [Filters]
form there is a subform [Find a Batch Cover Sheet subform] that has the same
values as the [Filters] form. I would like to choose 1, or all 5 of the
combo box's on the [Filters] form and then hit a command button to filter the
[Find a Batch Cover Sheet subform] with the values that I have selected. Is
this possible, and if so, can anyone help me please?? Thank you in
advance....!

That is not a problem. Note the code that should go in the click event of
the button. It only includes filtering on those combo's that are not null.
If, in fact, none of the 5 fields to be filtered have any relation to each
other, then there is no need to filter the combos.

"Ryan" wrote:

What if I wanted to use combo box 2 and 4 only leaving combo box 1,3,& 4
null? Im trying to not be restricted to updating all preceding combo boxes.
My [Filters] table and my [Find a Batch Cover Sheet subform] are not related
in any way other than the field names. I'm not sure if this is possible, but
if field two is employee and field 4 is date and I only put values in those
field, could I then hit a button to just filter on field 2 and 4? Is your
solution still the answer to this kind of filter? Thank you so much for your
response and help.

Cascading Combo Boxes.
Not that hard to do. The basic part of this filtering the row source of
each combo box on the value in the previous combo box. That is, combo 5's
row source should filter on the value in Combo 4. Combo 4's row source
should filter on the value in Combo 3, etc.

Now, starting with Combo 1. Use the After Update event to requery Combo2.
Use the After Update event of Combo 2 to requery Combo 3, etc.

As an example of the row source filtering, here is an example of how Combo 2
would be set up:

SELECT SomeField FROM SomeTable WHERE PickAField = Combo1

Then when the button is pressed, you will have to build a Where statement
based on the values in the combos.

Dim strWhere As String

strWhere = ""
If Not IsNull(Me.Combo1) Then
strWhere = "[Field1] = '" & Me.Combo1 & "'"
End If

If Not IsNull(Me.Combo2) Then
strWhere = AddAnd(strWhere)
strWhere = "[Field2] = '" & Me.Combo2 & "'"
End If

If Not IsNull(Me.Combo3) Then
strWhere = AddAnd(strWhere)
strWhere = "[Field23] = '" & Me.Combo3 & "'"
End If

If Not IsNull(Me.Combo4) Then
strWhere = AddAnd(strWhere)
strWhere = "[Field4] = '" & Me.Combo4 & "'"
End If

If Not IsNull(Me.Combo5) Then
strWhere = AddAnd(strWhere)
strWhere = "[Field5] = '" & Me.Combo5 & "'"
End If

At this point, strWhere will contain a string that is a Where condition
without the word WHERE. You can use that in an OpenReport or to set a Filter
Property. If you need to inlcude it in an SQL statement, just put the word
WHERE in front of it:

strSQL = strSQL & " WHERE " & strWhere & ";"


Public Function AddAnd(strWhereNext As String) As String
If Len(strWhereNext) 0 Then
strWhereNext = strWhereNext & " AND "
End If
AddAnd = strWhereNext
End Function

"Ryan" wrote:

I have a form [Filters] that is based on a table [Filters]. On the [Filters]
form there is a subform [Find a Batch Cover Sheet subform] that has the same
values as the [Filters] form. I would like to choose 1, or all 5 of the
combo box's on the [Filters] form and then hit a command button to filter the
[Find a Batch Cover Sheet subform] with the values that I have selected. Is
this possible, and if so, can anyone help me please?? Thank you in
advance....!

Thank you for sticking with me on this issue. This is what I have done so
far to try and get just the first combo box to filter my subform.

Private Function Command28_Click()
Dim strWhere As String
strWhere = ""
If Not IsNull(Me.Batch_ID) Then
strWhere = "[Filters]![Find a Batch Cover Sheet subform].form![Batch ID]=" '
& Me.Batch_ID & "'"
End If
End Function

It does nothing when I push the button

"Klatuu" wrote:

That is not a problem. Note the code that should go in the click event of
the button. It only includes filtering on those combo's that are not null.
If, in fact, none of the 5 fields to be filtered have any relation to each
other, then there is no need to filter the combos.

"Ryan" wrote:

What if I wanted to use combo box 2 and 4 only leaving combo box 1,3,& 4
null? Im trying to not be restricted to updating all preceding combo boxes.
My [Filters] table and my [Find a Batch Cover Sheet subform] are not related
in any way other than the field names. I'm not sure if this is possible, but
if field two is employee and field 4 is date and I only put values in those
field, could I then hit a button to just filter on field 2 and 4? Is your
solution still the answer to this kind of filter? Thank you so much for your
response and help.

Cascading Combo Boxes.
Not that hard to do. The basic part of this filtering the row source of
each combo box on the value in the previous combo box. That is, combo 5's
row source should filter on the value in Combo 4. Combo 4's row source
should filter on the value in Combo 3, etc.

Now, starting with Combo 1. Use the After Update event to requery Combo2.
Use the After Update event of Combo 2 to requery Combo 3, etc.

As an example of the row source filtering, here is an example of how Combo 2
would be set up:

SELECT SomeField FROM SomeTable WHERE PickAField = Combo1

Then when the button is pressed, you will have to build a Where statement
based on the values in the combos.

Dim strWhere As String

strWhere = ""
If Not IsNull(Me.Combo1) Then
strWhere = "[Field1] = '" & Me.Combo1 & "'"
End If

If Not IsNull(Me.Combo2) Then
strWhere = AddAnd(strWhere)
strWhere = "[Field2] = '" & Me.Combo2 & "'"
End If

If Not IsNull(Me.Combo3) Then
strWhere = AddAnd(strWhere)
strWhere = "[Field23] = '" & Me.Combo3 & "'"
End If

If Not IsNull(Me.Combo4) Then
strWhere = AddAnd(strWhere)
strWhere = "[Field4] = '" & Me.Combo4 & "'"
End If

If Not IsNull(Me.Combo5) Then
strWhere = AddAnd(strWhere)
strWhere = "[Field5] = '" & Me.Combo5 & "'"
End If

At this point, strWhere will contain a string that is a Where condition
without the word WHERE. You can use that in an OpenReport or to set a Filter
Property. If you need to inlcude it in an SQL statement, just put the word
WHERE in front of it:

strSQL = strSQL & " WHERE " & strWhere & ";"


Public Function AddAnd(strWhereNext As String) As String
If Len(strWhereNext) 0 Then
strWhereNext = strWhereNext & " AND "
End If
AddAnd = strWhereNext
End Function

"Ryan" wrote:

I have a form [Filters] that is based on a table [Filters]. On the [Filters]
form there is a subform [Find a Batch Cover Sheet subform] that has the same
values as the [Filters] form. I would like to choose 1, or all 5 of the
combo box's on the [Filters] form and then hit a command button to filter the
[Find a Batch Cover Sheet subform] with the values that I have selected. Is
this possible, and if so, can anyone help me please?? Thank you in
advance....!

strWhere = "[Filters]![Find a Batch Cover Sheet subform].form![Batch ID]=" '
& Me.Batch_ID & "'"

There is a problem he
[Batch ID]=" '
It should be
[Batch ID]= '"

[Filters]![Find a Batch Cover Sheet subform].form![Batch ID]
I'm not sure this is correct. All it needs to be is the name of a field in
the form's recordsource, not a control on the form.

Now, I guess I still don't understand what you want to filter. Is the form,
a report, or what? Once you have strWhere correct, it is a matter of
applying it.

Let me know what you want to do with it and I will give you the syntax for it.

"Ryan" wrote:

Thank you for sticking with me on this issue. This is what I have done so
far to try and get just the first combo box to filter my subform.

Private Function Command28_Click()
Dim strWhere As String
strWhere = ""
If Not IsNull(Me.Batch_ID) Then
strWhere = "[Filters]![Find a Batch Cover Sheet subform].form![Batch ID]=" '
& Me.Batch_ID & "'"
End If
End Function

It does nothing when I push the button

"Klatuu" wrote:

That is not a problem. Note the code that should go in the click event of
the button. It only includes filtering on those combo's that are not null.
If, in fact, none of the 5 fields to be filtered have any relation to each
other, then there is no need to filter the combos.

"Ryan" wrote:

What if I wanted to use combo box 2 and 4 only leaving combo box 1,3,& 4
null? Im trying to not be restricted to updating all preceding combo boxes.
My [Filters] table and my [Find a Batch Cover Sheet subform] are not related
in any way other than the field names. I'm not sure if this is possible, but
if field two is employee and field 4 is date and I only put values in those
field, could I then hit a button to just filter on field 2 and 4? Is your
solution still the answer to this kind of filter? Thank you so much for your
response and help.

Cascading Combo Boxes.
Not that hard to do. The basic part of this filtering the row source of
each combo box on the value in the previous combo box. That is, combo 5's
row source should filter on the value in Combo 4. Combo 4's row source
should filter on the value in Combo 3, etc.

Now, starting with Combo 1. Use the After Update event to requery Combo2.
Use the After Update event of Combo 2 to requery Combo 3, etc.

As an example of the row source filtering, here is an example of how Combo 2
would be set up:

SELECT SomeField FROM SomeTable WHERE PickAField = Combo1

Then when the button is pressed, you will have to build a Where statement
based on the values in the combos.

Dim strWhere As String

strWhere = ""
If Not IsNull(Me.Combo1) Then
strWhere = "[Field1] = '" & Me.Combo1 & "'"
End If

If Not IsNull(Me.Combo2) Then
strWhere = AddAnd(strWhere)
strWhere = "[Field2] = '" & Me.Combo2 & "'"
End If

If Not IsNull(Me.Combo3) Then
strWhere = AddAnd(strWhere)
strWhere = "[Field23] = '" & Me.Combo3 & "'"
End If

If Not IsNull(Me.Combo4) Then
strWhere = AddAnd(strWhere)
strWhere = "[Field4] = '" & Me.Combo4 & "'"
End If

If Not IsNull(Me.Combo5) Then
strWhere = AddAnd(strWhere)
strWhere = "[Field5] = '" & Me.Combo5 & "'"
End If

At this point, strWhere will contain a string that is a Where condition
without the word WHERE. You can use that in an OpenReport or to set a Filter
Property. If you need to inlcude it in an SQL statement, just put the word
WHERE in front of it:

strSQL = strSQL & " WHERE " & strWhere & ";"


Public Function AddAnd(strWhereNext As String) As String
If Len(strWhereNext) 0 Then
strWhereNext = strWhereNext & " AND "
End If
AddAnd = strWhereNext
End Function

"Ryan" wrote:

I have a form [Filters] that is based on a table [Filters]. On the [Filters]
form there is a subform [Find a Batch Cover Sheet subform] that has the same
values as the [Filters] form. I would like to choose 1, or all 5 of the
combo box's on the [Filters] form and then hit a command button to filter the
[Find a Batch Cover Sheet subform] with the values that I have selected. Is
this possible, and if so, can anyone help me please?? Thank you in
advance....!

I'm new to VB, and I thank you for all your help so far. This is my best
explanation.
I have 5 text/combo boxes based on a table named [Filters] on a form named
[Filters]. On the form [Filters] there is a subform named [Find a Batch
Cover Sheet subform]. The goal is to be able to select 1,2, or all 5 of the
text/combo boxes on the [Filters] form to filter the records in the [Find a
Batch Cover Sheet subform]. Once the records are filtered I wrote a
DoCmd.OpenForm to be able to open the form [Find a Batch Cover Sheet] and
make changes to much more information than just the 5 filtering text/combo
boxes. I would be more than happy to email you the entire database or a
screenshot of the [Filters] form if this information doesn't clarify what I
am trying to do. Thank you again for your help.

"Klatuu" wrote:

strWhere = "[Filters]![Find a Batch Cover Sheet subform].form![Batch ID]=" '
& Me.Batch_ID & "'"

There is a problem he
[Batch ID]=" '
It should be
[Batch ID]= '"

[Filters]![Find a Batch Cover Sheet subform].form![Batch ID]
I'm not sure this is correct. All it needs to be is the name of a field in
the form's recordsource, not a control on the form.

Now, I guess I still don't understand what you want to filter. Is the form,
a report, or what? Once you have strWhere correct, it is a matter of
applying it.

Let me know what you want to do with it and I will give you the syntax for it.

"Ryan" wrote:

Thank you for sticking with me on this issue. This is what I have done so
far to try and get just the first combo box to filter my subform.

Private Function Command28_Click()
Dim strWhere As String
strWhere = ""
If Not IsNull(Me.Batch_ID) Then
strWhere = "[Filters]![Find a Batch Cover Sheet subform].form![Batch ID]=" '
& Me.Batch_ID & "'"
End If
End Function

It does nothing when I push the button

"Klatuu" wrote:

That is not a problem. Note the code that should go in the click event of
the button. It only includes filtering on those combo's that are not null.
If, in fact, none of the 5 fields to be filtered have any relation to each
other, then there is no need to filter the combos.

"Ryan" wrote:

What if I wanted to use combo box 2 and 4 only leaving combo box 1,3,& 4
null? Im trying to not be restricted to updating all preceding combo boxes.
My [Filters] table and my [Find a Batch Cover Sheet subform] are not related
in any way other than the field names. I'm not sure if this is possible, but
if field two is employee and field 4 is date and I only put values in those
field, could I then hit a button to just filter on field 2 and 4? Is your
solution still the answer to this kind of filter? Thank you so much for your
response and help.

Cascading Combo Boxes.
Not that hard to do. The basic part of this filtering the row source of
each combo box on the value in the previous combo box. That is, combo 5's
row source should filter on the value in Combo 4. Combo 4's row source
should filter on the value in Combo 3, etc.

Now, starting with Combo 1. Use the After Update event to requery Combo2.
Use the After Update event of Combo 2 to requery Combo 3, etc.

As an example of the row source filtering, here is an example of how Combo 2
would be set up:

SELECT SomeField FROM SomeTable WHERE PickAField = Combo1

Then when the button is pressed, you will have to build a Where statement
based on the values in the combos.

Dim strWhere As String

strWhere = ""
If Not IsNull(Me.Combo1) Then
strWhere = "[Field1] = '" & Me.Combo1 & "'"
End If

If Not IsNull(Me.Combo2) Then
strWhere = AddAnd(strWhere)
strWhere = "[Field2] = '" & Me.Combo2 & "'"
End If

If Not IsNull(Me.Combo3) Then
strWhere = AddAnd(strWhere)
strWhere = "[Field23] = '" & Me.Combo3 & "'"
End If

If Not IsNull(Me.Combo4) Then
strWhere = AddAnd(strWhere)
strWhere = "[Field4] = '" & Me.Combo4 & "'"
End If

If Not IsNull(Me.Combo5) Then
strWhere = AddAnd(strWhere)
strWhere = "[Field5] = '" & Me.Combo5 & "'"
End If

At this point, strWhere will contain a string that is a Where condition
without the word WHERE. You can use that in an OpenReport or to set a Filter
Property. If you need to inlcude it in an SQL statement, just put the word
WHERE in front of it:

strSQL = strSQL & " WHERE " & strWhere & ";"


Public Function AddAnd(strWhereNext As String) As String
If Len(strWhereNext) 0 Then
strWhereNext = strWhereNext & " AND "
End If
AddAnd = strWhereNext
End Function

"Ryan" wrote:

I have a form [Filters] that is based on a table [Filters]. On the [Filters]
form there is a subform [Find a Batch Cover Sheet subform] that has the same
values as the [Filters] form. I would like to choose 1, or all 5 of the
combo box's on the [Filters] form and then hit a command button to filter the
[Find a Batch Cover Sheet subform] with the values that I have selected. Is
this possible, and if so, can anyone help me please?? Thank you in
advance....!

I understand what it is you are trying to do.
The first order of business is to get all the filtering combo's working first.

Then in the button to open the subform, just use the strWhere string as the
Where argument in your filter:

Me.[Find a Batch Cover Sheet subform].Form.Filter = strWhere
Me.[Find a Batch Cover Sheet subform].Form.FilterOn = True

Note: The syntax above assumes [Find a Batch Cover Sheet subform] is the
name of the subform control on you main form, not necessarily the name of the
form being used as a subform.


"Ryan" wrote:

I'm new to VB, and I thank you for all your help so far. This is my best
explanation.
I have 5 text/combo boxes based on a table named [Filters] on a form named
[Filters]. On the form [Filters] there is a subform named [Find a Batch
Cover Sheet subform]. The goal is to be able to select 1,2, or all 5 of the
text/combo boxes on the [Filters] form to filter the records in the [Find a
Batch Cover Sheet subform]. Once the records are filtered I wrote a
DoCmd.OpenForm to be able to open the form [Find a Batch Cover Sheet] and
make changes to much more information than just the 5 filtering text/combo
boxes. I would be more than happy to email you the entire database or a
screenshot of the [Filters] form if this information doesn't clarify what I
am trying to do. Thank you again for your help.

"Klatuu" wrote:

strWhere = "[Filters]![Find a Batch Cover Sheet subform].form![Batch ID]=" '
& Me.Batch_ID & "'"

There is a problem he
[Batch ID]=" '
It should be
[Batch ID]= '"

[Filters]![Find a Batch Cover Sheet subform].form![Batch ID]
I'm not sure this is correct. All it needs to be is the name of a field in
the form's recordsource, not a control on the form.

Now, I guess I still don't understand what you want to filter. Is the form,
a report, or what? Once you have strWhere correct, it is a matter of
applying it.

Let me know what you want to do with it and I will give you the syntax for it.

"Ryan" wrote:

Thank you for sticking with me on this issue. This is what I have done so
far to try and get just the first combo box to filter my subform.

Private Function Command28_Click()
Dim strWhere As String
strWhere = ""
If Not IsNull(Me.Batch_ID) Then
strWhere = "[Filters]![Find a Batch Cover Sheet subform].form![Batch ID]=" '
& Me.Batch_ID & "'"
End If
End Function

It does nothing when I push the button

"Klatuu" wrote:

That is not a problem. Note the code that should go in the click event of
the button. It only includes filtering on those combo's that are not null.
If, in fact, none of the 5 fields to be filtered have any relation to each
other, then there is no need to filter the combos.

"Ryan" wrote:

What if I wanted to use combo box 2 and 4 only leaving combo box 1,3,& 4
null? Im trying to not be restricted to updating all preceding combo boxes.
My [Filters] table and my [Find a Batch Cover Sheet subform] are not related
in any way other than the field names. I'm not sure if this is possible, but
if field two is employee and field 4 is date and I only put values in those
field, could I then hit a button to just filter on field 2 and 4? Is your
solution still the answer to this kind of filter? Thank you so much for your
response and help.

Cascading Combo Boxes.
Not that hard to do. The basic part of this filtering the row source of
each combo box on the value in the previous combo box. That is, combo 5's
row source should filter on the value in Combo 4. Combo 4's row source
should filter on the value in Combo 3, etc.

Now, starting with Combo 1. Use the After Update event to requery Combo2.
Use the After Update event of Combo 2 to requery Combo 3, etc.

As an example of the row source filtering, here is an example of how Combo 2
would be set up:

SELECT SomeField FROM SomeTable WHERE PickAField = Combo1

Then when the button is pressed, you will have to build a Where statement
based on the values in the combos.

Dim strWhere As String

strWhere = ""
If Not IsNull(Me.Combo1) Then
strWhere = "[Field1] = '" & Me.Combo1 & "'"
End If

If Not IsNull(Me.Combo2) Then
strWhere = AddAnd(strWhere)
strWhere = "[Field2] = '" & Me.Combo2 & "'"
End If

If Not IsNull(Me.Combo3) Then
strWhere = AddAnd(strWhere)
strWhere = "[Field23] = '" & Me.Combo3 & "'"
End If

If Not IsNull(Me.Combo4) Then
strWhere = AddAnd(strWhere)
strWhere = "[Field4] = '" & Me.Combo4 & "'"
End If

If Not IsNull(Me.Combo5) Then
strWhere = AddAnd(strWhere)
strWhere = "[Field5] = '" & Me.Combo5 & "'"
End If

At this point, strWhere will contain a string that is a Where condition
without the word WHERE. You can use that in an OpenReport or to set a Filter
Property. If you need to inlcude it in an SQL statement, just put the word
WHERE in front of it:

strSQL = strSQL & " WHERE " & strWhere & ";"


Public Function AddAnd(strWhereNext As String) As String
If Len(strWhereNext) 0 Then
strWhereNext = strWhereNext & " AND "
End If
AddAnd = strWhereNext
End Function

"Ryan" wrote:

I have a form [Filters] that is based on a table [Filters]. On the [Filters]
form there is a subform [Find a Batch Cover Sheet subform] that has the same
values as the [Filters] form. I would like to choose 1, or all 5 of the
combo box's on the [Filters] form and then hit a command button to filter the
[Find a Batch Cover Sheet subform] with the values that I have selected. Is
this possible, and if so, can anyone help me please?? Thank you in
advance....!

Would there be any way to get in contact with you? I have tried every way
possible to apply your syntax, but for some reason I'm missing something. I
would love to email you my .mdb file and speak with you about what I'm trying
to do. My project is way overdue and I have had no luck contacting anyone
locally to help me. I live in Oklahoma and would entertain a fee if
necessary. Thank you again for all your help.

"Klatuu" wrote:

I understand what it is you are trying to do.
The first order of business is to get all the filtering combo's working first.

Then in the button to open the subform, just use the strWhere string as the
Where argument in your filter:

Me.[Find a Batch Cover Sheet subform].Form.Filter = strWhere
Me.[Find a Batch Cover Sheet subform].Form.FilterOn = True

Note: The syntax above assumes [Find a Batch Cover Sheet subform] is the
name of the subform control on you main form, not necessarily the name of the
form being used as a subform.


"Ryan" wrote:

I'm new to VB, and I thank you for all your help so far. This is my best
explanation.
I have 5 text/combo boxes based on a table named [Filters] on a form named
[Filters]. On the form [Filters] there is a subform named [Find a Batch
Cover Sheet subform]. The goal is to be able to select 1,2, or all 5 of the
text/combo boxes on the [Filters] form to filter the records in the [Find a
Batch Cover Sheet subform]. Once the records are filtered I wrote a
DoCmd.OpenForm to be able to open the form [Find a Batch Cover Sheet] and
make changes to much more information than just the 5 filtering text/combo
boxes. I would be more than happy to email you the entire database or a
screenshot of the [Filters] form if this information doesn't clarify what I
am trying to do. Thank you again for your help.

"Klatuu" wrote:

strWhere = "[Filters]![Find a Batch Cover Sheet subform].form![Batch ID]=" '
& Me.Batch_ID & "'"

There is a problem he
[Batch ID]=" '
It should be
[Batch ID]= '"

[Filters]![Find a Batch Cover Sheet subform].form![Batch ID]
I'm not sure this is correct. All it needs to be is the name of a field in
the form's recordsource, not a control on the form.

Now, I guess I still don't understand what you want to filter. Is the form,
a report, or what? Once you have strWhere correct, it is a matter of
applying it.

Let me know what you want to do with it and I will give you the syntax for it.

"Ryan" wrote:

Thank you for sticking with me on this issue. This is what I have done so
far to try and get just the first combo box to filter my subform.

Private Function Command28_Click()
Dim strWhere As String
strWhere = ""
If Not IsNull(Me.Batch_ID) Then
strWhere = "[Filters]![Find a Batch Cover Sheet subform].form![Batch ID]=" '
& Me.Batch_ID & "'"
End If
End Function

It does nothing when I push the button

"Klatuu" wrote:

That is not a problem. Note the code that should go in the click event of
the button. It only includes filtering on those combo's that are not null.
If, in fact, none of the 5 fields to be filtered have any relation to each
other, then there is no need to filter the combos.

"Ryan" wrote:

What if I wanted to use combo box 2 and 4 only leaving combo box 1,3,& 4
null? Im trying to not be restricted to updating all preceding combo boxes.
My [Filters] table and my [Find a Batch Cover Sheet subform] are not related
in any way other than the field names. I'm not sure if this is possible, but
if field two is employee and field 4 is date and I only put values in those
field, could I then hit a button to just filter on field 2 and 4? Is your
solution still the answer to this kind of filter? Thank you so much for your
response and help.

Cascading Combo Boxes.
Not that hard to do. The basic part of this filtering the row source of
each combo box on the value in the previous combo box. That is, combo 5's
row source should filter on the value in Combo 4. Combo 4's row source
should filter on the value in Combo 3, etc.

Now, starting with Combo 1. Use the After Update event to requery Combo2.
Use the After Update event of Combo 2 to requery Combo 3, etc.

As an example of the row source filtering, here is an example of how Combo 2
would be set up:

SELECT SomeField FROM SomeTable WHERE PickAField = Combo1

Then when the button is pressed, you will have to build a Where statement
based on the values in the combos.

Dim strWhere As String

strWhere = ""
If Not IsNull(Me.Combo1) Then
strWhere = "[Field1] = '" & Me.Combo1 & "'"
End If

If Not IsNull(Me.Combo2) Then
strWhere = AddAnd(strWhere)
strWhere = "[Field2] = '" & Me.Combo2 & "'"
End If

If Not IsNull(Me.Combo3) Then
strWhere = AddAnd(strWhere)
strWhere = "[Field23] = '" & Me.Combo3 & "'"
End If

If Not IsNull(Me.Combo4) Then
strWhere = AddAnd(strWhere)
strWhere = "[Field4] = '" & Me.Combo4 & "'"
End If

If Not IsNull(Me.Combo5) Then
strWhere = AddAnd(strWhere)
strWhere = "[Field5] = '" & Me.Combo5 & "'"
End If

At this point, strWhere will contain a string that is a Where condition
without the word WHERE. You can use that in an OpenReport or to set a Filter
Property. If you need to inlcude it in an SQL statement, just put the word
WHERE in front of it:

strSQL = strSQL & " WHERE " & strWhere & ";"


Public Function AddAnd(strWhereNext As String) As String
If Len(strWhereNext) 0 Then
strWhereNext = strWhereNext & " AND "
End If
AddAnd = strWhereNext
End Function

"Ryan" wrote:

I have a form [Filters] that is based on a table [Filters]. On the [Filters]
form there is a subform [Find a Batch Cover Sheet subform] that has the same
values as the [Filters] form. I would like to choose 1, or all 5 of the
combo box's on the [Filters] form and then hit a command button to filter the
[Find a Batch Cover Sheet subform] with the values that I have selected. Is
this possible, and if so, can anyone help me please?? Thank you in
advance....!