upgraded Access from 2000 to 2003; subform not working

[Hegler]

I built a simple Access database several years ago using Access 2000. When
we upgraded our software to 2003, the subform population (driven by a combo
box in the form) stopped working. I had to make an adjustment on every PC
that would access this database. We have since had a PC to crash and I
cannot recall, nor find any of my documentation or any on the Microsoft
website, to assist in reviving my memory as to what I had to change. I was
hoping that someone could assist me in what changed between the two versions
so I may fix our troublesome PC and better document what was done to correct
the problem for next time this should occur. Thank you!

 

[Allen Browne]

Access 2002 and 2003 have a bug in the way they handle the AccessField type.

Say a main form shows clients, and the subform shows client roles. The
subform control has these properties:
Link Master Fields ClientID
Link Child Fields ClientID
You may have a text box on the main form showing ClientID, but chances are
you don't have one in the subform for the ClientID foreign key. Therefore
the LinkChildFields is not referring to a control, but to a field in the
subform's RecordSource table/query. This is what Access calls an
AccessField. It's buggy, and, in certain circumstances, it can cause Access
2002 or 2003 to crash. (Access 2000 and earlier don't crash IME, and I'm not
crazy enough to build 2007 that way.)

The solution is therefore to avoid referring to AccessField objects. Make
sure you have controls on the main form for the LinkMasterFields, and
controls on the subform for the LinkChildFields.

But it's not quite that simple. You have to force Access to break its
current understanding of the form, and then get it to build the right
understanding. Therefore:

1. Make sure Name AutoCorrect options are unchecked. You don't want Access
mis-identifying things. Details:
http://allenbrowne.com/bug-03.html

2. Open the main form in design view, and delete the LinkMasterFields and
LinkChildFields properties. Save. Close the form.

3. Compact/Repair the database. (This gets rid of the Name AutoCorrupt junk,
and also decompiles the queries and loses the linked table caches.)

4. Decompile.

5. Compact again. Twice.

6. Open the form in deisgn view again, and add a text box to each form for
the fields named in LinkMasterFields and LinkChildFields. Then put these
control names back into the 2 properties again. (Having the same name for
the controls and fields will not cause a problem; but if you have different
names, use the control names.)

If decompiling is new, here's more info on that:
http://allenbrowne.com/recover.html

And the problem itself is listed in:
Preventing corruption
at:
http://allenbrowne.com/ser-25.html
(Its #3 under 'during development.')

 

[Hegler]

Sorry but I have figured out that my subform is actually working. It is a
combo box that is in my form header that is tied to a text box in the form
detail section that is no longer working properly. Nothing has changed with
my query or form. All we have done is change from 2000 to 2003. Any more
suggestions???

 

[Allen Browne]

Okay, I misread your question (thinking that the A2003 was crashing now.)

You may have applied the hotfix for SP3 for Office 2003:
http://support.microsoft.com/kb/945674

More info:
http://allenbrowne.com/bug-Access2003SP3.html