Adding set amount of new records

[Guest]

I hope someone can help me with this. I have three tables, one containing questions (quesntionnr, question) and a table containing the answers (answernr, answer, customernr) and a table customer (customrernr, customername). A question can have multiple answers from different customers.

These questions are typed in by the user in a form so it is easy to add new questions. These questions appear on a form (wich is made with the query containing questionnr, question, ansernr, answer) with a memofield for the corresponding answer. However, at the moment i have to manually add each question (creating a record). I do this by selecting a questionnr from a combobox.

I would like the amount of questions to be counted and that number of records to be added when i open the form. Can anybody help me?

cheers

 

[Allen Browne]

In the AfterInsert event of your Customer form, execute an Append query
statement to add a record for each question.

Assuming that QuestionNr and AnswerNr are the same thing:
1. Create a query into the Questions table.

2. Change it to an Append query (Append on Query menu).
Access asks what table to append to: specify your Answers table.

3. Drag QuestionNr into the grid. Choose AnswerNr as the target field.

4. Type 99 into the Field row (as a fake customer number) and choose
CustomerNr as the target field.

5. Switch the query to SQL View (View menu), and copy what you see there.

Now use that string in the AfterInsert event procedure of your Customers
form. Concatenate the new customer number in place of the 99. Your code will
look like this:

Private Sub Form_AfterInsert()
Dim strSql As String
strSQL = "INSERT INTO Answers ( QuestionNr, CustomerNr ) " & _
"SELECT QuestionNr, " & Me.[CustomerNr] & " FROM ...
dbEngine(0)(0).Execute strSQL, dbFailOnError
End Sub
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I hope someone can help me with this. I have three tables, one containing

questions (quesntionnr, question) and a table containing the answers
(answernr, answer, customernr) and a table customer (customrernr,
customername). A question can have multiple answers from different
customers.

These questions are typed in by the user in a form so it is easy to add

new questions. These questions appear on a form (wich is made with the query
containing questionnr, question, ansernr, answer) with a memofield for the
corresponding answer. However, at the moment i have to manually add each
question (creating a record). I do this by selecting a questionnr from a
combobox.

I would like the amount of questions to be counted and that number of

records to be added when i open the form. Can anybody help me?