Quad Cascading ComboBoxes

T

Thomas

Hello I am working on a database for a Tile company. We have well over 100
Suppliers with over 100 types of tile each. We need a way to filter
selections for products. Better ideas welcome.

I have been working on a Quad Cascading ComboBoxes the ComboBoxes work when
I have them set up /open it as a single form.

Code for single form the where clause in my code
WHERE (([Products.SupplierID]) = [Forms]![Orders Subform]![CompanyName]) " &
_

For subForm I added the [Orders]
WHERE (([Products.SupplierID]) = [Forms]![Orders]![Orders
Subform]![CompanyName]) " & _

I get no error for input just a blank ComboBox with header label only
Before I added the [Orders] I was prompt for input value.(when opened as a
subform)


When I try to get the code to work when the form is loaded as a SubForm the
code can't find the input information or something ?
The database is based on the Northwind sample database. I am using Access
2003.

Form: Names

Main form = Orders
SubForm =Orders SubForm

Here is the code

1st Combo ( This one works)

CompanyName
Row source Type = Table/Query
Row source = SELECT Suppliers.SupplierID, Suppliers.CompanyName FROM
Suppliers ORDER BY Suppliers.CompanyName;


2nd Combo

Category
Row source Type = Table/Query
Row Source is code in the on enter event

Private Sub Category_Enter()
Dim strSQL As String
strSQL = "SELECT DISTINCT CategoryID,CategoryName" & _
" From [Products] " & _
" WHERE (([Products.SupplierID]) = [Forms]![Orders]![Orders
Subform]![CompanyName]) " & _
" ORDER BY [CategoryName];"
Me!Category.RowSourceType = "Table/Query"
Me!Category.RowSource = strSQL

End Sub

3rd Combo

Size
Row source Type = Table/Query
Row Source is code in the on enter event


Private Sub Size_Enter()
Dim strSQL As String
strSQL = "SELECT DISTINCT TypeID,Typeorsize" & _
" From [Products] " & _
" WHERE ((Products.SupplierID) = [Forms]![Orders]![Orders
Subform]![CompanyName]) " & _
" And ((Products.CategoryID)= [Forms]![orders]![Orders
Subform]![Category])" & _
" ORDER BY [Typeorsize];"
Me!Size.RowSourceType = "Table/Query"
Me!Size.RowSource = strSQL
End Sub


4th ComboBox

ProductID
Row source Type = Table/Query
Row Source is code in the on enter event


Private Sub ProductID_Enter()
Dim strSQL As String
strSQL = "SELECT DISTINCT ProductID,ProductName" & _
" From [Products] " & _
" WHERE ((Products.SupplierID) = [Forms]![Orders]![Orders
Subform]![CompanyName]) " & _
" And ((Products.CategoryID)= [Forms]![Orders]![Orders
Subform]![Category])" & _
" And ((Products.TypeID)= [forms]![Orders]![Orders Subform]!Size) " & _
" ORDER BY [ProductName];"
Me!ProductID.RowSourceType = "Table/Query"
Me!ProductID.RowSource = strSQL
End Sub


Thanks in advance

Thomas
 
A

Allen Browne

From your example, I take it that these combos have 2 columns. The first is
the bound column, and it is zero-width. Consequently Access shows the text
in the 2nd column.

When you filter combos like that, there is nothing Access can show if the
combo is filtered in such as way that the display value is not present.
Consequently, you must load the combo as soon as Access moves into a record.
The combo's Enter event won't do: you must do it in the Current event of the
Form (as well as the AfterUpdate event of the controls it depends upon.)
Even then, there is sometimes a timing problem and the combo may show as
blank even when the value is present.

The example below is for the last combo in the sequence. It assumes that you
don't want any products available until the user has chosen a supplier,
category and size, and so it sets up a WHERE clause that will be false for
all records if any of these 3 are null. If it doesn't work correctly, you
can print the query statement to the Immediate Window (Ctrl+G), and make a
query like that to see what's wrong. It assumes all 3 fields are numeric: if
they are text you need extra quotes. For info on how to do quotes inside
quotes, see:
http://allenbrowne.com/casu-17.html
Hopefully you can sort out the 3 earlier combos, since they are actually
simpler.

Private Sub Size_AfterUpdate()
Const strcStub = "SELECT DISTINCT ProductID, ProductName FROM Products
WHERE ("
Const strcTail = ") ORDER BY ProductName;"

If IsNull(Me.CompanyName) OR IsNull(Me.CategoryID) OR IsNull(Me.Size)
Then
strWhere = "False"
Else
strWhere = "(SupplierID = " & Me.CompanyName & _
") AND (CategoryID = " & Me.Category & _
") AND (Size = " & Me.Size & ")"
End If
'Debug.Print strcStub & strWhere & strcTail
Me.ProductID.RowSource = strcStub & strWhere & strcTail
End Sub

Private Sub Category_AfterUpdate()
Call Size_AfterUpdate
End Sub

Private Sub CompanyName_AfterUpdate()
Call Size_AfterUpdate
End Sub

Private Sub Form_Current()
Call Size_AfterUpdate
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thomas said:
Hello I am working on a database for a Tile company. We have well over 100
Suppliers with over 100 types of tile each. We need a way to filter
selections for products. Better ideas welcome.

I have been working on a Quad Cascading ComboBoxes the ComboBoxes work
when I have them set up /open it as a single form.

Code for single form the where clause in my code
WHERE (([Products.SupplierID]) = [Forms]![Orders Subform]![CompanyName]) "
& _

For subForm I added the [Orders]
WHERE (([Products.SupplierID]) = [Forms]![Orders]![Orders
Subform]![CompanyName]) " & _

I get no error for input just a blank ComboBox with header label only
Before I added the [Orders] I was prompt for input value.(when opened as a
subform)


When I try to get the code to work when the form is loaded as a SubForm
the code can't find the input information or something ?
The database is based on the Northwind sample database. I am using Access
2003.

Form: Names

Main form = Orders
SubForm =Orders SubForm

Here is the code

1st Combo ( This one works)

CompanyName
Row source Type = Table/Query
Row source = SELECT Suppliers.SupplierID, Suppliers.CompanyName FROM
Suppliers ORDER BY Suppliers.CompanyName;


2nd Combo

Category
Row source Type = Table/Query
Row Source is code in the on enter event

Private Sub Category_Enter()
Dim strSQL As String
strSQL = "SELECT DISTINCT CategoryID,CategoryName" & _
" From [Products] " & _
" WHERE (([Products.SupplierID]) = [Forms]![Orders]![Orders
Subform]![CompanyName]) " & _
" ORDER BY [CategoryName];"
Me!Category.RowSourceType = "Table/Query"
Me!Category.RowSource = strSQL

End Sub

3rd Combo

Size
Row source Type = Table/Query
Row Source is code in the on enter event


Private Sub Size_Enter()
Dim strSQL As String
strSQL = "SELECT DISTINCT TypeID,Typeorsize" & _
" From [Products] " & _
" WHERE ((Products.SupplierID) = [Forms]![Orders]![Orders
Subform]![CompanyName]) " & _
" And ((Products.CategoryID)= [Forms]![orders]![Orders
Subform]![Category])" & _
" ORDER BY [Typeorsize];"
Me!Size.RowSourceType = "Table/Query"
Me!Size.RowSource = strSQL
End Sub


4th ComboBox

ProductID
Row source Type = Table/Query
Row Source is code in the on enter event


Private Sub ProductID_Enter()
Dim strSQL As String
strSQL = "SELECT DISTINCT ProductID,ProductName" & _
" From [Products] " & _
" WHERE ((Products.SupplierID) = [Forms]![Orders]![Orders
Subform]![CompanyName]) " & _
" And ((Products.CategoryID)= [Forms]![Orders]![Orders
Subform]![Category])" & _
" And ((Products.TypeID)= [forms]![Orders]![Orders Subform]!Size) " & _
" ORDER BY [ProductName];"
Me!ProductID.RowSourceType = "Table/Query"
Me!ProductID.RowSource = strSQL
End Sub


Thanks in advance

Thomas
Click to expand...
 
T

Thomas

Hello Allen and thank you for your reply.
I was able to build a new SQL statement with the code builder.

Do you have any ideas or a way to select and look up different prices ?

Retail
Contractor
Dealer

And toggle Tax on and off ?




It all works now just as a follow up here is the code for the last combobox.


Private Sub ProductID_Enter()
Dim strSQL As String
strSQL = "SELECT DISTINCT ProductID,ProductName" & _
" From [Products] " & _
" WHERE ((Products.SupplierID) = [Forms]![Orders]![Orders
Subform].[Form]![CompanyName] ) " & _
" And ((Products.CategoryID)= [Forms]![Orders]![Orders
Subform].Form![Category] )" & _
" And ((Products.TypeID)= [forms]![Orders]![Orders
Subform].Form![Size] )" & _
" ORDER BY [ProductName];"
Me!ProductID.RowSourceType = "Table/Query"
Me!ProductID.RowSource = strSQL
End Sub


Thank You for your help





Allen Browne said:
From your example, I take it that these combos have 2 columns. The first is the bound column, and
it is zero-width. Consequently Access shows the text in the 2nd column.

When you filter combos like that, there is nothing Access can show if the combo is filtered in
such as way that the display value is not present. Consequently, you must load the combo as soon
as Access moves into a record. The combo's Enter event won't do: you must do it in the Current
event of the Form (as well as the AfterUpdate event of the controls it depends upon.) Even then,
there is sometimes a timing problem and the combo may show as blank even when the value is
present.

The example below is for the last combo in the sequence. It assumes that you don't want any
products available until the user has chosen a supplier, category and size, and so it sets up a
WHERE clause that will be false for all records if any of these 3 are null. If it doesn't work
correctly, you can print the query statement to the Immediate Window (Ctrl+G), and make a query
like that to see what's wrong. It assumes all 3 fields are numeric: if they are text you need
extra quotes. For info on how to do quotes inside quotes, see:
http://allenbrowne.com/casu-17.html
Hopefully you can sort out the 3 earlier combos, since they are actually simpler.

Private Sub Size_AfterUpdate()
Const strcStub = "SELECT DISTINCT ProductID, ProductName FROM Products WHERE ("
Const strcTail = ") ORDER BY ProductName;"

If IsNull(Me.CompanyName) OR IsNull(Me.CategoryID) OR IsNull(Me.Size) Then
strWhere = "False"
Else
strWhere = "(SupplierID = " & Me.CompanyName & _
") AND (CategoryID = " & Me.Category & _
") AND (Size = " & Me.Size & ")"
End If
'Debug.Print strcStub & strWhere & strcTail
Me.ProductID.RowSource = strcStub & strWhere & strcTail
End Sub

Private Sub Category_AfterUpdate()
Call Size_AfterUpdate
End Sub

Private Sub CompanyName_AfterUpdate()
Call Size_AfterUpdate
End Sub

Private Sub Form_Current()
Call Size_AfterUpdate
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thomas said:
Hello I am working on a database for a Tile company. We have well over 100 Suppliers with over
100 types of tile each. We need a way to filter selections for products. Better ideas welcome.

I have been working on a Quad Cascading ComboBoxes the ComboBoxes work when I have them set up
/open it as a single form.

Code for single form the where clause in my code
WHERE (([Products.SupplierID]) = [Forms]![Orders Subform]![CompanyName]) " & _

For subForm I added the [Orders]
WHERE (([Products.SupplierID]) = [Forms]![Orders]![Orders Subform]![CompanyName]) " & _

I get no error for input just a blank ComboBox with header label only
Before I added the [Orders] I was prompt for input value.(when opened as a subform)


When I try to get the code to work when the form is loaded as a SubForm the code can't find the
input information or something ?
The database is based on the Northwind sample database. I am using Access 2003.

Form: Names

Main form = Orders
SubForm =Orders SubForm

Here is the code

1st Combo ( This one works)

CompanyName
Row source Type = Table/Query
Row source = SELECT Suppliers.SupplierID, Suppliers.CompanyName FROM Suppliers ORDER BY
Suppliers.CompanyName;


2nd Combo

Category
Row source Type = Table/Query
Row Source is code in the on enter event

Private Sub Category_Enter()
Dim strSQL As String
strSQL = "SELECT DISTINCT CategoryID,CategoryName" & _
" From [Products] " & _
" WHERE (([Products.SupplierID]) = [Forms]![Orders]![Orders Subform]![CompanyName]) " & _
" ORDER BY [CategoryName];"
Me!Category.RowSourceType = "Table/Query"
Me!Category.RowSource = strSQL

End Sub

3rd Combo

Size
Row source Type = Table/Query
Row Source is code in the on enter event


Private Sub Size_Enter()
Dim strSQL As String
strSQL = "SELECT DISTINCT TypeID,Typeorsize" & _
" From [Products] " & _
" WHERE ((Products.SupplierID) = [Forms]![Orders]![Orders Subform]![CompanyName]) " & _
" And ((Products.CategoryID)= [Forms]![orders]![Orders Subform]![Category])" & _
" ORDER BY [Typeorsize];"
Me!Size.RowSourceType = "Table/Query"
Me!Size.RowSource = strSQL
End Sub


4th ComboBox

ProductID
Row source Type = Table/Query
Row Source is code in the on enter event


Private Sub ProductID_Enter()
Dim strSQL As String
strSQL = "SELECT DISTINCT ProductID,ProductName" & _
" From [Products] " & _
" WHERE ((Products.SupplierID) = [Forms]![Orders]![Orders Subform]![CompanyName]) " & _
" And ((Products.CategoryID)= [Forms]![Orders]![Orders Subform]![Category])" & _
" And ((Products.TypeID)= [forms]![Orders]![Orders Subform]!Size) " & _
" ORDER BY [ProductName];"
Me!ProductID.RowSourceType = "Table/Query"
Me!ProductID.RowSource = strSQL
End Sub


Thanks in advance

Thomas
Click to expand...
Click to expand...
 
A

Allen Browne

This is probably best as a new question.

When you post it, include information about how your table structure looks.
Presumably you have fields such as:
ProductID which product this price is for
PriceTypeID 'retail', 'contractor', 'dealer', etc
PriceEach currency
If so, you can use DLookup() to get the relevent price for the ProductID and
PriceTypeID. Details in:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

For tax, my preferred approach is to store the tax rate (percentage) in a
field in the OrderDetail table. This approach copes with:
- orders where some items are tax exempt and some are not;
- changes to the tax rate over time.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thomas said:
Hello Allen and thank you for your reply.
I was able to build a new SQL statement with the code builder.

Do you have any ideas or a way to select and look up different prices ?

Retail
Contractor
Dealer

And toggle Tax on and off ?




It all works now just as a follow up here is the code for the last
combobox.


Private Sub ProductID_Enter()
Dim strSQL As String
strSQL = "SELECT DISTINCT ProductID,ProductName" & _
" From [Products] " & _
" WHERE ((Products.SupplierID) = [Forms]![Orders]![Orders
Subform].[Form]![CompanyName] ) " & _
" And ((Products.CategoryID)= [Forms]![Orders]![Orders
Subform].Form![Category] )" & _
" And ((Products.TypeID)= [forms]![Orders]![Orders
Subform].Form![Size] )" & _
" ORDER BY [ProductName];"
Me!ProductID.RowSourceType = "Table/Query"
Me!ProductID.RowSource = strSQL
End Sub


Thank You for your help

Allen Browne said:
From your example, I take it that these combos have 2 columns. The first
is the bound column, and it is zero-width. Consequently Access shows the
text in the 2nd column.

When you filter combos like that, there is nothing Access can show if the
combo is filtered in such as way that the display value is not present.
Consequently, you must load the combo as soon as Access moves into a
record. The combo's Enter event won't do: you must do it in the Current
event of the Form (as well as the AfterUpdate event of the controls it
depends upon.) Even then, there is sometimes a timing problem and the
combo may show as blank even when the value is present.

The example below is for the last combo in the sequence. It assumes that
you don't want any products available until the user has chosen a
supplier, category and size, and so it sets up a WHERE clause that will
be false for all records if any of these 3 are null. If it doesn't work
correctly, you can print the query statement to the Immediate Window
(Ctrl+G), and make a query like that to see what's wrong. It assumes all
3 fields are numeric: if they are text you need extra quotes. For info on
how to do quotes inside quotes, see:
http://allenbrowne.com/casu-17.html
Hopefully you can sort out the 3 earlier combos, since they are actually
simpler.

Private Sub Size_AfterUpdate()
Const strcStub = "SELECT DISTINCT ProductID, ProductName FROM Products
WHERE ("
Const strcTail = ") ORDER BY ProductName;"

If IsNull(Me.CompanyName) OR IsNull(Me.CategoryID) OR IsNull(Me.Size)
Then
strWhere = "False"
Else
strWhere = "(SupplierID = " & Me.CompanyName & _
") AND (CategoryID = " & Me.Category & _
") AND (Size = " & Me.Size & ")"
End If
'Debug.Print strcStub & strWhere & strcTail
Me.ProductID.RowSource = strcStub & strWhere & strcTail
End Sub

Private Sub Category_AfterUpdate()
Call Size_AfterUpdate
End Sub

Private Sub CompanyName_AfterUpdate()
Call Size_AfterUpdate
End Sub

Private Sub Form_Current()
Call Size_AfterUpdate
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thomas said:
Hello I am working on a database for a Tile company. We have well over
100 Suppliers with over 100 types of tile each. We need a way to filter
selections for products. Better ideas welcome.

I have been working on a Quad Cascading ComboBoxes the ComboBoxes work
when I have them set up /open it as a single form.

Code for single form the where clause in my code
WHERE (([Products.SupplierID]) = [Forms]![Orders Subform]![CompanyName])
" & _

For subForm I added the [Orders]
WHERE (([Products.SupplierID]) = [Forms]![Orders]![Orders
Subform]![CompanyName]) " & _

I get no error for input just a blank ComboBox with header label only
Before I added the [Orders] I was prompt for input value.(when opened as
a subform)


When I try to get the code to work when the form is loaded as a SubForm
the code can't find the input information or something ?
The database is based on the Northwind sample database. I am using
Access 2003.

Form: Names

Main form = Orders
SubForm =Orders SubForm

Here is the code

1st Combo ( This one works)

CompanyName
Row source Type = Table/Query
Row source = SELECT Suppliers.SupplierID, Suppliers.CompanyName FROM
Suppliers ORDER BY Suppliers.CompanyName;


2nd Combo

Category
Row source Type = Table/Query
Row Source is code in the on enter event

Private Sub Category_Enter()
Dim strSQL As String
strSQL = "SELECT DISTINCT CategoryID,CategoryName" & _
" From [Products] " & _
" WHERE (([Products.SupplierID]) = [Forms]![Orders]![Orders
Subform]![CompanyName]) " & _
" ORDER BY [CategoryName];"
Me!Category.RowSourceType = "Table/Query"
Me!Category.RowSource = strSQL

End Sub

3rd Combo

Size
Row source Type = Table/Query
Row Source is code in the on enter event


Private Sub Size_Enter()
Dim strSQL As String
strSQL = "SELECT DISTINCT TypeID,Typeorsize" & _
" From [Products] " & _
" WHERE ((Products.SupplierID) = [Forms]![Orders]![Orders
Subform]![CompanyName]) " & _
" And ((Products.CategoryID)= [Forms]![orders]![Orders
Subform]![Category])" & _
" ORDER BY [Typeorsize];"
Me!Size.RowSourceType = "Table/Query"
Me!Size.RowSource = strSQL
End Sub


4th ComboBox

ProductID
Row source Type = Table/Query
Row Source is code in the on enter event


Private Sub ProductID_Enter()
Dim strSQL As String
strSQL = "SELECT DISTINCT ProductID,ProductName" & _
" From [Products] " & _
" WHERE ((Products.SupplierID) = [Forms]![Orders]![Orders
Subform]![CompanyName]) " & _
" And ((Products.CategoryID)= [Forms]![Orders]![Orders
Subform]![Category])" & _
" And ((Products.TypeID)= [forms]![Orders]![Orders Subform]!Size) " &
_
" ORDER BY [ProductName];"
Me!ProductID.RowSourceType = "Table/Query"
Me!ProductID.RowSource = strSQL
End Sub


Thanks in advance

Thomas
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

Cascading Combo in datasheet view 1
combobox population record dependent 4
Many-to-many subform guidance needed 6
Form Problem 1
Access Control on subform / combo box requesting parameter 0
Form/Query Problem 2
Main Form updates Sub-Form and associated table? 1
automatically entering data 1

Top