RE: [AccessDevelopers] Multi Column Report

[Crosier, Dawn]

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" From: AccessDevelopers@yahoogroups.com [mailto:[EMAIL PROTECTED] On Behalf Of A.D.Tejpal Sent: Wednesday, January 18, 2006 11:16 PM To: AccessDevelopers@yahoogroups.com Subject: Re: [AccessDevelopers] Multi Column Report 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 ----- From: Crosier, Dawn To: AccessDevelopers@yahoogroups.com 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;     BTW - I am modifing the sample database found at:  http://office.microsoft.com/en-us/templates/TC010186351033.aspx   Dawn Crosier Application Specialist "Education Lasts a Lifetime" From: AccessDevelopers@yahoogroups.com [mailto:[EMAIL PROTECTED] On Behalf Of A.D.Tejpal Sent: Wednesday, January 18, 2006 12:54 PM To: AccessDevelopers@yahoogroups.com Cc: ADT Subject: Re: [AccessDevelopers] Multi Column Report 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 Please zip all files prior to uploading to Files section. SPONSORED LINKS Microsoft access developer Microsoft access help Microsoft access database Microsoft access training Microsoft access training course Microsoft access programming YAHOO! GROUPS LINKS  Visit your group "AccessDevelopers" on the web.    To unsubscribe from this group, send an email to:  [EMAIL PROTECTED]    Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.