Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Jan 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Search boxes on forms (Access 2003)

    How do you create a search box on a field on a form? I want it to search for all or part of a word e.g a company name on an contacts database. A list or combo box is no use in this instance as there will be large volumes of data to search through.

    Thank you.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Search boxes on forms (Access 2003)

    You could use a text box, say txtSearch, and use the On Change event to search as the user types. Here is code to search in a field CompanyName for a match on the start of the name.

    Private Sub txtSearch_Change()
    Dim rst As DAO.Recordset
    Dim strSearch As String
    On Error GoTo Err_Sub
    If Me.txtSearch.Text = "" Then
    strSearch = "[CompanyName] is Null"
    Else
    strSearch = "[CompanyName] LIKE " & _
    Chr(34) & Me.txtSearch.Text & "*" & Chr(34)
    End If
    Set rst = Me.RecordsetClone
    rst.FindFirst strSearch
    If rst.NoMatch Then
    Beep
    Else
    Me.Bookmark = rst.Bookmark
    Me.txtSearch.SelStart = Len(Me.txtSearch.Text)
    End If

    Exit_Sub:
    Set rst = Nothing
    Exit Sub

    Err_Sub:
    MsgBox Err.Description, vbExclamation
    Resume Exit_Sub
    End Sub

    If you want to match any part of the name, use

    strSearch = "[CompanyName] LIKE " & _
    Chr(34) & "*" & Me.txtSearch.Text & "*" & Chr(34)

  3. #3
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search boxes on forms (Access 2003)

    Instead of going to a record on a form, how would I make this code go to that record in a list box?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Search boxes on forms (Access 2003)

    You could use a textbox and a combobox. In the AfterUpdate event of the text box, rebuild the rowsource of the combobox. This would allow for use of wildcards by user. The code might look something like this:

    cboFind.rowsource = "SELECT CustNo, CustName from tblCustomers WHERE CustName Like " & chr(34) & txtFind & "*" & chr(34) & " ORDER BY CustName"
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Search boxes on forms (Access 2003)

    You'd have to loop through the items in the list box and compare the relevant column to the search text. If you find a match, highlight the item and exit the loop.

  6. #6
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search boxes on forms (Access 2003)

    Ok, so I think I'm close, maybe not, but I think I am....I have a text box above my list box. My text box name (for now) is text59. My list box is populated from a query. I have a field I want to filter, it is called zapp_code. I am trying to filter the list by what is inputted in text59. in the on change event of the text box, I have the code :
    me.list53.requery
    for the query criteria, under the field zapp_code, I have:
    iif(isnull([forms]![frms1]![text59]),[zapp_code],([tblpersonnel].[zapp_code]) like " & chr(34) & [forms]![frms1]![text59] & "*" & chr(34) & ")

    What is wrong with what I'm doing? A combo box is not practical here, and I cannot sort zapp code ascending.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Search boxes on forms (Access 2003)

    In <post#=476441>post 476441</post#> higher up in this thread, you asked "Instead of going to a record on a form, how would I make this code go to that record in a list box?". Now you seem to want to filter the list box instead of going to a specific record in a list box. I don't think using the On Change event of a text box is suitable for filtering a list box - the list box has to be requeried for each character the user types or deletes - but you haven't told us what the problem is:
    - Do you get an error message? If so, what does it say?
    - Does the code do something different from what you expected? If so, in what way?
    - Does the code do nothing at all?
    And what do you mean by "I cannot sort zapp code ascending"?

    Please help us to help you by providing relevant and specific information.

  8. #8
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search boxes on forms (Access 2003)

    Hans, please disregard my statement "I cannot sort by zapp code", I was referring to something I needn't be. My problem is, nothing happens. Nothing at all. [img]/forums/images/smilies/sad.gif[/img]
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Search boxes on forms (Access 2003)

    Try changing the criteria for the zapp_code field to

    Like [Forms]![frms1]![text59] & "*"

Posting Permissions

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