How does this return null?
So this code is for a simple search engine to navigate to specific records in a recordset. I originally had this, so they needed to hit the btnGoToID button to do the search. I decided to make it a little more user-friendly and have the "Enter" button heard in the search field and that would do the search as well.
The problem I am having when the code gets the value strID = Trim(Nz(Me.txtSearch.Value, ""))
will randomly return as an empty string, although visually I can see there is a value in the textbox.
I have not been able to narrow down any pattern when this problem occurs. At the moment I don't even know how to fix this issue, no search words that can lead to any results on Google. All I can say is that it WATCHES how a change between entries will affect whether or not a search passes.
This always worked until I included the txtSearch_KeyPress subroutine.
'============================================================================
' 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 talking in the comments, I narrowed it down a lot easier. This gives the error "Invalid use of null" even after there are multiple characters in the text field. Why does this happen, and what can I do to get it to get values ββin the textbox?
'============================================================================
' txtUnitNoToSearch_KeyPress
'============================================================================
Private Sub txtUnitNoToSearch_KeyPress(KeyAscii As Integer)
MsgBox Me.txtUnitNoToSearch
End Sub
source to share
I think that your problem is related to the fact that the text field has 2 properties, .Value
and .Text
, which look the same, but behave differently.
While editing is in progress in the text box, the changing content is available through the property .Text
. However, the content of the property .Value
has not yet been updated to match.
After the text box After the event, the refresh .Value
will contain the new content. And if the focus has moved away from the textbox, its property .Text
will no longer be available.
Sorry, I couldn't think of a better way to explain this. 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 out Debug.Print
for the Immediate window when you make changes to the text box context. ( Ctrl+ gwill open the Immediate window.)
The end point is that only Me.txtUnitNoToSearch
gets your default property, which is .Value
. Keep this in mind as you work with the rest of your code.
source to share