Display Data in MultiSelect Listbox

J

JMS

I'm trying to synchronize a multiselect listbox with data
in a table.

My frmMatrixMain has a listbox called lstMTXResponses.
The user clicks a button [cmdEditMTXRespones] to access
another form called frmMatrixChoices with a listbox
[lstMTXChoices] containing a multitude of options to
choose from. Once the options are selected, I have a
save/close button to update the information to
tblMTXSelections and return the user to frmMatrixMain.
The selected options now are shown in lstMTXResponses. Up
to this point, everything seems to be working. If I
click the [cmdEditMTXResponses] button to edit my
selections, none of my previous selections are highlighted
in lstMTXChoices.

I would really appreciate any suggestions. I've been
working on this for days now. I think I'm very close but
since I'm not very proficient in VB, I'm not sure what
other options are available.

Here's my code for the on Activate event on
frmMatrixChoices:

Private Sub Form_Activate()
On Error GoTo Err_Form_Activate

Dim db As DAO.Database, rs As DAO.Recordset, rs2 As
DAO.Recordset
Dim strSQL As String, Criteria As String
Dim i As Integer

strSQL = "SELECT tblOptions.OptionID, " & _
"tblOptions.TitleCombo, " & _
"tblMTXSelections.OrderID " & _
"FROM tblOptions INNER JOIN tblMTXSelections ON
tblOptions.OptionID = tblMTXSelections.OptionID " & _
"WHERE (((tblMTXSelections.OrderID=[Forms]!
[frmMatrixMain]![OrderID]));"

'MsgBox strSQL
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

For i = 0 To lstMTXChoices.ListCount - 1
Criteria = "[TitleCombo] = '" & lstMTXChoices.Column
(1, i) & "'"
'MsgBox Criteria
If Not rs.BOF Then
rs.FindFirst Criteria
If rs.NoMatch = False Then
lstMTXChoices.Selected(i) = True
Else
lstMTXChoices.Selected(i) = False
End If
End If
Next i

Exit_Form_Activate:
Exit Sub

Err_Form_Activate:
If Err.Number = 3075 Then
Resume Exit_Form_Activate
Else
MsgBox Err.Description
Resume Exit_Form_Activate
End If
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top