Add new record for existing ID (fk) and append to table creating new pk (1 Viewer)

AccessNovice123

Registered User.
Local time
Today, 02:38
Joined
Aug 16, 2017
Messages
13
Hello,
I have a customers database and within in there, I have tabbed subforms within tabbe subforms to view specific and organized data. For example, users can click on the Employment tab (all tabs within this form correspond to my emp_tbl) which will take them to subform with tabs (Skills, Range, Trade School, State, Certifications).


I need to click on the "Add Record" button while I am in a customer record and have it create a new record in my emp_tbl for the existing customer. My cust_tbl has the pk = CUST_ID and is the fk in emp_tbl with the pk = EMP_ID. So, for the existing customer, I need to add a record to the emp_tbl associated with the CUST_ID. I would like to be able to do this by going through each tab but otherwise I could create a modal to enter the new data.

My customers and employment table have an inner join on CUST_ID...so one customer can have many EMP_ID records.

I am working in Access 2016.

Any help is greatly appreciated. Thank you in advance. I am a novice at best.
 

Mark_

Longboard on the internet
Local time
Today, 02:38
Joined
Sep 12, 2017
Messages
2,111
Have you set up your relationships under database tools? If so, if you link the child forms by their copy of their parents ID then ACCESS will put the parent ID into child records without you needing to do any more work.
 

AccessNovice123

Registered User.
Local time
Today, 02:38
Joined
Aug 16, 2017
Messages
13
Have you set up your relationships under database tools? If so, if you link the child forms by their copy of their parents ID then ACCESS will put the parent ID into child records without you needing to do any more work.

Yes, I have them related but when I try to just add a new record, I get the error that access cannot find a record in my cust_tbl with the matching CUST_ID...it does exist I just want to add a new employment record for the customer record that i am currently viewing.
 

Mark_

Longboard on the internet
Local time
Today, 02:38
Joined
Sep 12, 2017
Messages
2,111
On your subform, what fields are used to link the child to the parent?
 

Mark_

Longboard on the internet
Local time
Today, 02:38
Joined
Sep 12, 2017
Messages
2,111
CUST_ID on both? Do you have any code or any default values that are trying to update CUST_ID? With the databases I am working with so long as I simply let ACCESS handle the parent/child relationship I have no issues.

One thing you can do to check what you are putting in is to have a field in the parent that displays CUST_ID as well as a field in the child.
 

AccessNovice123

Registered User.
Local time
Today, 02:38
Joined
Aug 16, 2017
Messages
13
Yes, CUST_ID is the primary key in my customers table and the foreign key in my employment table. I let Access set the relationship between my master/child forms. CUST_ID is a field in both main form and sub form.

I am not using a list box to filter.

I think I need to pass CUST_ID between my forms and simultaneously create a new EMP_ID...i am just not sure how to do it. My subforms in my employment tabs are continuous forms but I do not think that is a proble
 

Users who are viewing this thread

Top Bottom