Populating records in a continuous form

[Access Virgin]

Really stuck here,

I am trying to set up a questionnaire form, I have a form with case no and then a subform on this form to display the questions and answers, after taking advise from a post on the forum I split my tables up, one for questions and one for questions and answer. This is working fine.

The problem is that i am going to have approx 70 plus questions, so I was looking for a way to automatically fill the subform with the questions as having the user select the question from a combo box isnt a good solution with so many questions, is there a way to do this.

I had thought of adding a button, with code behind to run a loop of some sort that would add a new record for each question from 1 through to 70+ consecutively, then all the user would have to do is move through the questions and fill in the answers but as im abit of a novice with regards to coding im completely lost on were to even begin

I have been looking at this for quite a while and its wrecking my head!!!

 

[SforSoftware]

Hi Virgin,

I would think about an append-query. ie (SQL):
INSERT INTO Table2 ( QuestionID ) SELECT Table1.QuestionID, FROM Tabel1;

With this SQL-statement, you 'copy' the field QuestionID from Table1 into the same field in Table2. When you don't add a WHERE-clausule, all records from Table1 are copied.

Succes,
Bert

 

[Access Virgin]

that didnt work for me, this is what is used

INSERT INTO tblMain ( QuestionID )
SELECT tblQuestions.QuestionID
FROM tblQuestions;

I created a button on my form to run this query but when I run it i get a message box saying that I cant append all the records due to type conversion failure

I'll explain my case abit more

table1 - tblCaseno
field1 - caseno (key)

table2 - tblMain
field1 - caseno (key)
field2 - questionid (key)
field3 - answer

table3 - tblQuestions
field1 - questionid (key)
field2 - question

relationships between tables
tblCaseno 1 to many tblMain
tblQuestions 1 to many tblMain

my main form is based on tblCaseno, the subform where i want the questions and answers to appear is based on tblMain, the question id field in the subform pulls in the full question from tblQuestions

Anyone help???

 

[SforSoftware]

I suggest you've on your main form a field (textbox or combobox) named like 'txtCaseno'
Then when you run it form VBA:

DoCmd.RunSQL "INSERT INTO tblMain ( QuestionID, Caseno ) SELECT tblQuestions.QuestionID, " & Me!txtCaseno & " FROM tblQuestions"

(Didn't test it yet)