Append Query Re-run

[dee]

Hi,

I'm not sure if I'm on the right track. I have a MainForm and SubForm.
When tab to end of MainForm, SubForm is populated through an append query.

Works very well.

Here is my problem.

Let's say I have 5 questions and possible answers. When the append query
runs, I see the 5 questions and possible answers, I select the participant's
response and all is fine.
BUT
If I add other question(s) to the questions/answers table and scroll to an
existing ppt's data, I only see the initial 5 questions, answers, responses.
(New ppts are no problem, the append query runs and populates all questions,
etc.)

I am trying to create another append query for those ppts that I run through
a button on the form that looks for the existing question ID and, if it
doesn't already show, add it. In other words, if questions 6 and 7 (and
corresponding answers) haven't been added for this ppt because they didn't
exist when their data was initially input, then add them.

Is this possible, or am I totally off track with how I'm trying to
accomplish it?

Thanks so much, as always, to the wonderful people in these groups.

 

[Allen Browne]

That sounds right: in the AfterInsert event procedure of the main form, you
execute an Append query that adds the survey questions to the related table.
If you add further questions later, they won't automatically turn up in past
surveys. So perhaps you want a command button on the main form that adds any
new questions to the survey in the form?

If so, the Click event procedure of the command button will execute a
similar append query, but with a subquery that stops it duplicating the
questions that have been previously inserted. The code will take this
approach:

Private Sub cmdInsertNewQuestion_Click()
strSql = "INSERT INTO YourSubformTable (F1, F2, ...) " & _
"SELECT QuestionID, ... FROM YourQuestionTable " & _
"WHERE (FKID = " & Me.YourMainformPrimaryKey & _
") AND NOT EXISTS (SELECT ...

dbEngine(0)(0).Execute strSql, dbFailOnError
End Sub

If subqueries are a new concept, here's an introduction:
Subquery basics
at:
http://allenbrowne.com/subquery-01.html

If Execute is new, see:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

 

[n00b]

Yes, unfortunately no easy table linking will achieve the results you
are after. I would suggest creating another form that is used for
maintaining the master list of questions. You definitely have the
correct idea.

 

[dee]

Hi Allen,

I've been trying your code, but guess my understanding just isn't there.
Unfortunately, I'm only able to work with it after very long work days, and
so perhaps that's the problem.

In any case, I created an append query with the following SQL: (Tried to
base it on my original append query with the Not Exists)
INSERT INTO tbl_response_details ( qstn_id, response_id )
SELECT tbl_questions.qstn_id,
[Forms]![frm_general_qstnaire_info]![response_id] AS response_id
FROM tbl_questions
WHERE
(((tbl_questions.qstnaire_id)=[Forms]![frm_general_qstnaire_info]![cbo_qstnaire_id]) AND where NOT EXISTS (SELECT [tbl_response_details].[qstn_id]));


I get run time error 3075.

What I'm trying to do is:
Append to tbl_response_details

Fields:
qstn_id from tbl_questions
response_id from my form
Where the questionnaire Id matches my main form
but where my qstn_id doesn't already exists for that response_id

I hope I'm making sense. Any help would be appreciated so much.

Thank you.

--
Thanks!

Dee

That sounds right: in the AfterInsert event procedure of the main form, you
execute an Append query that adds the survey questions to the related table.
If you add further questions later, they won't automatically turn up in past
surveys. So perhaps you want a command button on the main form that adds any
new questions to the survey in the form?

If so, the Click event procedure of the command button will execute a
similar append query, but with a subquery that stops it duplicating the
questions that have been previously inserted. The code will take this
approach:

Private Sub cmdInsertNewQuestion_Click()
strSql = "INSERT INTO YourSubformTable (F1, F2, ...) " & _
"SELECT QuestionID, ... FROM YourQuestionTable " & _
"WHERE (FKID = " & Me.YourMainformPrimaryKey & _
") AND NOT EXISTS (SELECT ...

dbEngine(0)(0).Execute strSql, dbFailOnError
End Sub

If subqueries are a new concept, here's an introduction:
Subquery basics
at:
http://allenbrowne.com/subquery-01.html

If Execute is new, see:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html