More Combo Box help

[ssanfu]

Stay with me Dave

From the tblCategory I put the Category_PK into the tblProducts, right? Does it go in as "CategoryID_PK" -or- "CategoryID_FK"?

You can call the fields anything you want; the main idea is that the primary key (PK) value from "tblCategory" is entered into the foreign key (FK) field in "tblProducts". Usually, I create a mainform - subform between the two tables to enter the data.

How I name fields:
"CategoryID_PK" - this is the primary key (PK) in "tblCategories"
"CategoryID_FK" - this is the foreign key (FK) in "tblProducts"

If the category "Beverage" in "tblCategories" has a PK of 1, every Beverage in "tblProducts" (Pepsi, Coke, Tea) has a 1 in the FK field in "tblProducts".
If the category "Beverage" in "tblCategories" has a PK of 6, every Beverage in "tblProducts" (Pepsi, Coke, Tea) has a 6 in the FK field in "tblProducts".
This is how tables are related. This is a one-to-many relationship. Look at the images in post #8 again.

Did you enter the numbers? " What do you mean, I thought the numbers were generated by the FK?

The FK is just a field. You have to enter the data into the tables, or use a form or import the data.


Attached is a zip file with 3 mdb files. Tear each one apart to see the progression.

Open aCC_Box_Tables.mdb first. It has just the tables/relationship.


Next is bCC_Box_Tables.mdb. It tables and queries and a form.
Last is cCC_Box_Tables.mdb. It has the form "frmMain" and a form to add new categories and products.

[djclntn]

Ok Steve, I looked at all 3 of your databases. Thanks for the much effort!
1) In your post yesterday you had an picture of the Relationship Window illustrating exactly how to relate the Categrory_PK from the tblCategory à to the tblProducts (a one-to-many Relationship). The databeses that you sent me I see NO such Relationships…? 2) No matter how I joggles things around in the tblProducts I CANNOT get the Category_PK number to appear as you have in your tblProducts. In the tblCategory it’s “Category_PK” & in my tblProducts I have, “Category_PK” yet the field is blank! I think that’s the root of my problem. 3) Suppose I was to use your databases/form as a shell to implement what I want to use cascading cbx for, my first question is, after I picked a ‘Product’ & it’s ‘Category’ how I add a New Record?

Steve, I know that you (amongst others) have spent a tremendous amount of time working with me on this topic & I want to deeply give out a BIG thanks to you Steve, but I don’t feel right in leaving out June, Orange, PD & ling. The problem in which I’m having is trying to decide if I want to tear completely apart/stripe my (not one/several) car dealerships databases JUST so I can have the ‘Make’ fld be a dropdown menu e.g. “Ford” which will then only populate “Ford” models in my “Model” dropdown list. I don’t mean to sound unappreciated towards you because I’m learning; the goal (or my objective) I had 3-weeks ago doesn’t seem important after seeing what’s all involved. Right now, in my Dealership’s database(s) I have it set up as, went I enter a vehicles’ model, “Mustang” the make “Ford” pops into the ‘Make’ fld. When I first started these dealership databases I thought that was great, I didn’t realize that I would over time accumulate 50-60+ ‘Models.’ If I’m repeating myself & already told you this, please I do apologize. Sometimes in life you think something will be simple & it turns out to seem more complicated than you originally barging for.

With that said, regardless of what I decide to do I want to know why in my tblProducts the Category_PK is blank? Would you be interested in me sending you my database/what I have? I can’t Attached it, I would have to send you a link to Box.com. Let me know & please Steve I really appreciate & thank you for your time!!!

[djclntn]

Wait, Steve in the tblProducts, in the Category_PK fld did you manually typed in the numbers? Why wouldn't the Category_PK (auto numbers) populate in the tblProducts if somehow the 2-tbls were related? But I related thenexactly has shown...?

[djclntn]

Steve, I added numbers in the tblProductCategory_PK. Open the FrmMain, selected a Category, "Beverage" when I pulled down the product dropdown it has 3-columns, the ProductID, the Product (which you can hardly make out) & probably the 'Category_PK number. Calling it a night -- catch you ...

[ssanfu]

The "Category_PK" field in tblCategories is an AUTONUMBER type. Access enters/creates number this automatically whenever a new record is created.

The "Category_FK" field in tblProducts is an LONG INTEGER type. It is just a field that stores numbers (instead of text or dates). It is manually entered. (Well, the exception is if you set a default value)

Wait, Steve in the tblProducts, in the Category_PK fld did you manually typed in the numbers?

YES,YES, I manually entered the numbers into the "Category_FK" field in tblProducts. It is just another field.

Why wouldn't the Category_PK (auto numbers) populate in the tblProducts

Because the "Category_PK" field (you created/named) CANNOT be an autonumber type field in tblProducts. There can be only ONE autonumber field in a table. And that field is the ProductID_PK field (in tblProducts).

In "tblProducts" change the name of the field "Category_PK" to "BANANAS" (you will have to update the queries ). As long as the field type is still a Long Integer, you can still create a relationship with tblCategories "Category_PK" field as a one-to-many.

tblCategories (the one)           tblProducts (the many)
Category_PK (autonumber)    ->    Category_FK (LONG INTEGER)

In the "cCC_Box" mdb, on the main form, there is an "ADD" button. It opens a form to add new categories and/or products. In the top part, select Beverage. In the sub-form enter "Orange" and "Root Beer" Add a new record to the categories "Meat". In the subform enter "Beef", "Fish" & "Chicken".

Close the "Add" form and open the main form. In the category combo box, select Meat, then look at the combo box Products.