Cascading Combobox changes entry on each form automatically

[sowieso]

Hello together,

I started working with access recently and have a question regarding cascading comboboxes. I recently completed the cascading combobox and it is working probperly. As follows my tables, I have one table with companies and thec country and in the other I have products which are on the market:

Table A:
CompanyName Country
CompanyB DK

Table B:
AutoID Product CompanyName


1 Pr.A CompanyA
2 Pr.B CompanyA

In my form I have a first combobox where you can select the company name and depending on this choice you only the the products offered by this company in the second combobox. All working fine so far.
If I choose now companyB in a new form, the products from companyA in previous forms are only shown by their AutoId, not by their name anymore. How could I solve this problem that product keeps staying in the other forms.

Thank you a lot

[ranman256]

the combo box should have 2 columns,
ID;PRODUCT

bound col = 1 (the ID)
set the COLUMNWIDTHS = 0;2
this hides the prodID and users only see PRODUCT NAME.

[sowieso]

Thanks for you help, but I already have changed this settings and it doesn't help. Product name is shown as long it fits with the company name last updated in a new form, if it does not match, AutoId is shown instead of product name.
For example if I am in an already filled out form and change the company name, product name is changing to AutoId because the filled in product are not from that company. Is it possible that the product name stay there no matter if company has changed? Or to cut the dependency from once filled out form to a the combobox of a new form?

[aytee111]

In the AfterUpdate event of the first combobox, you need to requery the combobox for the product.

Post your database so we can help further.

[sowieso]

Hello together,
Thanks for you help. I already did the Afterupdate Event. I uploaded a small sample of how the real data bank looks like. In form Bundle you can choose the company and select the products they offer and which are included in the product bundle, it is saved in table "Bundle".
What I mean now, if you switch through the different forms you can see that sometimes you see the name of the products in the field product bundle and sometimes only the autoID. Is it possible that you see always the name of the product.

TestFormular.zip

[aytee111]

The problem is not with the form, it is with the table design. Read up about "normalization" and learn what it is, it will be very helpful for future database designs. Also naming conventions.

1) company name table (Companies_tbl)
- must have an autonumber primary key, do not use the name, this changes too often (spelling mistakes, etc)
- give the primary key a name, such as CompanyID
- set the field size to the correct size, do not use Access defaults

2) item list table (Items_tbl)
- same autonumber primary key, ItemID
- CompanyID
- fix field sizes

3) bundle table (Bundles_tbl)
- BundleID
- name of bundle
- CompanyID

With the way you have it currently your reporting needs will be a nightmare.

[aytee111]

On rereading this, I need more clarification. You have companies which have products attached to them. When you enter a bundle, why are you even showing the product list? Does the user then select certain products from this list?