Cascading combo box question

[amy3000]

I followed the instructions on Martin Green's website(

http://www.fontstuff.com/access/acctut10.htm).

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.

 

[PHV]

An unbound control in the detail section of a continuous form has the same value for all displayed records.
Have you tried to put the combo in the header section of the continuous subform, populating a bound textbox in the detail section ?
Or perhaps bind the combo with the relevant field.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[amy3000]

Thanks for the response PHV but neither worked. I think I'll just need to instruct the people doing data entry to ignore it.

 

[formerTexan]

Hello Amy,

Looking at your initial post, I see that you have some more fundemental issues with the table structure and normalization. Setting these to rights will make subsequent life go easier and allow your combo boxes to work correctly.


1. Tables should have a Primary Key field that provides a unique identifier for each record. This field usually should be non-data related. (there has been lots of past postings and discussions that go into further detail) An autonumber field may suffice and is satisfactory in many situations.

2. Where data can have a one-to-many relationship, two tables are called for. One Category may have many Products. Therefore you need to have a "look up" table for Categories with a primary key field CategoryID (or some other appropriate name).

Instead of storing the Category name in the Product table, store only the CategoryID.

And you also need a ProductID field in the Product table. This in turn will provide the relationship to the Order table. An OrderID will provide the relationship to the OrderDetail table.

At this point you can sort out your combo boxes. You will need a bound (to the Orders table) combo box in a continuous form. The combo box's row source should be a query or SQL string with the Category and Products tables.

Cheers,
Bill