Go to intended target control after requerying on LostFocus/AfterUpdate

[bernieburnham]

Firstly, this is my first post, so hello to everyone.

[For reference, I'm using an accdb database in Access 2010 on Windows XP]

I have a form with several fields, a record navigation listbox (created using the "Find a record on my form..." part of the listbox wizard) and a few search and filter controls in the header to filter said record navigation listbox. The listbox is a two-column one with the primary key bound and hidden and the item name field as the visible column.

For the purpose of keeping the listbox up-to-date with changes to the item name, I am using a macro on the LostFocus event of the ItemName control (using AfterUpdate has similar results) to requery the listbox, and return to the active record because the requery goes back to the first record. But another consequence of the requery is that the focus jumps to the first control in the section (which coincidentally is the ItemName field), rather than continuing on to the field to which the user intended to navigate.

The only workarounds I've found so far are to set the focus to a particular control (usually suggested as the next control in the tab order) after the requery. However, users aren't always going to that specific control; they may have pressed SHIFT+TAB instead or clicked to a completely different control. Does anyone know of a solution that retains the transition to the intended control in spite of the requery, or a solution that updates the listbox while avoiding the requery altogether?

I'm now off to introduce myself properly in the Introductions section.

Thanks,
Adam Burnham

P.S.: I'm open to VBA, but would rather avoid it for maintainability purposes.

 

[bernieburnham]

Typically, half an hour after posting, I find the answer.

It turns out that 'refresh' will update the listbox. I think in hindsight that when I'd tried that previously there were other things in the macro that bugged out before the macro even got there, thus preventing it from working properly. These bits have since been stripped out (because they weren't working as intended either).

Never mind. It's always good when you answer your own questions. Thanks for reading, and hopefully this thread helps someone in some manner or other.

Thanks,
Adam Burnham