If you are in a position to upload a zipped
copy of your file with some sample data, I could examine the optimum way
for adapting the basic idea to your specific situation.
----- Original Message -----
Sent: Friday, January 20, 2006
21:15
Subject: RE: [AccessDevelopers] Multi
Column Report
A.D. -
Could you help me
better understand your function?
Function
Fn_RecNum(ByVal TableName As String,
_
ByVal FieldName As String,
_
ByVal FieldValue As Variant,
_
ByVal PkName As String,
_
ByVal PkValue As Variant) As Long
I am guessing that I
should be getting the values from the table associated with the sub
report. However, my sub report is based on a multi-table query, so I am
not sure what TableName, FieldName, and PkName to use. What I have been
trying so far, returns 0 for RecNum and 0 for Batch.
My record source for the Sub
Report:
SELECT DISTINCTROW
[Recipe Ingredients].*, Nz([Quantity]+" ")+[Ingredients.Ingredient] &
nz(", "+[comments]) AS MyIngredients FROM Ingredients INNER JOIN [Recipe
Ingredients] ON Ingredients.IngredientID = [Recipe
Ingredients].IngredientID;
IngredientID is the PK for the Table
Ingredients.
RecipeIngredientID is the PK for the Table Recipe
Ingredients
Thank
you.
Dawn
Crosier
Application Specialist
"Education Lasts a
Lifetime"
Dawn,
You don't have to worry about total number of
records in the subreport. Batch size (its value is 52 in the sample)
merely indicates the max number of records that can be safely
accommodated on a page. You can experiment by entering
different values in the text box on start-up form and choose the one
that gives best results.
If the total number of records happens to
exceed batch size, the contents of intended subreport
are automatically sub-divided into convenient packets and get
printed as a series of subreports in continuation in such a manner that max
number of records in any of such subreports remains within the specified batch
size. All this is meant to ensure that the height of subreport control does
not spill over to next page (this is important in the context of balancing of
columns in Down & then Across layout).
If the number of records in main
subreport is well within what can be accommodated on a page without
any spill-over to next page (as already specified by batch size), the
subreport gets printed straightaway without any subdivision (as it
is not needed). Batch size does not play any role in such a situation.
Best wishes,
A.D.Tejpal
----- Original Message -----
Sent: Thursday, January 19, 2006
04:10
Subject: RE: [AccessDevelopers] Multi
Column Report
A.D.
-
Thank you for the
sample. However, in my instance, I don't know how many records are
going to be in the sub report. So, how do I modify the query Q_Sub_A
to allow for different quantities?
In my particular
instance, I have been creating/modifying a recipe database. Therefore,
for each RecipeID, I have a varying number of ingredients associated with
each recipe. I can see that the Batch is used to assist in the
calculation of the number of detail records, but I am not sure how to get
that calculation if I don't know what the "Gap" is going to be?
My recordsource for
the Main Report:
SELECT
Recipes.*, [Food Categories].FoodCategory FROM [Food Categories] RIGHT JOIN
Recipes ON [Food Categories].FoodCategoryID =
Recipes.FoodCategoryID;
My recordsource for the Sub
Report:
SELECT DISTINCTROW
[Recipe Ingredients].*, Nz([Quantity]+" ")+[Ingredients.Ingredient] &
nz(", "+[comments]) AS MyIngredients FROM Ingredients INNER JOIN [Recipe
Ingredients] ON Ingredients.IngredientID = [Recipe
Ingredients].IngredientID;
Dawn Crosier
Application
Specialist
"Education Lasts a Lifetime"
Dawn,
Sample db named
SubReportTwoColBalanced_2K.zip, uploaded to files section, demonstrates
balanced display of multi-column subreport despite its layout being
"Down & Then Across".
When you click the command button for report
preview, the process involves two stage opening. At first the subreport is
opened temporarily as an independent report so as to build up an array of
required heights of subreport control on each page. It then closes,
followed by opening of main report.
The underlying approach could be adapted suitably
for your specific situation.
Best wishes,
A.D.Tejpal
