Combo Box migration problem, from 2000 to 2003

[Mike Da Commie]

I have an ACCESS database originally built in 95, and subsequently moved to
97 and then 2000. My employer controls the ACCESS version. We recently
moved to 2003 on an XP OS. I am running ACCESS 2000, 2003 & 2007 on the same
design system off site. The error also occurs on other systems.

Upon upgrading to 2003 some combo boxes on forms stopped working while
others still worked. I looked at the tables and it was the same problem. I
had used the “Lookup Wizard†in building the tables for things like “cityâ€,
“provinceâ€. I understand this maybe an issue so I converted the fields in
the table back to a plain text box, and put combo boxes on the forms.

I’m still having the same problem on data entry forms, some combo boxes work
and some do not. I’m getting frustrated as I’ve tried various ways to correct
the problem, new tables, new queries, and new forms and still I get the same
results. I’m self taught in ACCESS and not used to VBA.

Ironically if I open up the Database in 2000, the combo boxes work – either
table based or form based. When viewing or converting to 2003, they do not
work. Another Database I built in 2003 does not have this problem. If I view
or converting to 2007, they work!

There is a table called “MASTER INFO†which has two fields, one called
“STATION†and another called “CLASSâ€, both are “Text Boxesâ€. There are two
tables with values, “lkptblSTATION†and “tblCLASSIFICATIONSâ€.

On the form, one combo box has the following SQL statement:

SELECT lkptblSTATION.STATION
FROM lkptblSTATION
ORDER BY lkptblSTATION.STATION;

And it works. The other combo box has the following SQL statement:

SELECT tblCLASSIFICATIONS.ID, tblCLASSIFICATIONS.CLASSIFICATIONS
FROM tblCLASSIFICATIONS;

It doesn’t work.

I’m lost as to the problem and a solution.

I could just force the organization to migrate to 2007, but I’m concerned
about the problem, as it could rear its head later.

If anyone has some insight, I would appreciate it. I can send a sample of
the three tables and one form in all three versions is a zip file to anyone
interested.

I put up a video of the problem:

http://www.screentoaster.com/watch/stU0lUQ0JIR11aRlxVXFpeU1Rd


or

Combo Box problems

 

[tina]

when you built the db, did you turn OFF the Name Autocorrect setting under
Tools | Options | General tab? and did you do so when converting the db to
A2003?

hth

 

[Jeanette Cunningham]

Hi Mike,
let's start with the simple things first.

The other combo box has the following SQL statement:
SELECT tblCLASSIFICATIONS.ID, tblCLASSIFICATIONS.CLASSIFICATIONS
FROM tblCLASSIFICATIONS;
It doesn't work.


There was a bug for combo boxes in A2003 with one of the servic packs -
from memory it was SP3 and a hotfix was released.

Here is a link to a description of the problem and how to fix it
http://allenbrowne.com/bug-Access2003SP3.html


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Mike Da Commie]

Jeanette:

Thank you so much, I installed the hotfix and the problem is solved, The
link you sent and the MS article indicated a blank combo box if the RowSource
is a table field that has something in the "format" property.

Thanks again!