Full Version: 'Unchecking' a checkbox to clear values in table
UtterAccess Forums > Microsoft® Access > Access Forms
I'm using Access 2003 and to create a simple data entry form for survey results. The form is bound to a single table. I'm pretty comfortable using all the wizards but am not a coder.
want the Access form to look exactly like the original questionnaire on which there were several yes/no questions. Therefore, I want the Access form to show two choices (one for 'Yes' and one for 'No') for those questions.
I've set those fields up as 'Text' data types, no default values, and 'not required' in the table. On the form, I've created an option box with checkboxes: 'Yes'=value of 1, 'No'=value of 2. All works fine until I accidentally check a question where there actually was no response.
For example: if the person skipped question 1, but during data entry I accidentally check 'yes', I cannot 'uncheck' the box to clear the value now entered in the table. I can change the 'yes' to 'no' but cannot clear the response all together. I want the field in the table to show nothing for skipped questions.
I've tried changing the data type to 'Y/N' in the table, but then I only show one checkbox on the form which doesn't meet the criteria of having the form match the questionnaire. I've also tried an option group when data type is 'Y/N' but still can't 'uncheck' boxes once they're checked. Creating a third choice in the option box of 'No Response' (with a '0' value) also doesn't meet the criteria of the form looking exactly like the questionnaire. Radio buttons didn't make a difference either.
This is easily done in Filemaker - but I can't figure it out in Access and can't find it in their 'Help' either. Is there a way to make this work?
For the case where you want YES, NO, or NO RESPONSE, a checkbox is not the way to go. Actual checkboxes are boolean types, not string. Even though you can set the triple state to YES for NO RESPONSE, it confuses people because a checkbox is typically seen as checked (or TRUE or -1) or unchecked (or FALSE or zero). No response (or NULL) is shown as a gray box, but it's not intuitive to most users.

For those types of situations, if you're going to make YES, NO, and NO RESPONSE type questions, my preference is the one-character string that holds NULL or "Y" or "N". Do not set a default which renders the field set to NULL to start. In that way, people looking at your form will see the question.

Example: "Would you like to be contacted (Y/N)?"

If you see nothing there, you know that the person didn't answer the question. Otherwise, you'd see a "Y" or "N".

Edited by: SerranoG on Wed May 27 11:26:29 EDT 2009.
Jack Cowley
Welcome to Utter Access Forums!! -o!
et me jump in here and stir the pot a little... I'm sorry to say, but your approach to a Survey is not correct. The data should not be in one table but in at least three... If you have one table then you have many Yes/No FIELDS in each RECORD and that would be wrong as it goes against the rules of Normalization. Here is what your table structure should look like:
PersonID (PK and auto)
QuestionID (PK and auto)
Question (Text)
ResponsesID (PK and auto)
PersonID (FK to tblPersons. This is the ID of the person taking the survey.)
QuestionID (FK to tblQuestions)
Response (This is your Yes/No or whatever type field you need for your response)
You will not get the same 'look' with an Access survey that you have with your paper forms, but people can adjust. I strongly suggest that you normalize your structure as it will save you tons of time and grief in the future when you want to get the results of the survey.
If you want I can post a sample for you to look at so you can see how it is done...
Sorry for possibly raining on your parade...
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.