Dynamically populating combo-boxes

[Ralph Schwehr]

I have a subform with 3 combo-boxes. The second combo-box populates based on
the selection of the first one by setting the rowsource to:
SELECT Res_Sales_Table.ID, Res_Sales_Table.Page
FROM Res_Sales_Table
WHERE
(((Res_Sales_Table.Book)=[Forms]![frmCompSalesApproach].[Form]![frmCompSalesApproach_Subform1]![cmbBook].[Text]));

However, when trying to populate the third combo-box based on the content of
the first and the second nothing will populate the 3rd combo-box. Here is the
rowsource of the 3rd combo:
SELECT Res_Sales_Table.ID, Res_Sales_Table.Parcel
FROM Res_Sales_Table
WHERE
(((Res_Sales_Table.Book)=[Forms]![frmCompSalesApproach].[Form]![frmCompSalesApproach_Subform1]![cmbBook].[Text])
AND
((Res_Sales_Table.Page)=[Forms]![frmCompSalesApproach].[Form]![frmCompSalesApproach_Subform1]![cmbPage].[Text]));

Any ideas what could cause this?

 

[Ken Sheridan]

You'll need to requery the third combo box in the AfterUpdate event
procedures of both the first and second combo boxes as you cannot be
absolutely certain of the order in which the user will select them. Even
though it would seem logical that the book be selected first, a user could
find on selecting the page in the second that they'd inadvertently selected
the wrong book in the first and then go back to it.

Me.YourThirdComboBox.Requery

As these three combo boxes are all on the same subform you should be able to
simplify your references to the controls considerably in the RowSource
properties, e.g.

WHERE Book = Form!cmbBook.Text AND Page =Form!cmbPage.Text;

The Form property here returns a reference to the form object in which the
SQL statement is called, i.e. the subform, so you don't need the explicit
fully qualified reference. Also as the FROM clause includes only one table
you don't need to qualify the column names with the table name.

 

[Ralph Schwehr]

Ken,
thank you for your response. I had requeried the third combo-box on the
After Update event of the first two combo-boxes, prior to your suggestion.
That didn't seem to be the issue.
I have simplified the references to the controls of the subform and, still,
get the same behavior.
It is very odd that one of the combo-boxes populates but the not the other.
--
Ralph Schwehr

You'll need to requery the third combo box in the AfterUpdate event
procedures of both the first and second combo boxes as you cannot be
absolutely certain of the order in which the user will select them. Even
though it would seem logical that the book be selected first, a user could
find on selecting the page in the second that they'd inadvertently selected
the wrong book in the first and then go back to it.

Me.YourThirdComboBox.Requery

As these three combo boxes are all on the same subform you should be able to
simplify your references to the controls considerably in the RowSource
properties, e.g.

WHERE Book = Form!cmbBook.Text AND Page =Form!cmbPage.Text;

The Form property here returns a reference to the form object in which the
SQL statement is called, i.e. the subform, so you don't need the explicit
fully qualified reference. Also as the FROM clause includes only one table
you don't need to qualify the column names with the table name.

I have a subform with 3 combo-boxes. The second combo-box populates based on
the selection of the first one by setting the rowsource to:
SELECT Res_Sales_Table.ID, Res_Sales_Table.Page
FROM Res_Sales_Table
WHERE
(((Res_Sales_Table.Book)=[Forms]![frmCompSalesApproach].[Form]![frmCompSalesApproach_Subform1]![cmbBook].[Text]));

However, when trying to populate the third combo-box based on the content of
the first and the second nothing will populate the 3rd combo-box. Here is the
rowsource of the 3rd combo:
SELECT Res_Sales_Table.ID, Res_Sales_Table.Parcel
FROM Res_Sales_Table
WHERE
(((Res_Sales_Table.Book)=[Forms]![frmCompSalesApproach].[Form]![frmCompSalesApproach_Subform1]![cmbBook].[Text])
AND
((Res_Sales_Table.Page)=[Forms]![frmCompSalesApproach].[Form]![frmCompSalesApproach_Subform1]![cmbPage].[Text]));

Any ideas what could cause this?

 

[Ralph Schwehr]

Ken,
after doing some 'tinkering' I came up with a solution that finally works
and I thought I share it with you:

SELECT ID, Parcel
FROM Res_Sales_Table
WHERE (((Book)=(SELECT Book FROM [Res_Sales_Table] WHERE [ID]=[Form]![ID]))
AND ((Page)=(SELECT Page FROM [Res_Sales_Table] WHERE [ID]=[Form]![ID])));

It wasn't the way I would normally think this should work but it got the job
done.

Thanks again for your response!

--
Ralph Schwehr

You'll need to requery the third combo box in the AfterUpdate event
procedures of both the first and second combo boxes as you cannot be
absolutely certain of the order in which the user will select them. Even
though it would seem logical that the book be selected first, a user could
find on selecting the page in the second that they'd inadvertently selected
the wrong book in the first and then go back to it.

Me.YourThirdComboBox.Requery

As these three combo boxes are all on the same subform you should be able to
simplify your references to the controls considerably in the RowSource
properties, e.g.

WHERE Book = Form!cmbBook.Text AND Page =Form!cmbPage.Text;

The Form property here returns a reference to the form object in which the
SQL statement is called, i.e. the subform, so you don't need the explicit
fully qualified reference. Also as the FROM clause includes only one table
you don't need to qualify the column names with the table name.

I have a subform with 3 combo-boxes. The second combo-box populates based on
the selection of the first one by setting the rowsource to:
SELECT Res_Sales_Table.ID, Res_Sales_Table.Page
FROM Res_Sales_Table
WHERE
(((Res_Sales_Table.Book)=[Forms]![frmCompSalesApproach].[Form]![frmCompSalesApproach_Subform1]![cmbBook].[Text]));

However, when trying to populate the third combo-box based on the content of
the first and the second nothing will populate the 3rd combo-box. Here is the
rowsource of the 3rd combo:
SELECT Res_Sales_Table.ID, Res_Sales_Table.Parcel
FROM Res_Sales_Table
WHERE
(((Res_Sales_Table.Book)=[Forms]![frmCompSalesApproach].[Form]![frmCompSalesApproach_Subform1]![cmbBook].[Text])
AND
((Res_Sales_Table.Page)=[Forms]![frmCompSalesApproach].[Form]![frmCompSalesApproach_Subform1]![cmbPage].[Text]));

Any ideas what could cause this?