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

Topic

New Reply

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.

Reply | Quote

Replies

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 = “[RegistrationNumber]=” & Me.txtSearch
ElseIf IsDate(Me.txtSearch) Then
strWhere = “[DateOfBirth]=#” & Format(Me.txtSearch, “mm/dd/yyyy”) & “#”
Else
strWhere = “[Surname]=” & 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.

Reply | Quote

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

Reply | Quote