Access Access 2000 design problem

[davidlovegrove]

This is my first post - apologies if I get anything wrong.

I am a novice Access “writer” and am not an IT professional – I could be described as a power user. I am writing a recruitment management application using Access 2000. There are two main elements to the data:-

· A recruitment brief (requirement) holding fields such as client details and the specific job spec etc

· Candidate records for each requirement holding eg a candidate name and action status


Each requirement has a unique reference field which is specified to be an Autonumber.


Requirement and candidate records are linked one-to-many via the reference field which has also been specified to be part of a candidate record (as an Autonumber)

I have designed a form with requirement data at the top and with candidate data as a subform underneath in continuous form mode.


I want to enter new candidate records for any particular requirement using this form and therefore want to ensure that each candidate record picks up the unique reference number from the associated requirement record.

I don’t think that I will achieve this by specifying the reference field in the candidate record in the way that I have. I think a new candidate entry will just force an incremented ref no. I suspect that I have not grasped something fundemental about table relationships. How should I proceed please?