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.
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.