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

Posted on 2007-08-06
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
    LVL 4

    Expert Comment

    can you explain more what you are trying to do? what do you mean by select row by row?
    LVL 4

    Expert Comment

    is name, the name of the person taking the survey?

    Author Comment

    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?
    LVL 4

    Accepted Solution

    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

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    745 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now