What is wrong with my listbox?

[Buakaw]

Hi,


I'm trying to modify a survey database that I downloaded from the net. Here's some background on this database:

- fmnuMain is the main form that starts up when you launch this
- frmSurveys is where you go to design the survey
- frmSurveyResponses is where a survey respondent goes to enter/answer questions about a survey.

frmSurveyResponses includes a sub-form called sfrmResponses.

Here's what I'm trying to do.

- I'm trying to modify this database to allow users to enter multiple answers for a single question. Right now, you can't do that, you can only pick one answer and that's it.
- That doesn't work for certain kinds of questions, for example, which days do you watch a certain TV channel. Respondent might want to answer, Mon, Wed, Fri.

So my plan is to do this:
- on sfrmResponses, create a ListBox control to permit users to select multiple answers.
- Make its attribute hidden at first, and add some code in the VB module to make it visible, and make the combo box invisible, when a question is of the type "Multi" for Multiple Choices.
- Note: I don't want to do away with the Combo Box because the Combo Box is used for allowing users to enter open-ended questions.

This is the problem I'm facing now:
- on sub-form sfrmResponses, I added a ListBox control called MultiRspns.
- I copied the Row Source query over from the [Rspns] combo box on this form.
- I've checked and re-checked the SQL code many times, but I just cannot figure out why it is not displaying to correct response options.
- I made the [QstnID] control visible for debugging purposes.

Can someone help me to check why the ListBox is not displaying the possible list of responses that the user can pick from?

AYS2000 is saved in Access 2007 format.
AYS2000_2000 is saved in Acess 2000 format so everyone can view it.

Thanks!

[pkstormy]

I think you'll need to change the sfrmResponses to a single form. I also changed the criteria for the listbox rowsource from

[Forms]![frmSurveyResponses]![sfrmResponses].Form![QstnID]
to
[Forms]![frmSurveyResponses]![sfrmResponses]![QstnID]

See attached. I think you'll need to do a little bit of re-designing on this form to accomodate a single form versus a continuous form. You 'may' be able to keep it as a continous form but I didn't have time to look at it closer and figure out a way (and I'm not sure it can be done.) Usually this kind of setup with a listbox works only for single type forms.

[Buakaw]

Thanks. Is there really no way to do this without doing any major changes to the forms? Is there a possibly simpler way to making this work? Making major changes is way over my head for the moment.

[pkstormy]

There are some limitations when it comes to making a subform continuous. Making it a single form isn't ideal and I can't say for sure that this is the route you need to go to make your listbox work. There may be another way but I couldn't see another way without diving deeply into your design. This was the only way I could make it work though.

[pkstormy]

I really don't use listboxes in continous subforms so I can't specify all the problems with doing so. (a listbox on a continous form would probably take a heavy toll on a db with several thousand records and be prone to problems with multiple users and constant updating.) But when you create a lisbox on a continous form, to make that listbox work correctly (I'm assuming), you have to think about how you can setup the table structure/design the rowsource of the listbox so that it knows to only list the values pertaining to that specific record in the continous form. If the listbox shows repeating type values for every continous record, sometimes adding another field to the relational table (the listbox is based off of), will allow you to create a rowsource using additional criteria so that it only shows the values for that specific record on the continous form.
ex of criteria:

ID = someID AND Date = somedate (where date would be unique for that ID).

Ie.
If ID repeats in the continous form, designing a listbox using criteria for ID only would cause the listbox to show repeated values in every continous record. But adding the criteria for Date along with the ID criteria (or some other field) which tells the listbox to only show records for that specific continuous record might work.

But I think you had it setup so that it was based off the QstnID which was supposed to do this uniquely for each continous record (but I couldn't find out why specifically it wasn't working). I just know when I changed the criteria and made it a single form, it then worked.

Listboxes can be problematic with continous forms and what I will do sometimes is create a 2nd subform, popup form or put the listbox on the main form and the user would then have to click on the continous record to see the values. It gets tricky to set the form(s) up in a user-friendly way. I once concatenated the fields in the listbox to 1 field in the relational table (and kept it updated) so that I could easily show the values by simply adding that field to the continous form.


Also note: I changed the criteria in the rowsource query from [Forms]![frmSurveyResponses]![sfrmResponses].Form![QstnID] to [Forms]![frmSurveyResponses]![sfrmResponses]![QstnID] which then showed values in the listbox, but they kept repeating for each continous record. Changing the form to a single versus continuous then resolved the repeating value issue. I can't say if having the listbox in the continous form is possible for your issue and whether or not you would need to modify your table structure but I gave up trying different things after 1/2 hour or so.

I hope that helps. I'm hoping you'll get some other responses.