2 Combo Boxes the second requiring selection based on the first

[Guest]

I have a order details subform opened as a datasheet. The first column is
the product id that I display using a combo box that actually displays the
product name. The next column is the product size column. I only want this
to display the sizes available for the product that has been chosen in the
previous column.

I have attempted the following in the ProductID_AfterUpdate event:

Me![ProductSizeID].Filter = "ProductID = 1" -- hard code for the moment
Me![ProductSizeID].FilterOn = True
Me![ProductSizeID].Requery

This does not work - as the ProductSizeID does not support this property or
method.

Then I attempted to change the RowSource for the ProductSizeID in the
ProductID_AfterUpdate event using :


Me![ProductSizeID].RowSource = " SELECT DISTINCTROW ProductSizesLookup.*,
[ProductSizesLookup].Description, [ProductSizesLookup].ProductName,
[ProductSizesLookup].ProductSizeID " & _
" FROM ProductSizesLookup WHERE ProductID = 1 "

This also failed with Object does not support this property or method.

I have read lots of previous questions in this discussion about combo boxes
but could not understand the answers. Please answer with as much detail as
possible.

Many thanks in advance,
Annie

 

[Guest]

The methods you are using are for the Form object not a combo. You need to
set the RowSource property of the second combo from the selection in the
first combo using the AfterUpdate event of the first combo as you were doing:
eg
Me.cboProdName.Rowsource = "Select ProdID, ProdName from products WHERE
ProdID = '" &me.cboProdID &"' ORDER BY ProdName"
Me.cboProdName.Requery
HTH
Terry

 

[Guest]

Terry,

Thsi is exactly what I'm trying to do however I'm having trouble creating
the Afterupdate event. I'm not extremely familiar with working with Visual
Basic and I'm keep getting compile errors. I'm assuming the following:

cboProdName is the name of the field that holds the value selected in the
combo box and the "cbo" identifies it as such
products is the name of the table or query called in the combo box
ProdID is the name of the field in the table or query

in the expression '" &me.cboProdID &"' its single quote, double quote,
&combo field name &, double quote, single quote

I'm getting Compile error: Expected: expression at the first single quote.

What am I doing wrong?

--
Elise King-Lynch

The methods you are using are for the Form object not a combo. You need to
set the RowSource property of the second combo from the selection in the
first combo using the AfterUpdate event of the first combo as you were doing:
eg
Me.cboProdName.Rowsource = "Select ProdID, ProdName from products WHERE
ProdID = '" &me.cboProdID &"' ORDER BY ProdName"
Me.cboProdName.Requery
HTH
Terry

I have a order details subform opened as a datasheet. The first column is
the product id that I display using a combo box that actually displays the
product name. The next column is the product size column. I only want this
to display the sizes available for the product that has been chosen in the
previous column.

I have attempted the following in the ProductID_AfterUpdate event:

Me![ProductSizeID].Filter = "ProductID = 1" -- hard code for the moment
Me![ProductSizeID].FilterOn = True
Me![ProductSizeID].Requery

This does not work - as the ProductSizeID does not support this property or
method.

Then I attempted to change the RowSource for the ProductSizeID in the
ProductID_AfterUpdate event using :


Me![ProductSizeID].RowSource = " SELECT DISTINCTROW ProductSizesLookup.*,
[ProductSizesLookup].Description, [ProductSizesLookup].ProductName,
[ProductSizesLookup].ProductSizeID " & _
" FROM ProductSizesLookup WHERE ProductID = 1 "

This also failed with Object does not support this property or method.

I have read lots of previous questions in this discussion about combo boxes
but could not understand the answers. Please answer with as much detail as
possible.

Many thanks in advance,
Annie

 

[Guest]

Hello Elise
On reading my original reply the following appears on two lines but is
actually one complete line:

Me.cboProdName.Rowsource = "Select ProdID, ProdName from products WHERE
ProdID = '" &me.cboProdID &"' ORDER BY ProdName"

From your question I believe you may have interpreted this as two spearate
lines
Terry

Terry,

Thsi is exactly what I'm trying to do however I'm having trouble creating
the Afterupdate event. I'm not extremely familiar with working with Visual
Basic and I'm keep getting compile errors. I'm assuming the following:

cboProdName is the name of the field that holds the value selected in the
combo box and the "cbo" identifies it as such
products is the name of the table or query called in the combo box
ProdID is the name of the field in the table or query

in the expression '" &me.cboProdID &"' its single quote, double quote,
&combo field name &, double quote, single quote

I'm getting Compile error: Expected: expression at the first single quote.

What am I doing wrong?

--
Elise King-Lynch

The methods you are using are for the Form object not a combo. You need to
set the RowSource property of the second combo from the selection in the
first combo using the AfterUpdate event of the first combo as you were doing:
eg
Me.cboProdName.Rowsource = "Select ProdID, ProdName from products WHERE
ProdID = '" &me.cboProdID &"' ORDER BY ProdName"
Me.cboProdName.Requery
HTH
Terry

I have a order details subform opened as a datasheet. The first column is
the product id that I display using a combo box that actually displays the
product name. The next column is the product size column. I only want this
to display the sizes available for the product that has been chosen in the
previous column.

I have attempted the following in the ProductID_AfterUpdate event:

Me![ProductSizeID].Filter = "ProductID = 1" -- hard code for the moment
Me![ProductSizeID].FilterOn = True
Me![ProductSizeID].Requery

This does not work - as the ProductSizeID does not support this property or
method.

Then I attempted to change the RowSource for the ProductSizeID in the
ProductID_AfterUpdate event using :


Me![ProductSizeID].RowSource = " SELECT DISTINCTROW ProductSizesLookup.*,
[ProductSizesLookup].Description, [ProductSizesLookup].ProductName,
[ProductSizesLookup].ProductSizeID " & _
" FROM ProductSizesLookup WHERE ProductID = 1 "

This also failed with Object does not support this property or method.

I have read lots of previous questions in this discussion about combo boxes
but could not understand the answers. Please answer with as much detail as
possible.

Many thanks in advance,
Annie