Catching an updates on a combobox bound to non-updatable recordset

[kaymc]

Two tables, one contains a list of questions to ask the user about a selected client, the other table contains the answers assigned to that client. One record per question answered, if the question is skipped, there should be no record in the answer table for that question.

It should go something like this...

[1] Question One? [combobox for answer]
[2] Question Two? [Combobox for answer]
[3] Question three? [combobox for answer]

And so on. The number of question may change on a regular basis.

When the user returns to the form, their previous answers are still there, able to be edited, along with all the unanswered questions, awaiting a response.

The query behind this is a little complex and, needless to say, if you try making a change to the bound combobox field (which ultimately is based on a subselect) you get a "recordset not updatable" message in the status bar. Fair enough, but I need someway to intercept the chosen answer, run a query to update or insert an answer record and then refresh the browser.

Can it be done without having to resort to an array or having to pre-populate the answer table?

 

[MikeRBS]

This won't work as you have it. You need a third table which is the list of answers for each question. Then you fill each listbox with a query from that table. When the user selects a value it is bound to the answer field in the customer answer table - a straight join.

So no subselects.

However, that's the easy part. I'm not sure how you are easily going to cope with varying numbers of questions on the form.