In Access, can cursor default to the search box on a form?

[twinnyfo]

NoePa,

I guess that owuld explain it....

[NeoPa]

I asked about this in an MVP group and got a response from one of the Access Product Team (Shane Groff - Many thanks Shane) who said:

Shane Groff:
I don't think we have any command/method to put the focus there, but adding this works for me:

Private Sub Form_Open(Cancel As Integer)
SendKeys "%{F5}"
End Sub

Alt+F5 is the shortcut to put the focus in the record count textbox.

From this I worked out you could determine the number of TABs required to move it across (Depends on which of the buttons are available.) and update it to reflect that. On my form, which uses a snapshot recordset (No adding records.), I needed four TABs and came up with :

Private Sub Form_Open(Cancel As Integer)
    Call SendKeys("%{F5}{TAB}{TAB}{TAB}{TAB}")
End Sub

[twinnyfo]

I know how ugly SendKeys can be and I think I remember a little birdie telling me to try to avoid this particular function. I've also heard that this method is less reliable on new Windows 10 systems (and can sometimes not work at all).

So.... I've developed an alternate method for this that I sometimes use to automate things that are outside the bounds of normal "MS Office Automation". It functions quite similarly to SendKeys but uses the system dll to work at that level.

Create a new modules called modKeyStrokes:

Option Compare Database
Option Explicit

Public Declare Sub keybd_event Lib "user32" ( _
    ByVal bVk As Byte, _
    ByVal bScan As Byte, _
    ByVal dwFlags As Long, _
    ByVal dwExtraInfo As Long)

Declare Function GetKeyState Lib "user32.dll" ( _
    ByVal nVirtKey As Long) As Integer

Private Const vk_Tab = &H9   'TAB key
Private Const vk_Alt = &H12  'Alt key
Private Const vk_F5 = &H74   'F5 Key
Private Const vk_KEYUP = &H2 'indicates the key being released

Public Function vkTab()
    keybd_event vk_Tab, 1, 0, 0
End Function
Public Function vkTabUp()
    keybd_event vk_Tab, 1, vk_KEYUP, 0
End Function
Public Function vkAlt()
    keybd_event vk_Alt, 1, 0, 0
End Function
Public Function vkAltUp()
    keybd_event vk_Alt, 1, vk_KEYUP, 0
End Function
Public Function vkF5()
    keybd_event vk_F5, 1, 0, 0
End Function
Public Function vkF5Up()
    keybd_event vk_F5, 1, vk_KEYUP, 0
End Function

Now, in your form, when you want to go to the search box:

Private Sub cmdGoToSearch_Click()
    Call vkAlt
    Call vkF5
    Call vkF5Up
    Call vkF5Up
    Call vkAltUp
    Call vkTab
    Call vkTabUp
    Call vkTab
    Call vkTabUp
    Call vkTab
    Call vkTabUp
    Call vkTab
    Call vkTabUp
    Call vkTab
    Call vkTabUp
End Sub

I have an extra Tab in there, because my Access also has a "Filter" control that must be skipped over. Keep in mind that you may have to check to see if you are at a new record, because you will need fewer Tab characters if you are.

It may take some experimenting to do exactly what you need, but it should work (it did for me).

Hope this hepps!

[NeoPa]

Why ?

[twinnyfo]

I can tell the what--not the why.

When our office first upgraded to Win10, SendKeys worked on my machine and maybe one or two others. But other machines just sat that looking dumb.

After swithing to the alternate method, things worked fine on all machines.

I wish I could 'splain why....

[NeoPa]

I should say more.

As a general rule I don't like SendKeys(), or any code that relies on the state of something. That said, there are times when it can do what's required. In this case, I would expect that the requirement for being run in the Form_Open() event procedure would/should make its behaviour fairly predictable. It would also have the advantage of being understandable (if somewhat obscure) and readable in one go.

I haven't experienced such issues in Win 10. Not that I question others may. I just don't use it (I didn't just get off the boat ;-) ).

Maybe if I found it didn't work reliably as expected I'd move on to something like your code, but I'd certainly be upset to have to.

I guess the important thing to take away from this is that the only way to put the focus in that box that we know of is to TAB across from the Record Number box on the Navigation Bar, and the only way we know to get there is to use Alt-F5 (somehow).

NB. I have used O/S calls in my code where I absolutely have to, and one even that mimics keystrokes (Bypassing code when opening a database in Access for instance.) but I always try to avoid it if I can.