I followed the instructions on Martin Green's website( I created a table with the following fields: Category, Product, Product Number, Publication Date, Supplier (from another table)Notes. The Categories are types of printed materials eg., brochures, cards, letters. Each product is within one of those categories. I created an order and order details tables and forms. I was able to successfully create the cascading combo boxes so that only the products in a particular category were displayed. However, I wanted to have a continuous form or datasheet for the order detail subform to add multiple products per order. It works and records the data correctly but it doesn't display correctly to the data entry person. Here is how I want it to display:
Category Product Quantity Ordered
Brochures B1 500
Brochures B2 200
Letters L1 100
Posters P3 10
But when I enter each consequent item, the category displayed changes for all the products like this.
Category Product Quantity Ordered
Posters B1 500
Posters B2 200
Posters L1 100
Posters P3 10
I did a query and the data is being accurately recorded.
Here is the code I copied and modified from Green's website with some of his commentary. I always have to add the caveat that I don't really understand VBA, I'm just good at copying, pasting and modifying.
Private Sub cboCategory_AfterUpdate()
On Error Resume Next
cboProduct.RowSource = "Select tblProducts.Product " & _
"FROM tblProducts " & _
"WHERE tblProducts.Category = '" & cboCategory.Value & "' " & _
"ORDER BY tblProducts.Product;"
End Sub
Private Sub Form_Current()
On Error Resume Next
' Synchronise country combo with existing city
cboCategory = DLookup("[Category]", "tblCatProd", "[Product_Name]='" & cboProduct.Value & "'")
' Synchronise city combo with existing city
cboProduct.RowSource = "Select tblCatProd.Product_Name " & _
"FROM tblCatProd " & _
"WHERE tblCatProd.Category = '" & cboCategory.Value & "' " & _
"ORDER BY tblCatProd.Product_Name;"
End Sub
And this is the row source for the unbound category field:
SELECT DISTINCT [tblProducts].[Category] FROM tblProducts ORDER BY [tblProducts].[Category];
If there's a way to make this display accurately that would be great. If not, I'll just explain to the data entry people that they can ignore it.
I would have done a search but it hasn't been working for a few hours. Thanks.
Category Product Quantity Ordered
Brochures B1 500
Brochures B2 200
Letters L1 100
Posters P3 10
But when I enter each consequent item, the category displayed changes for all the products like this.
Category Product Quantity Ordered
Posters B1 500
Posters B2 200
Posters L1 100
Posters P3 10
I did a query and the data is being accurately recorded.
Here is the code I copied and modified from Green's website with some of his commentary. I always have to add the caveat that I don't really understand VBA, I'm just good at copying, pasting and modifying.
Private Sub cboCategory_AfterUpdate()
On Error Resume Next
cboProduct.RowSource = "Select tblProducts.Product " & _
"FROM tblProducts " & _
"WHERE tblProducts.Category = '" & cboCategory.Value & "' " & _
"ORDER BY tblProducts.Product;"
End Sub
Private Sub Form_Current()
On Error Resume Next
' Synchronise country combo with existing city
cboCategory = DLookup("[Category]", "tblCatProd", "[Product_Name]='" & cboProduct.Value & "'")
' Synchronise city combo with existing city
cboProduct.RowSource = "Select tblCatProd.Product_Name " & _
"FROM tblCatProd " & _
"WHERE tblCatProd.Category = '" & cboCategory.Value & "' " & _
"ORDER BY tblCatProd.Product_Name;"
End Sub
And this is the row source for the unbound category field:
SELECT DISTINCT [tblProducts].[Category] FROM tblProducts ORDER BY [tblProducts].[Category];
If there's a way to make this display accurately that would be great. If not, I'll just explain to the data entry people that they can ignore it.
I would have done a search but it hasn't been working for a few hours. Thanks.