Append Query in VBA 1

[gwoman]

I
need some code to wrap around an append query to append/create new records from 2 existing tables....

I have a table called Survey Question Table that contains the question ID's and text for my Survey Question Input formA
I have a Survey Table where a user inputs demographic information via a formA ... when they click the continue button I need the click event to save the current data input into the Survey Table (that part is easy) ... then I need it to create records in my 3rd table Survey Question Response table ... that consists of the newly created SurveyID (this is a number not an auto number field) from the Survey Table and the 10 Question ID's from the Survey Question table.
It should cursor through the 10 records that are in the Survey Question table and create 10 records in the Survey Question Response table with the new/current SurveyID and the 10 existing Question ID's...
Say I just input SurveyID # 123 ....
The records that should be created should look like...

Survey ID QuestionID
123 1
123 2
123 3
123 4
123 5
123 6
123 7
123 8
123 9
123 10

Tried using a For... Each ... Next ... and haven't had any luck ...
I would greatly appreciate any suggestions or snipets or code that anyone may have already used ...
Thanks much ... have a good one!
g-woman

 

[Remou]

I suppose the easiest way would be to create a table with numbers for each question (1 to 10) then use an append query:
[tt]strSQL="INSERT INTO tblTable ( ID, QNo ) " _
& "SELECT " & SurveyID & ", tblQCount.QCount " _
& "FROM tblQCount"[/tt]

 

[gwoman]

I have an existing table Survey Question Table ... that contains the Question ID's (1 to 10) and the text ...
If the Question ID's are (1 to 10) and will not change ... do I really need to cursor through the table ... or can I set up an array and just assign the numbers 1 to 10 in the append query? If so ... what type of loop would be the best to use?

Thanks!
g-woman

 

[Remou]

It is not necessary to use a loop at all, if you already have a table with the numbers in it. An append query can be run from code.

strSQL="INSERT INTO tblTable ( ID, QNo ) " _
       & "SELECT " & SurveyID & ", tblQCount.QCount " _
       & "FROM tblQCount"
DoCmd.RunSQL strSQL

Where:
- tblTable is the name of the Survey Question Response table
- tblQCount is the name of the table with the 10 questions
- ID, QNo are the names of the fields to be updated
- SurveyID is the ID that you wish to add, I am not sure where you will get this, from a form, perhaps?
- QCount is the question number field in the question number table.

This should add 10 records numbered 1 to 10 with an ID of SurveyID. I hope this is clearer. Or perhaps you would prefer to use a loop?

 

[gwoman]

I see what you are saying now ... thanks much ... I will try it!

 

[gwoman]

Thanks ... it works perfectly ... have a great Day!
g-woman