Syndicate content

MS-Access and Focus handling

MS-Access can be programmed with VBA which is neat. I do that a lot because the default forms do not allow you to strongly verify the data validity or even better: prevent you from editing certain record because they are too old, you don't have permissions, it was already archived in some way (i.e. invoice paid), etc.

However, I often encounter problems with the focus. The main thing I do to make it as safe as possible is show or hide buttons. If you cannot activate function A, then there is no point in having a button for function A. Instead, we should hide the function A button when we can determine that the function is not required.

It is actually very easy to do so. When I do that, I create a function for my form called SetStatus() [always using the same way makes it easier on you, although in some cases you may want to have multiple such functions, in most cases one per form is more than enough and is likely to be easier to maintain.] The SetStatus() checks the current state and depending on the state, changes the visibility of my buttons. Something that looks like this:

Private Sub SetStatus()
    Dim some_flag As Boolean

    ' Compute all my flags (i.e. Current State)
    some_flag = Iff(SomeControl = "this value", True, False)

    ' Apply the state to buttons, labels, etc.
    ButtonA.Visible = some_flag
    ButtonB.Visible = Not some_flag
End Sub

As you can see, this is very simple. I set the visibility of the button (A or B) to true or false depending on the value of SomeControl. As you add more controls and functionality and parameters, your SetStatus() grows and grows to check all the possible statuses and determine the proper visibility.

Note that you could also set the Enabled flag:

    ButtonA.Enabled = some_flag

In our case, we prefer to make them disappear. It's for a cleaner form and it allows us to place several different buttons in the same location (i.e. I have view only forms that have a Close button at the bottom right, and when you click Edit, the Close button gets hidden and replaced by a Cancel button so you can cancel the edit, but you cannot just close the window anymore.)

This generally works very well, but there one gotcha. You cannot hide or disable a control if it has the focus. The only way around this is to change the focus to another widget (what I generally do) or void the focus altogther. Now, how do you really handle all of these buttons and go around this problem?

In most cases I can specifically set the focus on a control which I know will never disappear on me. For example, I may have a FirstName control and do this:

Private Sub SetStatus()
    Dim ...

    FirstName.SetFocus

    ... ' Code that may hide some buttons and other controls
End Sub

There are cases when you may have to test one thing or another if no field is really satisfactory. But the worst case is when you have to do something like that while the user is actively editing your form. This is a problem because you do not want to take control under the user's feet. This problem can be limited by testing what currently has focus. If one of the controls to be hidden has the focus, then change it to another object without thinking too much whether it will annoy the user... the button with focus is going away anyway!

The focus is changed with SetFocus on the control you are giving focus to. To test which one of your controls has the focus, however, it is done by request to the form with the ActiveControl function. This returns an object that you can compare to your controls, or it is undefined (no control has the focus.)

Private Sub SetStatus()
    Dim ...

    ... ' some code

    If Form.ActiveControl Is some_control Then
        this_other_control.SetFocus
    End If

    ... ' more code
End Sub

Remember that read-only controls can be given focus, if nothing else.

As a side note, if you are looking into getting the current control with the focus in MS-Windows as a whole, use the Screen instead of the Form variable.

Private Sub MyFunction()
    If Screen.ActiveControl Is some_control Then
        ...
    Else
       ...
    End If
End Sub

At this point I never had to know what was the screenwide active control.

Problems resolved: Set Focus to a Control, Find Which Control Has Focus, Unfocus a Control by Setting the Focus on Another.

Syndicate content