Form Problem (2003)

Topic

New Reply

Edited by HansV to reduce picture in size – please don’t post pictures larger than 640 x 480 pixels.

I am in the process of converting something from a web-based tool to an Access tool.

It is similar to a survey.

I have a list of questions in a table (question#, QuestionText,Active). I need this list to be dynamic, meaning if I set Active to False, then the question will not be presented. Each question can have one of 3 responses, Acceptable, NotAcceptable, or NotApplicable. Also, each question needs to allow the user to provide comments specific to that question, if that makes sense.

I also need to get some other info from the users, but I have this info on a form already.

What I can not figure out is how to present the questions to the users. I tried a continuous form based on the questions table, but when I placed radio buttons for the response, when I change one, they all change. How do I fix that?

I also need to know how to reference the question/answer/comments so I can save them in a different table.

Any suggestions?

In the attached, the part on the right, in the red rectangle, would ideally go inside the solid red on the left.

Thanks!

Reply | Quote

Replies

You need a table tblResponses to store the responses in. This table would contain SampleID (a field from a Samples table to uniquely identify the sample), Question# (a field from the Questions table to uniquely identify the question), Response (a number field that’ll store 1, 2 or 3, corresponding to Acceptable, Not Acceptable and Not Applicable), and Comments (a text or memo field).

Create a query based on tblResponses and the Questions table, linked on Question#. Add the fields from tblResponses, plus QuestionText and Active from the Questions table. Enter True in the Criteria row for Active.

This query will be the Record Source for your continuous form. The option group will be bound to the Response field. Because it’s bound, the option group can have a different option selected for each record.
Lock the text box bound to QuestionText – the user shouldn’t be able to edit this field.

The continuous form will become a subform on frmNewSample, bound to the main table on SampleID.

When a new Sample record is created, use code to add a record with the new SampleID for each Question# in tblResponses, so that the subform will display all active questions.

Reply | Quote

Hmmm…I ALMOST have it, but I’m not quite there.

The query I’ve created works as the Record Source for the subForm, but it is not updatable. The form reflects the query correctly, including the Option Group, but I can’t click on another option.

I must be missing something…

Also, my main form is not bound. What differences will that make?

Reply | Quote

Make QuestionID the primary key in the Questions table.
A query based on two linked tables is only updatable if there is a unique key on the join field on the “one” side of the join.

Reply | Quote

Well that was simple enough…

Thanks!!

Reply | Quote