473,322 Members | 1,718 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Highlight listbox record problems using recordset/.findfirst

Scott Price
1,384 Expert 1GB
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
5 7724
JKing
1,206 Expert 1GB
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
1,384 Expert 1GB
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
1,206 Expert 1GB
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
1,384 Expert 1GB
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
1,206 Expert 1GB
Great, glad you got things working. Sometimes all you need is that little nudge.

Jared
Aug 8 '07 #6

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

Similar topics

4
by: Skully Matjas | last post by:
I am using the following code (created by the wizard) to allow to bring my form to a particular entery. But when I edit the entery (ex: put new information into a blank cell), it puts that record...
1
by: Skully Matjas | last post by:
Thank you for getting back to me. I am very new at this so i didnot understand what you said, here i will give as much cetails as possible: 1) The combo box i am using is combox39 2) I imported...
17
by: Danny J. Lesandrini | last post by:
The following code works with a standard MDB to navigate to a particluar record (with a DAO recordset, of course) but it's giving me problems in an ADP I'm working on. Dim rs As ADODB.Recordset...
1
by: Geir Baardsen | last post by:
Hi! On frmItems I have a two listboxes. The first, lstAllCategories, is loaded with data from tblCategory when frmItems open. When user click lstAllCategories, the lstSelectedItems will show all...
20
by: MS | last post by:
Access 97 I want to requery the data being displayed on a form, then I want to return to the record I was in. Why doesn't this code work? Private Sub CmdRefsh_Click()
8
by: Zlatko Matiæ | last post by:
There is a form (single form) and a combobox. I want that current record of the form is adjusted according to selected value in the combobox. Cuurrent record should be the same as the value in the...
10
by: ApexData | last post by:
Hello How do I goto a specific record in a BOUND form when the form has a primary index of ID which is autonumbered. I need to visit 4 seperate records, one at a time. This works for RecNO...
7
by: boyleyc | last post by:
Hi all I have written a database in access and used ADODB recordsets all the way through. The only recordsets that are not ADODB are the listbox navigation code automatically generated by access...
3
by: boliches | last post by:
I am trying to get a continuous form to highlight lines individually (ideally on hover). I have used the following code as recommended by a previous entry on this website. My problems begin in that...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.