How is this returning a blank value?

Question

So this code is meant to serve as a simple search system to go to certain records in a recordset. I originally had it so they had to click the btnGoToID button in order to perform the search. I decided to just make it a little more user friendly and make it so the search field listened for the Enter button and that would perform the search as well.

The issue that I am running into when the code gets to strID = Trim(Nz(Me.txtSearch.Value, "")) the value will randomly come back as an empty string even though visually I can see that there is a value in the textbox.

I haven't been able to narrow down any pattern for when this issue occurs. At this point I don't really even know how to troubleshoot this problem, nor the words to search for that could yield any results in Google. All I can say is that it SEEMS like changing between records will affect whether or not the search goes through.

This has always worked up until I put in the txtSearch_KeyPress sub procedure.

'============================================================================
' txtSearch_KeyPress
'============================================================================
Private Sub txtSearch_KeyPress(KeyAscii As Integer)
    'If user pressed enter
    If KeyAscii = 13 Then
        Call btnGoToID_Click
    End If
End Sub



'============================================================================
' btnGoToID_Click
'============================================================================
' <<Purpose>>
' Allow the user to search for a specific ID 
'============================================================================
Private Sub btnGoToID_Click()
On Error GoTo Err_Handler

    Dim rs As Recordset
    Dim strID As String

    Set rs = Me.RecordsetClone

    strID = Trim(Nz(Me.txtSearch.Value, ""))

    If (strID <> "") Then

        'Go to the ID
        rs.FindFirst "ID = '" & strID & "'"

        If rs.NoMatch Then
            MsgBox "ID does not exist"
        Else

            'If we have a match, set the record as the current record
            Me.Bookmark = rs.Bookmark
        End If
    Else
        MsgBox "Please enter a valid ID.", vbOKOnly, "Invalid ID"
    End If


Exit_Handler:
On Error Resume Next

    Me.txtSearch.Value = ""
    rs.Close
    Set rs = Nothing
    Exit Sub

Err_Handler:
    Call LogError(Err.Number, Err.Description, "txtSearch on " & Me.Name)
    Resume Exit_Handler
End Sub

After the conversation in the comments I have narrowed down this question to be a whole lot simpler. This yields an "Invalid use of null" error message even after there are several characters in the text field. Why would that happen, and what can I do to make it pick up the values in the textbox?

'============================================================================
' txtUnitNoToSearch_KeyPress
'============================================================================
Private Sub txtUnitNoToSearch_KeyPress(KeyAscii As Integer)
    MsgBox Me.txtUnitNoToSearch
End Sub

Answer 1

I think your problem is related to the fact that a text box has 2 properties, .Value and .Text, which appear similar but behave differently.

While you have an edit in progress in the text box, the changing content is available via the .Text property. However, the content of the .Value property has not yet been updated to match.

After the text box's After Update event, .Value will contain the new content. And if focus has moved away from the text box, its .Text property will no longer even be available.

Sorry, I couldn't think how to explain that better. But I think the situation will be clearer with this KeyPress event procedure:

Private Sub txtUnitNoToSearch_KeyPress(KeyAscii As Integer)
    Debug.Print "Text: '" & Me.txtUnitNoToSearch.Text & "'"
    Debug.Print "Value: '" & Me.txtUnitNoToSearch.Value & "'"
End Sub

Watch the output from Debug.Print in the Immediate window as you make changes to the context of the text box. (Ctrl+g will open the Immediate window.)

One final point is that just Me.txtUnitNoToSearch gets you its default property which is .Value. Keep that in mind as you work through the rest of your code.