cascade combo box (1 Viewer)

[Pat Hartman]

The problem is that you are using Store rather than StoreID to make the relationships. You need to change the other two tables to have a numeric field that will be used to reference the StoreID NOT the StoreName. Relationships between tables are always on a data field in the many-side table to the primary key of the one-side table. You are trying to use a data field on the one-side table rather than its primary key. Just FYI, naming all your primary keys "ID" is poor practice and will cause nothing but confusion as you make relationships and build queries.

 

[accessuser67]

Before I had the primary key set to the storeId in the stores table but the combo box CategoryCombo was showing blank. But did you see how the CategoryCombo box sticks on the first record?

Your assistance is greatly appreciated.

Regards.

 

[Pat Hartman]

I have no idea where "NONE" is coming from if that is what you are talking about. Start by fixing the tables and we'll go from there. There is no point in trying to fix what you have.

 

[accessuser67]

Thanks for all the suggestions on this matter. Regards to everyone that helped out, I think I can proceed from here.

 

[accessuser67]

The problem is that you are using Store rather than StoreID to make the relationships. You need to change the other two tables to have a numeric field that will be used to reference the StoreID NOT the StoreName. Relationships between tables are always on a data field in the many-side table to the primary key of the one-side table. You are trying to use a data field on the one-side table rather than its primary key. Just FYI, naming all your primary keys "ID" is poor practice and will cause nothing but confusion as you make relationships and build queries.

If I were to change the primary key to storeId I would then have to change and look up in the store table for the correct StorId whenever I'm adding data to the category aisle table. I was trying to make entering data on all tables simple.

But yet I still the CategoryCombo box gets stuck on the first record. You think it is how I have my primary key setup?

 

[Pat Hartman]

1. You need to do some research on normalization so you understand how tables are related to each other.
2. You need to remove all the lookups from your tables. Having lookups in tables only causes problems. The lookups should be done on forms with combos.

Once you have fixed your schema, rebuild the combo so you get the correct settings. If it still doesn't work, repost the database.

 

[accessuser67]

Could I use a text field as my primary key in Access 2010? Or will this cause problems for combo boxes and other controls in a form?

Regards.

 

[Pat Hartman]

When dealing with large tables, long integers are more efficient for joins but there is nothing inherently "bad" about using text fields for PKs as long as they are relatively short and will NEVER change. In the long run, it will be better to bite the bullet now and switch to an autonumber. Even if you use an autonumber as the PK for a table, it is perfectly acceptable to have a user-friendly field as the human "key". Just make sure to add a unique index to this external "key" so duplicates are not added.

 

[Dell Inspiron 15]

Folks,
I have 2 Tables
Field Name (and data types):
Category ID (Autonumber)
Category (Text)
Sub-Category ID (Autonumber)
Cateogry ID (number)
Sub-Category (text)
Will be sufficient to display me sub-category commensurate to my clicking of Category? Help me please as I am not able to get it right.