MS Access TextBox Value is always NULL

Question

I am doing some work for someone that insists on using MS Access. I don't usually use it so I am a bit new to the whole control structure and best practices. What I am trying to achieve is to have a filter textbox on a form which, when a value is entered, it will filter the rows in the detail section. That seems like a straightforward use case. I initially tried the following behaviour as the event handler for the On Change event:

Private Sub FilterGrid()
    Me.Text32.SetFocus

    If Not IsNull(Me.Text32.Text) And Me.Text32.Text <> "" Then
        Me.Filter = "JobNumber LIKE '*" & Me.Text32.Text & "*'"
        Me.FilterOn = True
    End
    Else
        Me.FilterOn = False
    End If
End Sub

This worked perfectly until I typed something that didn't have any rows matching and the whole thing exploded with this error (and was unrecoverable without closing the form): Run-time error '2185': You can't reference a property or method for a control unless the control has the focus.

I did some reading around and the general opinion was that .Text should not be used and .Value (or simply the Text32 without a property) should be used. That produced some very strange behaviour. The Text32.Value is ALWAYS null. I have a watch window and I can see that for the normal behaviour, Text32.Text has an actual value, but Text32.Value is NULL.

Obviously I am doing something wrong, but I don't have enough experience with Access to know what it is.

Just as an aside, another suggestion was to do Text32.SetFocus right before accessing the Text property. This doesn't resolve the error I mentioned. It still throws the exact same error.

Is anyone able to point me in the right direction here?

Just another update. I have continued testing. It would seem that the Value property of the textbox is not updated until the control loses focus. I have tested the Value property on every event the control has registered and the only time it correctly displays what I have 'changed' in the text box is in the AfterUpdate event once I click somewhere besides that textbox. This would be easily solved then by adding a 'Search' button that has to be clicked, but I find that isn't a good user experience. It is much nicer if it filters as they type. (I know there's a performance concern). — Adam Emerton

Renaud Bompuis Renaud Bompuis · Accepted Answer · 2013-08-16T04:17:05

As you found out, the textbox's Value is only set after the control loses focus.
Conversely, the Text property is only accessible while the control has focus.

The Value property is defined as the default member for controls; that means Text32 will be implicitly the same as Text32.Value, however, depending on the context,Text32 can sometimes refer to the control itself, not just its value.

All these discrepancies can sometimes be infuriating.

To go back to the matter at hand: you have 2 ways to handle filtering.

if the list to filter is large, it's probably better that the user type their filter, then press ENTER to validate it.
if your list is not too large, you can implement filter as you type.

First case, wait for user input to be validated by ENTER.

Say your filtering textbox is called txtFilter and is located on a form whose subform is showing a datasheet (or continuous form) that you want to filter.

All you need to do is wire the textbox OnKeyDown events as such:

' We will only perform the filter if the user press the ENTER key
Private Sub txtFilter_KeyDown(KeyCode As Integer, Shift As Integer)
    Select Case KeyCode
        Case 13, 9
            KeyCode = 0
            QuickFilter
    End Select
End Sub

' Perform the actual filtering on the subform
Private Sub QuickFilter()
    Dim sql As String
    Dim filter As String
    If txtFilter.Text = vbNullString Then
        ' Reset the filter if the textbox is emtpy
        SubForm.Form.FilterOn = False
    Else
        'Some common substitutions that users may have already inserted as wildchars
        filter = Replace(txtFilter.Text, "%", "*")
        filter = Replace("*" & filter & "*", "**", "*")
        ' We construct the filter SQL
        sql = "([JobNumber ] LIKE """ & filter & """)"
        sql = sql & " OR ([ProjectCode] LIKE """ & filter & """)"
        sql = sql & " OR ([SupplierName] LIKE """ & filter & """)"
        '... Add as many columns to filter on as you want

        ' Assign the filter to the subform
        SubForm.Form.filter = sql
        SubForm.Form.FilterOn = True
    End If
End Sub

Second case, filter as you type

Well, it's fairly easy, we just need to add to the above solution a way to track changes as the user is typing.
This is best done through the OnChange event of the texbox.

Private Sub txtFilter_Change()
    QuickFilter
End Sub

That's all you need to add.

MS Access TextBox Value is always NULL

1 Answers

First case, wait for user input to be validated by ENTER.

Second case, filter as you type