As a reminder, the purpose of the weArrowKeyNav class is to change the behavior of the up and down arrow keys to make continuous forms in Access behave more like Excel worksheets. For example, pressing the up arrow key moves to the previous record (instead of the previous control).
To do this, the class overrides the up and down arrow key behavior for each of the controls in the Detail section of a continuous form. When the user presses the up or down arrow, the code calls the form recordset's MovePrevious or MoveNext method, respectively.
This produces a more intuitive user experience; one that is much more in line with Excel's familiar behavior.
Dropped-down combo boxes
This does result in one case where the behavior is not what the user would expect. That situation is when a combo box is in its "dropped-down" state.
Consider the screenshot above. At the top of the screenshot are the four previous records. The current record is the one where the Supplier's name is "Kelley." The previous record is the one where the Supplier's name is "Sousa."
The combo box is in its dropped-down state. Within the dropdown, "Kelley" is highlighted. The immediately preceding option is "Weiler." One would expect that pressing the up arrow key on the keyboard would switch the current record's Supplier value from "Kelley" to "Weiler."
Instead, the focus will jump to the previous record and the "Sousa" combo box will be highlighted.
Canceling the override
The solution is simple. If the combo box is open, then we exit immediately and allow the default up and down arrow key behavior to work normally. To do this, we add a guard clause that checks the combo box's dropped-down state:
Now, we'll just compile our code and call it a nigh–
OK, so as it turns out, there is no combo box property that indicates its dropped-down status. It looks like we're going to have to go searching the interwebs...
The code in the link above is so old...[audience]: HOW OLD IS IT?!?!...the code in the link is so old that it predates VBA7 and 64-bit support.
The good news for you is that I've updated the code (at least the Dev Ashish approach) to run in all versions of Access. Here it is:
The Code: fIsComboOpen() w/64-bit support
The code below is mostly Dev Ashish's original work. To the best of my recollection, the only changes I made were the additions of the conditional #VBA7 code blocks to provide 64-bit VBA compatibility.
And here is how we put that function to use in our ComboBox's KeyDown event handler:
Never miss an article.
Sign up to receive a Sunday morning email with links and recaps of the seven articles published that week. (I'll never sell your email. Unsubscribe any time.)