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
skull52Asked:
Who is Participating?
 
hnasrCommented:
The form design needs modification.
Run Form1.
Webdata---Copy3.accdb
0
 
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
 
hnasrCommented:
Main form, subform approach may be the simplest.

If you think otherwise, upload a demo database and explain more,
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
skull52Author 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
 
hnasrCommented:
Upload this sample database.
0
 
skull52Author 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
 
hnasrCommented:
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
 
hnasrCommented:
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
 
skull52Author 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
 
skull52Author Commented:
All I want is to link SubCategory to Category and store the ID in the Subcategory table how do i do that
0
 
hnasrCommented:
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
 
hnasrCommented:
Try this after modifying Subcategory table by adding a lookup to CAT_ID field.
Webdata---Copy2.accdb
0
 
skull52Author Commented:
Yeah... that's what I was experiencing also,adding records in the Subcategary table.
0
 
skull52Author 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
 
skull52Author Commented:
That worked, Hnasr, thanks what was i doing wrong?
0
 
hnasrCommented:
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
 
skull52Author Commented:
yeah... I was wondering about that. thanks again.
0
 
hnasrCommented:
Welcome!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.