Vehicle Search Form

[marc]

Hi -

Any help would be appreciated with this request...

I need to build a vehicle type search form...

I have one table with about 20,000 records...In it, there are about 60
unique fields that describe the vehicle...For example, make, model,
modelyear, enginetype, fueltype, vehicletype, etc...

I would like to be able to search by, in this order
1)vehicletype (car/truck)
2)modelyear (1973, 1996, etc)
3)vehiclemake (Chevrolet, Chrysler, etc)
4)vehiclemodel (Corvette, 300M, Camry, etc)

and then, having a subform populate (linked off of the afterupdate of the
vehiclemodel field) with all of the other info - enginetype, fueltype,
transmissiontype, etc.

I'll also mention that I've built some simpler search forms in the past,
cities-to-county search forms and stuff like that, but here I'm running into
an issue...I can explain it better by giving an example...

From that one maintable, I've built 4 smaller ones, the same titles as
listed above...In each of those 4, I've created UniqueIDs and made them
PrimaryKeys...The relationships are set as one-to-manys for
VehicleType to ModelYear
ModelYear to VehicleMake
VehicleMake to VehicleModel

The VehicleType Table has 2 records, P-Passenger, T-Truck...
The ModelYear Table has 22 records, 1973-2005
The VehicleMake Table has 88 records
The VehicleModel Table has around 2000 records

Here's the issue, when I created my comboboxes, say ModelYearcmb, I end up
with 2 of each model year in the dropdown (setting the one-to-many from
VehicleType to ModelYear...I'm getting, in essence, 1 1973 'Passenger' option
and 1 1973 'Truck' option in the dropdown, but only shown as 1973
twice)...And this, 'duplication' gradually gets worse when I introduce Make
and Model....Right now, I have over 50 1973's in my dropdown...

I have no idea what my next step would be...Maybe a different approach???

I tried to make this as detailed as possible, but if anyone could help or
needs more information, by all means, let me know...Hopefully, in a kind of,
roundabout, almost way, it makes sense...

Thanx in advance,
marc

 

[Michel Walsh]

Hi,

If you have a table with all 4 fields (type, year, maker, model), then the
first combo box can have, as row source:


SELECT DISCTINCT type FROM thatTable


The second combo box can have:


SELECT DISTINCT year FROM thatTable


and so on.


If you wish to take into account the previous combo box, you can have the
second combo box row source like:


SELECT DISTINCT year FROM thatTable WHERE
type=Nz(FORMS!formNameHere!FirstComboBoxNameHere, type)


and the third combo box:

SELECT DISTINCT maker FROM thatTable WHERE
type=Nz(FORMS!formNameHere!FirstComboBoxNameHere, type) AND
year=Nz(FORM!FormNameHere!SecondComboBoxNameHere, year)


and so on.


Hoping it may help,
Vanderghast, Access MVP