Text Field auto update when adding new records

[blues763]

I have 2 tables
Master table (Jobs) containing the primary key ("Job Reference")
and 2nd table (Candidates) with the foreign key ("Job Reference")

2 Forms
frmJobs Form to view job details
frmCandidates form to view Candates information

I have placed add new candidate button on the frmJobs form which opens the candidate form in the add mode as a blank form. This is fine but I'm looking to add a record where it picks up the "Job Reference" text field value from the main frmJobs and update it in the "Job Refernce" text field on the frmCandidate when I click add new candidate.

How can I achieve this?
I'm fairly new to access/vba

Any help would be great....

 

[GinaWhipp]

Why? More importantly, can a Candidate have more than one Job Reference? Can a Job Reference by assigned to more than one Candidate? If the answer to either of those is yes then you are missing a table...

tblCandidateJobReferences
cjrID
cjrCandidateID (Foreign Key related to the Primary Key in the Candidate table)
cjrJobReferenceID (Foreign Key related to the Primary Key in the Job Reference table)

 

[blues763]

Why? More importantly, can a Candidate have more than one Job Reference? Can a Job Reference by assigned to more than one Candidate? If the answer to either of those is yes then you are missing a table...

tblCandidateJobReferences
cjrID
cjrCandidateID (Foreign Key related to the Primary Key in the Candidate table)
cjrJobReferenceID (Foreign Key related to the Primary Key in the Job Reference table)

Candidate cannot have more than 1 Job Ref but Yes a Job Ref can be assigned to more than 1 canditate.

I do have tables for Candidates data with Job Ref as a Foreign Key which is on one-to-many with Job Ref (Primary Key- Jobs Table)

The add button I have on the Main Form which has only Job Details simply need to open a blank candidate form in the add mode with the same Job Ref auto updated on it

 

[GinaWhipp]

So the Job Reference table is going to have duplicates? If that is so, you really need a Junction table.

That said, on the button to open the new Candidate Form something like this *might* work...

Forms![FormYourAreOpening].ControlYouWantToUpdate= Me![ControlOnMainFormYouAreOpeningFrom]

...on the Command Button that opens the Form.

 

[blues763]

I'm sorry I might not be very clear.
Job Ref Table is the main table containing the "Job_Ref_No." as primary key , therefore it cannot have duplicates.
Candidates can have a multiple "Job Refs Nos" which also act as foreign key in the candidate tbl

code im using is on the button placed on "frmJobs"
DoCmd.OpenForm "frmCandidate", acNormal, "", "", acAdd, acNormal

where I need text value of Job_Ref_No on frmJobs to be updated on frmCandidate "Job_Ref_No" text box

 

[GinaWhipp]

Did the line I gave you above not work?

 

[blues763]

Did the line I gave you above not work?

Yes it did
Thanks a lot

 

[GinaWhipp]

Hmm, then I don't understand your post, #5. It sounds like it's still not doing what you want.