Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2004
    Posts
    48

    Unanswered: Search Box in Access Database

    In an Access database, I have a form that, after the user performs a search, displays the name of an employee, the parking tag #(s) that have been assigned to them and their phone extension. I'm using a search box (a combo box) on that form where the user can search using the Tag # to get the results mentioned above. A query showing all the employees and their related Parking Tag numbers is the data source used in the combo box. The combo box also has the following code in an After Update event procedure:

    Sub Combo3_AfterUpdate()
    ' Find the record that matches the control.
    Me.RecordsetClone.FindFirst "[TagInventory#] = '" & Me![Combo3] & "'"
    Me.Bookmark = Me.RecordsetClone.Bookmark
    End Sub]


    This works well when the user searches by the Tag number.

    I'm trying to create a similar search box on another form but in this case the user can search by name. The above code doesn't work where an employee has 2 Parking Tags assigned to them. How do I adapt the code or what other procedure can I use so that all the records are displayed when an employee has more than 1 Tag# assigned to them. What can be used in place of FindFirst or FindLast?

    As always, thanks in advance for any assistance.

  2. #2
    Join Date
    Aug 2006
    Posts
    559
    I've done searches by first and / or last name but had a textbox that the user would search by entering any portion of the name. It required an "If , Then" statement.

    I've seen it done with multiple search criteria. So you'd have to use an "If, Then" for the first and last name and I believe an "else" for searching by the tag number.

    if I can find the example, I'll upload it but I think it's on the machine at the house.

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Knowing the table structure would really help.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    *Agrees with StarTrekker*
    How are the parking tags assigned to employees?

  5. #5
    Join Date
    Aug 2004
    Posts
    48
    StarTrekker and weejas:
    The TagInventory table consists of a TagID# (autonumber), TagColor, TagStatus, DateIssued and an EmployeeID. The form the user uses displays the above fields. The user finds a Tag# where the status is "unassigned" and enters the EmployeeID in the EmployeeID field on the form.

    Does this help?

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Ok, it helps a little... you haven't revealed the structure of the Employee table though, the underlying query for the form or the key properties of the combo box.

    I'll make some assumptions, but you'll have to adapt it to your situation.

    Assumptions:
    - the Employee table is included in the underlying SQL for the form.
    - EmployeeName is a field visible somewhere on the form.
    - the form's Record Source is "qryTagDetails".
    - the combo box is called NameSearchCombo and has the text field EmployeeName as its bound column.

    Code:
    Sub NameSearchCombo_AfterUpdate()
       ' Find the records that matches the control, based on EmployeeName.
       Me.RecordSource = "SELECT * FROM qryTagDetails WHERE EmployeeName Like """ & Nz(Me.NameSearchCombo,"*") & """"
    End Sub
    That should show only tags for the name selected in the combo box.

    Hope that helps....
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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