List Box and Updating the table

[Lisa]

I want a form with 24 list boxes that the user can have multiple selections
for each box..I understand that I need to have a table that will hold results
and a new row for each selection, what i need to know is how do i get the
data from the List boxes populated into the table?

Thanks.

 

[Klatuu]

Lisa, there are several problems with this.
First, You will have to open the form on Friday and come back Monday to
actually see it. I once tried a form with only 8 list boxes and it took 2 to
5 minutes to load the form. It has to retrieve the data from the database
for each list box, one at a time.

Next, With a multi select list box, you will have multiple entries for each
field. Each field holds only one value. How would you know which value to
put in the table? It would be impossible to think about adding multiple
records, because with 24 different fields and an unknown number of options,
it would be a daunting task. Also, how would you know which selections went
in which records.

I suggest you rethink your design. If you can describe What you want to do,
perhaps we can offer suggesions on How to do it.

 

[Lisa]

Its a quality form.. to monitor a phone call. so there is the standard data
that needs to be collected.. phone #, rep taking the call, customer
information, then there are 24 questions that we need to monitor the call
for..the person monitoring will answer yes or no to whether they rep
performed the task correctly and if they didn't we wanted to be able to have
a dropdown for the question on different options that they did wrong .. and
they could have done multiple things wrong.

I hope this makes sense.. if not i can try and lay out an example of the
form.

Thanks.

Lisa

 

[Klatuu]

No, I think I can understand the problem, but the technical issues remain.
This will be an application so slow that I don't know if it would be useful
at all. Although the solution I will suggest is not good relational design,
I think it is necessary for performance sake in this case.

Rather than using all those list boxes, I would suggest a tab control with a
page for each question. One each question tab, put a check box for the
things that could go wrong that the user can check.

On the form, I would put the basic call info on the top of the form and the
tab control under the controls for the call record. Then each tab would be a
subform to the call record related by the call id.

Then I would have two tables. One for the call info and one for the
questions. In the questions table, I would have a Yes/No field for each
question. I would have the callid as a foreign key and a field to hold the
question number.

I know it is not elegant, but the performance would be much better.

 

[John W. Vinson]

I want a form with 24 list boxes that the user can have multiple selections
for each box..I understand that I need to have a table that will hold results
and a new row for each selection, what i need to know is how do i get the
data from the List boxes populated into the table?

Thanks.

You're building your database backwards. If you're building a house, you pour
the foundations before you start assembling the roof soffits. If you're
building a database, you build your (properly normalized) tables first, and
then design forms to fit those tables - not the other way around!!!

Read Klatuu's advice. It's right on the money.

John W. Vinson [MVP]