Combo box to filter records (Tina)

D

deeds

I have incorporated the code below that Tina suggested...

I followed the instructions below to
add 3 combo boxes to a header of a form with the details below. Now, when I
add one combo box...it works great...as soon as I add another combo box
nothing works...no data is returned. Why when i add more than 1 combo box
does it not bring back results? Can anyone help me figure out why this is
not working for me? What am I missing? Thanks much!

in the Header section of the form, add the unbound combo boxes. i'll call
them cboVendor, cboCategory, and cboDepartment. in the form's Properties
box, click on the RecordSource property and then the ellipsis (...) button
at the right side, which will take you into the Query Builder. add the
Contacts table to the top section, and pull the fields you want to see into
the grid below. in the field that holds the vendor data, add the following
criteria, as

Forms!FormName!cboVendor Or Forms!FormName!cboVendor Is Null

in the field that holds the category data, add the following criteria, as

Forms!FormName!cboCategory Or Forms!FormName!cboCategory Is Null

do the same for the field that holds the department data.

back in the form Design view, add a command button to the Header section.
add code to the button's Click event procedure, as

Me.Requery

now when you open the form in Form view, all the Contacts records should
display. choose a selection in any combination of the combo boxes, from one
to all three, and click the command button to requery the form's
RecordSource. you may want to add another command button to "Display all
records" again. add code to the button's Click event procedure, as

Me!cboVendor = Null
Me!cboCategory = Null
Me!cboDepartment = Null
Me.Requery
 
S

SteveS

Please post:

the SQL of your form
the names and the SQL of the row source of the combo boxes
and the bound column of the combo boxes

Maybe we can see a problem.
 
D

deeds

Thanks....here is the code from the record source of the form: Now, when I
add one of the combo boxes only..I can choose a plant and have it filter on
that....however, when I add a second combo box neither work...it comes back
with no records on the form. I cannot figure this one out....Thanks again!
The combo boxes are PlantCombo and ProdCombo.

SELECT [Per Pound].[Product Name], [Per Pound].[Branch Plant Id], [Per
Pound].[Prod Level 5], [Per Pound].[Level 5 Desc], [Per Pound].[Product
Number], [Per Pound].[SumOfInvoiced Cost], [Per Pound].[SumOfInvoiced
Pounds], [Per Pound].PerPound, [Per Pound].Adjustment, [Per Pound].[New Cost]
FROM [Per Pound]
WHERE ((([Per Pound].[Product Name])=[Forms]![MultiChoose2]![ProdCombo]) AND
(([Per Pound].[Branch Plant Id])=[Forms]![MultiChoose2]![PlantCombo])) OR
((([Forms]![MultiChoose2]![ProdCombo]) Is Null) AND
(([Forms]![MultiChoose2]![PlantCombo]) Is Null));
 
D

deeds

To add...I noticed that when I choose something in each combo box it brings
back results...however, when I choose from one combo box and leave the other
one blank...it does not return data...something with the "null"? Basically I
want to be able to either choose 1 or 2 combo boxes...thanks again!

deeds said:
Thanks....here is the code from the record source of the form: Now, when I
add one of the combo boxes only..I can choose a plant and have it filter on
that....however, when I add a second combo box neither work...it comes back
with no records on the form. I cannot figure this one out....Thanks again!
The combo boxes are PlantCombo and ProdCombo.

SELECT [Per Pound].[Product Name], [Per Pound].[Branch Plant Id], [Per
Pound].[Prod Level 5], [Per Pound].[Level 5 Desc], [Per Pound].[Product
Number], [Per Pound].[SumOfInvoiced Cost], [Per Pound].[SumOfInvoiced
Pounds], [Per Pound].PerPound, [Per Pound].Adjustment, [Per Pound].[New Cost]
FROM [Per Pound]
WHERE ((([Per Pound].[Product Name])=[Forms]![MultiChoose2]![ProdCombo]) AND
(([Per Pound].[Branch Plant Id])=[Forms]![MultiChoose2]![PlantCombo])) OR
((([Forms]![MultiChoose2]![ProdCombo]) Is Null) AND
(([Forms]![MultiChoose2]![PlantCombo]) Is Null));


SteveS said:
Please post:

the SQL of your form
the names and the SQL of the row source of the combo boxes
and the bound column of the combo boxes

Maybe we can see a problem.
Click to expand...
Click to expand...
 
S

SteveS

To add...I noticed that when I choose something in each combo box it brings
back results...however, when I choose from one combo box and leave the other
one blank...it does not return data...something with the "null"? Basically I
want to be able to either choose 1 or 2 combo boxes...thanks again!
Click to expand...

The problem is in the WHERE clause.

When Tina wrote (modified for your object names):

[Per Pound].[Product Name] = [Forms]![MultiChoose2]![ProdCombo] OR
[Forms]![MultiChoose2]![ProdCombo] Is Null

the first part evaluates to the [Product Name] = the value of the combo box.

and [Forms]![MultiChoose2]![ProdCombo] Is Null evaluates to TRUE or FALSE.

If the combo box has a value, then the WHERE uses [Product Name] = the value
of the combo box and the condition must evaluate to TRUE to be in the
recordset.

If the combo box is NULL, then the statement evaluates to

[Product Name] = TRUE which matches all records.


The difference between "AND" and "OR"....

"AND" means all conditions have to be met, ie the value for the field
[Product Name] has to match the entry in the combo box (the condition have to
be TRUE) for all conditions for the record to be included in the recordset.
If there are three condition, all three conditions must be TRUE.


"OR" means that if one or more conditions evaluate to TRUE the record is
included in the returned recordset. If there are three condition, only one of
the conditions must be TRUE.


You want to be able to select either combo boxes or both. So you cannot use
"AND" in the WHERE clause.


Try this as your recordsource:

SELECT [Per Pound].[Product Name], [Per Pound].[Branch Plant Id], [Per
Pound].[Prod Level 5], [Per Pound].[Level 5 Desc], [Per Pound].[Product
Number], [Per Pound].[SumOfInvoiced Cost], [Per Pound].[SumOfInvoiced
Pounds], [Per Pound].PerPound, [Per Pound].Adjustment, [Per Pound].[New Cost]
FROM [Per Pound]
WHERE ([Per Pound].[Product Name] = [Forms]![MultiChoose2]![ProdCombo] OR
[Forms]![MultiChoose2]![ProdCombo] Is Null) OR ([Per Pound].[Branch Plant Id]
= [Forms]![MultiChoose2]![PlantCombo] OR [Forms]![MultiChoose2]![PlantCombo]
Is Null);



HTH
 
D

deeds

Still no luck....same thing happening. When I choose a plant it does not
filter on plant. I can choose a product type and all come up at all
plants...however, I can't seem to get the chosen product to come up at the
chosen plant only. Any ideas? Thanks.

SteveS said:
To add...I noticed that when I choose something in each combo box it brings
back results...however, when I choose from one combo box and leave the other
one blank...it does not return data...something with the "null"? Basically I
want to be able to either choose 1 or 2 combo boxes...thanks again!
Click to expand...

The problem is in the WHERE clause.

When Tina wrote (modified for your object names):

[Per Pound].[Product Name] = [Forms]![MultiChoose2]![ProdCombo] OR
[Forms]![MultiChoose2]![ProdCombo] Is Null

the first part evaluates to the [Product Name] = the value of the combo box.

and [Forms]![MultiChoose2]![ProdCombo] Is Null evaluates to TRUE or FALSE.

If the combo box has a value, then the WHERE uses [Product Name] = the value
of the combo box and the condition must evaluate to TRUE to be in the
recordset.

If the combo box is NULL, then the statement evaluates to

[Product Name] = TRUE which matches all records.


The difference between "AND" and "OR"....

"AND" means all conditions have to be met, ie the value for the field
[Product Name] has to match the entry in the combo box (the condition have to
be TRUE) for all conditions for the record to be included in the recordset.
If there are three condition, all three conditions must be TRUE.


"OR" means that if one or more conditions evaluate to TRUE the record is
included in the returned recordset. If there are three condition, only one of
the conditions must be TRUE.


You want to be able to select either combo boxes or both. So you cannot use
"AND" in the WHERE clause.


Try this as your recordsource:

SELECT [Per Pound].[Product Name], [Per Pound].[Branch Plant Id], [Per
Pound].[Prod Level 5], [Per Pound].[Level 5 Desc], [Per Pound].[Product
Number], [Per Pound].[SumOfInvoiced Cost], [Per Pound].[SumOfInvoiced
Pounds], [Per Pound].PerPound, [Per Pound].Adjustment, [Per Pound].[New Cost]
FROM [Per Pound]
WHERE ([Per Pound].[Product Name] = [Forms]![MultiChoose2]![ProdCombo] OR
[Forms]![MultiChoose2]![ProdCombo] Is Null) OR ([Per Pound].[Branch Plant Id]
= [Forms]![MultiChoose2]![PlantCombo] OR [Forms]![MultiChoose2]![PlantCombo]
Is Null);



HTH
Click to expand...
 
B

BruceM

FWIW, there should be four possiblilties there: ProdCombo and PlantCombo
both contain values; ProdCombo contains a value and PlantCombo does not;
PlantCombo contains a value and ProdCombo does not; neither contains a
value. If you base the form on a query, then go to the query design grid
you can add as the criteria for the ProductName field:
[Forms]![MultiChoose2]![ProdCombo] OR [Forms]![MultiChoose2]![ProdCombo] Is
Null
Do the same thing with PlantCombo, except use PlantCombo as the criteria
expression.
Switch the query in SQL view to study the resulting code.
You could probably just requery in the After Update event for each combo
box.

deeds said:
Still no luck....same thing happening. When I choose a plant it does not
filter on plant. I can choose a product type and all come up at all
plants...however, I can't seem to get the chosen product to come up at the
chosen plant only. Any ideas? Thanks.

SteveS said:
To add...I noticed that when I choose something in each combo box it
brings
back results...however, when I choose from one combo box and leave the
other
one blank...it does not return data...something with the "null"?
Basically I
want to be able to either choose 1 or 2 combo boxes...thanks again!
Click to expand...

The problem is in the WHERE clause.

When Tina wrote (modified for your object names):

[Per Pound].[Product Name] = [Forms]![MultiChoose2]![ProdCombo] OR
[Forms]![MultiChoose2]![ProdCombo] Is Null

the first part evaluates to the [Product Name] = the value of the combo
box.

and [Forms]![MultiChoose2]![ProdCombo] Is Null evaluates to TRUE or
FALSE.

If the combo box has a value, then the WHERE uses [Product Name] = the
value
of the combo box and the condition must evaluate to TRUE to be in the
recordset.

If the combo box is NULL, then the statement evaluates to

[Product Name] = TRUE which matches all records.


The difference between "AND" and "OR"....

"AND" means all conditions have to be met, ie the value for the field
[Product Name] has to match the entry in the combo box (the condition
have to
be TRUE) for all conditions for the record to be included in the
recordset.
If there are three condition, all three conditions must be TRUE.


"OR" means that if one or more conditions evaluate to TRUE the record is
included in the returned recordset. If there are three condition, only
one of
the conditions must be TRUE.


You want to be able to select either combo boxes or both. So you cannot
use
"AND" in the WHERE clause.


Try this as your recordsource:

SELECT [Per Pound].[Product Name], [Per Pound].[Branch Plant Id], [Per
Pound].[Prod Level 5], [Per Pound].[Level 5 Desc], [Per Pound].[Product
Number], [Per Pound].[SumOfInvoiced Cost], [Per Pound].[SumOfInvoiced
Pounds], [Per Pound].PerPound, [Per Pound].Adjustment, [Per Pound].[New
Cost]
FROM [Per Pound]
WHERE ([Per Pound].[Product Name] = [Forms]![MultiChoose2]![ProdCombo]
OR
[Forms]![MultiChoose2]![ProdCombo] Is Null) OR ([Per Pound].[Branch Plant
Id]
= [Forms]![MultiChoose2]![PlantCombo] OR
[Forms]![MultiChoose2]![PlantCombo]
Is Null);



HTH
Click to expand...
Click to expand...
 
S

SteveS

What is the SQL of the row source for [Forms]![MultiChoose2]![PlantCombo]?
What is the bound column for [Forms]![MultiChoose2]![PlantCombo]?

What is the datatype for the field [Per Pound].[Branch Plant Id]? (Long Int?)



If you want, delete or change any sensitive info, do a compact and repair,
then WinZip it and email it to me. (I have Acc2K)
 
S

SteveS

I whipped up a table and a form... and you're right - it didn't work for me
either.
So I deleted the WHERE clause for the fom's recordsource. Then I added two
buttons in the form header.

One button's caption was "Filter" and the other was "No filter".

The code assumes the Field [Product Name] is a string and
the Field [Branch Plant Id] is a number



In the Click event of the "Filter" button put this code:

'-----------------------------------
Dim strFilter As String

strFilter = ""

'assumes
' Field [Product Name] is a string
' Field [Branch Plant Id] is a number

'Create filter
'entries in both combos
If Not IsNull(Me.ProdCombo) And Not IsNull(Me.PlantCombo) Then
strFilter = "[Product Name] = '" & Me.ProdCombo & "' AND "
strFilter = strFilter & " [Branch Plant Id] = " & Me.PlantCombo
'entry in product combo
ElseIf Not IsNull(Me.ProdCombo) Then
strFilter = "[Product Name] = '" & Me.ProdCombo & "'"
'entry in plant combo
ElseIf Not IsNull(Me.PlantCombo) Then
strFilter = "[Branch Plant Id] = " & Me.PlantCombo
End If

'Apply filter??
If Len(strFilter) > 0 Then
'yes - filter
Me.Filter = strFilter
Me.FilterOn = True
Else
' no remove filter
Me.Filter = ""
Me.FilterOn = False
End If
'-----------------------------------


For the click event of the "No Filter", put:

'-----------------------------------
Me.ProdCombo = Null
Me.PlantCombo = Null
Me.FilterOn = False
'-----------------------------------


Remove any code in the AfterUpdate events of the combo boxes.


HTh
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


SteveS said:
What is the SQL of the row source for [Forms]![MultiChoose2]![PlantCombo]?
What is the bound column for [Forms]![MultiChoose2]![PlantCombo]?

What is the datatype for the field [Per Pound].[Branch Plant Id]? (Long Int?)



If you want, delete or change any sensitive info, do a compact and repair,
then WinZip it and email it to me. (I have Acc2K)

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


deeds said:
Still no luck....same thing happening. When I choose a plant it does not
filter on plant. I can choose a product type and all come up at all
plants...however, I can't seem to get the chosen product to come up at the
chosen plant only. Any ideas? Thanks.
Click to expand...
Click to expand...
 
D

deeds

Thanks for the help!

SteveS said:
I whipped up a table and a form... and you're right - it didn't work for me
either.
So I deleted the WHERE clause for the fom's recordsource. Then I added two
buttons in the form header.

One button's caption was "Filter" and the other was "No filter".

The code assumes the Field [Product Name] is a string and
the Field [Branch Plant Id] is a number



In the Click event of the "Filter" button put this code:

'-----------------------------------
Dim strFilter As String

strFilter = ""

'assumes
' Field [Product Name] is a string
' Field [Branch Plant Id] is a number

'Create filter
'entries in both combos
If Not IsNull(Me.ProdCombo) And Not IsNull(Me.PlantCombo) Then
strFilter = "[Product Name] = '" & Me.ProdCombo & "' AND "
strFilter = strFilter & " [Branch Plant Id] = " & Me.PlantCombo
'entry in product combo
ElseIf Not IsNull(Me.ProdCombo) Then
strFilter = "[Product Name] = '" & Me.ProdCombo & "'"
'entry in plant combo
ElseIf Not IsNull(Me.PlantCombo) Then
strFilter = "[Branch Plant Id] = " & Me.PlantCombo
End If

'Apply filter??
If Len(strFilter) > 0 Then
'yes - filter
Me.Filter = strFilter
Me.FilterOn = True
Else
' no remove filter
Me.Filter = ""
Me.FilterOn = False
End If
'-----------------------------------


For the click event of the "No Filter", put:

'-----------------------------------
Me.ProdCombo = Null
Me.PlantCombo = Null
Me.FilterOn = False
'-----------------------------------


Remove any code in the AfterUpdate events of the combo boxes.


HTh
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


SteveS said:
What is the SQL of the row source for [Forms]![MultiChoose2]![PlantCombo]?
What is the bound column for [Forms]![MultiChoose2]![PlantCombo]?

What is the datatype for the field [Per Pound].[Branch Plant Id]? (Long Int?)



If you want, delete or change any sensitive info, do a compact and repair,
then WinZip it and email it to me. (I have Acc2K)

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


deeds said:
Still no luck....same thing happening. When I choose a plant it does not
filter on plant. I can choose a product type and all come up at all
plants...however, I can't seem to get the chosen product to come up at the
chosen plant only. Any ideas? Thanks.
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

Clearing combo boxes and a text box 2
Creating a Search using a combo box and then creating a report. 4
Combo or list box 0
Combo box based on another combo box 0
filter on combo box possible? 1
Combo and List boxes filter 1
Combine combo boxes with date range 16
Combo Box Adding Button to edit list items 5

Top