cascade combobox with one combobox in a subform

D

deb

I have a form with a subform.
The formfPerfEmissionGua) has a field called FuelClass.
(Gas,Liquid,Duel,Multi)
The subform(fPerfEmissionGuaDetails)is based off of a table called tFuel
with the fields...
FuelID -- GasLiquid -- FuelName
1 -- Gas -- Propane
2 -- Gas -- Buthane
3 -- Liquid -- JetA
4 -- Liquid -- Crude

The subform is used as a dropdown choice of the above fields. It is a
contiguous form.

I would like to be able to choose the FuelClass - Gas in the main form, and
the sub form drop down only show the fuels for Gas only (Propane and Buthane).
If the FuelClass is Multi or Dual, show all choices.


Thanks in advance
deb
 
N

NetworkTrade

your title and your description seem slightly different however I think the
key for you is that the first combobox is the criteria for a query...

and the subform (or 2nd combobox) is sourced on that query...

in the query design you need to reference that first combobox in the criteria:
Forms!FormName.Combobox

keep in mind that the combobox value is the bound column, which is not what
you visually see in many cases....

when it comes to 'dual' or 'multi' - - that is going to be dependent on how
you structure your tables and data so a search will work;
 
K

Ken Sheridan

I assume that if the FuelClass is Liquid you'd also want to restrict the
combo box to those fuels whose GasLiquid column is 'Liquid'. For the
RowSource property of the subform's combo box reference the FuelClass control
on the parent form:

SELECT FuelID, FuelName
FROM tFuel
WHERE GasLiquid = Parent.FuelClass
OR Parent.FuelClass IN("Multi", "Dual")
OR Parent.FuelClass IS NULL;

Note that you can reference the parent form by means of the Parent property;
you don't have to reference it by name. You could of course also include the
GasLiquid column in the SELECT clause if you want it to show in a
multi-column drop down list for the combo box. FuelID would normally be a
hidden bound column. I've allowed for the FuelClass in the parent form being
unspecified, i.e Null, but if that's not legitimate remove the final 'OR
Parent.FuelClass IS NULL' from the SQL statement.

In the parent form's Current event procedure, and in the AfterUpdate event
procedure of the FuelClass control on the parent form requery the subform's
combo box, referencing it via the Form property of the subform control:

Me.YourSubformControl.Form.YourComboBox.Requery

where YourSubformControl is the name of the control in the parent form which
houses the subform, not its underlying form object (unless both have the same
name of course), and YourComboBox is the name of the combo box in the subform.

On thing to be aware of here is that all rows in the subform would have to
be of the same fuel class if Gas or Liquid are selected as the parent form's
FuelClass. Selecting a class in the parent form will restrict the combo
boxes in all rows in the subform, so if for instance there are already rows
of class 'Gas' and 'Liquid' is selected in the parent form, the controls in
the subform's rows will go blank. The underlying data in the subform's table
won't change as the values will still be the hidden FuelID values, but you
won't see the corresponding names.

Ken Sheridan
Stafford, England
 
K

Ken Sheridan

I assume that if the FuelClass is Liquid you'd also want to restrict the
combo box to those fuels whose GasLiquid column is 'Liquid'. For the
RowSource property of the subform's combo box reference the FuelClass control
on the parent form:

SELECT FuelID, FuelName
FROM tFuel
WHERE GasLiquid = Parent.FuelClass
OR Parent.FuelClass IN("Multi", "Dual")
OR Parent.FuelClass IS NULL;

Note that you can reference the parent form by means of the Parent property;
you don't have to reference it by name. You could of course also include the
GasLiquid column in the SELECT clause if you want it to show in a
multi-column drop down list for the combo box. FuelID would normally be a
hidden bound column. I've allowed for the FuelClass in the parent form being
unspecified, i.e Null, but if that's not legitimate remove the final 'OR
Parent.FuelClass IS NULL' from the SQL statement.

In the parent form's Current event procedure, and in the AfterUpdate event
procedure of the FuelClass control on the parent form requery the subform's
combo box, referencing it via the Form property of the subform control:

Me.YourSubformControl.Form.YourComboBox.Requery

where YourSubformControl is the name of the control in the parent form which
houses the subform, not its underlying form object (unless both have the same
name of course), and YourComboBox is the name of the combo box in the subform.

On thing to be aware of here is that all rows in the subform would have to
be of the same fuel class if Gas or Liquid are selected as the parent form's
FuelClass. Selecting a class in the parent form will restrict the combo
boxes in all rows in the subform, so if for instance there are already rows
of class 'Gas' and 'Liquid' is selected in the parent form, the controls in
the subform's rows will go blank. The underlying data in the subform's table
won't change as the values will still be the hidden FuelID values, but you
won't see the corresponding names.

Ken Sheridan
Stafford, England
 
K

Ken Sheridan

I assume that if the FuelClass is Liquid you'd also want to restrict the
combo box to those fuels whose GasLiquid column is 'Liquid'. For the
RowSource property of the subform's combo box reference the FuelClass control
on the parent form:

SELECT FuelID, FuelName
FROM tFuel
WHERE GasLiquid = Parent.FuelClass
OR Parent.FuelClass IN("Multi", "Dual")
OR Parent.FuelClass IS NULL;

Note that you can reference the parent form by means of the Parent property;
you don't have to reference it by name. You could of course also include the
GasLiquid column in the SELECT clause if you want it to show in a
multi-column drop down list for the combo box. FuelID would normally be a
hidden bound column. I've allowed for the FuelClass in the parent form being
unspecified, i.e Null, but if that's not legitimate remove the final 'OR
Parent.FuelClass IS NULL' from the SQL statement.

In the parent form's Current event procedure, and in the AfterUpdate event
procedure of the FuelClass control on the parent form requery the subform's
combo box, referencing it via the Form property of the subform control:

Me.YourSubformControl.Form.YourComboBox.Requery

where YourSubformControl is the name of the control in the parent form which
houses the subform, not its underlying form object (unless both have the same
name of course), and YourComboBox is the name of the combo box in the subform.

On thing to be aware of here is that all rows in the subform would have to
be of the same fuel class if Gas or Liquid are selected as the parent form's
FuelClass. Selecting a class in the parent form will restrict the combo
boxes in all rows in the subform, so if for instance there are already rows
of class 'Gas' and 'Liquid' is selected in the parent form, the controls in
the subform's rows will go blank. The underlying data in the subform's table
won't change as the values will still be the hidden FuelID values, but you
won't see the corresponding names.

Ken Sheridan
Stafford, England
 
K

Ken Sheridan

I assume that if the FuelClass is Liquid you'd also want to restrict the
combo box to those fuels whose GasLiquid column is 'Liquid'. For the
RowSource property of the subform's combo box reference the FuelClass control
on the parent form:

SELECT FuelID, FuelName
FROM tFuel
WHERE GasLiquid = Parent.FuelClass
OR Parent.FuelClass IN("Multi", "Dual")
OR Parent.FuelClass IS NULL;

Note that you can reference the parent form by means of the Parent property;
you don't have to reference it by name. You could of course also include the
GasLiquid column in the SELECT clause if you want it to show in a
multi-column drop down list for the combo box. FuelID would normally be a
hidden bound column. I've allowed for the FuelClass in the parent form being
unspecified, i.e Null, but if that's not legitimate remove the final 'OR
Parent.FuelClass IS NULL' from the SQL statement.

In the parent form's Current event procedure, and in the AfterUpdate event
procedure of the FuelClass control on the parent form requery the subform's
combo box, referencing it via the Form property of the subform control:

Me.YourSubformControl.Form.YourComboBox.Requery

where YourSubformControl is the name of the control in the parent form which
houses the subform, not its underlying form object (unless both have the same
name of course), and YourComboBox is the name of the combo box in the subform.

On thing to be aware of here is that all rows in the subform would have to
be of the same fuel class if Gas or Liquid are selected as the parent form's
FuelClass. Selecting a class in the parent form will restrict the combo
boxes in all rows in the subform, so if for instance there are already rows
of class 'Gas' and 'Liquid' is selected in the parent form, the controls in
the subform's rows will go blank. The underlying data in the subform's table
won't change as the values will still be the hidden FuelID values, but you
won't see the corresponding names.

Ken Sheridan
Stafford, England
 
K

Ken Sheridan

I assume that if the FuelClass is Liquid you'd also want to restrict the
combo box to those fuels whose GasLiquid column is 'Liquid'. For the
RowSource property of the subform's combo box reference the FuelClass control
on the parent form:

SELECT FuelID, FuelName
FROM tFuel
WHERE GasLiquid = Parent.FuelClass
OR Parent.FuelClass IN("Multi", "Dual")
OR Parent.FuelClass IS NULL;

Note that you can reference the parent form by means of the Parent property;
you don't have to reference it by name. You could of course also include the
GasLiquid column in the SELECT clause if you want it to show in a
multi-column drop down list for the combo box. FuelID would normally be a
hidden bound column.

In the parent form's Current event procedure, and in the AfterUpdate event
procedure of the FuelClass control on the parent form requery the subform's
combo box, referencing it via the Form property of the subform control:

Me.YourSubformControl.Form.YourComboBox.Requery

where YourSubformControl is the name of the control in the parent form which
houses the subform, not its underlying form object (unless both have the same
name of course), and YourComboBox is the name of the combo box in the subform.

On thing to be aware of here is that all rows in the subform would have to
be of the same fuel class if Gas or Liquid are selected as the parent form's
FuelClass. Selecting a class in the parent form will restrict the combo
boxes in all rows in the subform, so if for instance there are already rows
of class 'Gas' and 'Liquid' is selected in the parent form, the controls in
the subform's rows will go blank. The underlying data in the subform's table
won't change as the values will still be the hidden FuelID values, but you
won't see the corresponding names.

Ken Sheridan
Stafford, England
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Limit data entries 2
delete fields in subform combobox after main form combobox is chan 4
cascade combobox in a subform 3
must select from combobox first 2
cascade combobox and auto populate fields. 11
Cascading ComboBox on SubForm 6
Using Combobox to Sort subform 4
multiselect listbox to filter subform combobox 3

Top