#error in report using linked tables

Using Access 2003 w/Windows XP.
I have a database originally created with the tables internal. I split
the database into a front-end/back-end with linked tables. Now when I
open one of the reports, I get #error in some of the fields. I created
two new databases from scratch and imported the front-end objects into
one database and imported the tables into the other and re-linked them.
I still get the error.
The report is opened from a form, using docmd.openreport specifying a
where clause. If I open the report directly, without the where clause,
there are no errors.
If I import the tables back into the front end, then the errors go
away.
Any ideas?
Thanks,
Jerry

What is in the Control Source of these text boxes?
Expression?
Calculated query field?
Table field?

What is the RecordSource of this report?

Is the report returning any records at all when you see #Error? How many
boxes show #Error?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"JerryWendell" wrote in message
...
Using Access 2003 w/Windows XP.
I have a database originally created with the tables internal. I split
the database into a front-end/back-end with linked tables. Now when I
open one of the reports, I get #error in some of the fields. I created
two new databases from scratch and imported the front-end objects into
one database and imported the tables into the other and re-linked them.
I still get the error.
The report is opened from a form, using docmd.openreport specifying a
where clause. If I open the report directly, without the where clause,
there are no errors.
If I import the tables back into the front end, then the errors go
away.
Any ideas?
Thanks,
Jerry

Thanks for responding Allen.
The problem is actually in a subreport. There are 15 subreports on this
report. The problem occurs in the 7th one (usually). There are no records
in this particular subreport, even though there are records in the query.
The control sources for all fields in the subreports are query fields. The
field on the main report where the #error occurs is a calculated field,
trying to pull data from the subreport that is showing no records. To make
sure I get a valid value I check to see of the subreport has data using the
code:
IIf(LaborGA.Report.HasData=-1,CLng(LaborGA!totalLaborGA),0)
The record source for all subreports is a query.

Here is more information probably related to the problem:
My MASTER linking control on the main report is named CPID whose
ControlSource is CP_ID. When I open the report in design mode, CPID has an
error indicator that says "No such field in field list", even though CP_ID IS
in the field list. When I reselect CP_ID as the ControlSource. The control
error goes away. But when I open the report again, it gives me the same
control error.

Thanks,
Jerry


"Allen Browne" wrote:

What is in the Control Source of these text boxes?
Expression?
Calculated query field?
Table field?

What is the RecordSource of this report?

Is the report returning any records at all when you see #Error? How many
boxes show #Error?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"JerryWendell" wrote in message
...
Using Access 2003 w/Windows XP.
I have a database originally created with the tables internal. I split
the database into a front-end/back-end with linked tables. Now when I
open one of the reports, I get #error in some of the fields. I created
two new databases from scratch and imported the front-end objects into
one database and imported the tables into the other and re-linked them.
I still get the error.
The report is opened from a form, using docmd.openreport specifying a
where clause. If I open the report directly, without the where clause,
there are no errors.
If I import the tables back into the front end, then the errors go
away.
Any ideas?
Thanks,
Jerry

Try setting the Control Source to:
=IIf([LaborGA].[Report].[HasData], Nz([LaborGA].[Report]![totalLaborGA],0),
0)
and set the Format to General Number to define the type (or wrap the entire
IIf() expression in CLng() if you prefer.)

The other issue hints at a corruption of the database. Make sure the Name
AutoCorrect boxes are unchecked under:
Tools | Options | General
Then compact the database. Hopefully it will now recognise and hold the
refererence.

I'm not clear from your description whether the text box has the same name
as its control source, or whether the underscore is missing, but be sure to
use the Name of the *control* in the Link Master Fields.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"JerryWendell" wrote in message
...
Thanks for responding Allen.
The problem is actually in a subreport. There are 15 subreports on this
report. The problem occurs in the 7th one (usually). There are no
records
in this particular subreport, even though there are records in the query.
The control sources for all fields in the subreports are query fields.
The
field on the main report where the #error occurs is a calculated field,
trying to pull data from the subreport that is showing no records. To
make
sure I get a valid value I check to see of the subreport has data using
the
code:
IIf(LaborGA.Report.HasData=-1,CLng(LaborGA!totalLaborGA),0)
The record source for all subreports is a query.

Here is more information probably related to the problem:
My MASTER linking control on the main report is named CPID whose
ControlSource is CP_ID. When I open the report in design mode, CPID has
an
error indicator that says "No such field in field list", even though CP_ID
IS
in the field list. When I reselect CP_ID as the ControlSource. The
control
error goes away. But when I open the report again, it gives me the same
control error.

Thanks,
Jerry


"Allen Browne" wrote:

What is in the Control Source of these text boxes?
Expression?
Calculated query field?
Table field?

What is the RecordSource of this report?

Is the report returning any records at all when you see #Error? How many
boxes show #Error?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"JerryWendell" wrote in message
...
Using Access 2003 w/Windows XP.
I have a database originally created with the tables internal. I split
the database into a front-end/back-end with linked tables. Now when I
open one of the reports, I get #error in some of the fields. I created
two new databases from scratch and imported the front-end objects into
one database and imported the tables into the other and re-linked them.
I still get the error.
The report is opened from a form, using docmd.openreport specifying a
where clause. If I open the report directly, without the where clause,
there are no errors.
If I import the tables back into the front end, then the errors go
away.
Any ideas?
Thanks,
Jerry

I was having a similar problem, and upon looking at my tables and their
fields, I found that I had used the same field name in two different tables.
This confused some of my forms and reports, even when I specified the table
that the field was part of. When I changed the field names, so that each was
unique, I stopped having that problem.

"JerryWendell" wrote:

Using Access 2003 w/Windows XP.
I have a database originally created with the tables internal. I split
the database into a front-end/back-end with linked tables. Now when I
open one of the reports, I get #error in some of the fields. I created
two new databases from scratch and imported the front-end objects into
one database and imported the tables into the other and re-linked them.
I still get the error.
The report is opened from a form, using docmd.openreport specifying a
where clause. If I open the report directly, without the where clause,
there are no errors.
If I import the tables back into the front end, then the errors go
away.
Any ideas?
Thanks,
Jerry

That also sounds like a bug triggered by Name AutoCorrect.

If you are not familar with the problems this causes, here's an
introduction:
http://allenbrowne.com/bug-03.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"mnature" wrote in message
...
I was having a similar problem, and upon looking at my tables and their
fields, I found that I had used the same field name in two different
tables.
This confused some of my forms and reports, even when I specified the
table
that the field was part of. When I changed the field names, so that each
was
unique, I stopped having that problem.

"JerryWendell" wrote:

Using Access 2003 w/Windows XP.
I have a database originally created with the tables internal. I split
the database into a front-end/back-end with linked tables. Now when I
open one of the reports, I get #error in some of the fields. I created
two new databases from scratch and imported the front-end objects into
one database and imported the tables into the other and re-linked them.
I still get the error.
The report is opened from a form, using docmd.openreport specifying a
where clause. If I open the report directly, without the where clause,
there are no errors.
If I import the tables back into the front end, then the errors go
away.
Any ideas?
Thanks,
Jerry

Thanks Allen!
I had high hopes that turning off Name AutoCorrect would be my salvation.
However, this problem still taunts me.
The subreport, that is causing the problem has records it should be
displaying, but it is not. So I don't think changing the ControlSource on
the control referencing the subreport will affect the problem.
Just some more details for consideration:
If I remove all of the other subreports that are after the LaborGA
subreport, the problem goes away.
If I remove ONE of subreports that is before LaborGA, then the problem
shifts to a different subreport.
All of the subreports are linked with CPID as the master (control) and child
as CP_ID (query field). I remove underscores in control names because
sometimes access has problems when the control and the field have the same
name.
Some of the recordSource's for the subreports include some of the same
queries. Could this somehow cause a problem?

Thanks for your help on this.
Jerry

"Allen Browne" wrote:

Try setting the Control Source to:
=IIf([LaborGA].[Report].[HasData], Nz([LaborGA].[Report]![totalLaborGA],0),
0)
and set the Format to General Number to define the type (or wrap the entire
IIf() expression in CLng() if you prefer.)

The other issue hints at a corruption of the database. Make sure the Name
AutoCorrect boxes are unchecked under:
Tools | Options | General
Then compact the database. Hopefully it will now recognise and hold the
refererence.

I'm not clear from your description whether the text box has the same name
as its control source, or whether the underscore is missing, but be sure to
use the Name of the *control* in the Link Master Fields.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"JerryWendell" wrote in message
...
Thanks for responding Allen.
The problem is actually in a subreport. There are 15 subreports on this
report. The problem occurs in the 7th one (usually). There are no
records
in this particular subreport, even though there are records in the query.
The control sources for all fields in the subreports are query fields.
The
field on the main report where the #error occurs is a calculated field,
trying to pull data from the subreport that is showing no records. To
make
sure I get a valid value I check to see of the subreport has data using
the
code:
IIf(LaborGA.Report.HasData=-1,CLng(LaborGA!totalLaborGA),0)
The record source for all subreports is a query.

Here is more information probably related to the problem:
My MASTER linking control on the main report is named CPID whose
ControlSource is CP_ID. When I open the report in design mode, CPID has
an
error indicator that says "No such field in field list", even though CP_ID
IS
in the field list. When I reselect CP_ID as the ControlSource. The
control
error goes away. But when I open the report again, it gives me the same
control error.

Thanks,
Jerry


"Allen Browne" wrote:

What is in the Control Source of these text boxes?
Expression?
Calculated query field?
Table field?

What is the RecordSource of this report?

Is the report returning any records at all when you see #Error? How many
boxes show #Error?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"JerryWendell" wrote in message
...
Using Access 2003 w/Windows XP.
I have a database originally created with the tables internal. I split
the database into a front-end/back-end with linked tables. Now when I
open one of the reports, I get #error in some of the fields. I created
two new databases from scratch and imported the front-end objects into
one database and imported the tables into the other and re-linked them.
I still get the error.
The report is opened from a form, using docmd.openreport specifying a
where clause. If I open the report directly, without the where clause,
there are no errors.
If I import the tables back into the front end, then the errors go
away.
Any ideas?
Thanks,
Jerry

I don't think that reusing the same query for multiple subreports would
cause the problem.

Once Access is unable to calculate one control, then others can fail to
calculate as well. This can make it quite difficult to track down the
problem, because the cause can be somewhere other than where you are seeing
the effect.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"JerryWendell" wrote in message
...
Thanks Allen!
I had high hopes that turning off Name AutoCorrect would be my salvation.
However, this problem still taunts me.
The subreport, that is causing the problem has records it should be
displaying, but it is not. So I don't think changing the ControlSource on
the control referencing the subreport will affect the problem.
Just some more details for consideration:
If I remove all of the other subreports that are after the LaborGA
subreport, the problem goes away.
If I remove ONE of subreports that is before LaborGA, then the problem
shifts to a different subreport.
All of the subreports are linked with CPID as the master (control) and
child
as CP_ID (query field). I remove underscores in control names because
sometimes access has problems when the control and the field have the same
name.
Some of the recordSource's for the subreports include some of the same
queries. Could this somehow cause a problem?

Thanks for your help on this.
Jerry

"Allen Browne" wrote:

Try setting the Control Source to:
=IIf([LaborGA].[Report].[HasData],
Nz([LaborGA].[Report]![totalLaborGA],0),
0)
and set the Format to General Number to define the type (or wrap the
entire
IIf() expression in CLng() if you prefer.)

The other issue hints at a corruption of the database. Make sure the Name
AutoCorrect boxes are unchecked under:
Tools | Options | General
Then compact the database. Hopefully it will now recognise and hold the
refererence.

I'm not clear from your description whether the text box has the same
name
as its control source, or whether the underscore is missing, but be sure
to
use the Name of the *control* in the Link Master Fields.

"JerryWendell" wrote in message
...
Thanks for responding Allen.
The problem is actually in a subreport. There are 15 subreports on
this
report. The problem occurs in the 7th one (usually). There are no
records
in this particular subreport, even though there are records in the
query.
The control sources for all fields in the subreports are query fields.
The
field on the main report where the #error occurs is a calculated field,
trying to pull data from the subreport that is showing no records. To
make
sure I get a valid value I check to see of the subreport has data using
the
code:
IIf(LaborGA.Report.HasData=-1,CLng(LaborGA!totalLaborGA),0)
The record source for all subreports is a query.

Here is more information probably related to the problem:
My MASTER linking control on the main report is named CPID whose
ControlSource is CP_ID. When I open the report in design mode, CPID
has
an
error indicator that says "No such field in field list", even though
CP_ID
IS
in the field list. When I reselect CP_ID as the ControlSource. The
control
error goes away. But when I open the report again, it gives me the
same
control error.

Thanks,
Jerry


"Allen Browne" wrote:

What is in the Control Source of these text boxes?
Expression?
Calculated query field?
Table field?

What is the RecordSource of this report?

Is the report returning any records at all when you see #Error? How
many
boxes show #Error?

"JerryWendell" wrote in
message
...
Using Access 2003 w/Windows XP.
I have a database originally created with the tables internal. I
split
the database into a front-end/back-end with linked tables. Now when
I
open one of the reports, I get #error in some of the fields. I
created
two new databases from scratch and imported the front-end objects
into
one database and imported the tables into the other and re-linked
them.
I still get the error.
The report is opened from a form, using docmd.openreport specifying
a
where clause. If I open the report directly, without the where
clause,
there are no errors.
If I import the tables back into the front end, then the errors go
away.