Microsoft, MS Access, 2003, collect survey responses via form

I am collecting survey responses, and would like to do it via form.  I have a table (tblSurvey) with SurveyID (PK), Name, and SurveyDate and a separate table (tblSurveyDetail) with SurveyID (FK), QuestionID, QuestionsResponse.  The challenge is, the form should list all the questions (they are already predetermined), and the user would just enter in the responses.  I am not sure how to move the question list with response to the tblSuveyDetail for each response without forcing the user to select row-by-row the question and corresponding response.  The alternative, is to create a separate field for each question response, but I realize that is not acceptable.


Who is Participating?
ki_kiConnect With a Mentor Commented:
I see what you want to do.
Here is what I'd do:After the user enters her name, save that record in the tblSurvey. No need for the user to add the date. Make the default value for date as Now(). After that get surveyID (I am guessing it's an auto number). Use that(surveyID) and create an insert query (in code) : the insert query will insert as many records as you want in table tblSurveyDetail. Each record would have the PK as surveyID and the QuestionsResponse. At the end requery the subform...this will dispaly the questions in the subform (subform, starts as blank ...before the user added her name). On the subform lock the QuestionsResponse field (QuestionsResponse.locked=true). And also don't allow additions and don't allow deletion on the subform. Hope I was clear enough.
can you explain more what you are trying to do? what do you mean by select row by row?
is name, the name of the person taking the survey?
dustyworkAuthor Commented:
Hi ki ki,

What I'm trying to do is create a form which will capture responses to predetermined questions.  I could do this by creating a field for each question response with the PK being surveyID.  I would rather have a main form with SurveyID, Name of Person filling survey, date.  Then a subform with SurveyID, QuestionID, Quesion Response.  Normally, I would create a subform with a datasheet view and have the user select the question and the reponse.  Each time the did that, the record would be saved to the tblSurvyDetail table.  In this case, the questions are predetermined.  So, when I open the main form, a subform would also appear with the predetermined questions listed in datasheet view and already recorded to the tblSurveyDetail.  The user just enters in the survey responses.

Does that make sense?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.