Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

ListBox multiple selection from table query

Status
Not open for further replies.

bgv

Programmer
Sep 23, 2003
29
US
I've seen the threads for allowing a multiple selection in a listbox. How would you select multiple records in a listbox of 200 records based upon a table query? This should display a list of records with multiple selections previously saved in the database table.
For example:

Dim qualList As New ADODB.Recordset ' a recordset representing all of the emp qualifications
qualList.Fields.Append "rec", adInteger
qualList.Fields.Append "qual", adInteger
qualList.Open

Dim selList As New ADODB.Recordset ' a recordset representing the previously selected qualifications
selList.Fields.Append "sel", adInteger
selList.Open

Set db = CurrentDb()
sqlStr = "SELECT frs_employee.emp_id, frs_codes.code_id
sqlStr = sqlStr & " AS code_id, frs_codes.code"
sqlStr = sqlStr & " FROM frs_codes INNER JOIN "
sqlStr = sqlStr & " (frs_employee_quals INNER JOIN frs_employee "
sqlStr = sqlStr & " ON frs_employee_quals.employee_id = "
sqlStr = sqlStr & " frs_employee.emp_id) ON "
sqlStr = sqlStr & " frs_codes.code_id = frs_employee_quals.qual_id"
sqlStr = sqlStr & " WHERE frs_employee.emp_id=" & Forms!frmEmpListMstr!frmEmployeeListSubform.Form!emp_id & "; "

'Loop through quals from listbox building a recordset
For itm = 0 To Me.lbxQual.ListCount - 1
qualList.AddNew
qualList("rec") = itm
qualList("qual") = CInt(lbxQual.ItemData(itm))
Next
qualList.MoveFirst
If Not rst.EOF Then
qualList.Find (rst!code_id)
'If we find a match, add the id to new recordset
If rst.NoMatch = False Then
selList.AddNew
selList("sel") = qualList.Fields("rec").Value
End If
rst.MoveNext
End If
'Loop through qual list and select matching recs
Do While Not selList.EOF
Me.lstRedcard.Selected(selList("sel")) = True
selList.MoveNext
Loop

The above routine throws an error on the Find command. Cannot determine how to get this to work properly.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top