After incorporating the Microsoft example based on a one-to-many relationship and their use the AfterUpdate() Event code/macro to give the second combo box its RowSource value, I remain without an acceptable solution. Even with copying verbatum, the VBA code and the product and categories tables. My problem, is that when I run the form, everthing works fine! However, when I try to save the results to a table, the category saves the category ID number, versus the description, and the field for the sub-category (i.e. products related to the primary category) remains blank. I have tried changing table references, etc, all in vain. I searched for something similar, but found no results - apologize if I missed something. This is a critical path item on this project - and the only thing left to resolve. Would appreciate any assistaqnce.

Hi,



You are correct to save the Primary Key instead of the Description in your table. That way, even if the value of the Description changes, your table can still find it through the Primary Key.

To be able to give you any advice on how to fix the issue, you may have to post a small copy of your db with test data so we can see how you have set it up.

Please include a detailed step by step instruction on how to duplicate your problem.

Thanks for your response. The tables and forms were taken verbatum from the MS Access Synchronized Combo List blog.
To wit:
1. Create a new form by clicking the Create tab and selecting Form Design.

2. In the Controls group on the Design tab click the combo box icon (If you move
your cursor over each of the icons in the Controls group, you'll see a tooltip that
names the control.) Move your cursor to the design section of the form and hold
your left mouse button down while you draw the combo box rectangle on the form.

3. Cancel the combo box wizard if it launches when you let go of the mouse button.

4. Repeat steps 2 and 3 to add another combo box to the form.

5. Select the first combo box, right click and select Properties.

6. In the Properties window make the following adjustments on the All tab:

Name: cboCategories
RowSourceType: Table/Query
RowSource: Categories
ColumnCount: 2
ColumnWidths: 0";2"
BoundColumn: 1
AfterUpdate: [Event Procedure]

NOTE: To create the AfterUpdate Event:
A. Select the Event tab on the Property Sheet
B. Select After Update
C. Click the button at the end of the row
D. Click Code Builder. The Visual Basic editor opens.

7. Copy the following code under the line, Private Sub cboCategories_AfterUpdate,
in the Visual Basic code window:

Me.cboProducts.RowSource = "SELECT ProductName FROM" & _
" Products WHERE CategoryID = " & Me.cboCategories & _
" ORDER BY ProductName"
Me.cboProducts = Me.cboProducts.ItemData(0)

Note: There are two tables involved in this design: the Categories table, where
the first combo box gets its data (row source); and the Products table, where
the second combo box gets its data. We build the second combo box in the
next step.

The code sets the row source of the second combo box, cboProducts,
to show only records in the Products table where the CategoryID matches the
CategoryID of the Category chosen in the first combo box.

If you open the Relationships window from the Database Tools tab, you can
see the two tables are connected, or related by the Category ID. The Products
table, which can contain many records that belong to one category contains
a CategoryID field to keep track of what category the product belongs to.

8. Select the second combo box. Right click and select Properties.

9. In the Properties window on the All tab, make the following adjustments
for the second combo box:

Name: cboProducts
RowSourceType: Table/Query
ColumnWidths: 3"
Width: 3"

It is critical that I'm able to pull forward the fields (descriptions vs ID #) for detailed reports, etc.

Tables are pretty straight forward, and again straight off of the MS blog.

Categories: Category ID and Category Name
======== ============
1 Absenses
2 White Papers

Products: ID ProductCode ProductName Descrsip Discontinue Category ID
===============================================================
3 NWTCO-3 Sick Leave [ ] Absenses
4 NWD15-1 Project Revew [ ] White Papers

As highlighted earlier, I'm getting, for example, the # 2 instead of White Papers in the Category Field, and nothing shows up in the table for products;
even through everything worked just fine when I ran the form. go figure.....

Hope this helps! Something this useful, should not be this difficult. Especially when MS provides you a complete sample! Thansk!

Hi,

It would really help to see the actual db. Is it possible for you to post a small copy with test data? Then you'll have to tell use which form to examine.

Cheers.

I tried to post; however, the file is 4.2MB - way to large. I'm trying to figure out a way to eliminate non-critical and relevent tables, queries, etc. But maybe the following summary might help..
The main menu allows you to select "Add / Edit Record", among other tasks. When I select this icon, and try to create a new record, it works fine (to include both synchronized boxes where when you change the primary box value, the subordinate box refreshes with only the relevent items. The problem is in storing the values specified in the synchronized cbo boxes on the primary save table. It stores all fields, but these. It seems simple enough. I just need to learn how to capture the relationship of the cbo's so I can correctly store the values of each field in the table. Any help would be gratefully appreciated. Thank you in advance!!!!!

Hi,

What are the Control Sources for the comboboxes? Make sure they are bound to the correct fields in the table.