RG,
That is an excellent resource. Unfortunately, I think I am still implementing the list box incorrectly.
I have the following objects in my Access 2003 database:
tblContacts:
This holds a lot of different information about people (names, addresses, various contact info, and Expertise). I have set aside one field (AllExpertise) for storing the string of discplines that will be generated by the list box and code (below).
tblDiscipline:
Lists various disciplines that will be shown in the list box. There are 2 fields in this table: DisciplineNum (the primary key), and Discipline (Display Control is List Box).
frmContacts:
has a listbox named:lstExpertise
- Row Source: SELECT tblDiscipline.Discipline FROM tblDiscipline;
- Control Source: ~none~
- Multiselect: Extended
- Column Count: 1
- Bound Column: 1
Command Button associated with lstExpertise list box on form frmContacts
- Name: SelectExpertise
- Caption: OK
- Event: OnClick (see coding blow)
Code Used:
Private Sub SelectExpertise_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryMultiSelect")
For Each varItem In Me!lstExpertise.ItemsSelected
strCriteria = strCriteria & ",'" & Me!lstExpertise.ItemData(varItem) & "'"
Next varItem
If Len(strCriteria) = 0 Then
MsgBox "You did not select anything from the list" _
, vbExclamation, "Nothing to find!"
Exit Sub
End If
strCriteria = Right(strCriteria, Len(strCriteria) - 1)
strSQL = "SELECT * FROM tblContacts " & _
"WHERE tblContacts.AllExpertise IN(" & strCriteria & ");"
qdf.SQL = strSQL
DoCmd.OpenQuery "qryMultiSelect"
Set db = Nothing
Set qdf = Nothing
End Sub
Two undesireable things are happening:
1. Query never opens up to display data in a string (as selected in the list box). Actually, what I am really after is having the string entered into one field (AllExpertise) in tblContacts.
2. the selection in my list box keeps growing as I try to troubleshoot #1.
I am probably omiting some useful info here. Please let me know if you spot anything that I am obviously doing wrong, or if I need to provide more info.
Thanks!