I have a tblMaster and tblScreeningForm and a frmMaster and frmSubMaster
tblScreeningForm has questions such as salutation complete? etc...
frmMaster has name, dept. etc.. that is stored in tblMaster,
what i want is to have a frmSubMaster inside frmMaster as a subform
frmSubMaster will show the questions stored in tblScreeningForm, and also when these questions are answered (yes/no) the values are stored in tblScreeningForm
I want the ID from frmSubMaster stored in tblMaster so that the following scenario would apply
tblMaster captures agent name etc... Sub form would display the questions,
when tblMaster goes to nextrecord the subform is stored in tblScreeningForm and is cleared for the next assessment, also frmMaster is cleared.
When going back to the records i want it to show agent name and the answered questions for that agent.
Sounds like you want the basic Parent/Child Form setup. The easiest way to get started is to create your Parent Form, in this case the frmMaster. When you add the subform, make sure the Wizard is turned on (it's the icon with the "magic wand" on the toolbox toolbar.
The way to "connect" the recordset of the Parent and Child is through the Link Parent/Child Fields properties. You need to have the matching key value on both parent and child forms. The wizard tries to guess your relationship, you can also define your own.
When you pull up a specific record on the Parent form, it essentially "filters" the child form to all records with a matching key value. When you add a new record to the Child form, it automatically gets the key from the parent.
Ideally, you use an "Autonumber" field as your parent key so that when you go to a new record, a key value is generated for the child table, otherwise some issues may arise.
however, i have frmmaster, and this contains agent details etc...
the sub form is a list of questions asking how an evaluation went on that agent,
it would be ideal if i could press next record on the sub form, so potentially the the agent could have more than one evaluation on them (per master record)
but as the ID in the master and child are linked creating a second child causes problems
can anyone help on this?
I'm not quite sure what your after - it should work as you describe it.
Do not setup a relationship in the "Relationships" window - it is not necessary. If you do, make sure it is set to "One to Many" by right clicking on the link. If you have a relationship setup One-To-One, that may be the problem.
On your Form/SubForm, when a new record is created on the Subform, it automatically applies the lkey value from the parent form to the matching field on the sub form.