Null value in search

[Opid]

Hello,

Private Sub searchTXT_KeyDown(KeyCode As Integer, Shift As Integer)
Dim rs As Object
Dim lngBookmark As String
Select Case KeyCode
Case vbKeyReturn
lngBookmark = searchTXT.Value
DoCmd.OpenForm "updatedformTenant"


Set rs = Forms!updatedformTenant.RecordsetClone
rs.FindFirst "Last1 = '" & lngBookmark & "'"


If rs.NoMatch Then
  DoCmd.GoToRecord acForm, "updatedformTenant", acNewRec    
Else
  Forms!updatedformTenant.Bookmark = rs.Bookmark
End If
Set rs = Nothing
KeyCode = 0
End Select
End Sub

I am attempting to run this code when the user hits the enter key. For some reason lngBookMark = serachTXT.Value is giving me an Improper use of NULL exception. Any ideas?

[pbaldy]

You probably want the after update event. The key down event fires with every keystroke, so with the first one there's no value in the textbox yet.

[Opid]

The code works perfectly fine in an on-click of a button. I was hoping to make it so the user wouldn't have to click on the button... Are you not able to access values in form fields in a KeyDown event?

[Opid]

Thank you that works perfectly. Put the code into after update and removed the switch case statement. Thank you so much.

Private Sub searchTXT_AfterUpdate()
Dim rs As Object
Dim lngBookmark As String
lngBookmark = searchTXT
DoCmd.OpenForm "updatedformTenant"
'take it to the selected record
Set rs = Forms!updatedformTenant.RecordsetClone
rs.FindFirst "Last1 = '" & lngBookmark & "'"


'the lines marked as optional can be included if there's a chance the record won't exist in the form being opened
If rs.NoMatch Then   'optional - if no match, go to a new record
  DoCmd.GoToRecord acForm, "updatedformTenant", acNewRec   'optional
  'Forms!frmEmployeesDetail.txtEmpID = Me.txtEmpID  'optional - copy the employee ID from this form
Else   'optional
  Forms!updatedformTenant.Bookmark = rs.Bookmark
End If   'optional
Set rs = Nothing
KeyCode = 0
End Sub

[pbaldy]

No problem. That code looks hauntingly familiar.

[Opid]

Haha yeah you helped me out twice then

[Opid]

Found a little bug if the user erases whats in the the text fields and clicks out of the text field it throws a null exception again so add a null check:

Private Sub searchTXT_AfterUpdate()
Dim rs As Object
Dim lngBookmark As String
'Check if the user has something in text field
If (Not Nz(searchTXT, "") = "") Then
lngBookmark = searchTXT
DoCmd.OpenForm "updatedformTenant"
'take it to the selected record
Set rs = Forms!updatedformTenant.RecordsetClone
rs.FindFirst "Last1 = '" & lngBookmark & "'"


'the lines marked as optional can be included if there's a chance the record won't exist in the form being opened
If rs.NoMatch Then
  rs.FindFirst "First1 = '" & lngBookmark & "'"
  If rs.NoMatch Then
  DoCmd.Close
  MsgBox "No tenant with that name!"
  Else
  Forms!updatedformTenant.Bookmark = rs.Bookmark
  End If
Else
Forms!updatedformTenant.Bookmark = rs.Bookmark
End If
Set rs = Nothing
KeyCode = 0
End If
End Sub

also thanks to pbaldy for original code