Replies

Ok, after several variations, I can’t seem to reference the entire sheet and I have to include the path also. So right now it looks like such:

=’U:SharedCivil Justice ReformRegressive Hotline[Regressive Hotline Stats.xls]CALL TALLY’!Print_Area

Unfortunately, this only reproduces what’s in A1.

Also, when i open up the destination document, it asks me if i want to update the document from the document. How can this be changed… i want the document to only update when the parents documents are saved.
Further suggestions appreciated.

WORKS GREAT! – Is there an easy way to enter a value as criteria (a date)? in most cases i need to get the data since a certain date. or is it easier to recreate the pivot table each time and select the values manually? Jenn.

Francois,

I have just sent it. Thank you. Jenn

MEMID is not a field in tblAffiliation. There is a junction table that links affiliations to members and is called jctblAfftoMem and contains an autonumber, MEMID, and AffiliationID. The subreport called Specialy, that is in question here, is based upon this junction table and the parent report is based on qryCommList which supplies the member ID. Because of the use of the junction table… affiliation ID is just a number and has no meaningful value to the user of the report… so I need to go back to the original tblaffiliation so i can pull a value from the affiliation field.

the child and master fields from the parent report to sub report is MEMID. The subreport that contains the combo box is called Speciality. The fields in the subreport are Memid, AffiliationID and an autonumber. The SQL of the rowsource for the combo box is: SELECT DISTINCTROW tblAffiliation.AffliationID, tblAffiliation.Affiliation FROM tblAffiliation ORDER BY tblAffiliation.Affiliation; bound column is 2 (it’s a number).

Also… the parent report is based on a qry that has MEMID as one of it’s fields… memid is not used in any controls… just as the means of linking the subreports as it is the only field common to the subreport and the reports underlying query.

great, that worked… now how do I make it stop outputting all values and only output those with the same ID as the parent report?

That kinda sorta works but not completely. I am getting a list of numbers instead of words. The original combo box had a select distinct row statement. How do i now get the new text box to display a value other than the bound column? Also, when i try to view the main report the subreport code generates a runtime error #2478 which doesn’t allow me to use this method in the design view. Any further suggestions?

This is the sql statement for the query that the combo box should select the criteria for…

SELECT [State Legislators & Comm].LegTitle, [State Legislators & Comm].LegDist, [State Legislators & Comm].Party, [State Legislators & Comm].CAdd1, [State Legislators & Comm].MSSNYDist, [State Legislators & Comm].FName, [State Legislators & Comm].LName, [Current Event].[Proposed$], [Current Event].CkAmt, [Current Event].[Ck#], [Current Event].EvntDt, [State Legislators & Comm].PrevCycle
FROM [State Legislators & Comm] INNER JOIN [Current Event] ON [State Legislators & Comm].ID = [Current Event].ID
WHERE ((([State Legislators & Comm].ID)=[Forms]![Frm Operations]![Combo61]));

Providing the sql above is correct, there may be an problem with what the combo box is selecting… i have the bound colum of the combo box equal to the field in the table that holds the actual criteria… Is this a possible avenue for my inability to generate a recordset with this query?

thanks francois… works like a charm…

Thanks Francois and Chris – I used a combination of your two suggestions and my comma problem is a thing of the past! Was one of those things that always bugged me! I am appreciative of the time you took to answer me. Thanks again! Oh, the actual control source i used was as follows:

=Trim([Prefix] & ” ” & [FirstName] & ” ” & [LastName] & IIf(IsNull([Credentials])=True,””,”, “) & [Credentials] & IIf(IsNull([Title])=True,””,”, “) & [Title])

I apologize if my previous post was confusing (speaking of an articulation problem). But when you asked about the page footers being different, It got my wheels turning. I had noticed that on the first page only, there was less of a gap between the last detail and the page footer, however on all subsequent pages the gap was uniform. I realized that when I had entered the code for the pagesum that I had turned on the report header/footer. That had put additional space at the top of the first page of the report, thereby reducing the size of the gap. I then knew the solution was in the headers. I closed the space in the report header, increased the size of the page header, manipulated the header label and I’ll be darned…

I’ll thank you for all your help as I believe this horse is dead. Jenn.

You may not believe this but I thought your suggestion about using a graphic warranted further investigation. I counted the number of labels I was lacking on the last page (7) and copied/pasted 7 copies of just the formatted label into the “report footer” and lo and behold I have a complete form with 7 “empty” records. Also there are no gaps between the “real” records and the labels. It’s perfect.

Now, about the articulation problem. I have reviewed your solution and need some clarification. This page footer that does not articulate with the last detail on the page, is actually another formatted label and the pagesum control that holds the per page total. With that in mind, how can I put it in a subreport and still have it give me the per page total and where would this sub report go? There are no groupings either. It can’t go in the report footer as it needs to be present at the bottom of each page. Another possible avenue is the fact that there are exactly 9 detail records on each page – is there are creative solution that takes advantage of this?

Thanks again. Jenn.

The code from the Microsoft website did the trick but I would be lying if I said it actually made sense to me. If anyone would like to explain it.
Code is as follows:

Dim x as Double

report header_print
x=0

page footer_print
pagesum= runsum – x
x = runsum

***Where pagesum is the name of the unbound textbox in the page footer and runsum is an invisible textbox in the detail section who’s control source is the [Total] field.

I am interested in knowing the logic behind how this works as I would never have arrived at this solution myself.

Also, my report looks great, however the last page only has two records making more than half the page blank. Is there anyway to fill the remainder of the page with “empty” labels that have no data associated with it. (Note the way this report is set up is with a label containing the formating, in the background of the details section and the fields from the record source sitting on top of the label)

Lastly, I haven’t yet tried to implement the solution to the page footer articulation problem as I have not had the time. Will let you know how it turns out.

Thanks so much. Jenn.