Have a main form and sub form.
When generate a new form, fill in the main form and tab to sub-form, an
append query is executed that populates the questions and possible answers
for the questionnaire selected in the main form. The user then selects
there
response.
Now, however, additional questions have been added. This means I need to
execute some kind of additional append query that will ignore question ids
that have already been populated, and add new ones.
As he so often does, Allen Brown very kindly posted responses and I have
checked out the information on subqueries on his site, plus tried
(unsuccessfully) to achieve what he suggested. I'm a very basic SQL and
VBA
level user, so am having syntax errors.
This is my code:
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 NOT EXISTS
((SELECT * FROM tbl_response_details.qstn_id )= tbl_questions.qstn_id));
If anyone can provide guidance, it would be greatly appreciated.
dee said:First of all, my apologies to anyone who read this in the Forms Coding group.
I had misposted it there, when it should have been here.
Here is my post:
Awhile back, I posted about the following:Have a main form and sub form.
When generate a new form, fill in the main form and tab to sub-form, an
append query is executed that populates the questions and possible answers
for the questionnaire selected in the main form. The user then selects
there
response.
Now, however, additional questions have been added. This means I need to
execute some kind of additional append query that will ignore question ids
that have already been populated, and add new ones.
As he so often does, Allen Brown very kindly posted responses and I have
checked out the information on subqueries on his site, plus tried
(unsuccessfully) to achieve what he suggested. I'm a very basic SQL and
VBA
level user, so am having syntax errors.
This is my code:
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 NOT EXISTS
((SELECT * FROM tbl_response_details.qstn_id )= tbl_questions.qstn_id));
If anyone can provide guidance, it would be greatly appreciated.
Duane Hookom said:I have an append query like this in my "At Your Survey" demo. I believe I
simply have a primary or unique index set on qstn_id and response_id in
tbl_response_details so that I can append again and again without adding
duplicates.
--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm
dee said:First of all, my apologies to anyone who read this in the Forms Coding group.
I had misposted it there, when it should have been here.
Here is my post:
Awhile back, I posted about the following:Have a main form and sub form.
When generate a new form, fill in the main form and tab to sub-form, an
append query is executed that populates the questions and possible answers
for the questionnaire selected in the main form. The user then selects
there
response.
Now, however, additional questions have been added. This means I need to
execute some kind of additional append query that will ignore question ids
that have already been populated, and add new ones.
As he so often does, Allen Brown very kindly posted responses and I have
checked out the information on subqueries on his site, plus tried
(unsuccessfully) to achieve what he suggested. I'm a very basic SQL and
VBA
level user, so am having syntax errors.
This is my code:
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 NOT EXISTS
((SELECT * FROM tbl_response_details.qstn_id )= tbl_questions.qstn_id));
If anyone can provide guidance, it would be greatly appreciated.