Highlight listbox record problems using recordset/.findfirst

[Scott Price]

I'm not quite ready to give up on this yet... Using MS Access 2003, WinXP SP2.

I have a listbox that I'm trying to get to highlight (select) a specific record using the GotFocus event (the listbox setfocus is passed by a combobox afterupdate event).

The code I have is:

Private Sub lstRate_GotFocus()
Dim rs As DAO.Recordset
Dim SelDate As Date
'Dim MyVarBM As Long
 
SelDate = DMax("[DateChange]", "tblDate")
    Set rs = Me.lstRate.Recordset
    With rs
        .MoveLast
        .FindFirst "[DateChange] = #" & SelDate & "#"
        If .NoMatch Then
            MsgBox "No Match Found"
        End If
    End With
End Sub

The code executes fine, the list box recieves the focus, .nomatch is set to false, the variables populate as desired... However the desired record is not highlighted in the listbox!

Anyone able to help me here??

Thanks in advance!
Regards,
Scott

[JKing]

Hi there, what is the record source of the list box?

I think the basic logic of what you want to do is to loop through the items in your listbox until a column value = your criteria. Once found grab the row and pass that into the Selected property and set it to true. This will "highlight" or select the record you want.

So perhaps you can give some insight as to what the criteria is being based off and what info is in your listbox.

[Scott Price]

Hi there JKing...

The record source of the listbox is this query in the Row Source:

SELECT tblDate.DateID, tblDate.DateQuarter, tblDate.InvoiceNo, tblDate.QOrderID, tblDate.DateChange FROM tblDate; 

I'll try the selected property and get back to you...

Thanks!
Regards,
Scott

[JKing]

Here's a quick and dirty example of looping through the listbox and setting the selected property.

Dim intCount As Integer
For intCount = 0 To Me.List10.ListCount - 1
    If Me.List10.Column(0, intCount) = "Some Value" Then
        Me.List10.Selected(intCount) = True
    End If
Next
 

The Column property takes in a column position starting at 0 and then a row number also starting at 0. If you want to look at the first column in the first row it would be Column(0,0). Third column 10 row would look like (2,9). I'm sure you get the picture.

Any further questions let me know and good luck!

[Scott Price]

Thanks again JKing... You gave me the nudge on the right direction... This is the code I came up with that finally worked:

Private Sub lstDate_GotFocus()
Dim rs As DAO.Recordset
Dim SelDate As Date
Dim MyVarBM As Long
 
SelDate = DMax("[DateChange]", "tblDate")
    Set rs = Me.lstDate.Recordset
    With rs
        .MoveLast
        .FindFirst "[DateChange] = #" & SelDate & "#"
        MyVarBM = .AbsolutePosition 'returns the row # of the offending record
        'Debug.Print MyVarBM
        Me!lstDate.Selected(MyVarBM) = True 'selects the offending row
        If .NoMatch Then
            MsgBox "No Match Found"
        End If
    End With
End Sub

Thanks again,
Regards,
Scott

[JKing]

Great, glad you got things working. Sometimes all you need is that little nudge.

Jared