combo box problem in report

[wheat]

I have a combo box in my report that's giving me trouble. Here's the
setup:

I have a main form called frmStudent. It contains a combo box called
cboGender which has two values: male and female.

The subform has a combo box called cboShoeSize. This combo is
populated either by tblShoeSizeMen or tblShoeSizeWomen depending upon
the setting of cboGender. Each of the shoeSize* tables store 'shoeID'
and 'shoeSize' (with shoeID being the primary key and shoeSize being a
size name: S, M, L, XL, etc.)

The size info (and all the other relevant info) gets stored in a table
called tblOrder. I can get the report to display the primary key for
the shoes (shoeID), as that's what's stored in tblOrder, and it does
this correctly regarless of gender. But how can I get the shoeSize
value instead? When I try mucking with the combo box settings in the
report, specifying a row source will work for one gender but not the
other.

Very confusing. Any help is appreciated.

James

 

[Duane Hookom]

Is there any way that you can combine your two shoe size tables into one
with a column for male or female. If you can't do this, consider creating a
union query of the two tables with a derived column for gender.

 

[wheat]

Thanks for the help. I guess I've learned my lesson about using VBA to
switch row sources on combo boxes. It makes for a friendlier UI, but
it makes reporting a nightmare. What I eneed up doing was creating
some functions to parse out the IDs into their correct values and using
those in my queries. That made for a lot of work, but it fixed the
issue. Next time, I'm just keep the sizes in one table.

James