append posting 0 value AND order question of subform

[Guest]

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 hit a
button for long or 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. Two problems
1. the first record appended is the record has zero values 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)
2. The order of the questions in the subform is descending all the time.
I've set the order property in the subform to answers.order but it doesn't
have an effect (I assume because the form is already loaded). Can I refresh
this somehow?

Thanks Ian.

 

[Guest]

Forgot SQL statement
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));