Listbox not Showing Table Data

  • Thread starter Thread starter Jody
  • Start date Start date
J

Jody

My listbox [lstMatrixChoices] is not highlighting the
table data [tblMTXSelections]. I'm not sure if it's a
synchronization problem.

My frmMatrixMain has a listbox called lstMTXResponses.
The user clicks a button [cmdEditMTXRespones] to access
another form called frmMatrixChoices with a multiselect
listbox [lstMTXChoices] containing a multitude of options
to choose from. Once the options are all 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. But, if I
click the [cmdEditMTXResponses] button to edit my
selections, none of my previous selections are highlighted
in lstMTXChoices.

Could someone please tell me if my code looks correct?
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
 
Can anyone help me? I've posted my question numerous
times.

I have two forms one called frmMatrixMain and another form
called frmMatrixChoices. frmMatrixMain has a listbox that
shows data supplied by the second form called
frmMatrixChoices. frmMatrixChoices has a listbox that
contains items for the user to choose from. Once all
items are selected and saved the items show up in
frmMatrixMain. However, if the user wishes to go back and
edit or add additional items, the previously selected
items do not show up (highlighted) in the listbox on
frmMatrixChoices even though the data is being written to
tblMTXSelections correctly. See code below.

I'm at a dead end -- I'm not very proficient in VB
(obtained the code for this application from an article I
found).

Thanks in advance.
-----Original Message-----
My listbox [lstMatrixChoices] is not highlighting the
table data [tblMTXSelections]. I'm not sure if it's a
synchronization problem.

My frmMatrixMain has a listbox called lstMTXResponses.
The user clicks a button [cmdEditMTXRespones] to access
another form called frmMatrixChoices with a multiselect
listbox [lstMTXChoices] containing a multitude of options
to choose from. Once the options are all 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. But, if I
click the [cmdEditMTXResponses] button to edit my
selections, none of my previous selections are highlighted
in lstMTXChoices.

Could someone please tell me if my code looks correct?
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

.
Click to expand...
 
Back
Top