Combo Box as Criteria for Select Query

[jjyconsulting]

Newbie needing some help. I have a tblParticipants. The fields
include gender, education_level, income, occupation etc., I'm trying to
create a form where a user can run a query from the form and just
choose the appropriate criterias from the combo boxes to get the
results. I also want the query to run even if there is not a value in
all the combo boxes ie., i want just all males with income level of
over $100,000...Any insights or help would be greatly appreciated.
Thanks.

[salad]

jj***********@yahoo.com wrote:

Newbie needing some help. I have a tblParticipants. The fields
include gender, education_level, income, occupation etc., I'm trying to
create a form where a user can run a query from the form and just
choose the appropriate criterias from the combo boxes to get the
results. I also want the query to run even if there is not a value in
all the combo boxes ie., i want just all males with income level of
over $100,000...Any insights or help would be greatly appreciated.
Thanks.

I would create your combo boxes that will filter your record selection.
I assume you know how to create the combo boxes and supply the data.

I also assume you can do a little bit of vba coding.

Higlight all the combos for filtering, select the AfterUpdate event row
and enter
=MakeFilter()

Create a command button to reset the filter. This will clear out the
combo boxes and display all records. In the OnClick event, click on the
triple dot at end of row, select EventProcedure, and enter something like
Dim blnOK As Boolean
Me.ComboGender = Null
Me.ComboIncome = Null
'...null out rest of combos
blnOK = MakeFilter()

Now create your MakeFilter function. Create your filter with a series
of IF statements

Private Function MakeFilter() As Boolean
'if you are using string values in the combo, surround
'combo value with '' or "" (gender). If numerica value (due to
'lookup tables) don't surround (Occupation)
Dim strFilter As String
If Not IsNull(Me.ComboGender) Then
'assumes fld nm is gender and stuffing an M or F
strFilter = strFilter & _
"Gender = '" & Me.ComboGender & "' And "
Endif

If Not IsNull(Me.ComboOccupation) Then
'assumes fld nm is gender and stuffing an M or F
strFilter = strFilter & _
"Occupation = " & ComboOccupation & " And "
Endif
...continue this method for rest of combos

'now remove word AND at end of strFilter
if strFilter "" Then 'not all combos were null
strFilter = Left(strFilter,len(strFilter)-5)
Endif

'now set filter
Me.Filter = strFilter 'strFilter looks like Where statement
'without the word "Where"
'now turn on filter
Me.FilterOn = True

MakeFilter = True
End Function

This creates the filter and is called anytime you change a value in the
combos or resetting the filter.

[jjyconsulting]

salad wrote:

jj***********@yahoo.com wrote:

Newbie needing some help. I have a tblParticipants. The fields
include gender, education_level, income, occupation etc., I'm trying to
create a form where a user can run a query from the form and just
choose the appropriate criterias from the combo boxes to get the
results. I also want the query to run even if there is not a value in
all the combo boxes ie., i want just all males with income level of
over $100,000...Any insights or help would be greatly appreciated.
Thanks.

I would create your combo boxes that will filter your record selection.
I assume you know how to create the combo boxes and supply the data.

I also assume you can do a little bit of vba coding.

Higlight all the combos for filtering, select the AfterUpdate event row
and enter
=MakeFilter()

Create a command button to reset the filter. This will clear out the
combo boxes and display all records. In the OnClick event, click on the
triple dot at end of row, select EventProcedure, and enter something like
Dim blnOK As Boolean
Me.ComboGender = Null
Me.ComboIncome = Null
'...null out rest of combos
blnOK = MakeFilter()

Now create your MakeFilter function. Create your filter with a series
of IF statements

Private Function MakeFilter() As Boolean
'if you are using string values in the combo, surround
'combo value with '' or "" (gender). If numerica value (due to
'lookup tables) don't surround (Occupation)
Dim strFilter As String
If Not IsNull(Me.ComboGender) Then
'assumes fld nm is gender and stuffing an M or F
strFilter = strFilter & _
"Gender = '" & Me.ComboGender & "' And "
Endif

If Not IsNull(Me.ComboOccupation) Then
'assumes fld nm is gender and stuffing an M or F
strFilter = strFilter & _
"Occupation = " & ComboOccupation & " And "
Endif
...continue this method for rest of combos

'now remove word AND at end of strFilter
if strFilter "" Then 'not all combos were null
strFilter = Left(strFilter,len(strFilter)-5)
Endif

'now set filter
Me.Filter = strFilter 'strFilter looks like Where statement
'without the word "Where"
'now turn on filter
Me.FilterOn = True

MakeFilter = True
End Function

This creates the filter and is called anytime you change a value in the
combos or resetting the filter.

I took the above steps and when I click on the button it doesn't run
the query or anything. It just clears the selection made in the combo
boxes. Here is the code that I have for the form. Thanks.

Private Sub Command6_Click()
Dim blnOK As Boolean
Me.Combo0 = Null
Me.Combo2 = Null
blnkOK = MakeFilter()
End Sub

Private Function MakeFilter() As Boolean
Dim strFilter As String
If Not IsNull(Me.Combo0) Then
strFilter = strFilter & _
"Gender = '" & Me.Combo0 & "' And "
End If
If Not IsNull(Me.Combo2) Then
strFilter = strFilter & _
"Occupation = " & Me.Combo2 & " And "
End If
If strFilter "" Then
strFilter = Left(strFilter, Len(strFilter) - 5)
End If
Me.Filter = strFilter
Me.FilterOn = True
MakeFilter = True
End Function

[salad]

jj***********@yahoo.com wrote:

salad wrote:

>>jj***********@yahoo.com wrote:

>>>Newbie needing some help. I have a tblParticipants. The fields
include gender, education_level, income, occupation etc., I'm trying to
create a form where a user can run a query from the form and just
choose the appropriate criterias from the combo boxes to get the
results. I also want the query to run even if there is not a value in
all the combo boxes ie., i want just all males with income level of
over $100,000...Any insights or help would be greatly appreciated.
Thanks.

I would create your combo boxes that will filter your record selection.
I assume you know how to create the combo boxes and supply the data.

I also assume you can do a little bit of vba coding.

Higlight all the combos for filtering, select the AfterUpdate event row
and enter
=MakeFilter()

Create a command button to reset the filter. This will clear out the
combo boxes and display all records. In the OnClick event, click on the
triple dot at end of row, select EventProcedure, and enter something like
Dim blnOK As Boolean
Me.ComboGender = Null
Me.ComboIncome = Null
'...null out rest of combos
blnOK = MakeFilter()

Now create your MakeFilter function. Create your filter with a series
of IF statements

Private Function MakeFilter() As Boolean
'if you are using string values in the combo, surround
'combo value with '' or "" (gender). If numerica value (due to
'lookup tables) don't surround (Occupation)
Dim strFilter As String
If Not IsNull(Me.ComboGender) Then
'assumes fld nm is gender and stuffing an M or F
strFilter = strFilter & _
"Gender = '" & Me.ComboGender & "' And "
Endif

If Not IsNull(Me.ComboOccupation) Then
'assumes fld nm is gender and stuffing an M or F
strFilter = strFilter & _
"Occupation = " & ComboOccupation & " And "
Endif
...continue this method for rest of combos

'now remove word AND at end of strFilter
if strFilter "" Then 'not all combos were null
strFilter = Left(strFilter,len(strFilter)-5)
Endif

'now set filter
Me.Filter = strFilter 'strFilter looks like Where statement
'without the word "Where"
'now turn on filter
Me.FilterOn = True

MakeFilter = True
End Function

This creates the filter and is called anytime you change a value in the
combos or resetting the filter.

I took the above steps and when I click on the button it doesn't run
the query or anything. It just clears the selection made in the combo
boxes. Here is the code that I have for the form. Thanks.

Private Sub Command6_Click()
Dim blnOK As Boolean
Me.Combo0 = Null
Me.Combo2 = Null
blnkOK = MakeFilter()
End Sub

Private Function MakeFilter() As Boolean
Dim strFilter As String
If Not IsNull(Me.Combo0) Then
strFilter = strFilter & _
"Gender = '" & Me.Combo0 & "' And "
End If
If Not IsNull(Me.Combo2) Then
strFilter = strFilter & _
"Occupation = " & Me.Combo2 & " And "
End If
If strFilter "" Then
strFilter = Left(strFilter, Len(strFilter) - 5)
End If
Me.Filter = strFilter
Me.FilterOn = True
MakeFilter = True
End Function

The code you passed may or may not work. I was guessing at the form's
field names.

If the above code is working, it should allow you to view all records as
it clears/resets the filter for the form.

Here is something you can do. Open up the code module containing
MakeFilter. On the vertical bar of the code window on the left hand
side next to
Private Function MakeFilter()
click it. A red dot should appear. This will step you thru your code.
As you step thru the code you can put your cursor over variables to
discover the value of it. This may show you whether or not the code is
executed and what happens when it is executed.

[jjyconsulting]

salad wrote:

jj***********@yahoo.com wrote:

salad wrote:

>jj***********@yahoo.com wrote:

Newbie needing some help. I have a tblParticipants. The fields
include gender, education_level, income, occupation etc., I'm trying to
create a form where a user can run a query from the form and just
choose the appropriate criterias from the combo boxes to get the
results. I also want the query to run even if there is not a value in
all the combo boxes ie., i want just all males with income level of
over $100,000...Any insights or help would be greatly appreciated.
Thanks.
I would create your combo boxes that will filter your record selection.
I assume you know how to create the combo boxes and supply the data.

I also assume you can do a little bit of vba coding.

Higlight all the combos for filtering, select the AfterUpdate event row
and enter
=MakeFilter()

Create a command button to reset the filter. This will clear out the
combo boxes and display all records. In the OnClick event, click on the
triple dot at end of row, select EventProcedure, and enter something like
Dim blnOK As Boolean
Me.ComboGender = Null
Me.ComboIncome = Null
'...null out rest of combos
blnOK = MakeFilter()

Now create your MakeFilter function. Create your filter with a series
of IF statements

Private Function MakeFilter() As Boolean
'if you are using string values in the combo, surround
'combo value with '' or "" (gender). If numerica value (due to
'lookup tables) don't surround (Occupation)
Dim strFilter As String
If Not IsNull(Me.ComboGender) Then
'assumes fld nm is gender and stuffing an M or F
strFilter = strFilter & _
"Gender = '" & Me.ComboGender & "' And "
Endif

If Not IsNull(Me.ComboOccupation) Then
'assumes fld nm is gender and stuffing an M or F
strFilter = strFilter & _
"Occupation = " & ComboOccupation & " And "
Endif
...continue this method for rest of combos

'now remove word AND at end of strFilter
if strFilter "" Then 'not all combos were null
strFilter = Left(strFilter,len(strFilter)-5)
Endif

'now set filter
Me.Filter = strFilter 'strFilter looks like Where statement
'without the word "Where"
'now turn on filter
Me.FilterOn = True

MakeFilter = True
End Function

This creates the filter and is called anytime you change a value in the
combos or resetting the filter.

I took the above steps and when I click on the button it doesn't run
the query or anything. It just clears the selection made in the combo
boxes. Here is the code that I have for the form. Thanks.

Private Sub Command6_Click()
Dim blnOK As Boolean
Me.Combo0 = Null
Me.Combo2 = Null
blnkOK = MakeFilter()
End Sub

Private Function MakeFilter() As Boolean
Dim strFilter As String
If Not IsNull(Me.Combo0) Then
strFilter = strFilter & _
"Gender = '" & Me.Combo0 & "' And "
End If
If Not IsNull(Me.Combo2) Then
strFilter = strFilter & _
"Occupation = " & Me.Combo2 & " And "
End If
If strFilter "" Then
strFilter = Left(strFilter, Len(strFilter) - 5)
End If
Me.Filter = strFilter
Me.FilterOn = True
MakeFilter = True
End Function

The code you passed may or may not work. I was guessing at the form's
field names.

If the above code is working, it should allow you to view all records as
it clears/resets the filter for the form.

Here is something you can do. Open up the code module containing
MakeFilter. On the vertical bar of the code window on the left hand
side next to
Private Function MakeFilter()
click it. A red dot should appear. This will step you thru your code.
As you step thru the code you can put your cursor over variables to
discover the value of it. This may show you whether or not the code is
executed and what happens when it is executed.

I don't get anything like that when I open the code module at all. On
the left hand side I have :
(General)
Combo0
Combo2
Command6
Detail
Form

On the right hand side there is:
(Declarations)
MakeFilter

I'm using Access 2002 if that helps any. Here is all that I want the
user to be able to do:

I want a user to select different criterias from combo boxes--such as
gender, occupation, income. then click on a button to run that query.
what is returned to the users is everything from the participants table
that match those criterias. Am I going about this all wrong? Any
insights would be greatly appreciated. I'm new to all this and just
started to get my feet wet. Thanks.

[salad]

jj***********@yahoo.com wrote:

salad wrote:

>>jj***********@yahoo.com wrote:

>>>salad wrote:
jj***********@yahoo.com wrote:
>Newbie needing some help. I have a tblParticipants. The fields
>include gender, education_level, income, occupation etc., I'm trying to
>create a form where a user can run a query from the form and just
>choose the appropriate criterias from the combo boxes to get the
>results. I also want the query to run even if there is not a value in
>all the combo boxes ie., i want just all males with income level of
>over $100,000...Any insights or help would be greatly appreciated.
>Thanks.
>

I would create your combo boxes that will filter your record selection.
I assume you know how to create the combo boxes and supply the data.

I also assume you can do a little bit of vba coding.

Higlight all the combos for filtering, select the AfterUpdate event row
and enter
=MakeFilter()

Create a command button to reset the filter. This will clear out the
combo boxes and display all records. In the OnClick event, click on the
triple dot at end of row, select EventProcedure, and enter something like
Dim blnOK As Boolean
Me.ComboGender = Null
Me.ComboIncome = Null
'...null out rest of combos
blnOK = MakeFilter()

Now create your MakeFilter function. Create your filter with a series
of IF statements

Private Function MakeFilter() As Boolean
'if you are using string values in the combo, surround
'combo value with '' or "" (gender). If numerica value (due to
'lookup tables) don't surround (Occupation)
Dim strFilter As String
If Not IsNull(Me.ComboGender) Then
'assumes fld nm is gender and stuffing an M or F
strFilter = strFilter & _
"Gender = '" & Me.ComboGender & "' And "
Endif

If Not IsNull(Me.ComboOccupation) Then
'assumes fld nm is gender and stuffing an M or F
strFilter = strFilter & _
"Occupation = " & ComboOccupation & " And "
Endif
...continue this method for rest of combos

'now remove word AND at end of strFilter
if strFilter "" Then 'not all combos were null
strFilter = Left(strFilter,len(strFilter)-5)
Endif

'now set filter
Me.Filter = strFilter 'strFilter looks like Where statement
'without the word "Where"
'now turn on filter
Me.FilterOn = True

MakeFilter = True
End Function

This creates the filter and is called anytime you change a value in the
combos or resetting the filter.
I took the above steps and when I click on the button it doesn't run
the query or anything. It just clears the selection made in the combo
boxes. Here is the code that I have for the form. Thanks.

Private Sub Command6_Click()
Dim blnOK As Boolean
Me.Combo0 = Null
Me.Combo2 = Null
blnkOK = MakeFilter()
End Sub

Private Function MakeFilter() As Boolean
Dim strFilter As String
If Not IsNull(Me.Combo0) Then
strFilter = strFilter & _
"Gender = '" & Me.Combo0 & "' And "
End If
If Not IsNull(Me.Combo2) Then
strFilter = strFilter & _
"Occupation = " & Me.Combo2 & " And "
End If
If strFilter "" Then
strFilter = Left(strFilter, Len(strFilter) - 5)
End If
Me.Filter = strFilter
Me.FilterOn = True
MakeFilter = True
End Function

The code you passed may or may not work. I was guessing at the form's
field names.

If the above code is working, it should allow you to view all records as
it clears/resets the filter for the form.

Here is something you can do. Open up the code module containing
MakeFilter. On the vertical bar of the code window on the left hand
side next to
Private Function MakeFilter()
click it. A red dot should appear. This will step you thru your code.
As you step thru the code you can put your cursor over variables to
discover the value of it. This may show you whether or not the code is
executed and what happens when it is executed.

I don't get anything like that when I open the code module at all. On
the left hand side I have :
(General)
Combo0
Combo2
Command6
Detail
Form

On the right hand side there is:
(Declarations)
MakeFilter

I'm using Access 2002 if that helps any. Here is all that I want the
user to be able to do:

I want a user to select different criterias from combo boxes--such as
gender, occupation, income. then click on a button to run that query.
what is returned to the users is everything from the participants table
that match those criterias. Am I going about this all wrong? Any
insights would be greatly appreciated. I'm new to all this and just
started to get my feet wet. Thanks.

OK. You could scroll thru the code till you find MakeFilter or click on
MakeFilter in the dropdown. Then to the left of the word
Function
on the window's vertical bar click on the bar. Close the code window on
open the form.

I don't know your programming skill. I recommend books like the MS
StepByStep to get you started in learning event programming. If link
wraps, see if turning of wordwrap assists. These books step you through
coding. If you'll remain with Access, maybe get the Developer's
Handbook by Getz for more advanced stuff.

http://www.amazon.com/gp/product/073...e=UTF8&s=books
http://www.amazon.com/gp/product/073...e=UTF8&s=books