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:

Expand|Select|Wrap|Line Numbers

 
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:

Expand|Select|Wrap|Line Numbers

 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.

Expand|Select|Wrap|Line Numbers

 
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:

Expand|Select|Wrap|Line Numbers

 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

Highlight listbox record problems using recordset/.findfirst

re: Highlight listbox record problems using recordset/.findfirst

re: Highlight listbox record problems using recordset/.findfirst

re: Highlight listbox record problems using recordset/.findfirst

re: Highlight listbox record problems using recordset/.findfirst

re: Highlight listbox record problems using recordset/.findfirst

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/other

/bytes/about