MS Access Treeview generates error 35601 "Element not found"

Hello: (I have a ghost in the machine it seems...)

I've developed an Access 2003 front end to a sql server 2000 backend. The main form uses a treeview control which is populated using an adjacency modeled sql table (i.e., EMP_ID, BOSS_ID) called tbl_Dependencies.

The table tbl_Dependencies also has a third field which tracks the sort order of any row item.  Thus, the table has three fields: EMP_ID (autonumber), BOSS_ID (INT), and SORT_ORDER (INT).

Below is sample data:
1           null            0
2            1               0
3            1             10
4            2               0
5            2              10
6            3                0
7            3               10
8            3               20

On the Access main Form, when a person clicks on a node on the tree, a related subform displays the fields from tbl_Dependencies. A user can add/delete records from the subform quite easily; the subform auto-populates the BOSS_ID when adding new records. Whenever a record is added/deleted/changed in the subform, the event triggers a refresh of the treeview wherein the parent node of the record in the subform is selected so that the subform continues to display the subordinate rows.

I've encountered a problem in only one instance where changing the sort order of an item to show up last generates an error 35601 "Element not found".  This gets even stranger since this error occurs in only one specific location within the tree. The tree actually has hundreds of rows, some nesting 4 to 5 levels deep. Any other part of the tree can have items added or re-ordered to the end of a list without consequence.

But, for some reason, there is one subsection of the tree where VBA or the treeview cannot "tolerate" changing the sort order of an existing item to be last nor to add a new item to be last. A review of the sql table shows that the data is captured correctly, but the treeview fails to display the relationship.

Any thoughts?
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.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Which Treeview control are you using? Access doesn't have a native Treeview ...

Can you show the code where the error occurs?
GRIPA1Author Commented:
Thanks for the quick response.

As I was preparing various snippets of code to post for others to view I discovered that one of the sql tables had a duplicate entry which was causing the error. It also explains why I could not generate this error in any other part of the treeview. Upon deleting the duplicate row, the error no longer occurs.

Thanks for your original offer of assistance. It appears I need to add a couple of constraints to ensure data integrity.

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
GRIPA1Author Commented:
By the way, how do I Cancel my request for help? Do I just delete the question?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You should see a "Delete Question" button ... this will generate a question in Community Support, and they can delete it for you (you can't delete it if Comments have been posted, I believe).
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.