none
Searching a form where the primary key is a text value RRS feed

  • Question

  • I have a problem with a search function on a form, where I am searching the form where the primary key is a text value: -

    Option Compare Database
    Option Explicit
    
    
    Private Sub CmdCustomerSearch_Click()
    Dim strsearch As String '
    Dim strText As String
    strText = Me.TxtSearch.Value
    strsearch = "SELECT * from tblCustomers where ((LastName like ""*" & strText & "*"") or (GivenName like ""*" & strText & "*""))"
    Me.RecordSource = strsearch
    End Sub
    I receive a Run-time error '94:

    Invalid use of Null

    The debugging code occurs at line:-

    strText = Me.TxtSearch.Value

    Can you kindly assist me with this issue.

    Kind regards

    Tuesday, February 16, 2016 3:54 PM

Answers

  • Hi. Try changing it to this:

    strText = Nz(Me.TxtSearch,"")

    Hope that helps...

    • Marked as answer by wirejp Tuesday, February 16, 2016 4:34 PM
    Tuesday, February 16, 2016 4:00 PM
  • A text box Value is Null until the field is committed to the record. Try using the .Text property instead. In order to get the .Text string you have to first set focus on the control.

    Me.TxtSearch.SetFocus
    strText = Me.TxtSearch.Text
    


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    • Marked as answer by wirejp Tuesday, February 16, 2016 4:34 PM
    Tuesday, February 16, 2016 4:11 PM

All replies

  • Hi. Try changing it to this:

    strText = Nz(Me.TxtSearch,"")

    Hope that helps...

    • Marked as answer by wirejp Tuesday, February 16, 2016 4:34 PM
    Tuesday, February 16, 2016 4:00 PM
  • A text box Value is Null until the field is committed to the record. Try using the .Text property instead. In order to get the .Text string you have to first set focus on the control.

    Me.TxtSearch.SetFocus
    strText = Me.TxtSearch.Text
    


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    • Marked as answer by wirejp Tuesday, February 16, 2016 4:34 PM
    Tuesday, February 16, 2016 4:11 PM
  • Thank you for quick responses and for answering my question.
    Tuesday, February 16, 2016 6:12 PM
  • Hi. You're welcome! Bill and I were happy to assist. Good luck with your project.
    Tuesday, February 16, 2016 6:32 PM