Opening report hangs Access 2007 but not Access 2003

[Nick Meyer]

I have a mature app created in Access 2003. It works perfectly fine.
Opening a certain report in Access 2007 hangs the app. It's a little hard to
troubleshoot a hung app. I have created a demo app (~5MB) that shows the
problem. How do I post it, and get some help resolving the issue/issues that
Access 2007 has?


Nick67

 

[Allen Browne]

What can you tell us about this report, Nick?

Can you open it in design view?
Is there any code in the report's module?

There is a known issue in A2007 with reports that assign fields in
Report_Open. It's like:
http://support.microsoft.com/kb/927536
but the issue is not limited to Layout view.

 

[Nick Meyer]

I have separated the required objects out and put them into a DB that I can
post. It will open in every mode EXCEPT print preview in Access 2007
(although just what use Layout and Report views will be remains to be
seen...with this particular report, those views look so garbled that I
wouldn't touch an object in them ) The report's behavior is this: Both
the report and one of its sub-report use the same query for a datasource.
That query returns records, yet the print preview throws an error claiming
'no cuurent record' for the subreport. The debugger opens. A truly annoying
behavior then happens. Mousing over any variable that should have been set
by the recordset generates a popup 'no current record' (how do I turn that
off--its very annoying). When the debugger is closed, a message occurs that
a failure in a routine is preventing the report from being formatted, and the
app hangs. I will check the kb article you mentioned.

 

[Nick Meyer]

I looked at the kb article but it doesn't really seem applicable. No fields
get set by the report_open event of either the report or the sub-report, and
commenting out the code in either of those two events (which deals mostly
with default dynamic placement of controls on the report) does not change the
behavior. Commenting out the code in the detail_format event of the subform
DOES allow the report to open--in some ways that is not unexpected, as the
run-time errors thrown deal with 'no current record' in the detail_format
event. The thing is there SHOULD be a current record as the form and subform
share the same data source. If there was no record, nothing should open, not
just the subform. Just in case it was some weirdness with both using the
same query, I copied and renamed the query so two different objects are in
use--no change. It doesn't format, and closing the debugger hangs the app.

 

[Allen Browne]

Okay, so you have identified that the problem does relate to the code in the
report's events. (Access doesn't fire this code in Report or Layout view.)

You might check the LinkMasterFields/LinkChilds properties of the subreport
control to ensure this is correct.

The No Current Record error could be triggered by some code that deleted the
current record (unlikely.) It could also be bogus. For example, if the
report's source is a query that uses outer joins, and the table has any
yes/no fields, you may have hit this bug:
Outer join queries fail on Yes/No fields
at:
http://allenbrowne.com/bug-14.html

If the main report and subreport are both the same and filtered to the same
record, you may be able to dump the subreport. Instead, you could create a
Group Footer on the primary key field within the main report. Of course this
will occur for every record, and you could place whatever you have in the
subreport in this section of the main report instead.

 

[Nick Meyer]

You know, I have never really understood what is meant by an 'outer join.' I
see errors occasionally regarding "Access cannot evaluate ambigous outer
joins, do one first and then another...' but other than that, I never see
that syntax.

The query in question is:

SELECT tblInsDetails.JobID, tblInsDetails.OwnerID,
tblInsDetails.ConsigneeID, tblInsDetails.InsTypeID,
tblInsDetails.InsLocationID, tblInsDetails.InspectorID,
tblInsDetails.InspectorID2, tblInsDetails.InspectorID3,
tblInsDetails.InspectorID4, tblInsDetails.InspectorID5, tblInsDetails.Date,
tblInsDetails.[WO#], tblInsDetails.[PO#], tblInsTypes.InsTypeName,
tblInsTypes.[Eng Certification], tblInsTypes.[Capacity Rating],
tblCertResults.Item, tblCertResults.Make, tblCertResults.SerialNumber,
tblCertResults.Model, tblCertResults.Unit, tblCertResults.Capacity,
tblCertResults.Comments, tblInsDetails.WOPrinted,
tblCertResults.[Re-CertPeriod], tblCertResults.RP, tblCertResults.RepairedBy,
tblCertResults.LetterCertItem, tblClients.[Cert Address 1], tblClients.[Cert
Address 2], tblClients.[Cert City], tblClients.[Cert Province],
tblClients.[Cert Postal Code], tblInsDetails.EngPO,
tblInsDetails.CertPrinted, tblCertStatus.AdditionalInspectionInfo,
tblCertStatus.AdditionalDisclaimerInfo, tblCertStatus.AdditionalCapacityInfo,
tblCertResults.[10YearBoomCert], tblInsLocations.LocationName,
tblCertResults.GeneralEquipTypeID, tblClients_1.AllCertsSentHere,
tblClients_1.[Client Name], tblInsDetails.Notes

FROM ((tblClients AS tblClients_1 INNER JOIN ((tblInsTypes INNER JOIN
(tblClients INNER JOIN tblInsDetails ON tblClients.[Client ID] =
tblInsDetails.OwnerID) ON tblInsTypes.InsTypeID = tblInsDetails.InsTypeID)
INNER JOIN tblInsLocations ON tblInsDetails.InsLocationID =
tblInsLocations.InsLocationID) ON tblClients_1.[Client ID] =
tblInsDetails.ConsigneeID) INNER JOIN tblCertResults ON tblInsDetails.JobID =
tblCertResults.JobID) INNER JOIN tblCertStatus ON tblCertResults.ResultsID =
tblCertStatus.ResultsID

WHERE (((tblInsDetails.JobID)=[forms]![frmJobID]![jobid]));

There are Boolean fields invovled, but the SQL has no "outer join' in its
syntax. The sub report cannot be dumped, because, depending on the data, the
source object may get swapped out to a completely different subreport. Very
flexible, but it can sometimes get a little snaky debugging it when the
source object causing problems isn't the one you think it is None of that
is one the go with the record set involved here though. It is just weird.
The query loads data for the main report, heck, I put a docmd.openquery
"thatquery" line in the detail_format event that hangs the app--the query
loads, but the subreport still insists there is no current record. The child
and master fields are good. They are linked by the autonumber field of the
main lookup table--no better pedigree than that. I'll look at the arrticle
you mentioned.

Thanks
Nick

 

[Nick Meyer]

There are no left or right joins in the query and in the sample db I made,
hoping to be able to submit it to MS to demo an inter-version bug that needs
fixing, there is actually only one record. And none of the boolean fields
contain a null value.

Nick

 

[Allen Browne]

Okay: no outer joins, so the yes/no fields will not cause the problem.

You are actually changing the SourceObject of the subreport in the events of
the main report? That would have to be the number one suspect for what is
crashing Access 2007. Could you comment out that code temporarily, and see
if the crashes stop?

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

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

You know, I have never really understood what is meant by an 'outer join.'
I
see errors occasionally regarding "Access cannot evaluate ambigous outer
joins, do one first and then another...' but other than that, I never see
that syntax.

The query in question is:

SELECT tblInsDetails.JobID, tblInsDetails.OwnerID,
tblInsDetails.ConsigneeID, tblInsDetails.InsTypeID,
tblInsDetails.InsLocationID, tblInsDetails.InspectorID,
tblInsDetails.InspectorID2, tblInsDetails.InspectorID3,
tblInsDetails.InspectorID4, tblInsDetails.InspectorID5,
tblInsDetails.Date,
tblInsDetails.[WO#], tblInsDetails.[PO#], tblInsTypes.InsTypeName,
tblInsTypes.[Eng Certification], tblInsTypes.[Capacity Rating],
tblCertResults.Item, tblCertResults.Make, tblCertResults.SerialNumber,
tblCertResults.Model, tblCertResults.Unit, tblCertResults.Capacity,
tblCertResults.Comments, tblInsDetails.WOPrinted,
tblCertResults.[Re-CertPeriod], tblCertResults.RP,
tblCertResults.RepairedBy,
tblCertResults.LetterCertItem, tblClients.[Cert Address 1],
tblClients.[Cert
Address 2], tblClients.[Cert City], tblClients.[Cert Province],
tblClients.[Cert Postal Code], tblInsDetails.EngPO,
tblInsDetails.CertPrinted, tblCertStatus.AdditionalInspectionInfo,
tblCertStatus.AdditionalDisclaimerInfo,
tblCertStatus.AdditionalCapacityInfo,
tblCertResults.[10YearBoomCert], tblInsLocations.LocationName,
tblCertResults.GeneralEquipTypeID, tblClients_1.AllCertsSentHere,
tblClients_1.[Client Name], tblInsDetails.Notes

FROM ((tblClients AS tblClients_1 INNER JOIN ((tblInsTypes INNER JOIN
(tblClients INNER JOIN tblInsDetails ON tblClients.[Client ID] =
tblInsDetails.OwnerID) ON tblInsTypes.InsTypeID = tblInsDetails.InsTypeID)
INNER JOIN tblInsLocations ON tblInsDetails.InsLocationID =
tblInsLocations.InsLocationID) ON tblClients_1.[Client ID] =
tblInsDetails.ConsigneeID) INNER JOIN tblCertResults ON
tblInsDetails.JobID =
tblCertResults.JobID) INNER JOIN tblCertStatus ON tblCertResults.ResultsID
=
tblCertStatus.ResultsID

WHERE (((tblInsDetails.JobID)=[forms]![frmJobID]![jobid]));

There are Boolean fields invovled, but the SQL has no "outer join' in its
syntax. The sub report cannot be dumped, because, depending on the data,
the
source object may get swapped out to a completely different subreport.
Very
flexible, but it can sometimes get a little snaky debugging it when the
source object causing problems isn't the one you think it is None of
that
is one the go with the record set involved here though. It is just weird.
The query loads data for the main report, heck, I put a docmd.openquery
"thatquery" line in the detail_format event that hangs the app--the query
loads, but the subreport still insists there is no current record. The
child
and master fields are good. They are linked by the autonumber field of
the
main lookup table--no better pedigree than that. I'll look at the
arrticle
you mentioned.

Thanks
Nick

Okay, so you have identified that the problem does relate to the code in
the
report's events. (Access doesn't fire this code in Report or Layout
view.)

You might check the LinkMasterFields/LinkChilds properties of the
subreport
control to ensure this is correct.

The No Current Record error could be triggered by some code that deleted
the
current record (unlikely.) It could also be bogus. For example, if the
report's source is a query that uses outer joins, and the table has any
yes/no fields, you may have hit this bug:
Outer join queries fail on Yes/No fields
at:
http://allenbrowne.com/bug-14.html

If the main report and subreport are both the same and filtered to the
same
record, you may be able to dump the subreport. Instead, you could create
a
Group Footer on the primary key field within the main report. Of course
this
will occur for every record, and you could place whatever you have in the
subreport in this section of the main report instead.

 

[Nick Meyer]

I'll try that, but note that while the report is capable of changing the
SourceObject, it is not actually doing so in this case. Depending on the
data involved, the original subreport has 80% or more of its fields as "N/A"
and actually needs to display data from a different table. The most elegant
way to handle this is to test for a tell-tale value and adjust the
SourceObject accordingly. Works without a hitch in Access 2003. But
something gets VERY upset in Access 2007. I'll let you know how it goes.

Nick

 

[Nick Meyer]

I misspoke. While I have a report that does the SourceObject changing, it is
related to this report, and not ACTUALLY this report. My bad.
This report has a group header with siz sub reports in it. In this
particular case, five of those reports have .NoData = true and do not factor
into the problem. Code for the GroupHeader0_Format code is as follows:

If Me.Pages > 1 Then
Call TwoPageReportFix
End If

If MovedOnce <> 0 Then Exit Sub
Dim ctrl As Control
Dim OrderedControls(6) As String
Dim x As Integer



OrderedControls(0) = Me.subRegularCertDetails.Name
OrderedControls(1) = Me.subBoomInfo.Name
OrderedControls(2) = Me.subSideboomDetails.Name
OrderedControls(3) = Me.subOverheadReport.Name
OrderedControls(4) = Me.subBlockInfo.Name
OrderedControls(5) = Me.subManbasketInfo.Name
OrderedControls(6) = Me.subCapacities.Name
'
'For x = 0 To 6
' With Me.Controls(OrderedControls(x))
' .Visible = True 'screw with visible controls only
' .Top = x * 0.0021 * 1440
' .Height = 0.0021 * 1440
' End With
'Next x

For x = 0 To 6
With Me.Controls(OrderedControls(x))
If .Report.HasData = False Then 'screw with visible controls only
.Top = 0
.Visible = False
.Height = 0
End If
End With
Next x


Me.GroupHeader0.Height = 0
MovedOnce = 1

The part of the code that is commented out allows the report to be displayed
in print preview without crashing the app (via the sub report being
erroreously tagged as having no current record ect ect)

Why, oh why, is this innocuous bit of code

'For x = 0 To 6
' With Me.Controls(OrderedControls(x))
' .Visible = True 'screw with visible controls only
' .Top = x * 0.0021 * 1440
' .Height = 0.0021 * 1440
' End With
'Next x

Making Access report that the subreport has no current record and crashing
the app? It makes no sense. All this code does is spread the controls out
so that they are not overlapping (CanGrow looks after final sizing) and
hiding and moving unused controls out of the way?

Nick

Okay: no outer joins, so the yes/no fields will not cause the problem.

You are actually changing the SourceObject of the subreport in the events of
the main report? That would have to be the number one suspect for what is
crashing Access 2007. Could you comment out that code temporarily, and see
if the crashes stop?

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

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

You know, I have never really understood what is meant by an 'outer join.'
I
see errors occasionally regarding "Access cannot evaluate ambigous outer
joins, do one first and then another...' but other than that, I never see
that syntax.

The query in question is:

SELECT tblInsDetails.JobID, tblInsDetails.OwnerID,
tblInsDetails.ConsigneeID, tblInsDetails.InsTypeID,
tblInsDetails.InsLocationID, tblInsDetails.InspectorID,
tblInsDetails.InspectorID2, tblInsDetails.InspectorID3,
tblInsDetails.InspectorID4, tblInsDetails.InspectorID5,
tblInsDetails.Date,
tblInsDetails.[WO#], tblInsDetails.[PO#], tblInsTypes.InsTypeName,
tblInsTypes.[Eng Certification], tblInsTypes.[Capacity Rating],
tblCertResults.Item, tblCertResults.Make, tblCertResults.SerialNumber,
tblCertResults.Model, tblCertResults.Unit, tblCertResults.Capacity,
tblCertResults.Comments, tblInsDetails.WOPrinted,
tblCertResults.[Re-CertPeriod], tblCertResults.RP,
tblCertResults.RepairedBy,
tblCertResults.LetterCertItem, tblClients.[Cert Address 1],
tblClients.[Cert
Address 2], tblClients.[Cert City], tblClients.[Cert Province],
tblClients.[Cert Postal Code], tblInsDetails.EngPO,
tblInsDetails.CertPrinted, tblCertStatus.AdditionalInspectionInfo,
tblCertStatus.AdditionalDisclaimerInfo,
tblCertStatus.AdditionalCapacityInfo,
tblCertResults.[10YearBoomCert], tblInsLocations.LocationName,
tblCertResults.GeneralEquipTypeID, tblClients_1.AllCertsSentHere,
tblClients_1.[Client Name], tblInsDetails.Notes

FROM ((tblClients AS tblClients_1 INNER JOIN ((tblInsTypes INNER JOIN
(tblClients INNER JOIN tblInsDetails ON tblClients.[Client ID] =
tblInsDetails.OwnerID) ON tblInsTypes.InsTypeID = tblInsDetails.InsTypeID)
INNER JOIN tblInsLocations ON tblInsDetails.InsLocationID =
tblInsLocations.InsLocationID) ON tblClients_1.[Client ID] =
tblInsDetails.ConsigneeID) INNER JOIN tblCertResults ON
tblInsDetails.JobID =
tblCertResults.JobID) INNER JOIN tblCertStatus ON tblCertResults.ResultsID
=
tblCertStatus.ResultsID

WHERE (((tblInsDetails.JobID)=[forms]![frmJobID]![jobid]));

There are Boolean fields invovled, but the SQL has no "outer join' in its
syntax. The sub report cannot be dumped, because, depending on the data,
the
source object may get swapped out to a completely different subreport.
Very
flexible, but it can sometimes get a little snaky debugging it when the
source object causing problems isn't the one you think it is None of
that
is one the go with the record set involved here though. It is just weird.
The query loads data for the main report, heck, I put a docmd.openquery
"thatquery" line in the detail_format event that hangs the app--the query
loads, but the subreport still insists there is no current record. The
child
and master fields are good. They are linked by the autonumber field of
the
main lookup table--no better pedigree than that. I'll look at the
arrticle
you mentioned.

Thanks
Nick

Okay, so you have identified that the problem does relate to the code in
the
report's events. (Access doesn't fire this code in Report or Layout
view.)

You might check the LinkMasterFields/LinkChilds properties of the
subreport
control to ensure this is correct.

The No Current Record error could be triggered by some code that deleted
the
current record (unlikely.) It could also be bogus. For example, if the
report's source is a query that uses outer joins, and the table has any
yes/no fields, you may have hit this bug:
Outer join queries fail on Yes/No fields
at:
http://allenbrowne.com/bug-14.html

If the main report and subreport are both the same and filtered to the
same
record, you may be able to dump the subreport. Instead, you could create
a
Group Footer on the primary key field within the main report. Of course
this
will occur for every record, and you could place whatever you have in the
subreport in this section of the main report instead.

I have separated the required objects out and put them into a DB that I
can
post. It will open in every mode EXCEPT print preview in Access 2007
(although just what use Layout and Report views will be remains to be
seen...with this particular report, those views look so garbled that I
wouldn't touch an object in them ) The report's behavior is this:
Both
the report and one of its sub-report use the same query for a
datasource.
That query returns records, yet the print preview throws an error
claiming
'no cuurent record' for the subreport. The debugger opens. A truly
annoying
behavior then happens. Mousing over any variable that should have been
set
by the recordset generates a popup 'no current record' (how do I turn
that
off--its very annoying). When the debugger is closed, a message occurs
that
a failure in a routine is preventing the report from being formatted,
and
the
app hangs. I will check the kb article you mentioned.

 

[Nick Meyer]

Now, with that portion of the code commented out, I can get the report to
Print Preview. Attempting to right-click and move to Design View immediately
crashes the app, however....

Nick

Okay: no outer joins, so the yes/no fields will not cause the problem.

You are actually changing the SourceObject of the subreport in the events of
the main report? That would have to be the number one suspect for what is
crashing Access 2007. Could you comment out that code temporarily, and see
if the crashes stop?

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

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

You know, I have never really understood what is meant by an 'outer join.'
I
see errors occasionally regarding "Access cannot evaluate ambigous outer
joins, do one first and then another...' but other than that, I never see
that syntax.

The query in question is:

SELECT tblInsDetails.JobID, tblInsDetails.OwnerID,
tblInsDetails.ConsigneeID, tblInsDetails.InsTypeID,
tblInsDetails.InsLocationID, tblInsDetails.InspectorID,
tblInsDetails.InspectorID2, tblInsDetails.InspectorID3,
tblInsDetails.InspectorID4, tblInsDetails.InspectorID5,
tblInsDetails.Date,
tblInsDetails.[WO#], tblInsDetails.[PO#], tblInsTypes.InsTypeName,
tblInsTypes.[Eng Certification], tblInsTypes.[Capacity Rating],
tblCertResults.Item, tblCertResults.Make, tblCertResults.SerialNumber,
tblCertResults.Model, tblCertResults.Unit, tblCertResults.Capacity,
tblCertResults.Comments, tblInsDetails.WOPrinted,
tblCertResults.[Re-CertPeriod], tblCertResults.RP,
tblCertResults.RepairedBy,
tblCertResults.LetterCertItem, tblClients.[Cert Address 1],
tblClients.[Cert
Address 2], tblClients.[Cert City], tblClients.[Cert Province],
tblClients.[Cert Postal Code], tblInsDetails.EngPO,
tblInsDetails.CertPrinted, tblCertStatus.AdditionalInspectionInfo,
tblCertStatus.AdditionalDisclaimerInfo,
tblCertStatus.AdditionalCapacityInfo,
tblCertResults.[10YearBoomCert], tblInsLocations.LocationName,
tblCertResults.GeneralEquipTypeID, tblClients_1.AllCertsSentHere,
tblClients_1.[Client Name], tblInsDetails.Notes

FROM ((tblClients AS tblClients_1 INNER JOIN ((tblInsTypes INNER JOIN
(tblClients INNER JOIN tblInsDetails ON tblClients.[Client ID] =
tblInsDetails.OwnerID) ON tblInsTypes.InsTypeID = tblInsDetails.InsTypeID)
INNER JOIN tblInsLocations ON tblInsDetails.InsLocationID =
tblInsLocations.InsLocationID) ON tblClients_1.[Client ID] =
tblInsDetails.ConsigneeID) INNER JOIN tblCertResults ON
tblInsDetails.JobID =
tblCertResults.JobID) INNER JOIN tblCertStatus ON tblCertResults.ResultsID
=
tblCertStatus.ResultsID

WHERE (((tblInsDetails.JobID)=[forms]![frmJobID]![jobid]));

There are Boolean fields invovled, but the SQL has no "outer join' in its
syntax. The sub report cannot be dumped, because, depending on the data,
the
source object may get swapped out to a completely different subreport.
Very
flexible, but it can sometimes get a little snaky debugging it when the
source object causing problems isn't the one you think it is None of
that
is one the go with the record set involved here though. It is just weird.
The query loads data for the main report, heck, I put a docmd.openquery
"thatquery" line in the detail_format event that hangs the app--the query
loads, but the subreport still insists there is no current record. The
child
and master fields are good. They are linked by the autonumber field of
the
main lookup table--no better pedigree than that. I'll look at the
arrticle
you mentioned.

Thanks
Nick

Okay, so you have identified that the problem does relate to the code in
the
report's events. (Access doesn't fire this code in Report or Layout
view.)

You might check the LinkMasterFields/LinkChilds properties of the
subreport
control to ensure this is correct.

The No Current Record error could be triggered by some code that deleted
the
current record (unlikely.) It could also be bogus. For example, if the
report's source is a query that uses outer joins, and the table has any
yes/no fields, you may have hit this bug:
Outer join queries fail on Yes/No fields
at:
http://allenbrowne.com/bug-14.html

If the main report and subreport are both the same and filtered to the
same
record, you may be able to dump the subreport. Instead, you could create
a
Group Footer on the primary key field within the main report. Of course
this
will occur for every record, and you could place whatever you have in the
subreport in this section of the main report instead.

I have separated the required objects out and put them into a DB that I
can
post. It will open in every mode EXCEPT print preview in Access 2007
(although just what use Layout and Report views will be remains to be
seen...with this particular report, those views look so garbled that I
wouldn't touch an object in them ) The report's behavior is this:
Both
the report and one of its sub-report use the same query for a
datasource.
That query returns records, yet the print preview throws an error
claiming
'no cuurent record' for the subreport. The debugger opens. A truly
annoying
behavior then happens. Mousing over any variable that should have been
set
by the recordset generates a popup 'no current record' (how do I turn
that
off--its very annoying). When the debugger is closed, a message occurs
that
a failure in a routine is preventing the report from being formatted,
and
the
app hangs. I will check the kb article you mentioned.

 

[Allen Browne]

Okay, we know A2007 is very flakey in this area, so it sounds like you are
progressively zero-ing in on the code that is triggering the crash.

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

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

Now, with that portion of the code commented out, I can get the report to
Print Preview. Attempting to right-click and move to Design View
immediately
crashes the app, however....

Nick

Okay: no outer joins, so the yes/no fields will not cause the problem.

You are actually changing the SourceObject of the subreport in the events
of
the main report? That would have to be the number one suspect for what is
crashing Access 2007. Could you comment out that code temporarily, and
see
if the crashes stop?

You know, I have never really understood what is meant by an 'outer
join.'
I
see errors occasionally regarding "Access cannot evaluate ambigous
outer
joins, do one first and then another...' but other than that, I never
see
that syntax.

The query in question is:

SELECT tblInsDetails.JobID, tblInsDetails.OwnerID,
tblInsDetails.ConsigneeID, tblInsDetails.InsTypeID,
tblInsDetails.InsLocationID, tblInsDetails.InspectorID,
tblInsDetails.InspectorID2, tblInsDetails.InspectorID3,
tblInsDetails.InspectorID4, tblInsDetails.InspectorID5,
tblInsDetails.Date,
tblInsDetails.[WO#], tblInsDetails.[PO#], tblInsTypes.InsTypeName,
tblInsTypes.[Eng Certification], tblInsTypes.[Capacity Rating],
tblCertResults.Item, tblCertResults.Make, tblCertResults.SerialNumber,
tblCertResults.Model, tblCertResults.Unit, tblCertResults.Capacity,
tblCertResults.Comments, tblInsDetails.WOPrinted,
tblCertResults.[Re-CertPeriod], tblCertResults.RP,
tblCertResults.RepairedBy,
tblCertResults.LetterCertItem, tblClients.[Cert Address 1],
tblClients.[Cert
Address 2], tblClients.[Cert City], tblClients.[Cert Province],
tblClients.[Cert Postal Code], tblInsDetails.EngPO,
tblInsDetails.CertPrinted, tblCertStatus.AdditionalInspectionInfo,
tblCertStatus.AdditionalDisclaimerInfo,
tblCertStatus.AdditionalCapacityInfo,
tblCertResults.[10YearBoomCert], tblInsLocations.LocationName,
tblCertResults.GeneralEquipTypeID, tblClients_1.AllCertsSentHere,
tblClients_1.[Client Name], tblInsDetails.Notes

FROM ((tblClients AS tblClients_1 INNER JOIN ((tblInsTypes INNER JOIN
(tblClients INNER JOIN tblInsDetails ON tblClients.[Client ID] =
tblInsDetails.OwnerID) ON tblInsTypes.InsTypeID =
tblInsDetails.InsTypeID)
INNER JOIN tblInsLocations ON tblInsDetails.InsLocationID =
tblInsLocations.InsLocationID) ON tblClients_1.[Client ID] =
tblInsDetails.ConsigneeID) INNER JOIN tblCertResults ON
tblInsDetails.JobID =
tblCertResults.JobID) INNER JOIN tblCertStatus ON
tblCertResults.ResultsID
=
tblCertStatus.ResultsID

WHERE (((tblInsDetails.JobID)=[forms]![frmJobID]![jobid]));

There are Boolean fields invovled, but the SQL has no "outer join' in
its
syntax. The sub report cannot be dumped, because, depending on the
data,
the
source object may get swapped out to a completely different subreport.
Very
flexible, but it can sometimes get a little snaky debugging it when the
source object causing problems isn't the one you think it is None
of
that
is one the go with the record set involved here though. It is just
weird.
The query loads data for the main report, heck, I put a docmd.openquery
"thatquery" line in the detail_format event that hangs the app--the
query
loads, but the subreport still insists there is no current record. The
child
and master fields are good. They are linked by the autonumber field of
the
main lookup table--no better pedigree than that. I'll look at the
arrticle
you mentioned.

Thanks
Nick

:

Okay, so you have identified that the problem does relate to the code
in
the
report's events. (Access doesn't fire this code in Report or Layout
view.)

You might check the LinkMasterFields/LinkChilds properties of the
subreport
control to ensure this is correct.

The No Current Record error could be triggered by some code that
deleted
the
current record (unlikely.) It could also be bogus. For example, if the
report's source is a query that uses outer joins, and the table has
any
yes/no fields, you may have hit this bug:
Outer join queries fail on Yes/No fields
at:
http://allenbrowne.com/bug-14.html

If the main report and subreport are both the same and filtered to the
same
record, you may be able to dump the subreport. Instead, you could
create
a
Group Footer on the primary key field within the main report. Of
course
this
will occur for every record, and you could place whatever you have in
the
subreport in this section of the main report instead.

I have separated the required objects out and put them into a DB that
I
can
post. It will open in every mode EXCEPT print preview in Access
2007
(although just what use Layout and Report views will be remains to
be
seen...with this particular report, those views look so garbled that
I
wouldn't touch an object in them ) The report's behavior is this:
Both
the report and one of its sub-report use the same query for a
datasource.
That query returns records, yet the print preview throws an error
claiming
'no cuurent record' for the subreport. The debugger opens. A truly
annoying
behavior then happens. Mousing over any variable that should have
been
set
by the recordset generates a popup 'no current record' (how do I
turn
that
off--its very annoying). When the debugger is closed, a message
occurs
that
a failure in a routine is preventing the report from being
formatted,
and
the
app hangs. I will check the kb article you mentioned.

 

[Nick Meyer]

Very flakey in what area?
Why should some every simple code that moves a few controls around

'For x = 0 To 6
' With Me.Controls(OrderedControls(x))
' .Visible = True 'screw with visible controls only
' .Top = x * 0.0021 * 1440
' .Height = 0.0021 * 1440
' End With
'Next x

cause one of the control to open without its valid recordset, and hang the
debugger and app?

Nick

Okay, we know A2007 is very flakey in this area, so it sounds like you are
progressively zero-ing in on the code that is triggering the crash.

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

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

Now, with that portion of the code commented out, I can get the report to
Print Preview. Attempting to right-click and move to Design View
immediately
crashes the app, however....

Nick

Okay: no outer joins, so the yes/no fields will not cause the problem.

You are actually changing the SourceObject of the subreport in the events
of
the main report? That would have to be the number one suspect for what is
crashing Access 2007. Could you comment out that code temporarily, and
see
if the crashes stop?

You know, I have never really understood what is meant by an 'outer
join.'
I
see errors occasionally regarding "Access cannot evaluate ambigous
outer
joins, do one first and then another...' but other than that, I never
see
that syntax.

The query in question is:

SELECT tblInsDetails.JobID, tblInsDetails.OwnerID,
tblInsDetails.ConsigneeID, tblInsDetails.InsTypeID,
tblInsDetails.InsLocationID, tblInsDetails.InspectorID,
tblInsDetails.InspectorID2, tblInsDetails.InspectorID3,
tblInsDetails.InspectorID4, tblInsDetails.InspectorID5,
tblInsDetails.Date,
tblInsDetails.[WO#], tblInsDetails.[PO#], tblInsTypes.InsTypeName,
tblInsTypes.[Eng Certification], tblInsTypes.[Capacity Rating],
tblCertResults.Item, tblCertResults.Make, tblCertResults.SerialNumber,
tblCertResults.Model, tblCertResults.Unit, tblCertResults.Capacity,
tblCertResults.Comments, tblInsDetails.WOPrinted,
tblCertResults.[Re-CertPeriod], tblCertResults.RP,
tblCertResults.RepairedBy,
tblCertResults.LetterCertItem, tblClients.[Cert Address 1],
tblClients.[Cert
Address 2], tblClients.[Cert City], tblClients.[Cert Province],
tblClients.[Cert Postal Code], tblInsDetails.EngPO,
tblInsDetails.CertPrinted, tblCertStatus.AdditionalInspectionInfo,
tblCertStatus.AdditionalDisclaimerInfo,
tblCertStatus.AdditionalCapacityInfo,
tblCertResults.[10YearBoomCert], tblInsLocations.LocationName,
tblCertResults.GeneralEquipTypeID, tblClients_1.AllCertsSentHere,
tblClients_1.[Client Name], tblInsDetails.Notes

FROM ((tblClients AS tblClients_1 INNER JOIN ((tblInsTypes INNER JOIN
(tblClients INNER JOIN tblInsDetails ON tblClients.[Client ID] =
tblInsDetails.OwnerID) ON tblInsTypes.InsTypeID =
tblInsDetails.InsTypeID)
INNER JOIN tblInsLocations ON tblInsDetails.InsLocationID =
tblInsLocations.InsLocationID) ON tblClients_1.[Client ID] =
tblInsDetails.ConsigneeID) INNER JOIN tblCertResults ON
tblInsDetails.JobID =
tblCertResults.JobID) INNER JOIN tblCertStatus ON
tblCertResults.ResultsID
=
tblCertStatus.ResultsID

WHERE (((tblInsDetails.JobID)=[forms]![frmJobID]![jobid]));

There are Boolean fields invovled, but the SQL has no "outer join' in
its
syntax. The sub report cannot be dumped, because, depending on the
data,
the
source object may get swapped out to a completely different subreport.
Very
flexible, but it can sometimes get a little snaky debugging it when the
source object causing problems isn't the one you think it is None
of
that
is one the go with the record set involved here though. It is just
weird.
The query loads data for the main report, heck, I put a docmd.openquery
"thatquery" line in the detail_format event that hangs the app--the
query
loads, but the subreport still insists there is no current record. The
child
and master fields are good. They are linked by the autonumber field of
the
main lookup table--no better pedigree than that. I'll look at the
arrticle
you mentioned.

Thanks
Nick

:

Okay, so you have identified that the problem does relate to the code
in
the
report's events. (Access doesn't fire this code in Report or Layout
view.)

You might check the LinkMasterFields/LinkChilds properties of the
subreport
control to ensure this is correct.

The No Current Record error could be triggered by some code that
deleted
the
current record (unlikely.) It could also be bogus. For example, if the
report's source is a query that uses outer joins, and the table has
any
yes/no fields, you may have hit this bug:
Outer join queries fail on Yes/No fields
at:
http://allenbrowne.com/bug-14.html

If the main report and subreport are both the same and filtered to the
same
record, you may be able to dump the subreport. Instead, you could
create
a
Group Footer on the primary key field within the main report. Of
course
this
will occur for every record, and you could place whatever you have in
the
subreport in this section of the main report instead.

I have separated the required objects out and put them into a DB that
I
can
post. It will open in every mode EXCEPT print preview in Access
2007
(although just what use Layout and Report views will be remains to
be
seen...with this particular report, those views look so garbled that
I
wouldn't touch an object in them ) The report's behavior is this:
Both
the report and one of its sub-report use the same query for a
datasource.
That query returns records, yet the print preview throws an error
claiming
'no cuurent record' for the subreport. The debugger opens. A truly
annoying
behavior then happens. Mousing over any variable that should have
been
set
by the recordset generates a popup 'no current record' (how do I
turn
that
off--its very annoying). When the debugger is closed, a message
occurs
that
a failure in a routine is preventing the report from being
formatted,
and
the
app hangs. I will check the kb article you mentioned.

 

[Allen Browne]

Nick, the code as you presented it should not crash Access assuming that it
is in the Format event of the section that contains the 6 controls, and the
section is tall and wide enough to handle it.

But Access 2007 is very flakey, especially with code in the report events.
I'm aware of several scenarios that will bring it crashing down, so I'm not
surprised if you have another.

This assumes it's not merely a NameAutoCorrect bug, or bad binary
(decompile.)

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

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

Very flakey in what area?
Why should some every simple code that moves a few controls around

'For x = 0 To 6
' With Me.Controls(OrderedControls(x))
' .Visible = True 'screw with visible controls only
' .Top = x * 0.0021 * 1440
' .Height = 0.0021 * 1440
' End With
'Next x

cause one of the control to open without its valid recordset, and hang the
debugger and app?

Nick

Okay, we know A2007 is very flakey in this area, so it sounds like you
are
progressively zero-ing in on the code that is triggering the crash.

Now, with that portion of the code commented out, I can get the report
to
Print Preview. Attempting to right-click and move to Design View
immediately
crashes the app, however....

Nick

:

Okay: no outer joins, so the yes/no fields will not cause the problem.

You are actually changing the SourceObject of the subreport in the
events
of
the main report? That would have to be the number one suspect for what
is
crashing Access 2007. Could you comment out that code temporarily, and
see
if the crashes stop?

You know, I have never really understood what is meant by an 'outer
join.'
I
see errors occasionally regarding "Access cannot evaluate ambigous
outer
joins, do one first and then another...' but other than that, I
never
see
that syntax.

The query in question is:

SELECT tblInsDetails.JobID, tblInsDetails.OwnerID,
tblInsDetails.ConsigneeID, tblInsDetails.InsTypeID,
tblInsDetails.InsLocationID, tblInsDetails.InspectorID,
tblInsDetails.InspectorID2, tblInsDetails.InspectorID3,
tblInsDetails.InspectorID4, tblInsDetails.InspectorID5,
tblInsDetails.Date,
tblInsDetails.[WO#], tblInsDetails.[PO#], tblInsTypes.InsTypeName,
tblInsTypes.[Eng Certification], tblInsTypes.[Capacity Rating],
tblCertResults.Item, tblCertResults.Make,
tblCertResults.SerialNumber,
tblCertResults.Model, tblCertResults.Unit, tblCertResults.Capacity,
tblCertResults.Comments, tblInsDetails.WOPrinted,
tblCertResults.[Re-CertPeriod], tblCertResults.RP,
tblCertResults.RepairedBy,
tblCertResults.LetterCertItem, tblClients.[Cert Address 1],
tblClients.[Cert
Address 2], tblClients.[Cert City], tblClients.[Cert Province],
tblClients.[Cert Postal Code], tblInsDetails.EngPO,
tblInsDetails.CertPrinted, tblCertStatus.AdditionalInspectionInfo,
tblCertStatus.AdditionalDisclaimerInfo,
tblCertStatus.AdditionalCapacityInfo,
tblCertResults.[10YearBoomCert], tblInsLocations.LocationName,
tblCertResults.GeneralEquipTypeID, tblClients_1.AllCertsSentHere,
tblClients_1.[Client Name], tblInsDetails.Notes

FROM ((tblClients AS tblClients_1 INNER JOIN ((tblInsTypes INNER
JOIN
(tblClients INNER JOIN tblInsDetails ON tblClients.[Client ID] =
tblInsDetails.OwnerID) ON tblInsTypes.InsTypeID =
tblInsDetails.InsTypeID)
INNER JOIN tblInsLocations ON tblInsDetails.InsLocationID =
tblInsLocations.InsLocationID) ON tblClients_1.[Client ID] =
tblInsDetails.ConsigneeID) INNER JOIN tblCertResults ON
tblInsDetails.JobID =
tblCertResults.JobID) INNER JOIN tblCertStatus ON
tblCertResults.ResultsID
=
tblCertStatus.ResultsID

WHERE (((tblInsDetails.JobID)=[forms]![frmJobID]![jobid]));

There are Boolean fields invovled, but the SQL has no "outer join'
in
its
syntax. The sub report cannot be dumped, because, depending on the
data,
the
source object may get swapped out to a completely different
subreport.
Very
flexible, but it can sometimes get a little snaky debugging it when
the
source object causing problems isn't the one you think it is
None
of
that
is one the go with the record set involved here though. It is just
weird.
The query loads data for the main report, heck, I put a
docmd.openquery
"thatquery" line in the detail_format event that hangs the app--the
query
loads, but the subreport still insists there is no current record.
The
child
and master fields are good. They are linked by the autonumber field
of
the
main lookup table--no better pedigree than that. I'll look at the
arrticle
you mentioned.

Thanks
Nick

:

Okay, so you have identified that the problem does relate to the
code
in
the
report's events. (Access doesn't fire this code in Report or Layout
view.)

You might check the LinkMasterFields/LinkChilds properties of the
subreport
control to ensure this is correct.

The No Current Record error could be triggered by some code that
deleted
the
current record (unlikely.) It could also be bogus. For example, if
the
report's source is a query that uses outer joins, and the table has
any
yes/no fields, you may have hit this bug:
Outer join queries fail on Yes/No fields
at:
http://allenbrowne.com/bug-14.html

If the main report and subreport are both the same and filtered to
the
same
record, you may be able to dump the subreport. Instead, you could
create
a
Group Footer on the primary key field within the main report. Of
course
this
will occur for every record, and you could place whatever you have
in
the
subreport in this section of the main report instead.

I have separated the required objects out and put them into a DB
that
I
can
post. It will open in every mode EXCEPT print preview in Access
2007
(although just what use Layout and Report views will be remains
to
be
seen...with this particular report, those views look so garbled
that
I
wouldn't touch an object in them ) The report's behavior is
this:
Both
the report and one of its sub-report use the same query for a
datasource.
That query returns records, yet the print preview throws an error
claiming
'no cuurent record' for the subreport. The debugger opens. A
truly
annoying
behavior then happens. Mousing over any variable that should
have
been
set
by the recordset generates a popup 'no current record' (how do I
turn
that
off--its very annoying). When the debugger is closed, a message
occurs
that
a failure in a routine is preventing the report from being
formatted,
and
the
app hangs. I will check the kb article you mentioned.

 

[Nick Meyer]

It runs perfectly fine in Access 2003, so I don't see how there is anything
to do with bad binaries or NameAutoCorrect. I decompile the db on occasion
when it bloats more that compact and repair will take care of. The test db I
created was brand new, I just imported the objects to it, and it displays the
same behavior as the original. The damnable thing is that the code block
directly below it (from my post on the 22nd) is screwing with control
locations but doesn't kill the app. WTF. Now the problem. I have 3 new
machines to order, and O2003Pro OEM is NOT available anymore. That means
opening an OpenLicence agreement and getting O2003Pro media--and is gonna run
a couple hundred bucks more than OEM. Is this then a bona fide bug that I
have to HOPE gets fixed by SP1 whenever that comes around?

Access 2007 has been such a disappointment. Now, a show stopping bug.
Sigh. Any final advice?

Nick

Nick, the code as you presented it should not crash Access assuming that it
is in the Format event of the section that contains the 6 controls, and the
section is tall and wide enough to handle it.

But Access 2007 is very flakey, especially with code in the report events.
I'm aware of several scenarios that will bring it crashing down, so I'm not
surprised if you have another.

This assumes it's not merely a NameAutoCorrect bug, or bad binary
(decompile.)

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

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

Very flakey in what area?
Why should some every simple code that moves a few controls around

'For x = 0 To 6
' With Me.Controls(OrderedControls(x))
' .Visible = True 'screw with visible controls only
' .Top = x * 0.0021 * 1440
' .Height = 0.0021 * 1440
' End With
'Next x

cause one of the control to open without its valid recordset, and hang the
debugger and app?

Nick

Okay, we know A2007 is very flakey in this area, so it sounds like you
are
progressively zero-ing in on the code that is triggering the crash.

Now, with that portion of the code commented out, I can get the report
to
Print Preview. Attempting to right-click and move to Design View
immediately
crashes the app, however....

Nick

:

Okay: no outer joins, so the yes/no fields will not cause the problem.

You are actually changing the SourceObject of the subreport in the
events
of
the main report? That would have to be the number one suspect for what
is
crashing Access 2007. Could you comment out that code temporarily, and
see
if the crashes stop?

You know, I have never really understood what is meant by an 'outer
join.'
I
see errors occasionally regarding "Access cannot evaluate ambigous
outer
joins, do one first and then another...' but other than that, I
never
see
that syntax.

The query in question is:

SELECT tblInsDetails.JobID, tblInsDetails.OwnerID,
tblInsDetails.ConsigneeID, tblInsDetails.InsTypeID,
tblInsDetails.InsLocationID, tblInsDetails.InspectorID,
tblInsDetails.InspectorID2, tblInsDetails.InspectorID3,
tblInsDetails.InspectorID4, tblInsDetails.InspectorID5,
tblInsDetails.Date,
tblInsDetails.[WO#], tblInsDetails.[PO#], tblInsTypes.InsTypeName,
tblInsTypes.[Eng Certification], tblInsTypes.[Capacity Rating],
tblCertResults.Item, tblCertResults.Make,
tblCertResults.SerialNumber,
tblCertResults.Model, tblCertResults.Unit, tblCertResults.Capacity,
tblCertResults.Comments, tblInsDetails.WOPrinted,
tblCertResults.[Re-CertPeriod], tblCertResults.RP,
tblCertResults.RepairedBy,
tblCertResults.LetterCertItem, tblClients.[Cert Address 1],
tblClients.[Cert
Address 2], tblClients.[Cert City], tblClients.[Cert Province],
tblClients.[Cert Postal Code], tblInsDetails.EngPO,
tblInsDetails.CertPrinted, tblCertStatus.AdditionalInspectionInfo,
tblCertStatus.AdditionalDisclaimerInfo,
tblCertStatus.AdditionalCapacityInfo,
tblCertResults.[10YearBoomCert], tblInsLocations.LocationName,
tblCertResults.GeneralEquipTypeID, tblClients_1.AllCertsSentHere,
tblClients_1.[Client Name], tblInsDetails.Notes

FROM ((tblClients AS tblClients_1 INNER JOIN ((tblInsTypes INNER
JOIN
(tblClients INNER JOIN tblInsDetails ON tblClients.[Client ID] =
tblInsDetails.OwnerID) ON tblInsTypes.InsTypeID =
tblInsDetails.InsTypeID)
INNER JOIN tblInsLocations ON tblInsDetails.InsLocationID =
tblInsLocations.InsLocationID) ON tblClients_1.[Client ID] =
tblInsDetails.ConsigneeID) INNER JOIN tblCertResults ON
tblInsDetails.JobID =
tblCertResults.JobID) INNER JOIN tblCertStatus ON
tblCertResults.ResultsID
=
tblCertStatus.ResultsID

WHERE (((tblInsDetails.JobID)=[forms]![frmJobID]![jobid]));

There are Boolean fields invovled, but the SQL has no "outer join'
in
its
syntax. The sub report cannot be dumped, because, depending on the
data,
the
source object may get swapped out to a completely different
subreport.
Very
flexible, but it can sometimes get a little snaky debugging it when
the
source object causing problems isn't the one you think it is
None
of
that
is one the go with the record set involved here though. It is just
weird.
The query loads data for the main report, heck, I put a
docmd.openquery
"thatquery" line in the detail_format event that hangs the app--the
query
loads, but the subreport still insists there is no current record.
The
child
and master fields are good. They are linked by the autonumber field
of
the
main lookup table--no better pedigree than that. I'll look at the
arrticle
you mentioned.

Thanks
Nick

:

Okay, so you have identified that the problem does relate to the
code
in
the
report's events. (Access doesn't fire this code in Report or Layout
view.)

You might check the LinkMasterFields/LinkChilds properties of the
subreport
control to ensure this is correct.

The No Current Record error could be triggered by some code that
deleted
the
current record (unlikely.) It could also be bogus. For example, if
the
report's source is a query that uses outer joins, and the table has
any
yes/no fields, you may have hit this bug:
Outer join queries fail on Yes/No fields
at:
http://allenbrowne.com/bug-14.html

If the main report and subreport are both the same and filtered to
the
same
record, you may be able to dump the subreport. Instead, you could
create
a
Group Footer on the primary key field within the main report. Of
course
this
will occur for every record, and you could place whatever you have
in
the
subreport in this section of the main report instead.

I have separated the required objects out and put them into a DB
that
I
can
post. It will open in every mode EXCEPT print preview in Access
2007
(although just what use Layout and Report views will be remains
to
be
seen...with this particular report, those views look so garbled
that
I
wouldn't touch an object in them ) The report's behavior is
this:
Both
the report and one of its sub-report use the same query for a
datasource.
That query returns records, yet the print preview throws an error
claiming
'no cuurent record' for the subreport. The debugger opens. A
truly
annoying
behavior then happens. Mousing over any variable that should
have
been
set
by the recordset generates a popup 'no current record' (how do I
turn
that
off--its very annoying). When the debugger is closed, a message
occurs
that
a failure in a routine is preventing the report from being
formatted,
and
the
app hangs. I will check the kb article you mentioned.

 

[Allen Browne]

No further advice

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

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

It runs perfectly fine in Access 2003, so I don't see how there is
anything
to do with bad binaries or NameAutoCorrect. I decompile the db on
occasion
when it bloats more that compact and repair will take care of. The test
db I
created was brand new, I just imported the objects to it, and it displays
the
same behavior as the original. The damnable thing is that the code block
directly below it (from my post on the 22nd) is screwing with control
locations but doesn't kill the app. WTF. Now the problem. I have 3 new
machines to order, and O2003Pro OEM is NOT available anymore. That means
opening an OpenLicence agreement and getting O2003Pro media--and is gonna
run
a couple hundred bucks more than OEM. Is this then a bona fide bug that I
have to HOPE gets fixed by SP1 whenever that comes around?

Access 2007 has been such a disappointment. Now, a show stopping bug.
Sigh. Any final advice?

Nick

Nick, the code as you presented it should not crash Access assuming that
it
is in the Format event of the section that contains the 6 controls, and
the
section is tall and wide enough to handle it.

But Access 2007 is very flakey, especially with code in the report
events.
I'm aware of several scenarios that will bring it crashing down, so I'm
not
surprised if you have another.

This assumes it's not merely a NameAutoCorrect bug, or bad binary
(decompile.)

Very flakey in what area?
Why should some every simple code that moves a few controls around

'For x = 0 To 6
' With Me.Controls(OrderedControls(x))
' .Visible = True 'screw with visible controls only
' .Top = x * 0.0021 * 1440
' .Height = 0.0021 * 1440
' End With
'Next x

cause one of the control to open without its valid recordset, and hang
the
debugger and app?

Nick

:

Okay, we know A2007 is very flakey in this area, so it sounds like you
are
progressively zero-ing in on the code that is triggering the crash.

Now, with that portion of the code commented out, I can get the
report
to
Print Preview. Attempting to right-click and move to Design View
immediately
crashes the app, however....

Nick

:

Okay: no outer joins, so the yes/no fields will not cause the
problem.

You are actually changing the SourceObject of the subreport in the
events
of
the main report? That would have to be the number one suspect for
what
is
crashing Access 2007. Could you comment out that code temporarily,
and
see
if the crashes stop?

You know, I have never really understood what is meant by an
'outer
join.'
I
see errors occasionally regarding "Access cannot evaluate
ambigous
outer
joins, do one first and then another...' but other than that, I
never
see
that syntax.

The query in question is:

SELECT tblInsDetails.JobID, tblInsDetails.OwnerID,
tblInsDetails.ConsigneeID, tblInsDetails.InsTypeID,
tblInsDetails.InsLocationID, tblInsDetails.InspectorID,
tblInsDetails.InspectorID2, tblInsDetails.InspectorID3,
tblInsDetails.InspectorID4, tblInsDetails.InspectorID5,
tblInsDetails.Date,
tblInsDetails.[WO#], tblInsDetails.[PO#],
tblInsTypes.InsTypeName,
tblInsTypes.[Eng Certification], tblInsTypes.[Capacity Rating],
tblCertResults.Item, tblCertResults.Make,
tblCertResults.SerialNumber,
tblCertResults.Model, tblCertResults.Unit,
tblCertResults.Capacity,
tblCertResults.Comments, tblInsDetails.WOPrinted,
tblCertResults.[Re-CertPeriod], tblCertResults.RP,
tblCertResults.RepairedBy,
tblCertResults.LetterCertItem, tblClients.[Cert Address 1],
tblClients.[Cert
Address 2], tblClients.[Cert City], tblClients.[Cert Province],
tblClients.[Cert Postal Code], tblInsDetails.EngPO,
tblInsDetails.CertPrinted,
tblCertStatus.AdditionalInspectionInfo,
tblCertStatus.AdditionalDisclaimerInfo,
tblCertStatus.AdditionalCapacityInfo,
tblCertResults.[10YearBoomCert], tblInsLocations.LocationName,
tblCertResults.GeneralEquipTypeID, tblClients_1.AllCertsSentHere,
tblClients_1.[Client Name], tblInsDetails.Notes

FROM ((tblClients AS tblClients_1 INNER JOIN ((tblInsTypes INNER
JOIN
(tblClients INNER JOIN tblInsDetails ON tblClients.[Client ID] =
tblInsDetails.OwnerID) ON tblInsTypes.InsTypeID =
tblInsDetails.InsTypeID)
INNER JOIN tblInsLocations ON tblInsDetails.InsLocationID =
tblInsLocations.InsLocationID) ON tblClients_1.[Client ID] =
tblInsDetails.ConsigneeID) INNER JOIN tblCertResults ON
tblInsDetails.JobID =
tblCertResults.JobID) INNER JOIN tblCertStatus ON
tblCertResults.ResultsID
=
tblCertStatus.ResultsID

WHERE (((tblInsDetails.JobID)=[forms]![frmJobID]![jobid]));

There are Boolean fields invovled, but the SQL has no "outer
join'
in
its
syntax. The sub report cannot be dumped, because, depending on
the
data,
the
source object may get swapped out to a completely different
subreport.
Very
flexible, but it can sometimes get a little snaky debugging it
when
the
source object causing problems isn't the one you think it is
None
of
that
is one the go with the record set involved here though. It is
just
weird.
The query loads data for the main report, heck, I put a
docmd.openquery
"thatquery" line in the detail_format event that hangs the
app--the
query
loads, but the subreport still insists there is no current
record.
The
child
and master fields are good. They are linked by the autonumber
field
of
the
main lookup table--no better pedigree than that. I'll look at
the
arrticle
you mentioned.

Thanks
Nick

:

Okay, so you have identified that the problem does relate to the
code
in
the
report's events. (Access doesn't fire this code in Report or
Layout
view.)

You might check the LinkMasterFields/LinkChilds properties of
the
subreport
control to ensure this is correct.

The No Current Record error could be triggered by some code that
deleted
the
current record (unlikely.) It could also be bogus. For example,
if
the
report's source is a query that uses outer joins, and the table
has
any
yes/no fields, you may have hit this bug:
Outer join queries fail on Yes/No fields
at:
http://allenbrowne.com/bug-14.html

If the main report and subreport are both the same and filtered
to
the
same
record, you may be able to dump the subreport. Instead, you
could
create
a
Group Footer on the primary key field within the main report. Of
course
this
will occur for every record, and you could place whatever you
have
in
the
subreport in this section of the main report instead.

message
I have separated the required objects out and put them into a
DB
that
I
can
post. It will open in every mode EXCEPT print preview in
Access
2007
(although just what use Layout and Report views will be
remains
to
be
seen...with this particular report, those views look so
garbled
that
I
wouldn't touch an object in them ) The report's behavior is
this:
Both
the report and one of its sub-report use the same query for a
datasource.
That query returns records, yet the print preview throws an
error
claiming
'no cuurent record' for the subreport. The debugger opens. A
truly
annoying
behavior then happens. Mousing over any variable that should
have
been
set
by the recordset generates a popup 'no current record' (how do
I
turn
that
off--its very annoying). When the debugger is closed, a
message
occurs
that
a failure in a routine is preventing the report from being
formatted,
and
the
app hangs. I will check the kb article you mentioned.

 

[Nick Meyer]

Thank you for your help
I wouldn't be where I am at without sites like yours, or Tony Toews' or
Stephan Lebans'.
I appreciate the gift of your time.

Nick

No further advice

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

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

It runs perfectly fine in Access 2003, so I don't see how there is
anything
to do with bad binaries or NameAutoCorrect. I decompile the db on
occasion
when it bloats more that compact and repair will take care of. The test
db I
created was brand new, I just imported the objects to it, and it displays
the
same behavior as the original. The damnable thing is that the code block
directly below it (from my post on the 22nd) is screwing with control
locations but doesn't kill the app. WTF. Now the problem. I have 3 new
machines to order, and O2003Pro OEM is NOT available anymore. That means
opening an OpenLicence agreement and getting O2003Pro media--and is gonna
run
a couple hundred bucks more than OEM. Is this then a bona fide bug that I
have to HOPE gets fixed by SP1 whenever that comes around?

Access 2007 has been such a disappointment. Now, a show stopping bug.
Sigh. Any final advice?

Nick

Nick, the code as you presented it should not crash Access assuming that
it
is in the Format event of the section that contains the 6 controls, and
the
section is tall and wide enough to handle it.

But Access 2007 is very flakey, especially with code in the report
events.
I'm aware of several scenarios that will bring it crashing down, so I'm
not
surprised if you have another.

This assumes it's not merely a NameAutoCorrect bug, or bad binary
(decompile.)

Very flakey in what area?
Why should some every simple code that moves a few controls around

'For x = 0 To 6
' With Me.Controls(OrderedControls(x))
' .Visible = True 'screw with visible controls only
' .Top = x * 0.0021 * 1440
' .Height = 0.0021 * 1440
' End With
'Next x

cause one of the control to open without its valid recordset, and hang
the
debugger and app?

Nick

:

Okay, we know A2007 is very flakey in this area, so it sounds like you
are
progressively zero-ing in on the code that is triggering the crash.

Now, with that portion of the code commented out, I can get the
report
to
Print Preview. Attempting to right-click and move to Design View
immediately
crashes the app, however....

Nick

:

Okay: no outer joins, so the yes/no fields will not cause the
problem.

You are actually changing the SourceObject of the subreport in the
events
of
the main report? That would have to be the number one suspect for
what
is
crashing Access 2007. Could you comment out that code temporarily,
and
see
if the crashes stop?

You know, I have never really understood what is meant by an
'outer
join.'
I
see errors occasionally regarding "Access cannot evaluate
ambigous
outer
joins, do one first and then another...' but other than that, I
never
see
that syntax.

The query in question is:

SELECT tblInsDetails.JobID, tblInsDetails.OwnerID,
tblInsDetails.ConsigneeID, tblInsDetails.InsTypeID,
tblInsDetails.InsLocationID, tblInsDetails.InspectorID,
tblInsDetails.InspectorID2, tblInsDetails.InspectorID3,
tblInsDetails.InspectorID4, tblInsDetails.InspectorID5,
tblInsDetails.Date,
tblInsDetails.[WO#], tblInsDetails.[PO#],
tblInsTypes.InsTypeName,
tblInsTypes.[Eng Certification], tblInsTypes.[Capacity Rating],
tblCertResults.Item, tblCertResults.Make,
tblCertResults.SerialNumber,
tblCertResults.Model, tblCertResults.Unit,
tblCertResults.Capacity,
tblCertResults.Comments, tblInsDetails.WOPrinted,
tblCertResults.[Re-CertPeriod], tblCertResults.RP,
tblCertResults.RepairedBy,
tblCertResults.LetterCertItem, tblClients.[Cert Address 1],
tblClients.[Cert
Address 2], tblClients.[Cert City], tblClients.[Cert Province],
tblClients.[Cert Postal Code], tblInsDetails.EngPO,
tblInsDetails.CertPrinted,
tblCertStatus.AdditionalInspectionInfo,
tblCertStatus.AdditionalDisclaimerInfo,
tblCertStatus.AdditionalCapacityInfo,
tblCertResults.[10YearBoomCert], tblInsLocations.LocationName,
tblCertResults.GeneralEquipTypeID, tblClients_1.AllCertsSentHere,
tblClients_1.[Client Name], tblInsDetails.Notes

FROM ((tblClients AS tblClients_1 INNER JOIN ((tblInsTypes INNER
JOIN
(tblClients INNER JOIN tblInsDetails ON tblClients.[Client ID] =
tblInsDetails.OwnerID) ON tblInsTypes.InsTypeID =
tblInsDetails.InsTypeID)
INNER JOIN tblInsLocations ON tblInsDetails.InsLocationID =
tblInsLocations.InsLocationID) ON tblClients_1.[Client ID] =
tblInsDetails.ConsigneeID) INNER JOIN tblCertResults ON
tblInsDetails.JobID =
tblCertResults.JobID) INNER JOIN tblCertStatus ON
tblCertResults.ResultsID
=
tblCertStatus.ResultsID

WHERE (((tblInsDetails.JobID)=[forms]![frmJobID]![jobid]));

There are Boolean fields invovled, but the SQL has no "outer
join'
in
its
syntax. The sub report cannot be dumped, because, depending on
the
data,
the
source object may get swapped out to a completely different
subreport.
Very
flexible, but it can sometimes get a little snaky debugging it
when
the
source object causing problems isn't the one you think it is
None
of
that
is one the go with the record set involved here though. It is
just
weird.
The query loads data for the main report, heck, I put a
docmd.openquery
"thatquery" line in the detail_format event that hangs the
app--the
query
loads, but the subreport still insists there is no current
record.
The
child
and master fields are good. They are linked by the autonumber
field
of
the
main lookup table--no better pedigree than that. I'll look at
the
arrticle
you mentioned.

Thanks
Nick

:

Okay, so you have identified that the problem does relate to the
code
in
the
report's events. (Access doesn't fire this code in Report or
Layout
view.)

You might check the LinkMasterFields/LinkChilds properties of
the
subreport
control to ensure this is correct.

The No Current Record error could be triggered by some code that
deleted
the
current record (unlikely.) It could also be bogus. For example,
if
the
report's source is a query that uses outer joins, and the table
has
any
yes/no fields, you may have hit this bug:
Outer join queries fail on Yes/No fields
at:
http://allenbrowne.com/bug-14.html

If the main report and subreport are both the same and filtered
to
the
same
record, you may be able to dump the subreport. Instead, you
could
create
a
Group Footer on the primary key field within the main report. Of
course
this
will occur for every record, and you could place whatever you
have
in
the
subreport in this section of the main report instead.

message
I have separated the required objects out and put them into a
DB
that
I
can
post. It will open in every mode EXCEPT print preview in
Access
2007
(although just what use Layout and Report views will be
remains
to
be
seen...with this particular report, those views look so
garbled
that
I
wouldn't touch an object in them ) The report's behavior is
this:
Both
the report and one of its sub-report use the same query for a
datasource.
That query returns records, yet the print preview throws an
error
claiming
'no cuurent record' for the subreport. The debugger opens. A
truly
annoying
behavior then happens. Mousing over any variable that should
have
been
set
by the recordset generates a popup 'no current record' (how do
I
turn
that
off--its very annoying). When the debugger is closed, a
message
occurs
that
a failure in a routine is preventing the report from being
formatted,
and
the
app hangs. I will check the kb article you mentioned.

 

[Tony Toews [MVP]]

I wouldn't be where I am at without sites like yours, or Tony Toews' or
Stephan Lebans'.
I appreciate the gift of your time.

Thanks for your kind words. They make the effort worth while.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[lenovo]

Thanks for your kind words. They make the effort worth while.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/