Full Version: Creating A Questionnaire Form
UtterAccess Forums > Microsoft® Access > Access Forms
Hey UtterAccess

I'm attempting to create a form that will allow easy data entry of the answers to the 10 questions we will be asking. My idea for the form, will allow the user to select the respondent, display the 10 questions and input the answer. When ever i try to create such a form I end up with a list box to display the 10 questions.
How do i go about such a form?


Jeff B.
Will the questions ever change? Will all the users get the same 10 questions? Are all the questions "Yes/No" or multiple choice or essay? Are the question answerers entering their answer(s) or is someone else? Is there only one person who enters answers? For ever?

Search online for Duane Hookum's work on surveys -- it may prove to save you a lot of unneeded effort.
The question will remain the same, and every user is asked the same set of questions. Most of the questions are multiple choice (strongly agree, agree, neutral, disagree, strongly disagree) which has been converted to a point scale, and two questions that are yes/no but have also been converted to a point scale. Someone else will be entering the answers, it will be one or two people for the foreseeable future.

I had a look Duane At your survey, and other sources to build the tables and framework for the survey. I believe i have a good table structure to support the survey.
Like i said I'm struggling to get my form the way i want.

Can you please post your current table structure? You should have created a many-to-many relationship between the questions and the respondents using a junction table, which can be a tricky thing to set up if you're not familiar with the concept. Your tables should not have a field for each question/answer either, even though the questions may not change in the foreseeable future.

Knowing the table structure would help us to help you with building a form around it.


I hope this is want to meant by table structure.

That's pretty good, though a couple of minor things:

1. Your naming scheme. You might want to rename your "ID" fields to be more meaningful, like "QuestionID" and "AnswerID". I'd also rename "Readiness Assessment" to be "Answers" or "tblAnswers", which is a Hungarian Notation method of identifying the object type with a prefix. Finally, I'd remove any spaces from field/table names to simplify things. Spaces can cause problems with queries and code.

2. The "weight" field is a little confusing. Does this mean some questions are more "weight"-y than others? I would consider adding a "NumberOfChoices" field in the table of questions. That way, if the response is a multiple choice one, the value can be 4 or 5 or whatever, but if it's a yes/no question, the value can be 2. That way you can more easily validate the responses to questions. You mention a "point scale" but it's not clear if that's what the "weight" field is for or not. If you could clarify this, that would help.

3. This design assumes that each respondent will only answer the set of questions once. Is that right? Or will a respondent be able to answer the questions again at a later time?

The basic form strategy I'd use would be this:

Create a continuous form based on the table of answers and questions. On that form, include a textbox to display the question, and a combobox to list the possible answer choices. This will get a bit complicated, so I'll come back to it in a bit. Next, create a single form based on the Respondent's table. Include a continuous subform control that uses the continuous form you've already created. It should use the Respondent foreign key/primary key fields for the parent/child properties. When the user selects the respondent with an unbound combobox, navigate to the record in the main form for the respondent. If the respondent already has answers, the subform should display the respondent's answers. If there are no answers for this respondent, use a macro or VBA to run an Append query that adds ten records to the Answers table, one for each question, filling in the Question ID field and the Respondent ID field, but leaving the Answer field blank. Requery the subform after doing this, and those ten questions should now display in the subform with no answers yet selected.

Okay, back to the combobox on the subform. On a continuous form, you can't have comboboxes with different rowsources. They all have to be the same. So, you can't have one combobox listing "Yes" as an answer, and another combobox on the same continuous form listing "disagree". So, one trick to get around this: Add a textbox to display the selected answer, and place it directly on top of the combobox, hiding the combobox from view. In the textbox's Got Focus event, you would use the SendFocus method to move the cursor to the hidden combobox, which would bring it to focus. At this point you can requery the combobox to either display the five multiple choice options or the yes/no options. Once the user hits Enter or Tab or clicks on another control, the combobox gets hidden and the textbox displays their choice.

By the way, it would simplify your life to add a table of possible choices, including "agree", "disagree", "yes", "no", etc. so you can easily pick which choices you want to offer the user.

Hope this helps,

Your Readiness Assessment table is your Answer table. You have one record for each answer. The ay you have it setup, you have one set of answers for each Hospital. If that is the way it works that's fine.

But since each answer is a separate record, you have to deal with this differently. I recently built a similar app. In my question table I have a field for AnswerType. This field indicates whether the answer is a scope (Strongly agree, agree, etc), Yes/No, text etc. Then I have a form with the different types of answers. In the On Current event I read the AnswerType and and hide/unhide the controls applicable to that question. I have a NEXT button to go to the Next question.

1. Thank you for the tip, I will clean up my names, of tables and fields.
2. When i first started building the table i built it under the assumption that the questions had different weights, however i have come to find out that they are all equal, thus no need for this field anymore.
the Point scale is used to assign a numeric value to the answer, no relation to the weight field.
3. As of right now the respondents will answer only once, However i have updated my table structure based on other articles on building surveys, which will hopefully allow me to capture responses more than once.

I created a new table structure, based on readings about designing surveys in access, ( see attachment) Should i stick with the old structure or switch to the new one.

I'm going to attempt to create the form based on your instructions, I'll be back on here to ask a few more questions.

Really appreciate the help thus far!
Structure is better, but don't use spaces in object names.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.