Save primary key value from table A to to Foreign key field on table B

I have 2 access 2016 tables  Category Table (CAT_ID, CATEGORY) and Sub_Category Table (SUBCAT_ID, SUB_CATEGORY, CAT_ID)  I need to assign the CATEGORY  to the SUB CATEGORY. I have a form the with a unbound drop downs for the CATEGORY and SUBCATEGORY so when I select the CATEGORY and the corresponding SUB CATEGORY the I want CATEGORY CAT_ID (PK) value to be saved to the SUB CATEGORY CAT_ID field (FK)

Table schema
skull52IT director Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
There is no way to do this automatically with a table.  You need to use a form with a subform.  Bind the main form to a table or query of the parent table and then add a subform bound to the child table or query.  If you have already defined a relationship using the relationship window, Access will automatically create the necessary master/child links.  If they are not created automatically, you will need to create them manually.  Click on the subform control and open the properties dialog. They should be on the Data tab.  Once the master/child links are set, Access will automatically populate the foreign key in the subform when you add a new record.
0
Hamed NasrRetired IT ProfessionalCommented:
Main form, subform approach may be the simplest.

If you think otherwise, upload a demo database and explain more,
0
skull52IT director Author Commented:
OK I created a main form bound to  Category table with a dropdown to select the Category value, I then created a subform bound to the SubCategory table with a dropdown to select the SubCategory that ties to the Category Value and store the CAT_ID in the Subcategory CAT_ID field. But it is not working. When I select the Category from the Drop down then select the Subcategory form the Subform Subcategory Drop down it won't select that value and save the Cat_ID to the SUB FORM CAT_ID field
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Hamed NasrRetired IT ProfessionalCommented:
Upload this sample database.
0
skull52IT director Author Commented:
Ok, here is a sample DB , Basically what I want to do is select a Category value then a Subcategory value and assign the Category ID (Cat_ID) PK to the Subcategory (CAT_ID) FK in the Subcategory table
Webdata---Copy.accdb
0
Hamed NasrRetired IT ProfessionalCommented:
Subcategory names are repeated with different subcategory_ID, so which CAT_ID to use?
I think a little change in design is needed.
The subcategories have to be predefined in a code table, like the Categories table.
0
Hamed NasrRetired IT ProfessionalCommented:
Looking deeper, I think you need to immediately categories any new subcategory.
This is like adding a product, and immediately assigning a category for that product.
Example:
Apples, Fruits
Cucumber, Vegetables
....

ACCESSORY STYLE, Accessories
....
0
skull52IT director Author Commented:
Not sure what you mean the Subcategory names are repeated with different subcategory_ID they are unique names. As to your example Yes I want to Assign the SubCategory   ACCESSORY STYLE to  Category ACCESSORY storing the CAT_ID in the CAT_ID  FK in Subcategory table.
0
skull52IT director Author Commented:
All I want is to link SubCategory to Category and store the ID in the Subcategory table how do i do that
0
Hamed NasrRetired IT ProfessionalCommented:
I'll give it a try.
My mistake. The repeated subcategories came about after running the forms and entering new records. That should be change and not add.
0
Hamed NasrRetired IT ProfessionalCommented:
Try this after modifying Subcategory table by adding a lookup to CAT_ID field.
Webdata---Copy2.accdb
0
skull52IT director Author Commented:
Yeah... that's what I was experiencing also,adding records in the Subcategary table.
0
skull52IT director Author Commented:
Still not working When you select a Category from the drop down and then select a subcategory it duplicates the Subcategory as a new record in the Subcategory table with the ACCESSORY CAT_ID, See Attached
Web-Data.png
0
Hamed NasrRetired IT ProfessionalCommented:
The form design needs modification.
Run Form1.
Webdata---Copy3.accdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
skull52IT director Author Commented:
That worked, Hnasr, thanks what was i doing wrong?
0
Hamed NasrRetired IT ProfessionalCommented:
May be our comments suggested the sub form approach. The linked child field is null, and that shows no records in the sub form.
0
skull52IT director Author Commented:
yeah... I was wondering about that. thanks again.
0
Hamed NasrRetired IT ProfessionalCommented:
Welcome!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.