Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jul 2002
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to Search (Access 2002)

    If I create a table with customer info in it. How can I create an easy search function to see if the customer has been in before? I can use last name and first name or phone number or all three but where and how?

    Thanks

    Rbelly

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

    Re: How to Search (Access 2002)

    You can create unbound text boxes on your form (for instance in the form header section or form footer section) to act as search boxes, and a command button to start a search.

    Here is a simple example to search for last name. It assumes that the last name field is LastName, that the unbound text box is named txtLastNameSearch and the command button is named cmdSearch.

    The OnClick code for the button could look like this:

    Private Sub cmsSearch_Click()
    Dim rst As DAO.Recordset
    Set rst = Me.RecordsetClone
    rst.FindFirst "[LastName]=" & Chr(34) & [txtLastNameSearch] & Chr(34)
    If rst.NoMatch = False Then
    If Me.Dirty Then
    RunCommand acCmdSaveRecord
    End If
    Me.Bookmark = rst.Bookmark
    End If
    End Sub

    Note: you need a reference to the Microsoft DAO ... Object Library for this (set it in Tools/References... in the Visual Basic Editor).

    If you want to search on a combination of fields, you must extend the Where-condition in rst.FindFirst.

  3. #3
    Star Lounger
    Join Date
    Jul 2002
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to Search (Access 2002)

    I created a table with LastName and FirstName and I found the form header when I opened the form I created using the wizard in design view. Where do I name the text box txtLastName Search? How do I know if it is unbound? and How do I add the command button?

    Still a very new NEWBIE.

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

    Re: How to Search (Access 2002)

    I I don't want to sound harsh, but if you don't know how to add a command button or set the name property of a control, I would suggest that you take an Access for Beginners course first, or read one of the many Access for Beginners books. Loungers are very willing to help you with specific questions, but can't teach you the basics of Access - there is simply not enough space (or time). I will give detailed instructions below, but a lot of it will probably be abacadabra to you.

    When you have opened the form in design view, make sure that the Properties window is visible (View/Properties). The Properties window has several tabs - take a few minutes to have a look at each of them.

    You will also need the Toolbox (View/Toolbox). You can use the Toolbox to add controls to the form. If you hover the mouse over the buttons on the Toolbox, you'll see a description. Take a few minutes to see what kind of controls you can add.

    A bound control is a control that has its Control Source property set to the name of a field of the record source (table or query) of the form; an unbound control has an empty Control Source property. (There is a third kind: a calculated control has a formula starting with = in its Control Source property)

    To add a text box to the form, use the ab button. A text box created with the Toolbox is unbound by default. With the new text box selected, click the fourth tab of the Properties window. The first property in this tab is the Name. Set this to txtLastNameSearch (without spaces).

    Next, use the Toolbox to add a command button. Possibly, the Command Button Wizard starts as soon as you place the button on the form. If so, cancel it immediately. Set the name of the command button to cmdSearch in the Properties window. Next, click the first tab of the properties window and set the Caption property. Then, click the Events tab, select the On Click event, select Event Procedure from the dropdown list, and click the Builder button (the ... to the right). You will be taken to the Visual Basic Editor, with the form module opened. Access will already have created the skeleton of the event procedure for you; it should look like this:

    Private Sub cmdSearch_Click()

    End Sub

    with the insertion point (the blinking vertical line) in the empty line between. Enter the code given in my first reply here. To make it work, you need to set a reference to DAO. Select Tools/References..., and see if Microsoft DAO ... Object Library is checked. If not, locate it in the list and check the box to the left of it. Then click OK.

    Now you can go back to Access. Close and save the form, then open it in form view to test it. Type a name in the text box, then click the command button.

  5. #5
    Star Lounger
    Join Date
    Jul 2002
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to Search (Access 2002)

    Not being harsh! I am reading a few books and trying to get the hang of it.

    Thanks for your help and when I get stuck I will ask for help!

    Rbelly

Posting Permissions

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