Range.FindNext behaving differently on two scans of the same data

[tfurnivall]

I have a situation where I'm using the Range.FindNext function. Unfortunately, it is working in two distinctly different ways, when scanning the same set of data.

I have a relationship between MediaID and the Tracks on the Media. (Imagine a CD with a lot of tracks - I want to select all the Tracks that are associated with a given piece of Media.

In each instance the Find function works properly - to set up the Search range, KeyValue, and associated values. When I call this function the proper entry is returned. I then call FindNext, and even though the After parameter is identical, the result is different.

The call to FindNext is in a method of a class (clTrack), so it is exactly the same code being called each time - only the result is different!

I've attached the two methods I'm using (FindFirstSegmentedValue and FindNextSegmentedValue), and also the workbook.

FindFirstSegmentedValue

Please Login or Register  to view this content.

FindNextSegmentedValue

Please Login or Register  to view this content.

I know a) It's something I'm doing and not realizing (the same code, in a different class works perfectly consistently!) b) Other people's eyes don't have the same prejudices that mine do (I can overlook an elephant at fifty paces!).

I'd appreciate any quick thoughts, or else, feel free to delve into the extended code.

Thanks,

Tony

PS For those intrepid souls who head for the workbook.

IMPORTANT

The file is uploaded as a .xlsb file, in order to get past the 1MB limit on xlsm files!

This is a work-in-progress, so you'll need to start it up manually:
Open the spreadsheet
Alt+F11 to get into VBA
Load LMSForm into the IDE
F5 to get it going, then

Click Media page
Click FindFirst button (|< in the navigation pane at the bottom)
Click Test Tracks button - this works properly. You'll see two track entries being referenced
Click Tracks page - the bottom right list box on the new page shows only a single track.

Click exit.

f you are familiar with my code you will expect a trace file called LMS - Library Management System Trace.txt The last 44 lines of this file will show the initial TestTracks working properly, and then the second call to Find/FindNext not working.

[6StringJazzer]

Good explanation of what is going on but the code above that you called FindFirstSegmentedValue is actually FindNextKeyValue and it is not called from anywhere in your code. The code above that you called FindNextSegmentedValue is actually FindFirstSegmentValue. Can you clarify what code we are supposed to be looking at?

[tfurnivall]

OMG!

Yes - that was a really bad screen scrape - of the wrong procedure. So, to be certain:
1) The function s that should have been there are, indeed, FindFirstSegmentValue, and FindNextSegmentValue

2) I've attached a (clean) set of code to this message.

I've been working on this today, and I have been able to satisfy myself that the 'problem' is, indeed, inside the procedure that calls both of these functions, another function called LoadListControlEntries. This procedure allows me to scan a class using either a serial approach, or what I call a SegmentedSearch approach, which uses the Excel Find/FindNext functions. I did discover one possible problem (which didn't resolve the issue), in that I use a private variable called IRNG to do lots of Excel storage management. This is used both by LoadListControlEntries, and also by the helper function LoadEntryIntoObject. I've changed this so that the SegmentedSearch now use a completely different Private variable called SRNG (Segmented Range) to handle the hidden Excal capabilities which allow it to pick up on a range with hidden values (somewhat like the Sort function).

The version of LoadListControlEntries that is attached is the most recent version, with this separate variable. The uploaded .xlsb file does not necessarily contain the changes.

A more recent statement of my thinking about this is that somewhere in the gap between FindFirstSegmentValue, which sets up the parameters (including the actual SearchValue for the Find) and the call to FindNext, something is happening 'under the covers' to destroy the private-to-Excel values. Of course, because they are private-to-Excel, I can't get at them with debug, or Trace, or anything. I also can't think of any way in which I can test this hypothesis!

Thanks, 6 StringJazzer for pointing out my error. I'd love to hear you play sometime

Tony

1. FindFirstSegmentValue

Please Login or Register  to view this content.

2. FindNextSegmentValue

Please Login or Register  to view this content.

3. LoadListoControlEntries

Please Login or Register  to view this content.

If you are going to patch these versions of the code into the workbook I uploaded yesterday, you may well have to add a line of code to the top of clTrack:

Please Login or Register  to view this content.

This will allow the use of SRNG in the SegmentedSearch procedures.

Tony

[tfurnivall]

Here's an update on the problem (with a new upload)!

The issue is that FindNext behaves differently depending on how it is called.

The symptom is that the first call to FindNext, after the initial Find, returns a result of Npothing. According to MS this means that there is no matching value.
Fair enough - except that there are plenty of matching values in the worksheet!

And a different approach (using exactly the same FindNext API call!) can retrieve them.

So WHY is the case I'm experiencing not working?

End of rant - let's get down to some serious tracking down.

First - the workbook needs to be re-uploaded (downloaded for you poor souls who are struggling with me through this). Install, and then:
1: Run the Workbook (it's a .xslb file)
2: Alt + F11 to bring up the IDE
3: Load Form LMSForm
4: F5 to get the form running.

Now we need to see the possible ways to examine tracks.

A. Demonstrate the existence of all the tracks
Click the Tracks page
Click the FindFirst button ("|<" in the

[tfurnivall]

( Well - I really fat-fingered that! Here's what should have been in my post, continued past the point where I committed it to the world. APologies)


First - the workbook needs to be re-uploaded (downloaded for you poor souls who are struggling with me through this). Install, and then:
1: Run the Workbook (it's a .xslb file)
2: Alt + F11 to bring up the IDE
3: Load Form LMSForm
4: F5 to get the form running.

Now we need to see the possible ways to examine tracks.

A. Demonstrate the existence of all the tracks
Click the Tracks page
Click the FindFirst button ("|<" in the Navigation Panel in the bottom left corner of the form.
Click on the FindNext button (">" in the Navigation Panel)

repeat and you'll see the Tracks marching by in sequence. Notice how they are split between two different pieces of Media. This is important.

What does this show?
It shows that there are 10 tracks available in the worksheet. (Yopu can take a look at them by using the regular Excel UI and loooking in the Tracks Worksheet).

B. Demonstrate the existnmece of the relationship between Media and Tracks
Click the Media Page
Click the FindFirst button ("|<") and the FindLast button (">|")
Click the FindFirst button.
Click the TestTracks button. You will see 6 tracks (highly condensed data) appear in a message box. Then, after the last track message box is dismissed, you will see in the (conveniently available) listbox in the bottom right corner a single line, which is the first Track we found. Go through the Test Tracks sequence again, and note how the first record retrieved is the same one displayed in the listbox.
Click the FindLast button (brings up the other piece of media) and repeat the Test Tracks sequence. A different set of tracks appear in message boxes (4 of them), followed by a single entry in the listbox.

What does this show us?
It shows that the relation between Track and Media is intact. We can use it to get the tracks for a piece of media

C.

[tfurnivall]

Well, this unwanted cut happened at a logical place! We continue the saga.

C. Show that the relation works from both ends - Media AND Tracks
Exit out (Exit button at the bottom, just right of center), and then restart the app. (F5)
Go to the Media page, and get the First entry ({"|<"}. Then navigate to the Tracks page.

Navigate through the tracks (just as in test A. Note that rather than a random piece of Media, the tracks are all associated with the piece of media we chose earlier.
Go back to the Media page, click the Last button {">|"}, and then examine the tracks for that piece of media (only 4 of them, remember?).

What does this show?
This shows that the relationship between Media and Tracks is alive and well, and we can get at the 6 tracks of one piece of media, and the 4 tracks of the other, with little error.

Now for the rub

The code that loads the listbox uses the same code as the simple navigation even down to the filtering that selecting a different piece of media imposes.
The problem is that returned value of Nothing, from FindNext when called from the function LoadListControlEntries.

The Nothing means that there is no value that matches (we're looking for Media), and we've seen that there are, so, values that match.

HELP!

Can't figure this out,

Tony