"But I Don't Have Any Parameters!!"

[SilentAiche]

Hi All:

I have inherited a database and the boss wants a new report.

The general scenario: we review Institutions and the sites they sponsor (if any), which means the number of sites could be range from 0 to hundreds.

Two tables:

Administrative Reviews
ID - autonumber, primary key
Agreement # - number (always 4 digits)
Sponsor Name - text
Consultant - text
Notes - memo
(and about 16 more fields)

Site
ID - number, manually input, link to table above (this table has no primary key for some reason)
Agreement Number - text (should match above; don't know why name and data type are different)
Site Name - text
Consultant - text (could be different from Consultant field above)
Notes - memo
(and about five more fields)

I made two queries which draw 100% of the fields from the two tables above, named respectively qryAllFields and qrySiteInfo. They work.

I designed a report based on qryAllFields to summarize 100% of the data in the Administrative Reviews table. It works.

Using the Wizard, I tried to add a Subreport based on qrySiteInfo to reflect 100% of the information in the Sites table. The subreport runs fine if it is run alone (double click from reports list).

However, the report containing the subreport brings up three consecutive boxes asking me to Enter a Parameter Value, first for "Site" and then twice in a row for "Site.ID." I don't have any parameters set in the underlying queries, so I'm not sure what it's looking for.

If I click okay without entering anything I get a blank report. If I enter nothing for Site, "346" for the first request for Site.ID, and then either leave blank or reenter "346" for the second request for Site.ID, I get exactly the report I want for Site #346.

If I try to view Page 2 (using the buttons in the lower left corner), I no longer get the first "Enter a Parameter Value" box for Site, but I still get the two boxes asking for the Site.ID. If I enter a valid Site.ID for the first box, I get what i want. If I enter a Site.ID only in the last box I just get a blank report.

What I want is for the report to show me all the info from the Administrative Reviews table and all the related Site information in a subreport immediately beneath it.

I know this has been longwinded, so thanks to anyone who is still reading! Why is it asking for Parameter values?

Tim

[blue]_____________________________________________________
If you need immediate assistance, please raise your hand.
If you are outside of Raleigh, raise your hand and say[/blue] [red]Ooh! Ooh![/red]

 

[grande]

I had the exact same problem, and I can't for the life of me remember how I fixed it. I'm so sorry...

I'm going to offer a suggestion, though.:
Make sure that you have your Child/Master link fields named correctly. It's screwed me over more times than I can count.


-------------------------
Just call me Captain Awesome.

 

[SilentAiche]

Grande,

Thanks for responding. I'm used to the boxes saying Enter Parameter Value for a field (a name may have changed, or two fields such as ID in my example above might appear in the two different tables). The kicker for me is that here it's asking for a Parameter Value for a TABLE name. I have a table named SITE, but there is no field named Site.

I did, however, notice that I had a field named "Site Name" (witht the space), so I changed it to SiteName (without the space) since Name is a reserved word. Didn't help.

In fact, it's gotten a bit worse and I am concerned I may have misspoken in my OP.

If I enter nothing for Site, "346" for the first request for Site.ID, and then either leave blank or reenter "346" for the second request for Site.ID, I get exactly the report I want for Site #346.

This is no longer true, if it ever was. The main report is very "busy" with nearly twenty fields, many with ridiculously long field names. I fear I may have been so excited about finally seeing data in the subreport that I might not have noticed all those busy fields in the main report didn't actually contain any data. (It's possible in some of my post-post (?) tinkering that I did something to cause it to lose the main report data, but I kind of doubt it).

My apologies to all for the confusion, but the main question is still there.

Tim

[blue]_____________________________________________________
If you need immediate assistance, please raise your hand.
If you are outside of Raleigh, raise your hand and say[/blue] [red]Ooh! Ooh![/red]

 

[grande]

I'll try and take a look around for you, see if I can remember how I fixed it.

Don't you love inheriting shitty apps?

-------------------------
Just call me Captain Awesome.

 

[SilentAiche]

Since the OP: I have created a new query, qryAllFieldsPlus, that contains 100% of the fields from the tables Administrative Review and Site. This query returns correct results.

I made qryAllFieldsPlus the control source of both my Main Report and the Subreport. NOW, when I run the report I get a box asking me to Enter Parameter Value for Site. Leaving it empty and clicking OK, it brings up an error message advising that [blue]The specified field 'ID' could refer to more than one table listed in the FROM clause of your SQL statement.[/blue]

Click OK again and the main report is okay but the sub is empty.

Here is the SQL, as copied from qryAllFieldsPlus:

SELECT [Administrative Review].ID, [Administrative Review].[Agreement #], [Administrative Review].[Sponsor Name], [Administrative Review].Consultant, [Administrative Review].FY, [Administrative Review].[Date of  Last Review], [Administrative Review].[First Review], [Administrative Review].[Beginning Date of Operation], [Administrative Review].[Ending Date of Operation], [Administrative Review].[Pre-Op Reviewer], [Administrative Review].[Date of Pre-Operation Visit], [Administrative Review].Comments, [Administrative Review].[Scheduled Date], [Administrative Review].[Date of Review], [Administrative Review].[Ending Date of Review], [Administrative Review].[Received in Raleigh- within 10 days?], [Administrative Review].[Findings documentation], [Administrative Review].[Amount due state noted?], [Administrative Review].Notes, [Administrative Review].[Date of Follow Up], [Administrative Review].[Date of Follow Up (2nd)], [Administrative Review].[Followed up Review within 15 days], [Administrative Review].[Closed Date], [Administrative Review].[Review closed with CA Pending?], [Administrative Review].[Submitted As SD], Site.ID, Site.[Agreement Number], Site.SiteName, Site.Consultant, Site.[Scheduled Date], Site.[Start Date], Site.[Date Completed], Site.[Received in Raleigh within 10 days], Site.Notes
FROM [Administrative Review] LEFT JOIN Site ON [Administrative Review].ID = Site.ID;

It sure looks to me like ID is fully qualified in the FROM statement. What am I missing?

And how bad is the entire scheme (e.g., should Main and Sub reports have the same control source)?

Thanks in advance!
Tim

(Who the hell names a field with [red]six[/red] separated words?!)

[blue]_____________________________________________________
If you need immediate assistance, please raise your hand.
If you are outside of Raleigh, raise your hand and say[/blue] [red]Ooh! Ooh![/red]

 

[grande]

Wow... What a bad setup...
[Administrative Review].[Review closed with CA Pending?]
[Administrative Review].[Followed up Review within 15 days]
I'm not sure whether to laugh or cry... And a question mark in a field name? Wow...

So, to answer your question, I *think* (Although I'm far from sure) that your problem could be arising because you're using the same query for both the main and subform.

Now, if you really want to use the same query and can't break it into two, just copy and rename the query to qryAllFieldsPlus_Main and qryAllFieldsPlus_Sub, and put them as each control source.

BUT, if at all possible, make two seperate queries.

-------------------------
Just call me Captain Awesome.

 

[SilentAiche]

Thanks Grande. Actually, separate control sources was my first method (see OP re: qryAllFields and qrySiteInfo).

Tim

("Inheritance Tax" and "Death Tax" ARE interchangeable - this thing's killing me!)

[blue]_____________________________________________________
If you need immediate assistance, please raise your hand.
If you are outside of Raleigh, raise your hand and say[/blue] [red]Ooh! Ooh![/red]

 

[grande]

Death Tax? That can't be an industry term...

-------------------------
Just call me Captain Awesome.

 

[Lightning]

Tim

I did a copy/paste of your field names into tables and then created queries from them, then created two reports using the wizard. I based each report on a separate query, one for each table.

When I dropped the site report into the main report as a subreport, the whole report ran with no problems.

I then tried basing the report and subreport on the same query, and had the same problem you did. So i renamed the links to the table fields.

Child field ---> [Site].[ID]
Master field ---> [Administrative review].[ID]

I still get one parameter box asking for Administrative review, but the others are gone. Clicking OK on the parameter box, without entering anything, brings the report with all data showing.

Hopefully this will give you some ideas on where to go next.
Good luck

HTH
Lightning

 

[SilentAiche]

Lightning,

I really appreciate the help.

I think you and Grande are right - the main and sub need to be based on separate queries. I made a little headway yesterday by playing with a smaller version of the report which focused on the fields in the Site table. I based the report on a query that used all the fields from Site and only the ID field from Administrative Reviews (with tables linked on the ID fields). It worked - it showed me the various Sites for each Sponsor, but this was a straight report (no subreport).

A couple of questions: If I create the subreport separately, without using the wizard, how do I "drop" it into the main report?

If I use the wizard, at one point it asks which fields to use for the join. However, the field names are so long that I can't see what I'm choosing. Is there any way to see the whole line? In the alternative, if I make a blind choice in the Wizard, how do I manually go back and change it?

Thanks again!
Tim

[blue]_____________________________________________________
If you need immediate assistance, please raise your hand.
If you are outside of Raleigh, raise your hand and say[/blue] [red]Ooh! Ooh![/red]

 

[grande]

If you creat the two reports seperately, there is a control in your toolbox (where labels, textboxes, etc. are) called Subform/Subreport. The icon looks like a sunken form.

-------------------------
Just call me Captain Awesome.

 

[Lightning]

The Subform/Subreport tool wizard asks on the first screen

Use existing tables or queries
OR
use an existing report or form.

Simply choose the report option here to use any already created report as a subreport. I usually create subreports this way, because it gives me more control over the layout of the subreport.

However, the field names are so long that I can't see what I'm choosing. Is there any way to see the whole line? In the alternative, if I make a blind choice in the Wizard, how do I manually go back and change it?

<Aside>
Yeah, you'd think that Microsoft would have got that right by now!
</Aside>

On page 2 of the wizard, choose the "Define My Own" option. This opens a dialog that allows you to choose from a list of all fields in the master table or query, and match it to the required field in the child table or query.

To manually go back and change it, in Design view use the Property sheet for the subform control to change the Master and Child links.

HTH
Lightning

 

[SilentAiche]

Lightening,

I really appreciate your help.

I think (I hope) I'm close to solving this (with your and Grande's help), but I am scheduled to be in the "field' for the next two weeks, and for much of the rest of the month of May.

I'll post back when I get that "a ha" moment, but please know how much I appreciate your continued help (which will likely be called upon in the future - best wishes).

THANKS,
tim


[blue]_____________________________________________________
If you need immediate assistance, please raise your hand.
If you are outside of Raleigh, raise your hand and say[/blue] [red]Ooh! Ooh![/red]