ComboBox - Bound Column is Displayed Column?

[scottydel]

Hello,

I'm using Access 2003. I have a combobox whose row source is a query that
has 2 fields. The combobox is bound to the first field (or column, as access
describes it), and is displaying the second field in the picklist. The
records in my query are like this (double quotes used for clarity):

"I", "I-Some Text"
"II", "II-Some Text"
"III", "III-Some Text"
"", "No Number"

Keep in mind my combobox is bound to the first field. So if I pick "I-Some
Text" from the picklist, "I" is what is ultimately set in my database (form
is bound to a table). The last record in this query result set displays
correctly as "No Number" in my combobox's picklist, However, if I select "No
Number" from the picklist, it does not display it, but instantly sets it to
"".

I would think that the whole purpose of being able to set which field was
bound, and which field was displayed, would enable "No Number" to display in
the field, while "" is actually being set behing the scenes. But this
doesn't seem to be the case.

Any insights?

Thanks,

Scott

 

[Marshall Barton]

I'm using Access 2003. I have a combobox whose row source is a query that
has 2 fields. The combobox is bound to the first field (or column, as access
describes it), and is displaying the second field in the picklist. The
records in my query are like this (double quotes used for clarity):

"I", "I-Some Text"
"II", "II-Some Text"
"III", "III-Some Text"
"", "No Number"

Keep in mind my combobox is bound to the first field. So if I pick "I-Some
Text" from the picklist, "I" is what is ultimately set in my database (form
is bound to a table). The last record in this query result set displays
correctly as "No Number" in my combobox's picklist, However, if I select "No
Number" from the picklist, it does not display it, but instantly sets it to
"".

I would think that the whole purpose of being able to set which field was
bound, and which field was displayed, would enable "No Number" to display in
the field, while "" is actually being set behing the scenes. But this
doesn't seem to be the case.

You are probably running into an issue of the record source
table field bound to the combo box having its
AllowZeroLength property set to No.

I suggest that you use a valid value (such as "N") instead
of ""

 

[scottydel]

Thanks for the response.

My AllowZeroLength property is set to Yes. Unfortunately my design is
dependent on "" values (blanks), and setting to "N" would cascade into
re-development of other pieces of the system.

My explanation might not have been clear. The "" value is correctly being
set to the bound table's field. What is not happening correctly is the value
"No Number" is not displaying in the combo box's field space once it is
selected. "No Number" is present on the picklist, but if I select it on the
picklist, it automatically sets the combo box's field space to "", almost as
if it is changing the bound field to the visible field. For other options in
the picklist the behavior is normal, in that if I select "I-Some Text" from
the picklist, "I-Some Text" is correctly displayed in the combo box's field
space, and "I" is correctly set as the field's value in the bound table.

Any other ideas?

 

[Marshall Barton]

My AllowZeroLength property is set to Yes. Unfortunately my design is
dependent on "" values (blanks), and setting to "N" would cascade into
re-development of other pieces of the system.

My explanation might not have been clear. The "" value is correctly being
set to the bound table's field. What is not happening correctly is the value
"No Number" is not displaying in the combo box's field space once it is
selected. "No Number" is present on the picklist, but if I select it on the
picklist, it automatically sets the combo box's field space to "", almost as
if it is changing the bound field to the visible field. For other options in
the picklist the behavior is normal, in that if I select "I-Some Text" from
the picklist, "I-Some Text" is correctly displayed in the combo box's field
space, and "I" is correctly set as the field's value in the bound table.

I set up a test table and form (A2003) and can now confirm
the behavior you are seeing.

Some oddities:
When I set the Required property to No, the ZLS is changed
to Null. The display text is not displayed.

When the Required property is set to Yes, it leaves the
value as the ZLS. The display text is not displayed.

What really seems strange is that it all works the way it
should if the combo box is not bound to a record source
field.

This sure looks like a bug to me. The only workaround that
comes to mind is to make the combo box unbound and use a
line of code in its AfterUpdate event to stuff the value
into a hidden, bound text box.

I will try to make MS aware of this problem, though that
won't do you any good.

 

[scottydel]

Marshall,

Thanks for taking the time to reproduce it. I used your suggestion as a
workaround. Notifying MS of a bug might not direclty help me but it should
help us all down the road! Thanks!

-Scott