Linking a subreport to main report causes parameter box to display

[MrBillSC]

I am using Access 2003.

I have a main report that opens only one table named CompanyInfo. The subreport is a multi column report that opens only one table named Categories. The main report is grouped by CompanyInfo.CompanyName and is linked to the subreport via CompanyName (the field CompanyName is in both tables).

When I open the subreport by itself it works as expected.
When I open the main report with the subreport removed it works as expected.
When I have the subreport in the detail section of the main report linked by CompanyName, the main report open a parameter box for "CompanyInfo". I simply click 'OK' and the report runs correctly.

In the subreport properties I have:
Link Child Fields: [Categories].CompanyName
Link Master Fields: [CompanyInfo].CompanyName

I can't seem to get rid of the unnecessary parameter box.

Has anyone else run into this problem and solved it?

Thanks to all for your time.

MrBill

 

[dhookom]

Try get rid of the table name in at least the Link Master property.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MrBillSC]

Thanks dhookom for responding.

I tried removing the table name but then it prompts me for CompanyName over and over (for every linked record?). At least with the table name in there it only prompts once for CompanyInfo.

Any other ideas or suggestions will be appreciated.

MrBill

 

[dhookom]

Do you have the CompanyName field in both the main report and the subreport? Please confirm this.

Does either the main report or the subreport use a parameter query?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MrBillSC]

dhookom,

The subreport does not display the CompanyName, the main report does in the page header (there is a page break when CompanyName changes).

Neither report uses a parameter query. If I run the subreport by itself there are no prompts. If I remove the subreport from the main report and run the main report there are not prompts. I only get the CompanyInfo prompt when sub subreport is placed in the detail section of the main report.

MrBill

 

[dhookom]

I seriously question whether you have CompanyName in the record source of your subreport. Try add a text box on your subreport and set its Control Source to:
[CompanyName]
Open the subreport by itself to see if you get prompted for CompanyName. If you don't, then just set the text box to invisible and save and run.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MrBillSC]

I added CompanyName to my subreport by dragging it from the field list. It is definitely there. My linking is working. I have verified that the correct "Categories" table records are appearing on the report for the right companies. I just don't know why that irritating prompt for the table name "CompanyInfo" keeps appearing.

In the database relationships window I have CompanyInfo.CompanyName linked to Categories.CompanyName (left out join from CompanyInfo to Categories).

My subreport contains 1 table named Categories. Categories contains 2 fields:
1. CompanyName
2. Category

My main report contains 1 table named CompanyInfo. The primary key in CompanyInfo is CompanyName.

I have linked reports and subreports before. This is the first time I've run into this problem.

Any more suggestions will be appreciated.

MrBill

 

[dhookom]

I would start over with the report and subreport to see if you can either recreate or avoid the issue. If you can't resolve that way, I would consider accepting a compacted and zipped file by email.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]