If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
#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 |
#2
|
|||
|
|||
#error in report using linked tables
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 |
#3
|
|||
|
|||
#error in report using linked tables
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 |
#4
|
|||
|
|||
#error in report using linked tables
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 |
#5
|
|||
|
|||
#error in report using linked tables
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 |
#6
|
|||
|
|||
#error in report using linked tables
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 |
#7
|
|||
|
|||
#error in report using linked tables
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 |
#8
|
|||
|
|||
#error in report using linked tables
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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
subreport not displaying in main report | JohnLute | Setting Up & Running Reports | 15 | November 17th, 2005 04:02 PM |
query problem with linked SQL tables when importing to new mdb file | Keith G Hicks | Running & Setting Up Queries | 2 | March 22nd, 2005 09:44 PM |
Linked tables | Loi | New Users | 1 | January 26th, 2005 08:57 PM |
Help!! I'm running around in circles! | CathyA | New Users | 19 | December 12th, 2004 07:50 PM |
Still Hoping for help with a Query problem | Don Sealer | Using Forms | 15 | November 13th, 2004 06:24 AM |