My database in a nutshell, I can't post it because of security clearance. If I don't communicate the problem enough, please let me know.
I have a checklist that has 30 questions that need to be answered. It consists of the record ID, and an answer field (number) and a remark field (memo). There are other fields, but these three are key because they hold the meat. I am not happy with this large table holding all of these fields. I need an alternative, hopefully there is a better one. Fields are named like this: CID, SQ1, SQ1R, SQ2, SQ2R,, etc.
I have forms that display these questions throughout. My main love in Access is designing forms, but I will tell you that constantly revising these forms is making me tired.
Single Form, based on the table (called tbl_QualityChecklist). Fields are named based on which question they are on. So for example, Q1 I have 1) option group called SQ1F (radio, holding Yes with a value of 1, No with a value of 2, and NA with a value of 3. 2) A label with the question called Q1 and 3) a memo field for the remark called SQ1R. And so forth.
I have conditional formatting on the SQ1F - Green for yes, Red for no, Yellow for NA (NA also has a requirement to fill in the remark if they choose NA, the other fields don't).
I originally had labels on the forms for the questions. This is inefficient because the questions may change at anytime. So I created a table with the QID (question ID) and the question to go with it. On the forms I used DLookup to match up the questions with the form fields. This caused conditional formatting to fail and caused problems with the display. It flickered. As soon as I took away the DLookups conditional formatting was A OK.
I am curious as to what I should do.
Is there a better way to store the answers to the questions?
How can I display the question text on the form while it is stored in the table?
I hope I was clear. I have been trying to figure out how to play this so that I don't have repetitious data and I don't have to update a bunch of forms everytime someone sneezes a change at me.
Help?:banghead:
I have a checklist that has 30 questions that need to be answered. It consists of the record ID, and an answer field (number) and a remark field (memo). There are other fields, but these three are key because they hold the meat. I am not happy with this large table holding all of these fields. I need an alternative, hopefully there is a better one. Fields are named like this: CID, SQ1, SQ1R, SQ2, SQ2R,, etc.
I have forms that display these questions throughout. My main love in Access is designing forms, but I will tell you that constantly revising these forms is making me tired.
Single Form, based on the table (called tbl_QualityChecklist). Fields are named based on which question they are on. So for example, Q1 I have 1) option group called SQ1F (radio, holding Yes with a value of 1, No with a value of 2, and NA with a value of 3. 2) A label with the question called Q1 and 3) a memo field for the remark called SQ1R. And so forth.
I have conditional formatting on the SQ1F - Green for yes, Red for no, Yellow for NA (NA also has a requirement to fill in the remark if they choose NA, the other fields don't).
I originally had labels on the forms for the questions. This is inefficient because the questions may change at anytime. So I created a table with the QID (question ID) and the question to go with it. On the forms I used DLookup to match up the questions with the form fields. This caused conditional formatting to fail and caused problems with the display. It flickered. As soon as I took away the DLookups conditional formatting was A OK.
I am curious as to what I should do.
Is there a better way to store the answers to the questions?
How can I display the question text on the form while it is stored in the table?
I hope I was clear. I have been trying to figure out how to play this so that I don't have repetitious data and I don't have to update a bunch of forms everytime someone sneezes a change at me.
Help?:banghead:
