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