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
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