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:
[CODE=vb]
Private Sub lstRate_GotFocu s()
Dim rs As DAO.Recordset
Dim SelDate As Date
'Dim MyVarBM As Long

SelDate = DMax("[DateChange]", "tblDate")
Set rs = Me.lstRate.Reco rdset
With rs
.MoveLast
.FindFirst "[DateChange] = #" & SelDate & "#"
If .NoMatch Then
MsgBox "No Match Found"
End If
End With
End Sub[/CODE]


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.

[code=vb]
Dim intCount As Integer
For intCount = 0 To Me.List10.ListC ount - 1
If Me.List10.Colum n(0, intCount) = "Some Value" Then
Me.List10.Selec ted(intCount) = True
End If
Next
[/code]

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:

[CODE=vb]Private Sub lstDate_GotFocu s()
Dim rs As DAO.Recordset
Dim SelDate As Date
Dim MyVarBM As Long

SelDate = DMax("[DateChange]", "tblDate")
Set rs = Me.lstDate.Reco rdset
With rs
.MoveLast
.FindFirst "[DateChange] = #" & SelDate & "#"
MyVarBM = .AbsolutePositi on 'returns the row # of the offending record
'Debug.Print MyVarBM
Me!lstDate.Sele cted(MyVarBM) = True 'selects the offending row
If .NoMatch Then
MsgBox "No Match Found"
End If
End With
End Sub[/CODE]

Thanks again,
Regards,
Scott

[JKing]

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

Jared