Populating a ComboBox RowSource on a Continuous Form

[madereal]

howdy scriptors… here’s my question:

Project Background:
I’m creating a database that will hold questionnaire data.

Situation:
I would like to set up a combobox with a changing rowsource property on a continuous form. I have created a query for the record source so that the text value of the rowsource property is included as a separate field for each record. I have call this field: [AnsRowSource] and an example of the text is: (1;Yes;2;No).

Question:
How do I populate the combobox rowsource so that is changes with every record?

System Environment:
- I’m using Access 2000

[nico5038]

A combobox with changing rowsource won't work on a continuous form.
For answers I just use a separate row in a table and that's allowing me even to have multiple choice questions with as many posible answers as I need.
I created a questionnair with one general Q&A table and that's copied into a Students Q&A table by adding the StudentID so each student has it's own set of answers.

Getting the idea ?

Nic;o)

[madereal]

hi nico,

thanks for your feedback, I think that what you are suggesting is that I have a table where the columns are the questions, the rows are the students, and the cells are the answers for the corresponding question and student. I have created tables like this in the past however when you create a form to collect the data, you have to 'place' the question and answer objects in the form (thias makes it harder for other non-programming people to come along and add, delete or edit questions.

What I was trying to do instead, was to have a continuous form that loaded the relevant questions automatically and then stored them in a long format where a single row has the student id, question id, the chosen answer, and a timedate stamp (so that a 20 questions would be stored as 20 rows for every student as opposed to a single row for each student with 20 columns).

If you have any suggestions I'd love to hear back.

regards,
leanne

A combobox with changing rowsource won't work on a continuous form.
For answers I just use a separate row in a table and that's allowing me even to have multiple choice questions with as many posible answers as I need.
I created a questionnair with one general Q&A table and that's copied into a Students Q&A table by adding the StudentID so each student has it's own set of answers.

Getting the idea ?

Nic;o)

[JanM]

Hi Leanne,

I'm working on a similar flexible Q&A database and came across the same problem you have. Yesterday I found a solution for it by putting a textbox on top of the combobox (by leaving the combobox dropdown arrow visible). The textbox contains as rowsource a DLookup() where it points to the description of the answer-ID as given in the current record. The combobox has a requery so the correct list of possible answers is shown on every record.

The endresult is that you use the arrow of the combobox to make the selection of the answer, but the answer itself is visible via the textbox.

Some backgroundinfo I found on the way to the solution: in KB208866 (on support.microsoft.com) is stated "Microsoft Access maintains only one query recordset for a combo box rather than one recordset for each combo box in each record.". So no matter what you will attempt with the combobox alone you will never succeed.
In KB209738 in method 3 the DLookup is given as workaround.

Regards,
Jan

[madereal]

Hi Jan,

Thank you so much for your post. I think this will work as a solution to my problem.

Regards,
Leanne

Hi Leanne,

I'm working on a similar flexible Q&A database and came across the same problem you have. Yesterday I found a solution for it by putting a textbox on top of the combobox (by leaving the combobox dropdown arrow visible). The textbox contains as rowsource a DLookup() where it points to the description of the answer-ID as given in the current record. The combobox has a requery so the correct list of possible answers is shown on every record.

The endresult is that you use the arrow of the combobox to make the selection of the answer, but the answer itself is visible via the textbox.

Some backgroundinfo I found on the way to the solution: in KB208866 (on support.microsoft.com) is stated "Microsoft Access maintains only one query recordset for a combo box rather than one recordset for each combo box in each record.". So no matter what you will attempt with the combobox alone you will never succeed.
In KB209738 in method 3 the DLookup is given as workaround.

Regards,
Jan