append query posting data entry line too

[Guest]

Tried similiar post in general questions but no response. hoping someone
here has seen this before.

I'm using a form and subform (answers/answersub) to create a survey. The
user enters the patientID, date and user name in the main form then hits a
button for long or short survery.

This triggers a macro to open an append query that posts the patientID,
user, date (from the form) and questions info (from the questions table) into
an answers table. The first record appended is the record that has zero
values from the quetions table for the question, questionID and order (the
patientID, user and date are filled in).

I think it's grabbing the record marked [autonumber] from the question
table. I've tried to say that order and/or questionID Is Not Null or <>0 in
the append criteria but it doens't prevent the problem (see SQL statement
below)

INSERT INTO Answers ( PatientID, [Date], [User], QuestionID, Question,
[Order] )
SELECT forms.answers.patientID AS PatientID, forms!answers!date AS [Date],
forms.answers.user AS [User], Questions.QuestionID, Questions.Question,
Questions.OrderLOng
FROM Questions
WHERE (((Questions.ActiveLong)=Yes))
GROUP BY forms.answers.patientID, forms!answers!date, forms.answers.user,
Questions.QuestionID, Questions.Question, Questions.OrderLOng
HAVING (((Questions.OrderLOng)<>0));

 

[John Spencer]

Not sure what is causing the problem, but the first thing I would do would
be to change the append query. I don't see any reason for having the Group
by and having clause. Assuming that your questions table has no duplicates.

INSERT INTO Answers ( PatientID, [Date], [User], QuestionID, Question,
[Order] )
SELECT forms.answers.patientID AS PatientID, forms!answers!date AS [Date],
forms.answers.user AS [User], Questions.QuestionID, Questions.Question,
Questions.OrderLOng
FROM Questions
WHERE Questions.ActiveLong=True

Your speculation about the query grabbing the question marked Autonumber is
false. No record exists in the table with Autonumber as its value. That is
simply a view showing you where the next record will be added. The
"autonumber" text is just there to show you that a number will be
automatically generated when a record is created.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..