Combo box dependent on another combo box, text part is blank

[lorirobn]

Hi,

I have a form with several combo boxes, continuous form format, with
record source a query off an Item Table. The fields are Category,
Subcategory, and Color. I am displaying descriptions, not ID's, so I
have to convert the ID's from various lookup tables.

The combo boxes work fine except for subcategory, which is dependent on
category. Depending on category, the drop-down box for subcategory
will display different items. (for example, when Category is
Furniture, Subcategory will display drop-down list of 'Dresser,
Nightstand, Desk...". Without clicking on the drop-down box,
subcategory will display in its text area the Description of the
subcategory ID of the existing record on the table.

The drop-down list for Subcategory works fine. However, the text
portion is blank for records on the 2nd category. In other words,
there are 10 records for the first category, Accessories. Subcategory
displays fine for these. But for the 11th record and subsequent
records, subcategory is blank (text portion, although drop-down works
correctly).

If I just display subcategory ID, and not convert to description, it
works fine.

My SQL statement for subcategory is:
"SELECT ItemSubcatID, ItemSubcatDesc FROM tlkpItemSubcategory
WHERE (((ItemCategoryID)=[Forms]![frmItems]![cboItemCategory]))
ORDER BY ItemSubcatDesc;"

Column count is 2, column width is 0, 2. Bound column is 1.
I think the problem lies around here. When I have column width =
"1,1", I can see that my subcatgegory ID is displayed correctly.

I do a requery of subcategory on category_afterupdate and
category_change... but the combo boxes drop-down part seem to be
working fine.

If I run just the query that's the record source, it displays
subcategories correctly.

What would cause the text portion of my combo box to be blank?

Thanks in advance for any help,
Lori

[Steve]

You said the underlying query works fine when you run it by
itself...does each record in that query have a subcategory ID? Also, do
you have any records that have a subcategory ID but no text in
ItemSubcatDesc? When you ORDER BY a text field ascending, you get blank
fields first. For troubleshooting, you could change your ORDER BY to
ORDER BY ItemSubcatDesc DESC; and see if you get a better result.

Try making your column width something like four or five inches
(lengthen your combobox accordingly) to see if you simply have a couple
of records that have an awful lot of text for your current combobox
configuration. I realize you probably know exactly what's in your data
fields, but you never know what might pop up.

[Mike Preston]

On 16 Dec 2005 08:54:15 -0800, "lorirobn" <lo******@yahoo.com> wrote:

Hi,

I have a form with several combo boxes, continuous form format, with
record source a query off an Item Table. The fields are Category,
Subcategory, and Color. I am displaying descriptions, not ID's, so I
have to convert the ID's from various lookup tables.

The combo boxes work fine except for subcategory, which is dependent on
category. Depending on category, the drop-down box for subcategory
will display different items. (for example, when Category is
Furniture, Subcategory will display drop-down list of 'Dresser,
Nightstand, Desk...". Without clicking on the drop-down box,
subcategory will display in its text area the Description of the
subcategory ID of the existing record on the table.

The drop-down list for Subcategory works fine. However, the text
portion is blank for records on the 2nd category. In other words,
there are 10 records for the first category, Accessories. Subcategory
displays fine for these. But for the 11th record and subsequent
records, subcategory is blank (text portion, although drop-down works
correctly).

If I just display subcategory ID, and not convert to description, it
works fine.

My SQL statement for subcategory is:
"SELECT ItemSubcatID, ItemSubcatDesc FROM tlkpItemSubcategory
WHERE (((ItemCategoryID)=[Forms]![frmItems]![cboItemCategory]))
ORDER BY ItemSubcatDesc;"

Column count is 2, column width is 0, 2. Bound column is 1.
I think the problem lies around here. When I have column width =
"1,1", I can see that my subcatgegory ID is displayed correctly.

I do a requery of subcategory on category_afterupdate and
category_change... but the combo boxes drop-down part seem to be
working fine.

If I run just the query that's the record source, it displays
subcategories correctly.

What would cause the text portion of my combo box to be blank?

Thanks in advance for any help,

I think you will find that your rowsource for the dependent combobox
is changed, on the fly, when you change the value of the combobox that
controls it.

When you modify the rowsource, you will find that any boxes which have
ID's that are no longer in the rowsource will display as blank. This
is because while Access can display the underlying data if the
combobox is bound and the bound column is being displayed (even if
there is no match), if anything other than the bound column is being
displayed since the bound column does not have a match, the rest of
the columns are "undefined".

The "solution", such as it is (and it isn't a very good one) is to
ensure that any dependent comboboxes have their bound columns equal to
the information you wish to display on the screen.

mike

[Lorirobn]

Hi. Thank you both for your replies.

Steve:
I have just checked, and:
--Each record in the underlying query has a subcategory ID.
--all records that have subcategory ID have text in subcategory desc.
--I am not getting blank fields first. I get description for the first
category, then blanks for the 2nd category.

Mike:
Unless I am misunderstanding you, I don't actually actively change the
recordsource. The situation I describe is happening when the form is
initially being populated with values from the table. When it fills the
form with data, subcategory is filled in correctly for the first
category, then it's left blank when it is filling in data for the 2nd
category. I am not actually changing anything, just watching as the
form is being filled with data from the table. Does that make sense?

I am wondering if I am not correctly referencing the current CategoryID,
on this continuous form.

Thanks once again,
Lori


*** Sent via Developersdex http://www.developersdex.com ***

[Mike Preston]

On Mon, 19 Dec 2005 20:41:12 GMT, Lorirobn <an*******@devdex.com>
wrote:

Hi. Thank you both for your replies.

Steve:
I have just checked, and:
--Each record in the underlying query has a subcategory ID.
--all records that have subcategory ID have text in subcategory desc.
--I am not getting blank fields first. I get description for the first
category, then blanks for the 2nd category.

Mike:
Unless I am misunderstanding you, I don't actually actively change the
recordsource. The situation I describe is happening when the form is
initially being populated with values from the table. When it fills the
form with data, subcategory is filled in correctly for the first
category, then it's left blank when it is filling in data for the 2nd
category. I am not actually changing anything, just watching as the
form is being filled with data from the table. Does that make sense?
Sure it does. Read my other post. Whether the rowsource is being
changed or whether it is only being applied once, the fact is that
only the values that match the rowsource can show up.

Look at your rowsource. You will probably find that the rows are
limited to the "first subcategory". That means that anything which
attempts to match anything not in the "first subcategory" will not
match and therefore show as the bound result (an ID field, typically,
but, as I said, if you change it to text, then the text will not be
lost when there is nothing to match).
I am wondering if I am not correctly referencing the current CategoryID,
on this continuous form.

mike

[Lorirobn]

Hmmmm.... I think I am getting it. It sounds like the values are filled
in at one snapshot in time, and that's why there is no match for the 2nd
subcategory. (and yes, you are correct, rows are limited to the first
subcategory.) I thought it would check the values multiple times, for
each row being populated.

So, is there a way to do what I am attempting:
-- have a continuous form
-- have one combo box dependent on another
-- have the 2nd combo box display values during display of form that
correspond to 1st combo box's values FOR EACH ROW
???

thanks, Lori
*** Sent via Developersdex http://www.developersdex.com ***

[Mike Preston]

On Tue, 20 Dec 2005 02:56:10 GMT, Lorirobn <an*******@devdex.com>
wrote:

Hmmmm.... I think I am getting it. It sounds like the values are filled
in at one snapshot in time, and that's why there is no match for the 2nd
subcategory. (and yes, you are correct, rows are limited to the first
subcategory.) I thought it would check the values multiple times, for
each row being populated.

So, is there a way to do what I am attempting:
-- have a continuous form
-- have one combo box dependent on another
-- have the 2nd combo box display values during display of form that
correspond to 1st combo box's values FOR EACH ROW
???

Only if the bound column for the combobox is displayed and not a
number.

mike

[Lorirobn]

>>Only if the bound column for the combobox is displayed and not a
number.

ok, Mike, I can display the bound column for the combo box (which is not
a number). Then how do I proceed?

Just want to:
-- have the drop-down box selections dependent on the first combo box
value, and
-- display descriptions from lookup table in addition to ID's.

???
I am playing around and trying to figure it out, but so far no luck...

thanks,
Lori

*** Sent via Developersdex http://www.developersdex.com ***

[Mike Preston]

On Tue, 20 Dec 2005 21:39:07 GMT, Lorirobn <an*******@devdex.com>
wrote:

Only if the bound column for the combobox is displayed and not a

number.

ok, Mike, I can display the bound column for the combo box (which is not
a number). Then how do I proceed?

Just want to:
-- have the drop-down box selections dependent on the first combo box
value, and

You do this by changing the rowsource of the dependent combobox in the
after update routine of the first combobox.
-- display descriptions from lookup table in addition to ID's.

OK, this I don't understand. If you say that you will be displaying
descriptions that is fine. But what does "in addition to the ID's"
mean? That sounds like you are using the ID's as the bound column.
Hence, we are in an infinite loop. ;-)

mike

[Lorirobn]

Hi Mike,
I will try my best to abort the infinite loop! ...

If you say that you will be displaying descriptions that is fine. But what does "in addition to the ID's" mean?
That sounds like you are using the ID's as the bound column.

What I meant was:
I was originally just displaying descriptions. But because you said I
could do what I wanted to do only if the bound column for the combo box
is displayed, that's when I thought "ok, then I will display the ID,
also". But, even when I was displaying descriptions, I did actually use
the ID's as the bound column. Is this not correct? Could that be the
cause of my problem? You can view my sql at the beginning of this
thread. It shows that I use the ID as my bound column.

I am afraid this is as clear as mud????
You do this by changing the rowsource of the dependent combobox in the

after update routine of the first combobox.

What do I change the row source to? I have been doing a requery.

Thanks once again. Hope it's clearer. Thanks for sticking through the
infinite loop with me.

Lori


*** Sent via Developersdex http://www.developersdex.com ***

[Mike Preston]

On Wed, 21 Dec 2005 19:21:13 GMT, Lorirobn <an*******@devdex.com>
wrote:

Hi Mike,
I will try my best to abort the infinite loop! ...

If you say that you will be displayingdescriptions that is fine. But what does "in addition to the ID's" mean?
That sounds like you are using the ID's as the bound column.

What I meant was: I was originally just displaying descriptions. But because you said I
could do what I wanted to do only if the bound column for the combo box
is displayed, that's when I thought "ok, then I will display the ID,
also".
That isn't what I meant to say, even if I said it.

What I meant to say is that the bound column must be what is
displayed. If you want to bind the ID, that is fine, as long as you
display the id. If you want to display the description, however, you
need to bind the description.

This is easier said than done.

It requires denormalizing your data.

It requires using the description as the "key".

It is less than optimal (as I previously indicated).
But, even when I was displaying descriptions, I did actually use
the ID's as the bound column.
Ooopsies.
Is this not correct?
Yes, it is not correct.
Could that be the
cause of my problem?
Indeed.
You can view my sql at the beginning of this
thread. It shows that I use the ID as my bound column. I am afraid this is as clear as mud????
I think it would be clearer to you if you re-read each message,
parsing each word. These things tend to get jumbled up on a quick
read.

You do this by changing the rowsource of the dependent combobox in the

after update routine of the first combobox.

What do I change the row source to? I have been doing a requery.

That works.
Thanks once again. Hope it's clearer. Thanks for sticking through the
infinite loop with me.

Let us know if it works.

mike