Combo Box Display Missing After Office 2003 Service Pack 3

  • Thread starter Thread starter Stephen K. Young
  • Start date Start date
S

Stephen K. Young

I just installed the new Service Pack 3 for Office 2003.

This has caused a strange display problem in Access 2003 with combo boxes on
tables that have a formatted text field.

To see this problem, create a table named TableX with a single key field
FieldX that is a text field. (Use any table name or field name you like.)
Add a few rows of data, such as a, b, c.

Create a simple test form with a combo box that has SQL such as "Select
FieldX From TableX". Save the form. The combo box dropdown displays just
fine and you can select values.

Now go to design view on TableX and change the format property of FieldX to
have any format, such as &[Blue]. (This particular format displays the
FieldX
in a blue color, which I use to indicate key fields during development, and
it
propagates to queries. But any format seems to cause the problem.
The users, of course, always view data in forms.)

Suddenly the combo box on the test form is completely blank. The combo box
does have data (for example, from the Immediate Window,
?Screen.ActiveControl.ListCount has the correct value of 3),
but the display is all blank and you cannot actually see any of the dropdown
rows.

It does not matter what format is applied to FieldX, or whether the form
combo
box has formatting or not, the combo dropdown is blank.

The formatting on FieldX should have nothing to do with the combo box
display.

But for some reason, the field format interferes with the combo box display.

Prior to Service Pack 3, this was never an issue. (Nor was it ever an issue
in
prior versions of Access.) Am I missing something? Any suggestions for a
work-around?

- Steve Young
 
I have not installed SP3 for ACCESS 2003 yet, but I've asked fellow ACCESS
MVPs to check this out.

So far, it doesn't appear to be found with Vista operating system.

But reports about this behavior are being made in the Italian newsgroup, and
has been confirmed by one MVP in Polish version of ACCESS. There likely will
be additional results (good or bad) in the near future.

In the meantime, an MVP has suggested this as the workaround -- use an SQL
statement similar to this in the combo box's RowSource property:

SELECT FieldX & "" AS NewFieldX
FROM TableName;

Let us know if this workaround gives you back the formatting display
characteristics.
 
I have not installed SP3 for ACCESS 2003 yet, but I've asked fellow ACCESS
MVPs to check this out.

So far, it doesn't appear to be found with Vista operating system.

But reports about this behavior are being made in the Italian newsgroup, and
has been confirmed by one MVP in Polish version of ACCESS. There likely will
be additional results (good or bad) in the near future.

In the meantime, an MVP has suggested this as the workaround -- use an SQL
statement similar to this in the combo box's RowSource property:

SELECT FieldX & "" AS NewFieldX
FROM TableName;

Let us know if this workaround gives you back the formatting display
characteristics.

--

Ken Snell
<MS ACCESS MVP>

Stephen K. Young said:
I just installed the new Service Pack 3 for Office 2003.
Click to expand...
This has caused a strange display problem in Access 2003 with combo boxes
on
tables that have a formatted text field.
Click to expand...
To see this problem, create a table named TableX with a single key field
FieldX that is a text field. (Use any table name or field name you like.)
Add a few rows of data, such as a, b, c.
Click to expand...
Create a simple test form with a combo box that has SQL such as "Select
FieldX From TableX". Save the form. The combo box dropdown displays just
fine and you can select values.
Click to expand...
Now go to design view on TableX and change the format property of FieldX
to
have any format, such as &[Blue]. (This particular format displays the
FieldX
in a blue color, which I use to indicate key fields during development,
and it
propagates to queries. But any format seems to cause the problem.
The users, of course, always view data in forms.)
Click to expand...
Suddenly the combo box on the test form is completely blank. The combo box
does have data (for example, from the Immediate Window,
?Screen.ActiveControl.ListCount has the correct value of 3),
but the display is all blank and you cannot actually see any of the
dropdown rows.
Click to expand...
It does not matter what format is applied to FieldX, or whether the form
combo
box has formatting or not, the combo dropdown is blank.
Click to expand...
The formatting on FieldX should have nothing to do with the combo box
display.
Click to expand...
But for some reason, the field format interferes with the combo box
display.
Click to expand...
Prior to Service Pack 3, this was never an issue. (Nor was it ever an
issue in
prior versions of Access.) Am I missing something? Any suggestions for a
work-around?
Click to expand...
- Steve Young
Click to expand...
Click to expand...

I have another problem with access 2003 with sp3 adp poject. If you
have a table with a bit column and default value 0 before sp3 access
insert correctly false, after sp3 access insert true. This is only one
problem, with sp3 i discover a lot of problem, im very scared !!!
 
Yes, the combo box works normally after changing the SQL to add a zero
length string:

SELECT FieldX & "" FROM TableName.

I am running under Windows XP SP2.

By the way, there was a similar Access 2003 SP3 issue with combo boxes
mentioned in comp.databases.ms-access:
http://tinyurl.com/2bkq2y
(goes to http://groups.google.com)

It's interesting that the combo box issue does not arise for a numeric
field. In that case, the combo box is fine no matter what the numeric field
format.

And it is interesting that it does not occur under Vista.

Now, do I wait for a Microsoft hotfix, or run some code to change field
formats or combo boxes...

Thanks - Steve

Ken Snell (MVP) said:
I have not installed SP3 for ACCESS 2003 yet, but I've asked fellow ACCESS
MVPs to check this out.

So far, it doesn't appear to be found with Vista operating system.

But reports about this behavior are being made in the Italian newsgroup,
and has been confirmed by one MVP in Polish version of ACCESS. There
likely will be additional results (good or bad) in the near future.

In the meantime, an MVP has suggested this as the workaround -- use an SQL
statement similar to this in the combo box's RowSource property:

SELECT FieldX & "" AS NewFieldX
FROM TableName;

Let us know if this workaround gives you back the formatting display
characteristics.

--

Ken Snell
<MS ACCESS MVP>




Stephen K. Young said:
I just installed the new Service Pack 3 for Office 2003.

This has caused a strange display problem in Access 2003 with combo boxes
on
tables that have a formatted text field.

To see this problem, create a table named TableX with a single key field
FieldX that is a text field. (Use any table name or field name you like.)
Add a few rows of data, such as a, b, c.

Create a simple test form with a combo box that has SQL such as "Select
FieldX From TableX". Save the form. The combo box dropdown displays just
fine and you can select values.

Now go to design view on TableX and change the format property of FieldX
to
have any format, such as &[Blue]. (This particular format displays the
FieldX
in a blue color, which I use to indicate key fields during development,
and it
propagates to queries. But any format seems to cause the problem.
The users, of course, always view data in forms.)

Suddenly the combo box on the test form is completely blank. The combo
box
does have data (for example, from the Immediate Window,
?Screen.ActiveControl.ListCount has the correct value of 3),
but the display is all blank and you cannot actually see any of the
dropdown rows.

It does not matter what format is applied to FieldX, or whether the form
combo
box has formatting or not, the combo dropdown is blank.

The formatting on FieldX should have nothing to do with the combo box
display.

But for some reason, the field format interferes with the combo box
display.

Prior to Service Pack 3, this was never an issue. (Nor was it ever an
issue in
prior versions of Access.) Am I missing something? Any suggestions for a
work-around?

- Steve Young
Click to expand...
Click to expand...
 
Further testing by an MVP confirms that the problem does occur with text
field (not with numeric field) in Vista too. This has been reported to
Microsoft. I cannot say what may or may not be the response from Microsoft,
sorry. If this is a major issue, you may need to change your applications to
use the concatenation; or, if it can be done, roll back to SP2.

--

Ken Snell
<MS ACCESS MVP>



Stephen K. Young said:
Yes, the combo box works normally after changing the SQL to add a zero
length string:

SELECT FieldX & "" FROM TableName.

I am running under Windows XP SP2.

By the way, there was a similar Access 2003 SP3 issue with combo boxes
mentioned in comp.databases.ms-access:
http://tinyurl.com/2bkq2y
(goes to http://groups.google.com)

It's interesting that the combo box issue does not arise for a numeric
field. In that case, the combo box is fine no matter what the numeric
field format.

And it is interesting that it does not occur under Vista.

Now, do I wait for a Microsoft hotfix, or run some code to change field
formats or combo boxes...

Thanks - Steve

Ken Snell (MVP) said:
I have not installed SP3 for ACCESS 2003 yet, but I've asked fellow ACCESS
MVPs to check this out.

So far, it doesn't appear to be found with Vista operating system.

But reports about this behavior are being made in the Italian newsgroup,
and has been confirmed by one MVP in Polish version of ACCESS. There
likely will be additional results (good or bad) in the near future.

In the meantime, an MVP has suggested this as the workaround -- use an
SQL statement similar to this in the combo box's RowSource property:

SELECT FieldX & "" AS NewFieldX
FROM TableName;

Let us know if this workaround gives you back the formatting display
characteristics.

--

Ken Snell
<MS ACCESS MVP>




Stephen K. Young said:
I just installed the new Service Pack 3 for Office 2003.

This has caused a strange display problem in Access 2003 with combo
boxes on
tables that have a formatted text field.

To see this problem, create a table named TableX with a single key field
FieldX that is a text field. (Use any table name or field name you
like.)
Add a few rows of data, such as a, b, c.

Create a simple test form with a combo box that has SQL such as "Select
FieldX From TableX". Save the form. The combo box dropdown displays just
fine and you can select values.

Now go to design view on TableX and change the format property of FieldX
to
have any format, such as &[Blue]. (This particular format displays the
FieldX
in a blue color, which I use to indicate key fields during development,
and it
propagates to queries. But any format seems to cause the problem.
The users, of course, always view data in forms.)

Suddenly the combo box on the test form is completely blank. The combo
box
does have data (for example, from the Immediate Window,
?Screen.ActiveControl.ListCount has the correct value of 3),
but the display is all blank and you cannot actually see any of the
dropdown rows.

It does not matter what format is applied to FieldX, or whether the form
combo
box has formatting or not, the combo dropdown is blank.

The formatting on FieldX should have nothing to do with the combo box
display.

But for some reason, the field format interferes with the combo box
display.

Prior to Service Pack 3, this was never an issue. (Nor was it ever an
issue in
prior versions of Access.) Am I missing something? Any suggestions for a
work-around?

- Steve Young
Click to expand...
Click to expand...
Click to expand...
 
Hi Guys,

it's not strictly true that it does not affect numbers. try creating a multi
column query something like ID Field, Number, Description. Then show the last
2 colunms (hiding the ID Field) in the combo list but format the number
column and/or the combobox as a percentage. this would have worked under SP2
but now shows the number left justified (as though it were text) and not
formatted as a percentage.

Dave (Access developer)

Ken Snell (MVP) said:
Further testing by an MVP confirms that the problem does occur with text
field (not with numeric field) in Vista too. This has been reported to
Microsoft. I cannot say what may or may not be the response from Microsoft,
sorry. If this is a major issue, you may need to change your applications to
use the concatenation; or, if it can be done, roll back to SP2.

--

Ken Snell
<MS ACCESS MVP>



Stephen K. Young said:
Yes, the combo box works normally after changing the SQL to add a zero
length string:

SELECT FieldX & "" FROM TableName.

I am running under Windows XP SP2.

By the way, there was a similar Access 2003 SP3 issue with combo boxes
mentioned in comp.databases.ms-access:
http://tinyurl.com/2bkq2y
(goes to http://groups.google.com)

It's interesting that the combo box issue does not arise for a numeric
field. In that case, the combo box is fine no matter what the numeric
field format.

And it is interesting that it does not occur under Vista.

Now, do I wait for a Microsoft hotfix, or run some code to change field
formats or combo boxes...

Thanks - Steve

Ken Snell (MVP) said:
I have not installed SP3 for ACCESS 2003 yet, but I've asked fellow ACCESS
MVPs to check this out.

So far, it doesn't appear to be found with Vista operating system.

But reports about this behavior are being made in the Italian newsgroup,
and has been confirmed by one MVP in Polish version of ACCESS. There
likely will be additional results (good or bad) in the near future.

In the meantime, an MVP has suggested this as the workaround -- use an
SQL statement similar to this in the combo box's RowSource property:

SELECT FieldX & "" AS NewFieldX
FROM TableName;

Let us know if this workaround gives you back the formatting display
characteristics.

--

Ken Snell
<MS ACCESS MVP>




I just installed the new Service Pack 3 for Office 2003.

This has caused a strange display problem in Access 2003 with combo
boxes on
tables that have a formatted text field.

To see this problem, create a table named TableX with a single key field
FieldX that is a text field. (Use any table name or field name you
like.)
Add a few rows of data, such as a, b, c.

Create a simple test form with a combo box that has SQL such as "Select
FieldX From TableX". Save the form. The combo box dropdown displays just
fine and you can select values.

Now go to design view on TableX and change the format property of FieldX
to
have any format, such as &[Blue]. (This particular format displays the
FieldX
in a blue color, which I use to indicate key fields during development,
and it
propagates to queries. But any format seems to cause the problem.
The users, of course, always view data in forms.)

Suddenly the combo box on the test form is completely blank. The combo
box
does have data (for example, from the Immediate Window,
?Screen.ActiveControl.ListCount has the correct value of 3),
but the display is all blank and you cannot actually see any of the
dropdown rows.

It does not matter what format is applied to FieldX, or whether the form
combo
box has formatting or not, the combo dropdown is blank.

The formatting on FieldX should have nothing to do with the combo box
display.

But for some reason, the field format interferes with the combo box
display.

Prior to Service Pack 3, this was never an issue. (Nor was it ever an
issue in
prior versions of Access.) Am I missing something? Any suggestions for a
work-around?

- Steve Young
Click to expand...
Click to expand...
Click to expand...
 
Good observation. As you note, the columns' values in listbox row (and combo
box row) that are not the Bound Column will become text types. I've passed
this along to MS.
--

Ken Snell
<MS ACCESS MVP>




Dave D said:
Hi Guys,

it's not strictly true that it does not affect numbers. try creating a
multi
column query something like ID Field, Number, Description. Then show the
last
2 colunms (hiding the ID Field) in the combo list but format the number
column and/or the combobox as a percentage. this would have worked under
SP2
but now shows the number left justified (as though it were text) and not
formatted as a percentage.

Dave (Access developer)

Ken Snell (MVP) said:
Further testing by an MVP confirms that the problem does occur with text
field (not with numeric field) in Vista too. This has been reported to
Microsoft. I cannot say what may or may not be the response from
Microsoft,
sorry. If this is a major issue, you may need to change your applications
to
use the concatenation; or, if it can be done, roll back to SP2.

--

Ken Snell
<MS ACCESS MVP>



Stephen K. Young said:
Yes, the combo box works normally after changing the SQL to add a zero
length string:

SELECT FieldX & "" FROM TableName.

I am running under Windows XP SP2.

By the way, there was a similar Access 2003 SP3 issue with combo boxes
mentioned in comp.databases.ms-access:
http://tinyurl.com/2bkq2y
(goes to http://groups.google.com)

It's interesting that the combo box issue does not arise for a numeric
field. In that case, the combo box is fine no matter what the numeric
field format.

And it is interesting that it does not occur under Vista.

Now, do I wait for a Microsoft hotfix, or run some code to change field
formats or combo boxes...

Thanks - Steve

I have not installed SP3 for ACCESS 2003 yet, but I've asked fellow
ACCESS
MVPs to check this out.

So far, it doesn't appear to be found with Vista operating system.

But reports about this behavior are being made in the Italian
newsgroup,
and has been confirmed by one MVP in Polish version of ACCESS. There
likely will be additional results (good or bad) in the near future.

In the meantime, an MVP has suggested this as the workaround -- use an
SQL statement similar to this in the combo box's RowSource property:

SELECT FieldX & "" AS NewFieldX
FROM TableName;

Let us know if this workaround gives you back the formatting display
characteristics.

--

Ken Snell
<MS ACCESS MVP>




I just installed the new Service Pack 3 for Office 2003.

This has caused a strange display problem in Access 2003 with combo
boxes on
tables that have a formatted text field.

To see this problem, create a table named TableX with a single key
field
FieldX that is a text field. (Use any table name or field name you
like.)
Add a few rows of data, such as a, b, c.

Create a simple test form with a combo box that has SQL such as
"Select
FieldX From TableX". Save the form. The combo box dropdown displays
just
fine and you can select values.

Now go to design view on TableX and change the format property of
FieldX
to
have any format, such as &[Blue]. (This particular format displays
the
FieldX
in a blue color, which I use to indicate key fields during
development,
and it
propagates to queries. But any format seems to cause the problem.
The users, of course, always view data in forms.)

Suddenly the combo box on the test form is completely blank. The
combo
box
does have data (for example, from the Immediate Window,
?Screen.ActiveControl.ListCount has the correct value of 3),
but the display is all blank and you cannot actually see any of the
dropdown rows.

It does not matter what format is applied to FieldX, or whether the
form
combo
box has formatting or not, the combo dropdown is blank.

The formatting on FieldX should have nothing to do with the combo box
display.

But for some reason, the field format interferes with the combo box
display.

Prior to Service Pack 3, this was never an issue. (Nor was it ever an
issue in
prior versions of Access.) Am I missing something? Any suggestions
for a
work-around?

- Steve Young
Click to expand...
Click to expand...
Click to expand...
 
I have this format problem with listboxes and comboboxes after Office SP3.
How can I fix it?, where should I look for a fix?. Is this the place to look
for latest reviews.

Ken Snell (MVP) said:
Good observation. As you note, the columns' values in listbox row (and combo
box row) that are not the Bound Column will become text types. I've passed
this along to MS.
--

Ken Snell
<MS ACCESS MVP>




Dave D said:
Hi Guys,

it's not strictly true that it does not affect numbers. try creating a
multi
column query something like ID Field, Number, Description. Then show the
last
2 colunms (hiding the ID Field) in the combo list but format the number
column and/or the combobox as a percentage. this would have worked under
SP2
but now shows the number left justified (as though it were text) and not
formatted as a percentage.

Dave (Access developer)

Ken Snell (MVP) said:
Further testing by an MVP confirms that the problem does occur with text
field (not with numeric field) in Vista too. This has been reported to
Microsoft. I cannot say what may or may not be the response from
Microsoft,
sorry. If this is a major issue, you may need to change your applications
to
use the concatenation; or, if it can be done, roll back to SP2.

--

Ken Snell
<MS ACCESS MVP>



Yes, the combo box works normally after changing the SQL to add a zero
length string:

SELECT FieldX & "" FROM TableName.

I am running under Windows XP SP2.

By the way, there was a similar Access 2003 SP3 issue with combo boxes
mentioned in comp.databases.ms-access:
http://tinyurl.com/2bkq2y
(goes to http://groups.google.com)

It's interesting that the combo box issue does not arise for a numeric
field. In that case, the combo box is fine no matter what the numeric
field format.

And it is interesting that it does not occur under Vista.

Now, do I wait for a Microsoft hotfix, or run some code to change field
formats or combo boxes...

Thanks - Steve

I have not installed SP3 for ACCESS 2003 yet, but I've asked fellow
ACCESS
MVPs to check this out.

So far, it doesn't appear to be found with Vista operating system.

But reports about this behavior are being made in the Italian
newsgroup,
and has been confirmed by one MVP in Polish version of ACCESS. There
likely will be additional results (good or bad) in the near future.

In the meantime, an MVP has suggested this as the workaround -- use an
SQL statement similar to this in the combo box's RowSource property:

SELECT FieldX & "" AS NewFieldX
FROM TableName;

Let us know if this workaround gives you back the formatting display
characteristics.

--

Ken Snell
<MS ACCESS MVP>




I just installed the new Service Pack 3 for Office 2003.

This has caused a strange display problem in Access 2003 with combo
boxes on
tables that have a formatted text field.

To see this problem, create a table named TableX with a single key
field
FieldX that is a text field. (Use any table name or field name you
like.)
Add a few rows of data, such as a, b, c.

Create a simple test form with a combo box that has SQL such as
"Select
FieldX From TableX". Save the form. The combo box dropdown displays
just
fine and you can select values.

Now go to design view on TableX and change the format property of
FieldX
to
have any format, such as &[Blue]. (This particular format displays
the
FieldX
in a blue color, which I use to indicate key fields during
development,
and it
propagates to queries. But any format seems to cause the problem.
The users, of course, always view data in forms.)

Suddenly the combo box on the test form is completely blank. The
combo
box
does have data (for example, from the Immediate Window,
?Screen.ActiveControl.ListCount has the correct value of 3),
but the display is all blank and you cannot actually see any of the
dropdown rows.

It does not matter what format is applied to FieldX, or whether the
form
combo
box has formatting or not, the combo dropdown is blank.

The formatting on FieldX should have nothing to do with the combo box
display.

But for some reason, the field format interferes with the combo box
display.

Prior to Service Pack 3, this was never an issue. (Nor was it ever an
issue in
prior versions of Access.) Am I missing something? Any suggestions
for a
work-around?

- Steve Young
Click to expand...
Click to expand...
Click to expand...
 
Until MS releases a fix, the "complete workaround" is to remove the Format
property's value from the field's properties in the table's design view.
Otherwise, concatenating the field's value with an empty string also seems
to "overcome" the problem.

I would expect that MS will make some type of announcement when the fix is
released, but at this moment I don't know where it would be made. I
anticipate that we'll post some info in these newsgroups when we find out
that MS has released a fix -- might even be a KB article, too.
 
I have noticed that check boxes stop working after installation of SP3 if:
- You are using an access project (compiled (.ade) or uncompiled (.adp))
- The check box is bound to a bit field in SQL server

You can check the box, but not uncheck it!
 
Chris G said:
I have noticed that check boxes stop working after installation of SP3 if:
- You are using an access project (compiled (.ade) or uncompiled (.adp))
- The check box is bound to a bit field in SQL server

You can check the box, but not uncheck it!
Click to expand...


Reportted to MS. This appears to be another manifestation of the bug already
reported about being able to properly set the value of a Bit field in SQL
Server.
 
Either of the combo box work-arounds might be an acceptable solution if you
are NOT a developer that distributes applications.

But if you have several applications, each distributed to multiple clients
with multiple databases, this combo box situation is a real difficulty.

At any time, any customer installs SP3, and suddenly an application that has
been working for years is broken. By broken, I mean that the customer cannot
see combo box values and cannot select from these (unseen) combo box values.

To resolve this, I must either send every customer for every application a
new front end, with every combo box in every form converted to have an
extraneous string concatenation or function call such as CStr(), or I must
send a utility to modify their back-end databases to remove all formatting
on the raw table key fields. This is a HUGE effort.

The very nature of this bug is quite strange - why the formatting of a raw
table field should affect the display of a form combo box, I have no idea.
It seems that a simple table format property is affecting the form, even
after the form is finished.

Just complaining...

- Steve
 
We have this problem with our database and the Microsoft rep for Defense
Logistics Agency said Microsoft had not heard of this problem. Has microsoft
worked this issue yet?
 
As I noted elsethread, MS is definitely aware of this new bug, and is
working on the fix for it. I don't know what the timeframe will be for
publishing the fix, but it is being addressed by MS.
 
Thank you SOOO much Ken for keeping us posted...I'm still watching for an
answer here...our systems engineers are trying to figure out something,
too...if they come up with anything, I will let you folks know...
 
Another good fix, instead of adding an empty character is to use
TRIM([fieldname]). In addition to now populating the combobox, it
restores the ability to have the table/query auto-fill as you type.
 
Back
Top