Select Range of Fields from a Table

[mroberts653]

Hello Everyone!

I have a combo box on my form where I want to select a [Size] for a piece of equipment that has a particular ID. Here's what my query statement looks like:
SELECT tblMaintenance.Size, tblMaintenance.EquipmentID FROM tblMaintenance WHERE (((tblMaintenance.Size)=[Size]));

When you click the dropdown arrow in the combo box, it shows the complete list of all Sizes for all EquipmentID's.

Any help is appreciated

 

[mroberts653]

Is anybody out there that can help with this one

 

[Bungle]

I have no idea what your problem is! Please explain what you want to do and which bit you're having trouble with.

 

[Dennisk]

Do you want is to restrict the size(s) for that particular piece of equipment?

 

[mroberts653]

Exactly! The idea is that when the user selects EquipmentID #2 from the combo box control on the form, they will only be able to select from the size range associated with Equipment ID #2 from the Size combo box. Without knowing any other way to demonstrate this, I have added some information about the tables and the way I have set them up.

tblEquipmentList contains the following records:
EquipmentID;EquipCode;Description (examples below)
1 PKGU Packaged Unit Heat/Cool Air Cooled
2 PKGU Packaged Unit Heat/Cool Water Cooled
3 PKGU Packaged Gas Driven Unit Heat/Cool
4 FURN Split System Cool Only Air Cooled
5 HPMP Heat Pump Packaged Air Cooled

tblMaintenance contains the following records:
MaintenanceID;EquipmentID;Size (examples below)
1 1 2 tons
2 1 3 tons
3 to 24 1 4 tons, etc thru 200 tons
25 2 2 tons
26 2 3 tons
27 to 48 2 4 tons, etc thru 200 tons
49 3 2 tons
50 3 3 tons
51 to 72 3 4 tons, etc thru 200 tons


tblEstimateEquipment contains the following records:
EstimateEquipmentID;MaintenanceID;EquipmentID;EstimateID (see examples)
4 8 1 5
5 480 25 6
8 4 12 5
10 679 34 7
11 8 1 8
12 173 8 5
7 8 1 11

I hope this helps explain what I'm trying to do. Ultimately, these records would be associated with the EstimateID, which is the customer. I'm really confused on this, so I hope you can help.

 

[mroberts653]

Dennisk - Here it is again using commas instead of spaces. I didn't realize that the text would all blend to gether when I sent this the first time.

Exactly! The idea is that when the user selects EquipmentID #2 from the combo box control on the form, they will only be able to select from the size range associated with Equipment ID #2 from the Size combo box. Without knowing any other way to demonstrate this, I have added some information about the tables and the way I have set them up.

tblEquipmentList contains the following records:
EquipmentID;EquipCode;Description (examples below)
1, PKGU, Packaged Unit Heat/Cool Air Cooled
2, PKGU, Packaged Unit Heat/Cool Water Cooled
3, PKGU, Packaged Gas Driven Unit Heat/Cool
4, FURN, Split System Cool Only Air Cooled
5, HPMP, Heat Pump Packaged Air Cooled

tblMaintenance contains the following records:
MaintenanceID;EquipmentID;Size (examples below)
1, 1, 2 tons
2, 1, 3 tons
3 to 24, 1, 4 tons, etc thru 200 tons
25, 2, 2 tons
26, 2, 3 tons
27 to 48, 2, 4 tons, etc thru 200 tons
49, 3, 2 tons
50, 3, 3 tons
51 to 72, 3, 4 tons, etc thru 200 tons


tblEstimateEquipment contains the following records:
EstimateEquipmentID;MaintenanceID;EquipmentID;Esti mateID (see examples)
4, 8, 1, 5
5, 480, 25, 6
8, 4, 12, 5
10, 679, 34, 7
11, 8, 1, 8
12, 173, 8, 5
7, 8, 1, 11

I hope this helps explain what I'm trying to do. Ultimately, these records would be associated with the EstimateID, which is the customer. I'm really confused on this, so I hope you can help.

 

[Dennisk]

if you replace the sql with a query and the query refers to the 1st conbo box for filterering then all you need to do is to requery the second combo box in the after update event of the first combo box

 

[Bungle]

Assuming the bound column of combo1 is EquipmentID, then your rowsource for combo box 2 is selected from tblMaintenance again using EquipmentID to filter it: so something like "SELECT Size FROM tblMaintenance WHERE EquipmentID=" & Combo1 & ";"
As Dennisk says, refresh this on the After Update Event of combo1.