Fatal error

[Allen Browne]

The only issue I'm aware of if the text box referred to in LinkMasterFields
is unbound is that Access may not understand the data type you intend. If
it's meant to be a Number type, then set its Format to "General Number".

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Regarding the "control". Does it matter if the control in the parent form
(an unbound form) is an unbound text box control? The control in the
subform (for the LinkChildFields) is bound to a control and the subform is
based on an ODBC table stored on SQL Server 2000.

David

Okay, David,

You're in the seat where you can evaluate whether whatever you are doing
is likely to cause a change in the LinkMasterFields on the main form and
trigger an endless sequence.

The change I'm aware of between versions was the problem with the
LinkChildFields referring to an AccessFieldType rather than a control.

Not sure what else to suggest from this distance.

There isn't an actual requery in the Form_Current. It only resets the
RecordID in the parent form which is what the Child/Master links are
linked to. The parent form is unbound. Only the subforms are bound to
data sources and even 2 of them only bind when their tab is clicked (but
none of those are causing problems).

I still don't understand how this application had NO problems in Access
2000 and was running for years without bombing. And all we did was
upgrade from Office 2000 Pro to Office 2003 Pro.

David

If would be *very* easy to trigger a continuous cycle by including a
Requery in Form_Current.

Requerying the current form would cause it to reload records, which
would cause the Current event to fire, which requeries the form which
...

Requerying the parent form would cause the parent to reload its record,
so *all* of its subforms would reload and the Current event would
retrigger for all of them, and whichever one is requering the parent
causes that to retrigger.

That's exactly the kind of cycle you must not get yourself into.

Allen,

I spoke too quickly. Now users are crashing 2-5 times a day. It all
seems to stem around some sort of DoCmd and/or data requery (which
happens in the Form_Current event via Linked Child/Master on 2nd
subform). The problem is that the error is not trappable. I'm not
sure what to do next as this long-term problem has me perplexed and it
all started with a simple upgrade to Office 2003.

David

Did you try temporarily commenting out the Form_Current code, to see
if that avoids the crash?


Allen,
All subforms have a text box with the RecordID (foreign key) in it
and all
are not visible because it means nothing to the user.

Let me add one more thing. Whenever I look at the application Event
Log it
always fails on msjtes40.dll which, I think, is Microsoft Jet
Expression
something.

David

Okay, David, you could test if the Form_Current code triggers the
problem
by
commenting it out and see if the crash still occurs.

There is another issue that could be involved here though. There is
a flaw
in A2002 and 2003 that I never saw in A2000, so the conversion may
have
triggered it. It occurs if the field named in LinkChildFields is
not
represented by a text box in the subform. In that case, the
reference is
to
an object of type AccessField, and the bug seems to be with that
data
type.
If you put a text box on the subform, giving it the same Name and
Control
Source as the field named in LinkChildFields, the reference is then
to an
object of type Textbox and so you avoid the bug. I've not seen any
MS
documentation on this, but the symptoms have been reported by
several
users.

So, try this sequence:
1. Make sure Name AutoCorrect is off.
2. Open each subform in turn, and add a text box for the foreign
key
field.
3. Save.
4. Compact
5. Decompile
6. Compact

Solved?

No..let me explain. The value of LinkChildFields and
LinkMasterFields
is
alway set to RecordID and it is on a different subform then the
one that
is blowing up. It is part of the same tab control, but are
different
subforms. Subform1 sets the value of RecordID on the form that
the tab
control is on (this form is unbound). Subform2 is under the 2nd
tab of
this control and it has it's field named RecordID linked to the
master
form. Think of it as master form (unbound) with a 2-tab control.
Tab1
shows the orders subform and tab 2 shows the order detail. So
when I
click on a record in subform1 it sets the value to link to
subform2 so
that when the 2nd tab is clicked it shows me the order details
for that
order. Now, you should also know that there are 2 more tabs with
other
sets of orders, that is why I cannot link subform2 to value in
subform1.

David

That seems to be a logic problem.

Why set the value of the text box on the main form?
The subform record won't load unless it already maches.

Changing the value of the LinkMasterFields causes Access to
reload the
subform, which re-triggers Form_Current, and so you enter this
endless
cycle that crashes Access. The logic is wrong.


You may be onto something there. Yes, one of the subforms has
link
child/master settings so that the correct records appear for
that
RecordID. Any suggestions on how to set/reset? Can I do it on
the
Change event of the tab control? The subform on the 2nd tab
has
LinkMaster and Child set to the RecordID. Thanks.

David
Are RepairOrderID or RecordID named in the LinkMasterFields of
the
subform control? If so, that could trigger a crash: whenever
the
subform's Form_Current event fires, it triggers a change in
the
subform's records which forces it to reload its records, which
re-triggers Form_Current, which retriggers the cycle ad
infinitum.

If that's not the case, you might consider getting Access to
rebuild
the database. Create a new (blank) database, turning Name
AutoCorrect
off, and then import everything into the new database (File |
Get
External | Import)


Nope, still blowing. It seems to be limited to when on
subform gets
the focus and probably during the Current event of that
subform
because that is the only place where there is code on that
form.
Below is the code in the Current event of that subform:

Private Sub Form_Current()
On Error Resume Next

'put RecordID, RepairOrderID and EstimateID on parent form
Forms![frmOrderLists]![RepairOrderID] = Me.RepairOrderID
Forms![frmOrderLists]![RecordID] = Me.RecordID

'put RecordID into hidden field on frmMain for other
processing
that needs to know/use current RecordID
Forms![frmMain]![RecordID] = Me.RecordID

End Sub

As you can see there is nothing special. 3 other items of
note.
1. This is 1 or 4 subforms on a tab control.
2. There is a "Conditional formatting" on some of the
controls on
the
subform (but it does not blow up when you first display the
subform,
only when you return to it (and not always then either).
3. We are using ODBC attached tables to SQL Server 2000.

As I mentioned, this application ran fine in Office (Access)
2000
and
the problems started immediately after conversion and upgrade
to
Office 2003.

David

Access 2000 uses a different binary than Access 2003 does.
Use
Access
2000 to decompile a copy of the database by entering
something like
this at the command prompt while Access is not running. It
is all
one
line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe"
/decompile
"c:\MyPath\MyDatabase.mdb"

Still in Access 2000, uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://members.iinet.net.au/~allenbrowne/bug-03.html

Then compact the database:
Tools | Database Utilities | Compact

Now, assuming all your users are on Access 2003:
1. Use A2003 to create a new (blank) database.

2. Turn off Name AutoCorrect in this new database.

3. Import everything: File | Get External | Import

4. Open a code window, and set minimal references. More
info:
http://members.iinet.net.au/~allenbrowne/ser-38.html
Then make sure the code compiles:
Debug | Compile

At this point, you should have a stable A2003 database. If
not,
post
more information about the point at which the crash happens.

We converted an application from Access 2000 to Access 2003
and
now
we get
the ultimate Microsoft "crash" stating "Microsoft Office
Access
has
encountered a problem and needs to close. etc. etc." We
did not
get
these
in the 2000 application that has been running for 2 years.
How do
I
trace
this problem as I do not get an error code. My users want
to go
back to
Office 2000 and I don't blame them. How can "upgrading" be
worse
than the
old? Is there a way to force the error message rather than
this
dubious
"nothing" message? Thanks.

David

 

[David C]

OK, I'll try that. Also, I found a known problem in TechNet that references
Access 2002 but may apply to 2003 also. It suggests that the form control
reference not just use the control name, but full forms expression. For
example, on the detail subform control, the LinkMasterFields references
[Forms]![frmOrderLists]![RecordID] and the LinkChildFields refers to the
bound control on the subform named RecordID. It still works this way, but
do you see any problems with this?

David

The only issue I'm aware of if the text box referred to in
LinkMasterFields is unbound is that Access may not understand the data
type you intend. If it's meant to be a Number type, then set its Format to
"General Number".

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Regarding the "control". Does it matter if the control in the parent
form (an unbound form) is an unbound text box control? The control in
the subform (for the LinkChildFields) is bound to a control and the
subform is based on an ODBC table stored on SQL Server 2000.

David

Okay, David,

You're in the seat where you can evaluate whether whatever you are doing
is likely to cause a change in the LinkMasterFields on the main form and
trigger an endless sequence.

The change I'm aware of between versions was the problem with the
LinkChildFields referring to an AccessFieldType rather than a control.

Not sure what else to suggest from this distance.


There isn't an actual requery in the Form_Current. It only resets the
RecordID in the parent form which is what the Child/Master links are
linked to. The parent form is unbound. Only the subforms are bound to
data sources and even 2 of them only bind when their tab is clicked
(but none of those are causing problems).

I still don't understand how this application had NO problems in Access
2000 and was running for years without bombing. And all we did was
upgrade from Office 2000 Pro to Office 2003 Pro.

David

If would be *very* easy to trigger a continuous cycle by including a
Requery in Form_Current.

Requerying the current form would cause it to reload records, which
would cause the Current event to fire, which requeries the form which
...

Requerying the parent form would cause the parent to reload its
record, so *all* of its subforms would reload and the Current event
would retrigger for all of them, and whichever one is requering the
parent causes that to retrigger.

That's exactly the kind of cycle you must not get yourself into.

Allen,

I spoke too quickly. Now users are crashing 2-5 times a day. It all
seems to stem around some sort of DoCmd and/or data requery (which
happens in the Form_Current event via Linked Child/Master on 2nd
subform). The problem is that the error is not trappable. I'm not
sure what to do next as this long-term problem has me perplexed and
it all started with a simple upgrade to Office 2003.

David

Did you try temporarily commenting out the Form_Current code, to see
if that avoids the crash?


Allen,
All subforms have a text box with the RecordID (foreign key) in it
and all
are not visible because it means nothing to the user.

Let me add one more thing. Whenever I look at the application
Event Log it
always fails on msjtes40.dll which, I think, is Microsoft Jet
Expression
something.

David

Okay, David, you could test if the Form_Current code triggers the
problem
by
commenting it out and see if the crash still occurs.

There is another issue that could be involved here though. There
is a flaw
in A2002 and 2003 that I never saw in A2000, so the conversion may
have
triggered it. It occurs if the field named in LinkChildFields is
not
represented by a text box in the subform. In that case, the
reference is
to
an object of type AccessField, and the bug seems to be with that
data
type.
If you put a text box on the subform, giving it the same Name and
Control
Source as the field named in LinkChildFields, the reference is
then to an
object of type Textbox and so you avoid the bug. I've not seen any
MS
documentation on this, but the symptoms have been reported by
several
users.

So, try this sequence:
1. Make sure Name AutoCorrect is off.
2. Open each subform in turn, and add a text box for the foreign
key
field.
3. Save.
4. Compact
5. Decompile
6. Compact

Solved?

No..let me explain. The value of LinkChildFields and
LinkMasterFields
is
alway set to RecordID and it is on a different subform then the
one that
is blowing up. It is part of the same tab control, but are
different
subforms. Subform1 sets the value of RecordID on the form that
the tab
control is on (this form is unbound). Subform2 is under the 2nd
tab of
this control and it has it's field named RecordID linked to the
master
form. Think of it as master form (unbound) with a 2-tab
control. Tab1
shows the orders subform and tab 2 shows the order detail. So
when I
click on a record in subform1 it sets the value to link to
subform2 so
that when the 2nd tab is clicked it shows me the order details
for that
order. Now, you should also know that there are 2 more tabs
with other
sets of orders, that is why I cannot link subform2 to value in
subform1.

David

That seems to be a logic problem.

Why set the value of the text box on the main form?
The subform record won't load unless it already maches.

Changing the value of the LinkMasterFields causes Access to
reload the
subform, which re-triggers Form_Current, and so you enter this
endless
cycle that crashes Access. The logic is wrong.


You may be onto something there. Yes, one of the subforms has
link
child/master settings so that the correct records appear for
that
RecordID. Any suggestions on how to set/reset? Can I do it on
the
Change event of the tab control? The subform on the 2nd tab
has
LinkMaster and Child set to the RecordID. Thanks.

David
Are RepairOrderID or RecordID named in the LinkMasterFields
of the
subform control? If so, that could trigger a crash: whenever
the
subform's Form_Current event fires, it triggers a change in
the
subform's records which forces it to reload its records,
which
re-triggers Form_Current, which retriggers the cycle ad
infinitum.

If that's not the case, you might consider getting Access to
rebuild
the database. Create a new (blank) database, turning Name
AutoCorrect
off, and then import everything into the new database (File |
Get
External | Import)


Nope, still blowing. It seems to be limited to when on
subform gets
the focus and probably during the Current event of that
subform
because that is the only place where there is code on that
form.
Below is the code in the Current event of that subform:

Private Sub Form_Current()
On Error Resume Next

'put RecordID, RepairOrderID and EstimateID on parent
form
Forms![frmOrderLists]![RepairOrderID] = Me.RepairOrderID
Forms![frmOrderLists]![RecordID] = Me.RecordID

'put RecordID into hidden field on frmMain for other
processing
that needs to know/use current RecordID
Forms![frmMain]![RecordID] = Me.RecordID

End Sub

As you can see there is nothing special. 3 other items of
note.
1. This is 1 or 4 subforms on a tab control.
2. There is a "Conditional formatting" on some of the
controls on
the
subform (but it does not blow up when you first display the
subform,
only when you return to it (and not always then either).
3. We are using ODBC attached tables to SQL Server 2000.

As I mentioned, this application ran fine in Office (Access)
2000
and
the problems started immediately after conversion and
upgrade to
Office 2003.

David

Access 2000 uses a different binary than Access 2003 does.
Use
Access
2000 to decompile a copy of the database by entering
something like
this at the command prompt while Access is not running. It
is all
one
line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe"
/decompile
"c:\MyPath\MyDatabase.mdb"

Still in Access 2000, uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://members.iinet.net.au/~allenbrowne/bug-03.html

Then compact the database:
Tools | Database Utilities | Compact

Now, assuming all your users are on Access 2003:
1. Use A2003 to create a new (blank) database.

2. Turn off Name AutoCorrect in this new database.

3. Import everything: File | Get External | Import

4. Open a code window, and set minimal references. More
info:
http://members.iinet.net.au/~allenbrowne/ser-38.html
Then make sure the code compiles:
Debug | Compile

At this point, you should have a stable A2003 database. If
not,
post
more information about the point at which the crash
happens.

We converted an application from Access 2000 to Access
2003 and
now
we get
the ultimate Microsoft "crash" stating "Microsoft Office
Access
has
encountered a problem and needs to close. etc. etc." We
did not
get
these
in the 2000 application that has been running for 2 years.
How do
I
trace
this problem as I do not get an error code. My users want
to go
back to
Office 2000 and I don't blame them. How can "upgrading"
be worse
than the
old? Is there a way to force the error message rather
than this
dubious
"nothing" message? Thanks.

David

 

[Allen Browne]

I don't see any problem with using the full reference (except that it will
break of the form is renamed).

Do you still have the reference to the TechNet article?

 

[David C]

It was Q324582. I was searching on LinkChildFields and ran accross this KB
entry and it referred to a form control.

David

I don't see any problem with using the full reference (except that it will
break of the form is renamed).

Do you still have the reference to the TechNet article?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

OK, I'll try that. Also, I found a known problem in TechNet that
references Access 2002 but may apply to 2003 also. It suggests that the
form control reference not just use the control name, but full forms
expression. For example, on the detail subform control, the
LinkMasterFields references [Forms]![frmOrderLists]![RecordID] and the
LinkChildFields refers to the bound control on the subform named
RecordID. It still works this way, but do you see any problems with this?

David