How to add multiple detail records at once?

[aarky]

I'm a novice Access user; hope there is a simple answer to my dilemma.

I'm designing a database to track audits. I have four tables:
Checklists, keyed on checklist_ID
Questions, keyed on question_ID. Linked to Checklists by checklist_ID. Many questions to one checklist.
Audits, keyed on audit_ID. Linked to Checklists and Questions by checklist_ID. An audit consists of answers to the questions on a checklist.
Answers, keyed by answer_ID. Linked to Questions by question_ID (1 question to many answers) and to Audits by audit_ID (1 audit to many answers).

I have a form that allows edit of an audit. Main form is from Audits, subform is from Answers, with supporting information pulled from Questions.

My problem is when creating a new audit. The above form does not work because there are no Answers records yet. I want to automatically create records in Answers and then have the auditor populate the fields.

The algorithm I want is:

1. Create a new Audits record and specify the checklist_ID.
2. For each question_ID corresponding to checklist_ID, create a record in Answers, linked to the new audit_ID and the source question_ID. Audit results fields in Answers will be <null>.
3. Edit the audit as described above. Auditor will see all the questions in the checklist with <null> results and proceed to replace the results with real data.

This does not sound like an unusual situation, but like I said I'm a novice to Access and it has my stumped. I've gotten close with a form that has two subforms for questions and answers, but the result fields were not editable.

Appreciate any advice.

[pbaldy]

In this case I think the simplest solution would be an append query. You should be able to create one that pulls records from your questions table using the checklist_id field from the form the user is entering these items on. It can pull the audit_id from the form as well for insertion into the destination table. So you would have the user enter those two items, and then have a button that executed your query.

[aarky]

[QUOTE=pbaldy;55782] ... using the checklist_id field from the form the user is entering these items on. It can pull the audit_id from the form as well ... /QUOTE]

I have created a form that creates a new audit record. The audit_ID is autonumbered, and the checklist_ID is entered by the user.

I folowed the Access Help instructions to create an Append query. These instructions are for a manual process, i.e., hard-coded selection criteria in the query. I left the criteria blank hoping to get them from the form. However, that is not happening.

How to get the checklist_ID from the new audit record just created in the form into the query? Query is activated by a button.

(I feel kinda dumb not being able to firure this out appreciate any help.)

[pbaldy]

It would look something like:

INSERT INTO Answers(audit_ID, question_ID)
SELECT Forms!FormName.AuditControl, question_ID
FROM Questions
WHERE checklist_ID = Forms!FormName.ChecklistControl