Despite my best efforts, sometimes I end up with complicated code.

The logic snakes and wraps around like crazy river rapids. Today, I had such a part of my customer’s database that I was working in and it was not easy.

Most of the time this problem occurs because we’ve made some assumption in the system. I had a special kind of line item that was a parent for other line items. This line item always was a one of a kind line item in each category.

Today, the customer needed to put a second special line item in the same category for the first time.

The children items would only connect to one parent by default and it was not choosing the parent the customer wanted it to.

There was absolutely no way I was going to be able to change the baked in behavior in one day, but we did find in this case that we could work around the problem by ordering the line items differently. The only problem was, nothing we did ordered the line items correctly.

The code wove through modules, class modules, queries, and queries built 3 to 4 levels deep. I tried to follow the logic, but it was just taking too long. I couldn’t step through the code fast enough because there was so much of it.

In the case of the VBA debugger, there are some cool tricks like:

1. Stepping over code – This allows you to step to the next line in the module you’re in instead of descending into any child functions.
2. Setting break points – This allows you to specify where you want the code to go into break mode for debugging.
3. Setting variable watches – This lets you specify a variable to watch in a specific context. When it changes the code can go into break mode for debugging.
4. Using Debug.Asserts – This lets you specify an expression in the code to tell it whether to go into break mode or not. I think it was actually intended to use for testing code.
5. Stack Window. Oh, Ctrl-L is your friend when stepping through code or arriving at a break point that multiple paths lead to. The stack window shows the entire code execution path from where it started to the current statement. It’s pretty darn cool.

Anyway, I eventually found my way to where there was an order by clause I could use to specify an order and we made it dependent on a field the customer controls. This works for now, but the customer and I both know it will need to be updated more extensively later to allow for specifying which child items go with which parent.