using 2 combo boxes to filter another

krop

New member
Local time
Today, 04:20
Joined
Sep 11, 2003
Messages
9
I have 3 combo boxes and I would like them to interact as follows:

Equipment Type - (e.g. PC, laptop)
Regular Drop Down

Supplier - (e.g. Dell, Toshiba)
Shows only suppliers of selected Equipment Type

Model - (e.g. GX270, 2410-703)
Shows only models of selected Supplier AND Equipment Type

I have got the suppliers' dropdown limited to equipment type and the model dropdown limited to supplier (see code below).

select dbo_model.id, dbo_model.supplier_id, dbo_model.equipment_type_id, dbo_model.model
from dbo_model
where (((dbo_model.supplier_id) in (select supplier_id from dbo_hardware)));

But as Dell (as e.g.) make more than one equip_type I need the model dropdown to be filtered by both the supplier and equipment_type.

select dbo_model.id, dbo_model.supplier_id, dbo_model.equipment_type_id, dbo_model.model
from dbo_model
where (((dbo_model.supplier_id) in (select supplier_id from dbo_hardware))) AND (((dbo_model.equipment_id) in (select equipment_id from dbo_hardware)));

When I use the code above, access crashes when I try to enter Form View giving an error with my "msjet40.dll" I have all the availble service packs from M$ and the latest version of Jet.

Is it a coding error, and access error or an OS error?

Hope you can help!
 
bad post sorry, cant seem to delete it. Thus this message....
 
I'm having pretty much the same problem, but only with two combo boxes.

I am hoping that your code above, Krop, will help me. If it does, then I thank you. I'll let you know.

This looks like a superb board by the way. Nice work.
Blum
 
I've managed to solve it! It just seems that SQL doesn't like 2 "in" statements followed by subqueries.

Here's the code that works:

select dbo_model.id, dbo_model.supplier_id, dbo_model.equipment_type_id, dbo_model.model
from dbo_model, dbo_hardware
where (((dbo_model.supplier_id)=[dbo_hardware].[supplier_id]) and ((dbo_model.equipment_type_id)=[dbo_hardware].[equipment_type_id]));
 
Nice.

Gonna try to implement similar things into my application.
Same kind of scenario to yours really. We deal in phone systems and I want to limit options for models of Siemens, Samsung and NEC phone systems (initially).

Cheers,
Blum
 
Well, there's been loads of examples on Cascading Comboboxes to search through on this forum. :cool:
 
Mile-O-Phile said:
Well, there's been loads of examples on Cascading Comboboxes to search through on this forum. :cool:

HAHA, that's great, but I didn't know they were called cascading comboboxes :)

I searched for several things, but since this was on the first page I stuck with it
Blum
 

Users who are viewing this thread

Back
Top Bottom