Append

[Niniel]

Hello,

I have this survey application with the following tables - tblRespondent [PK RespondentID, other fields], tblAnswers [PK AnswersID, FK RespondentID, FK QuestionID, Answers {yes/no}, Notes {text}], tblQuestions [PK QuestionID, FK CategoryID, Question {text}], tblCategories [PK CategoryID, Categories {text}]. All ID fields are numbers, and all PKs are auto-numbers.

I also have a form based on a query that in turn is based on tblRespondent. That form is my main form, and I want to get the questions on it with the help of a subform, which is where my Append query comes into play.

When I enter a new respondent in my form, I put in some biographical information first, and then would like to send the newly-generated RespondentID to tblAnswers, together with QuestionsIDs from tblQuestions so that the Answers table receives a record for every question that I have.

I'm not having any luck with this, and am probably making some mistake somewhere.
It would be great if somebody could point me in the right direction.

Thank you.

 

[Ascaphus]

Use a button or some event in the main Respondent form to run something like this:

Dim sqlNewAnswers As String
sqlNewAnswers = "INSERT INTO tblAnswers ( RespondentID, QuestionID ) SELECT " & [Me].[RespondentID] & " AS RespondentID, tblQuestions.QuestionID
FROM tblQuestions;"
DoCmd.RunSQL sqlNewAnswers
Me!subformAnswers.Requery

Then requery the subform with the linked answers or answers/with/questions. This will insert one row into Answers for each row in the Questions using QuestionsID, and each new row will have the respondent ID from the current record in the Form. Is this close to what you were looking for?

Matt

 

[Niniel]

Yes, that is what I am trying to accomplish; thank you.

Unfortunately, there seems to be some error in the SQL - when I try to run it, it aborts with a Syntax error and the FROM statement highlighted.

 

[Niniel]

I never did get the SQL code to work, but after some research and trial-and-error, I managed to create a query in the query builder that does what I need when I use a command button on my form to save my record and then execute a macro that runs the query:

INSERT INTO tblAnswers ( RespondentID, QuestionID )
SELECT tblRespondent.RespondentID, tblQuestions.QuestionID
FROM tblQuestions, tblRespondent
WHERE (((tblRespondent.RespondentID)=Forms!frmInputForm!RespondentID));​

Thanks for taking the time to try and help, I appreciate it.

 

[Ascaphus]

Glad you found an answer! I haven't worked with macros much, since the code seems to cover it. Sorry there was an error in my code. I tried to copy it straight!


Matt