Asking for nonexistent parameter

[steveny]

I have a subreport in a report with master/child links set up between
the two. When I run the report a pop up asks user to enter a
parameter and the parameter has the name of one of the tables from the
main form. It doesn't matter whether you enter nothing or gibberish
into the parameter field, the report still runs fine. I just don't
want Access to ask for a parameter when one isn't needed. Is there a
way around this?

 

[Marshall Barton]

I have a subreport in a report with master/child links set up between
the two. When I run the report a pop up asks user to enter a
parameter and the parameter has the name of one of the tables from the
main form. It doesn't matter whether you enter nothing or gibberish
into the parameter field, the report still runs fine. I just don't
want Access to ask for a parameter when one isn't needed. Is there a
way around this?

That usually means that you spelled the table name
incorrectly or that there is some kind of minor syntax
error. If you post a Copy/Paste of the query's SQL
statement along with a exact copy of the thing you are
prompted for, maybe we can spot the cause.

 

[steveny]

Thanks for responding.

Here is the report's query:

SELECT dbo_tblBNY.[As-Of-Date], dbo_tblQED.[As-Of-Date], dbo_tblBNY.
[Fund-ID], dbo_tblQED.[Fund-ID], dbo_tblBNY.[Sec-ID], dbo_tblQED.[Sec-
ID], dbo_tblBNY.[Issuer-Description], dbo_tblQED.[Issuer-Description],
dbo_tblBNY.[Error-Type], dbo_tblQED.[Error-Type], dbo_tblBNY.[BC$-Book-
Value], dbo_tblQED.[BC$-Book-Value], dbo_tblBNY.[Position-Size],
dbo_tblQED.[Position-Size], dbo_tblBNY.[Market-Value], dbo_tblQED.
[Market-Value], dbo_tblSYSTEM_MAPFILE.[BONY-Acct-Number],
dbo_tblSYSTEM_MAPFILE.[Ret-System]
FROM (dbo_tblBNY INNER JOIN dbo_tblSYSTEM_MAPFILE ON dbo_tblBNY.[Fund-
ID] = dbo_tblSYSTEM_MAPFILE.[QED-Fund-Code]) INNER JOIN dbo_tblQED ON
(dbo_tblBNY.[As-Of-Date] = dbo_tblQED.[As-Of-Date]) AND (dbo_tblBNY.
[Sec-ID] = dbo_tblQED.[Sec-ID]) AND (dbo_tblBNY.[Fund-ID] = dbo_tblQED.
[Fund-ID])
ORDER BY dbo_tblBNY.[As-Of-Date], dbo_tblBNY.[Fund-ID], dbo_tblBNY.
[Sec-ID];

The linked master fields are:
dbo_tblBNY.Fund-ID;dbo_tblBNY.Sec-ID;dbo_tblBNY.As-Of-Date

The linked child fields are:
Fund-ID;Sec-ID;As-Of-Date

When running the report the parameter value it is asking me for is
dbo_tblBNY.

If I remove the subreport completely I do not get the pop up asking
for a parameter.

All of these tables are linked SQL Server tables.

Hope we can find a solution to this.

Thanks,
Steve

 

[steveny]

Hello,

I found the answer to my own question here:

http://support.microsoft.com/kb/207662

ACC2000: Parameter Prompt If LinkMasterFields or LinkChildFields
Contains Table Name

"The LinkMasterFields or LinkChildFields property of the subform or
subreport contains a reference to the underlying table or query. This
table or query could be the record source of the main form, the main
report, the subform, or the subreport. The property's value is in the
format
MainTable.FieldName
Because the reference to the table or query underlying the form or
report is implied, it is not necessary to include the table or query
name in the LinkMasterFields or LinkChildFields property."

Therefore, I renamed the linked fields in my query and I no longer get
the parameter pop up.

 

[Marshall Barton]

I found the answer to my own question here:

http://support.microsoft.com/kb/207662

ACC2000: Parameter Prompt If LinkMasterFields or LinkChildFields
Contains Table Name

"The LinkMasterFields or LinkChildFields property of the subform or
subreport contains a reference to the underlying table or query. This
table or query could be the record source of the main form, the main
report, the subform, or the subreport. The property's value is in the
format
MainTable.FieldName
Because the reference to the table or query underlying the form or
report is implied, it is not necessary to include the table or query
name in the LinkMasterFields or LinkChildFields property."

Therefore, I renamed the linked fields in my query and I no longer get
the parameter pop up.

Good search job. I was unaware of that KB article, but I
was going to suggest removing the table names too.