Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Providing a front-end search for records (2002 SP3)

    I feel like such a dummy - I'm assuming the following request is basic Access functionality but cannot get my head around the easiest approach

    Our call centre needs to look up individual's records by entering one of Surname, date of Birth or registration number.

    I thought it would be clever to allow them to type a single field into a 'Search' form, test for date and numeric and then fire up the resulting record
    I'd assumed if there were more than one record that a dropdown would let them narrow down the search and display the individual form.

    All my reading of unhelp has simply made me confused about what objects I'm trying to manipulate.

    So - overview question - is the above approach OK, or can someone recommend an alternate?
    and then - how do I make it work.

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

    Re: Providing a front-end search for records (2002 SP3)

    The easiest way is to use three separate combo boxes - one for surname, one for birth date and one for registration number on a form bound to the table/query. The Combo Box Wizard can be used to create the combo boxes, it will take care of all the details.

    It is quite possible to use one text box on an unbound form for all three fields too, if you prefer, but you'll have to write the code yourself. it would look like this:

    Private Sub txtSearch_AfterUpdate()
    Dim strWhere As String
    If IsNull(Me.txtSearch) Then
    Exit Sub
    End If
    If IsNumeric(Me.txtSearch) Then
    strWhere = "<!t>[RegistrationNumber]<!/t>=" & Me.txtSearch
    ElseIf IsDate(Me.txtSearch) Then
    strWhere = "<!t>[DateOfBirth]<!/t>=#" & Format(Me.txtSearch, "mm/dd/yyyy") & "#"
    Else
    strWhere = "<!t>[Surname]<!/t>=" & Chr(34) & Me.txtSearch & Chr(34)
    End If
    DoCmd.OpenForm "frmContacts", , , strWhere
    End Sub

    Here, txtSearch is the name of the search text box, RegistrationNumber, DateOfBirth and SurName are the names of the fields to be searched, and frmContacts is the name of the form to be opened.

  3. #3
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Providing a front-end search for records (2002 SP3)

    Hans
    Has always - thanks very much. Again - your expertise shines through.
    Andrew

Posting Permissions

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