By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,687 Members | 2,065 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,687 IT Pros & Developers. It's quick & easy.

Highlight listbox record problems using recordset/.findfirst

Scott Price
Expert 100+
P: 1,384
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
  1. Private Sub lstRate_GotFocus()
  2. Dim rs As DAO.Recordset
  3. Dim SelDate As Date
  4. 'Dim MyVarBM As Long
  5.  
  6. SelDate = DMax("[DateChange]", "tblDate")
  7.     Set rs = Me.lstRate.Recordset
  8.     With rs
  9.         .MoveLast
  10.         .FindFirst "[DateChange] = #" & SelDate & "#"
  11.         If .NoMatch Then
  12.             MsgBox "No Match Found"
  13.         End If
  14.     End With
  15. 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
Aug 8 '07 #1
Share this Question
Share on Google+
5 Replies


JKing
Expert 100+
P: 1,206
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.
Aug 8 '07 #2

Scott Price
Expert 100+
P: 1,384
Hi there JKing...

The record source of the listbox is this query in the Row Source:
Expand|Select|Wrap|Line Numbers
  1. 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
Aug 8 '07 #3

JKing
Expert 100+
P: 1,206
Here's a quick and dirty example of looping through the listbox and setting the selected property.

Expand|Select|Wrap|Line Numbers
  1. Dim intCount As Integer
  2. For intCount = 0 To Me.List10.ListCount - 1
  3.     If Me.List10.Column(0, intCount) = "Some Value" Then
  4.         Me.List10.Selected(intCount) = True
  5.     End If
  6. Next
  7.  
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!
Aug 8 '07 #4

Scott Price
Expert 100+
P: 1,384
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
  1. Private Sub lstDate_GotFocus()
  2. Dim rs As DAO.Recordset
  3. Dim SelDate As Date
  4. Dim MyVarBM As Long
  5.  
  6. SelDate = DMax("[DateChange]", "tblDate")
  7.     Set rs = Me.lstDate.Recordset
  8.     With rs
  9.         .MoveLast
  10.         .FindFirst "[DateChange] = #" & SelDate & "#"
  11.         MyVarBM = .AbsolutePosition 'returns the row # of the offending record
  12.         'Debug.Print MyVarBM
  13.         Me!lstDate.Selected(MyVarBM) = True 'selects the offending row
  14.         If .NoMatch Then
  15.             MsgBox "No Match Found"
  16.         End If
  17.     End With
  18. End Sub
Thanks again,
Regards,
Scott
Aug 8 '07 #5

JKing
Expert 100+
P: 1,206
Great, glad you got things working. Sometimes all you need is that little nudge.

Jared
Aug 8 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.