Searching and listing multiple records on a form with combo box

[Guest]

Hi there,

I have a table that keeps case records and one of the columns store the
vehicle no. It is possible that the same vehicle will appear in different
case records.

CaseID | Vehicle No
-------------------------
01 | Vehicle1
02 | Vehicle2
03 | Vehicle1

In a Form, I have a combo box to list only the Vehicle No.
SELECT DISTINCT Case.VehicleNo FROM [Case] ORDER BY Case.VehicleNo;

I have set the Default View of the form to be Continuous Form. This way, I
can list all of the relevant cases.

Then, in the on-click event, I have the following code:
Dim sVehicleNo
With VehicleSearch
If .ListIndex <> -1 Then
sVehicleNo = .SelText
'Retrieves the value of the bound column which may more may not be
'displayed in the list box
sVehicleNo = .Column(.BoundColumn - 1)
Filter = "VehicleNo=" & sVehicleNo
FilterOn = True
MsgBox "Filter Applied"
End If
End With

The problem I am having is that once I have chosen the Vehicle no using the
combo box, there will definitely be a pop-up, showing the vehicle no that I
have chosen and require me to key in the vehicle no again. If I leave it
blank, then the form will not list the case records. If I do, it will then
list them.

What needs to be changed so that all I need to do is choose the vehicle no
from the combo box and the form lists the cases below it, without having to
re-key-in the vehicle no again?

 

[Guest]

Kelvin,

Try using the After Update event of your combo box.

Use code like:

Dim sVehicleNo as long
'get the selectd value from the combo box
if not isnull(me.VehicleSeardh) Then
sVehicleNo = me.VehicleSearch
Me.filter = "VehicleNo=" & sVehicleNo
MsgBox "Filter Applied"
Me.FilterOn = true

--
HTH

Mr B
email if needed to:
draccess at askdoctoraccess dot com