Show matching records as new record is added

[Guest]

Hi

background info:
I have Drivers table (PK DriverID)
Vehicles table (PK VRM)
VehiclesDrivers table (PK VRM and DriverID)
I have a main form, Vehicles with a subform showing drivers associated with
the vehicle (from Vehicles Drivers table.)
I need to ensure that it is not possible for the user to add a "new" driver
that already exists in the database (perhaps who is assigned already to
another vehicle. The user may not be aware of this.)

Questions:
1) When I add a new driver to the vehicles drivers table (4 fields), how can
I check the drivers table to see the data entered in all four fields already
matches a record in the drivers table, before this driver is saved (both to
the Drivers table and the VehiclesDrivers table?) The 4 fields are First
Names, Surname, Date of Birth (DOB) and Sex.

2) I would also like to include a way of automatically looking up any
matching records as the user inputs data. I'd like to search by Surname,
First Names, DOB and Sex, in that order. In other words, as soon as the user
starts inputting data, a list of records from the drivers table that match
the entry so far are displayed, which the driver can just click on to
auto-populate the fields if the driver they're inputting already exists.
Combo boxes do this... but is it possible to have the user enter into a
textbox not directly into a combo, and have the combo (or other control)
appear at a different place on the form (maybe 1cm below the textbox) but
still displying "live" matching records, updating as the user types? The
reason I need this is that if a user just types directly into the combo
starting with surname, and enters "Smith", the combo straight away lists all
the "Smiths" in the table... there might be hundreds and it would take a
while to manually scroll through to see if the driver already exists. And if
the "Smith" in question doesn't exist, if the user tabs from the combo where
Smith has just been entered (ie the Surname "field") to move to the First
Names textbox, the record in the combo is selected - which is not what is
wanted!

So I need to search on multiple fields . Is a combo the right control to use
for this? It only searches one field (right?) I want to make this search
user friendly ie not using the access Filter Button at the top so it's all
automated . Can you help me?

TIA
rich

 

[Allen Browne]

One approach to your filtering question would be to put 4 unbound controls
at the top of your form (Form_Header section?) and 2 command buttons (one
for Filter, and one for Remove Filter.) The user can enter whatever they
want into any box, and click the button to limit the form to matching
entries.

If that sounds okay, the idea is that your cmdFilter will build up a filter
string from whichever boxes the user used. This kind of thing:

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.cboFilterFirstName) Then
strWhere = strWhere & "([FirstName] = """ & _
Me.cboFilterFirstName & """) AND "
End If

If Not IsNull(Me.cboFilterSurname) Then
strWhere = strWhere & "([Surname] = """ & _
Me.cboFilterSurame & """) AND "
End If

If Not IsNull(Me.txtFilterDOB) Then
strWhere = strWhere & "([DOB] = " & _
Format(Me.txtFilterDOB, "\#mm\/dd\/yyyy\#") & ") AND "
End If

If Not IsNull(Me.cboFilterSex) Then
strWhere = strWhere & "([Sex] = """ & _
Me.cboFilterSex & """) AND "
End If

lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lngLen <= 0 Then
MsgBox "Enter some criteria."
Else
If Me.Dirty Then Me.Dirty = False 'Save First.
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
End If
End Sub