selecting listbox record thru vba

I have a listbox on a main form that populates from the entry the user makes on the same form.  When the new entry is populated in the listbox it is not highlighted.  After the newly entered record populates the listbox it is not highlighted, like when the user clicks on it.  There is a caveat to this.  When the record populates and shows in the listbox, it may not always be the first or last record in the list.  The listbox is sorted by date.  So the new entry is in the list by date.  Also, the entry the user completed is still displayed on the form and the ID shows in the listbox and in the textbox of the entry.  Below is what I have so far.  If I breakpt and step thru, the IF line will match as the loop goes thru.  But, when the column equals the txtNoteID, it does not drop into the THEN but continues to loop until the end.

If anyone has any thoughts, ideas, suggestions, recommendations (other than retire) I am listening.

Thank you...John

    Dim i As Integer

    For i = 0 To Me.lstMeetingNote.ListCount - 1
        If Me.lstMeetingNote.Column(0, i) = Me.txtNoteID Then
            Me.lstMeetingNote.Selected(i) = True
            Me.lstMeetingNote = Me.lstMeetingNote.ItemData(i)
        End If
    Next i
billy bobAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark EdwardsChief Technology OfficerCommented:
Once you find the listbox entry you are looking for and select it, you need to exit the loop.  Try:

For i = 0 To Me.lstMeetingNote.ListCount - 1
         If Me.lstMeetingNote.Column(0, i) = Me.txtNoteID Then
             Me.lstMeetingNote.Selected(i) = True
             Me.lstMeetingNote = Me.lstMeetingNote.ItemData(i)
             exit for
         End If
 Next i
0
billy bobAuthor Commented:
Thanks for the response.  But, as mentioned, even when the Me.lstMeetingNote.Column(0, i) = Me.txtNoteID match with the ID numbers, it still does not go into the THEN phase of the FOR...NEXT.  It bypasses the 2 (now 3) lines following the THEN.

EDIT: This info might be the issue:  The listbox has a query for a rowsource.  The form is unbound.  The txtbox is unbound.

...John
0
Mark EdwardsChief Technology OfficerCommented:
o.k. I got a better idea of what you're asking now.
if Me.lstMeetingNote.Column(0, i) = Me.txtNoteID  is NOT ever evaluating to True, then they are never matching and you'll never go into the THEN lines of your code.

Break and step thru the code, checking the value of both Me.lstMeetingNote.Column(0, I) and Me.txtNoteID
You can also test the value of Me.lstMeetingNote.Column(0, i) = Me.txtNoteID in the immediate window to see if it is True or False with
?Me.lstMeetingNote.Column(0, i) = Me.txtNoteID
if the values for the two controls look like they should match, but don't evaluate to being equal (True), then you have to find out why, and only you can do that.  We can't help you there....

Keep in mind that that things like Null = Null does NOT equate to True, and unseen spaces or other invisible characters in a string can make it look like two strings that SHOULD match, won't, etc.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Mark EdwardsChief Technology OfficerCommented:
Here's a tip for anyone encountering a situation like this:
It's not important that YOU think the values are equal or not equal.  It is ALL IMPORTANT that the COMPUTER sees the values as equal or not equal.  Hidden characters like leading/trailing spaces, carriage returns (ASC 10) and line feeds (ASC 13) can throw a monkey wrench into human evaluations and analysis.  ALWAYS get the computer to tell you what IT sees and thinks!
0
Gustav BrockCIOCommented:
Try this:

    For i = 0 To Me.lstMeetingNote.ListCount - 1
        If Val(Me.lstMeetingNote.Column(0, i)) = Val(Me.txtNoteID) Then
            Me.lstMeetingNote.Selected(i) = True
            Exit For
        End If
    Next i 
    Me.lstMeetingNote = Me.lstMeetingNote.ItemData(i)

Open in new window

0
Mark EdwardsChief Technology OfficerCommented:
Just a caution:

If you use the Val() function, be sure to guard against trying to process a Null, as the Val() function throws an error if it tries to process a Null.
Enclose a control or field in the NZ() function to ensure against nulls:
Val(NZ(Me.txtNoteID,0))

Also, the use of the Val() function above assumes that we are comparing numerical values.  The design concept of an ID is usually a number, usually a Long Integer, so the urge to assume a number is strong, but it doesn't have to be a number.  If the two values being compared are strings, the Val() function will not work as intended.
0
billy bobAuthor Commented:
Mr Edwards and Mr Brock, thank you both for the feedback.  Mr Edwards, you comment or tip got me thinking about text versus integer that was being declared and how the ID in the listbox was being looked at.  So, I started experimenting and used 'CInt' in front of the listbox.column piece of the IF and left the txtNoteID as is.  And the true/false began showing properly in debug.  Mr Brock, your response validated what I was seeing (partly seeing) not to mention moving the ItemData line outside the FOR...NEXT.  I only moved it outside the End if.  I swapped out CInt with VAL and added the NZ as suggested and moved ItemData one more level and it works like a charm.  

Thank you both for your time, patience, and perseverance.

... John
0
Mark EdwardsChief Technology OfficerCommented:
John:  Glad I could help...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.