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
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" <sky @ stanley associates . com> wrote in message
news:eGhrVIs%23HHA...@TK2MSFTNGP04.phx.gbl...
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 !!!
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)" <kthsne...@ncoomcastt.renaetl> wrote in message
news:uZlLBow%23HHA...@TK2MSFTNGP02.phx.gbl...
--
Ken Snell
<MS ACCESS MVP>
"Stephen K. Young" <sky @ stanley associates . com> wrote in message
news:OEAAeAz%23HH...@TK2MSFTNGP04.phx.gbl...
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
<MS ACCESS MVP>
"Dave D" <Da...@discussions.microsoft.com> wrote in message
news:454CAC60-AA71-4138...@microsoft.com...
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.
--
Ken Snell
<MS ACCESS MVP>
"JCPatron" <JCPa...@discussions.microsoft.com> wrote in message
news:A1DC8B42-96DC-4571...@microsoft.com...
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.
You can check the box, but not uncheck it!
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
"Ken Snell (MVP)" <kthsne...@ncoomcastt.renaetl> wrote in message
news:%239GStZJ...@TK2MSFTNGP06.phx.gbl...
I hear you.... < smile >
--
Ken Snell
<MS ACCESS MVP>
"Bill The Cat" <Bill The C...@discussions.microsoft.com> wrote in message
news:D6646D47-127E-4360...@microsoft.com...