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

Posted on 2007-08-06
Medium Priority
Last Modified: 2013-11-28
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.


Question by:dustywork
  • 3

Expert Comment

ID: 19640080
can you explain more what you are trying to do? what do you mean by select row by row?

Expert Comment

ID: 19640085
is name, the name of the person taking the survey?

Author Comment

ID: 19640202
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?

Accepted Solution

ki_ki earned 900 total points
ID: 19641599
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.

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question