Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2014
    Posts
    1

    Unanswered: Search button with text field

    Hi,

    I'm fairly new to Access / VBA and have been trying relentlessly to get a text box / search button on my Access form to pull up a specific record. Although the null command produces the correct error msgBox, it does not show any record if I enter a correct primary key term. Probably something really simple, but I am completely stuck and cannot find the solution anywhere.

    Search button = SearchButton1
    Search criteria text field = SearchText1
    Primary key field = UniqueAEVRef

    Code:

    Private Sub SearchButton1_Click()
    If IsNull(SearchField1) = False Then
    Me.Recordset.FindFirst "[UniqueAEVRef]=" & SearchField1
    Me!SearchField1 = Null
    If Me.Recordset.NoMatch Then
    MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
    Me!SearchField1 = Null
    End If
    End If
    End Sub

    Please help!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if [UniqueAEVRef is string/text (not numeric) then it must be delimited
    Me.Recordset.FindFirst "[UniqueAEVRef]='" & SearchField1 & "'"
    ..for that matter if it was a date it woudl hgave to be in either ISO (yyyy/mm/dd) or US format mm/dd/yyyy and delimited with #

    I forget how findfirst works, but you may need to reset the pointer int he current recordset BEFORE using findfirst, I have a sneaking suspiscion that findfirst looks from the current row


    just a comment on your code
    you set Me!SearchField1 = Null for each side of the if, it woudl make more sense stylistically (but not in functionally) if you did this after the endif, as its common code. the reason, well its more stylistic, but there is a risk that when revisiting code you may wonder why its done that way, or forget to change one.

    also indent your code, it makes it easier to read, just as when posting code here please use the [ c o d e ] and [ / c o d e ] tags (remove thyspaces in each [ / c o d e ] becomes [/code]

    Code:
    Private Sub SearchButton1_Click()
    If IsNull(SearchField1) = False Then
      Me.Recordset.FindFirst "[UniqueAEVRef]=" & SearchField1
      If Me.Recordset.NoMatch Then
        MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
      End If
      Me!SearchField1 = Null
    End If
    End Sub
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2014
    Posts
    4
    Hi try to change:

    Me.Recordset.FindFirst "[UniqueAEVRef]=" & SearchField1

    into:

    Me.Recordset.FindFirst "[UniqueAEVRef] Like '*" & Me!SearchField1 & "*'"

    Make sure to put the ' and the " at the end !

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •