Multiselect Listbox with SQL Backend

[ascaife]

I have an Access 2007 db and I have just migrated the backend to SQL 2008.

My multiselect listbox has stopped working. Does anyone know of an alternative method to store multiple values?

It's an enquiry database and the listbox is used to tick courses of interest for prospective students.

 

[pbaldy]

I assume you were using the new multi-value field, which I'm not a fan of. I use techniques like this to store data in multiple records in a related table:

http://www.baldyweb.com/MultiselectAppend.htm

If you really want to stay with storing multiple values in one field:

http://support.microsoft.com/default.aspx?scid=kb;en-us;210203

 

[ascaife]

Thanks for your response Paul.

After some further reading, I agree it probably wouldn't be the best idea to store multiple values in the one field.

I had a look at the sample database though and can't really see how I could apply this to my database. Would you mind elaborating on how I might use this to select courses of interest for a prospective student?

 

[pbaldy]

Relevant to this discussion, you would have 3 tables: students, courses, and a many-to-many junction table to store the courses for each student. If I take 3 courses, I have 3 records in that table. On a form like the one in the sample, you would choose a student, choose the courses for that student, and the button would add them to the junction table.

 

[Solice]

I've been creating something very similar to this as i am trying to learn more about list boxes. So far i have the junction box setup and can add new records to it.

Does anyone know how i might go about highlighting any selections made on a form when you reopen it. At the moment if you close the form and open it again you can't tell what selections have been made.

I have searched for hours on this with no luck, only clue i have found is it might be done with a sub query. Does that sound right ?

 

[boblarson]

shirjusat reported to mods/admins for spam in signature.

 

[pbaldy]

I typically let users add records with the listbox, then if they have to edit I use a form/subform. If you want to repopulate the listbox, you can fill a string with the previous selections using a recordset, then (ctl previous set as the listbox, strSelected a semi-colon delimited list of the selections):

    For i = 0 To ctl.ListCount - 1
      SearchItem = ctl.ItemData(i) & ";"
      ctl.Selected(i) = (InStr(strSelected, SearchItem) > 0)
    Next i