Selecting rows in Listbox

[steamngn via AccessMonster.com]

Good Morning All,
I have a form that contains an unbound multiselect listbox and a subform.
When a user selects a record in the subform, I open a recordset that I wish
to compare to the listbox records and then select each record that matches.
Here is my non-working code:

Private Sub btnSelComp_Click()
On Error GoTo Err_btnSelComp_Click
Dim rs As Recordset, strWhere As String, i As Long
Me.Text20.Value = Me.ID
Parent!LstEnteredDivs.Enabled = True
Set rstUnfiltered = CurrentDb.OpenRecordset("EventDivisionMaster",
dbOpenDynaset, dbReadOnly)
rstUnfiltered.Filter = "[CompetitorID] = " & Me.ID & " And [EventID] = " &
Parent!txtEventId
Set rs = rstUnfiltered.OpenRecordset
'Check to see if any records are returned
If Not rs.BOF And Not rs.EOF Then
'Wait for the recordset to completely load
rs.MoveLast
i = rs.AbsolutePosition
'Just check to see if there are records for now
MsgBox rs.RecordCount
End If
Dim lngList As Long
With Parent!LstEnteredDivs
For lngList = 0 To .ListCount - 1
If .ItemData(lngList) = [DivisionID] Then
.selected(lngList) = True
.Value = .ItemData(lngList)
Exit For
End If
Next lngList
End With

Set rs = Nothing

Exit_btnSelComp_Click:
Exit Sub

Err_btnSelComp_Click:
MsgBox Err.Description
Resume Exit_btnSelComp_Click

End Sub

The recordset is returning records, but I cannot seem to get the rest of it
to work. can someone see the errors in my ways?

 

[Stuart McCall]

Good Morning All,
I have a form that contains an unbound multiselect listbox and a subform.
When a user selects a record in the subform, I open a recordset that I
wish
to compare to the listbox records and then select each record that
matches.
Here is my non-working code:

Private Sub btnSelComp_Click()
On Error GoTo Err_btnSelComp_Click
Dim rs As Recordset, strWhere As String, i As Long
Me.Text20.Value = Me.ID
Parent!LstEnteredDivs.Enabled = True
Set rstUnfiltered = CurrentDb.OpenRecordset("EventDivisionMaster",
dbOpenDynaset, dbReadOnly)
rstUnfiltered.Filter = "[CompetitorID] = " & Me.ID & " And [EventID] = "
&
Parent!txtEventId
Set rs = rstUnfiltered.OpenRecordset
'Check to see if any records are returned
If Not rs.BOF And Not rs.EOF Then
'Wait for the recordset to completely load
rs.MoveLast
i = rs.AbsolutePosition
'Just check to see if there are records for now
MsgBox rs.RecordCount
End If
Dim lngList As Long
With Parent!LstEnteredDivs
For lngList = 0 To .ListCount - 1
If .ItemData(lngList) = [DivisionID] Then
.selected(lngList) = True
.Value = .ItemData(lngList)
Exit For
End If
Next lngList
End With

Set rs = Nothing

Exit_btnSelComp_Click:
Exit Sub

Err_btnSelComp_Click:
MsgBox Err.Description
Resume Exit_btnSelComp_Click

End Sub

The recordset is returning records, but I cannot seem to get the rest of
it
to work. can someone see the errors in my ways?

Remove this line:

..Value = .ItemData(lngList)

The .Value property has no meaning for a multi-select list box.

 

[steamngn via AccessMonster.com]

Good Morning All,
I have a form that contains an unbound multiselect listbox and a subform.

[quoted text clipped - 48 lines]

it
to work. can someone see the errors in my ways?

Remove this line:

.Value = .ItemData(lngList)

The .Value property has no meaning for a multi-select list box.

Hi Stuart,
Thanks for the quick reply. I realized I posted the wrong code just as you
replied! (sorry)
Here is the correct, non-working code:

Private Sub btnSelComp_Click()
On Error GoTo Err_btnSelComp_Click
Dim rs As Recordset, strWhere As String, i As Long, lngList As Long, RC As
String
Me.Text20.Value = Me.ID
Parent!LstEnteredDivs.Enabled = True
Set rstUnfiltered = CurrentDb.OpenRecordset("EventDivisionMaster",
dbOpenDynaset, dbReadOnly)
rstUnfiltered.Filter = "[CompetitorID] = " & Me.ID & " And [EventID] = " &
Parent!txtEventId
Set rs = rstUnfiltered.OpenRecordset
'Check to see if any records are returned
If Not rs.BOF And Not rs.EOF Then
'Wait for the recordset to completely load
rs.MoveLast
i = rs.AbsolutePosition
'Now check the count
'MsgBox rs.RecordCount & DivisionID
End If
rs.MoveFirst
Do While Not rs.EOF
With Parent!LstEnteredDivs
For lngList = 0 To .ListCount - 1
RC = RC & " " & rs!DivisionID
If .ItemData(lngList) = rs!DivisionID Then
.selected(lngList) = True
End If
Exit For
Next lngList
End With
rs.MoveNext
Loop
MsgBox RC
Set rs = Nothing

Exit_btnSelComp_Click:
Exit Sub

Err_btnSelComp_Click:
MsgBox Err.Description
Resume Exit_btnSelComp_Click

End Sub

the loop returns recocrds correctly, but the listbox does not set each row to
selected. The line you referenced was taken out...

 

[steamngn via AccessMonster.com]

More of an update:
I had the loops out of order. I need to loop through the listbox, and then
check each record in the query against the listbox row. the correct looping
is below:

With Me.Parent!LstEnteredDivs
For lngList = Abs(.ColumnHeads) To (.ListCount - 1)
rs.MoveFirst
Do While Not rs.EOF
RC = RC & " " & rs!DivisionID & " " & lngList
If .ItemData(lngList) = rs!DivisionID Then
.selected(lngList) = True
End If
rs.MoveNext
Loop
Next
End With

However, it still won't hightlight rows. If I comment out the If .ItemData
(lngList) then the code will select ALL rows in the listbox, so it appears
that the .ItemData portion is screwed up...
Help!

 

[Stuart McCall]

More of an update:
I had the loops out of order. I need to loop through the listbox, and then
check each record in the query against the listbox row. the correct
looping
is below:

With Me.Parent!LstEnteredDivs
For lngList = Abs(.ColumnHeads) To (.ListCount - 1)
rs.MoveFirst
Do While Not rs.EOF
RC = RC & " " & rs!DivisionID & " " & lngList
If .ItemData(lngList) = rs!DivisionID Then
.selected(lngList) = True
End If
rs.MoveNext
Loop
Next
End With

However, it still won't hightlight rows. If I comment out the If .ItemData
(lngList) then the code will select ALL rows in the listbox, so it
appears
that the .ItemData portion is screwed up...
Help!

Try coercing both sides of the comparison into longs:

If Clng(.ItemData(lngList)) = Clng(rs!DivisionID) Then

 

[steamngn via AccessMonster.com]

More of an update:
I had the loops out of order. I need to loop through the listbox, and then

[quoted text clipped - 20 lines]

that the .ItemData portion is screwed up...
Help!

Try coercing both sides of the comparison into longs:

If Clng(.ItemData(lngList)) = Clng(rs!DivisionID) Then

Stuart,
Right on the money! I didn't even give that a thought as both values are
numbers, but i should know better.
Thanks for the 'coercion'!
andy

 

[Stuart McCall]

More of an update:
I had the loops out of order. I need to loop through the listbox, and
then

[quoted text clipped - 20 lines]

that the .ItemData portion is screwed up...
Help!

Try coercing both sides of the comparison into longs:

If Clng(.ItemData(lngList)) = Clng(rs!DivisionID) Then

Stuart,
Right on the money! I didn't even give that a thought as both values are
numbers, but i should know better.
Thanks for the 'coercion'!
andy

Glad to help. Sorry for the rather terse replies. I'm typing one-handed at
the moment.

 

[steamngn via AccessMonster.com]

[quoted text clipped - 12 lines]

Thanks for the 'coercion'!
andy

Glad to help. Sorry for the rather terse replies. I'm typing one-handed at
the moment.

No problem! i hope the one-handed issue is due to a glass of beer in the
other one!
Andy