Dynamically Add fields/controls to reports
in [Reports]
|
Prev: Report summary
Next: Dynamic Reporting System
From: jpm on 17 May 2010 11:58 Steve, My objective is to use one report as a "template" and feed it different recordsources that have varying numbers of fields with varying field names which can be displayed for a user to review the information. This is what I posted earlier: I would like to add the controls and relate the recordsource fields to them at run time.(when the report is programmatically loaded) I'm understanding you to say this isn't possible. . . If I stick a sub-form/report on a report object, can I set the source object to an ADO recordset in a non-ADP database file? If so, will it automatically display the recordset(create fields for display to the user?) Are there examples of this that anyone can share? Much appreciated. Jim M. "Steve" <notmyemail(a)address.com> wrote in message news:uM3w9vR9KHA.5476(a)TK2MSFTNGP06.phx.gbl... > Please post what your database is about and why you want to do what you > describe here. There are ways to crate a dynamic report you may be able to > use. > > Steve > santus(a)penn.com > > > "jpm" <uh(a)Idetestspam@mars> wrote in message > news:e0$kB1E9KHA.3840(a)TK2MSFTNGP02.phx.gbl... >> Hello, >> >> I've not much experience with building reports in MsAccess. Can one >> dynamicall add fields/controls to reports from a vba module such as that >> linked to a form? i.e. do processing . . .set a sql string as a report's >> recordsource, then have the fields of the recordsource added or displayed >> in the report. I want to do this without having predefined the report >> content/format, etc. Can someone give me information or point me to >> information on this? >> >> Part of my reason for persuing this data display method is that there >> doesn't seem to be a grid control for displaying data on a form. So, how >> about a report that is built dynamically; can I do that? >> >> Thanks for any help, >> >> Jim M. >> >> >> > >
From: Steve on 17 May 2010 14:10 Jim, See if the following helps at all ....... Steve Marshall Barton says in the next post: If you want users to create their own reports with whatever data and layout they might dream up, then explore providing them with a separate playground mdb file that has links to the tables mdb file and maybe a few queries to collect data from related tables. From my files: Ad Hoc Report I only did this because a client required this functionality. No guarantees or rights are implied by the code shown here. It is provided mostly for those with extra time on their hands. Suppose a base table has many fields. It may be desirable to allow the user to select any fields (say up to 10 fields) in any order and have a report set up headings, values and totals for the selected fields. To do this I created a field selection form. This form has a check box for every field in the base table along with a listbox to show the fields in the order selected by the user. I also have a command button to clear all the columns and a combobox to select and fill saved report configurations. When a checkbox is unchecked it takes the field out of the list. This form interacts with the following table: tblCostingReportColumnOrder: ColumnName T50 --The name of the field in the base table ListOrderNumber Int --All 0's except for 1, 2, 3, etc. of selected fields ReportColumnName T50 --Aliases for the ColumnName heading ValsIndex Int --Used to order the ColumnName values FieldType T50 --Double, Date, Text, Currency, etc. MaxWidth Int --For text it's the field size, Y/N is 2, Currency is 10 ActualMaxWidth Int --Value computed from qryFillFlex The 'Print Report' command button on the same form creates a SQL string that selects fields whose ListOrderNumber > 0. This SQL string is used to calculate the maximum characters used by each field (Note: ActualMaxWidth >= Heading Width). The sum of these maximums is used to decide whether to open a report in LetterPortrait, LetterLandscape, LegalLandscape or LegalLandscapeVariableFont (shrink font to fit page). The report has a RecordSource of qryFillFlex. The report has all the headings invisible and stacked on top of one another in the Page Header. The text boxes with corresponding Control Sources are in the Detail Section. The text boxes for totals are in the Report Footer. Note that these must have names that look like lbl<FieldName>, txt<FieldName> and txtTotal<FieldName> for the way it was implemented here. Note: This report is usually used from a search form that calls the field selection form and also creates qryFillFlex to limit the records to those shown on the search subform. The Report_Open code for LetterPortrait looks like: ------------------------------------ Private Sub Report_Open(Cancel As Integer) Dim MyDB As Database Dim CRS As Recordset Dim ColumnName(10) As String Dim ReportColumnName(10) As String Dim ActualMaxWidth(10) As Integer Dim LabelName(10) As String Dim TextBoxName(10) As String Dim TotalBoxName(10) As String Dim MaxWidthNumber(10) As Long Dim LeftNumber(10) As Long Dim FieldWidth(10) As Long Dim strSQL As String Dim lngCount As Long Dim lngI As Long Dim TotalWidthInTwips As Long Dim TotalCharacters As Long Dim Response As Variant Dim strPrompt As String Dim strTitle As String strTitle = "Get Report Title" strPrompt = "Click OK or Type in a Report Title" Response = InputBox(strPrompt, strTitle, "Costing Report") lblTitle.Caption = Nz(Response, "") If IsFormOpen("frmSelectCostingReportFields") Then If Not IsNull(Forms!frmSelectCostingReportFields!cbxCostingReports.Value) Then lblSavedReportName.Caption = Forms!frmSelectCostingReportFields!cbxCostingReports.Value End If End If 'Obtain chosen fields and ActualMaxWidth values so that 'appropriate spacing can be chosen 'Select only the first 10 fields chosen 'Report_rptFlexCostingLetter.RecordSource = GetstrFlexCostingReportSQL() Set MyDB = CurrentDb strSQL = "SELECT * FROM tblCostingReportColumnOrder WHERE " strSQL = strSQL & "[ListOrderNumber] > 0 AND [ListOrderNumber] < 11 " strSQL = strSQL & "ORDER BY ListOrderNumber DESC;" Set CRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot) 'Get ActualMaxWidth and Names of fields chosen If CRS.RecordCount > 0 Then CRS.MoveLast lngCount = CRS.RecordCount CRS.MoveFirst TotalWidthInTwips = Int(9.9 * 1440) '9.9 allows 1" margins for letter portrait For lngI = 1 To lngCount ColumnName(lngI) = CRS("ColumnName") ReportColumnName(lngI) = Nz(CRS("ReportColumnName"), "") ActualMaxWidth(lngI) = CRS("ActualMaxWidth") If ActualMaxWidth(lngI) < Len(Nz(ReportColumnName(lngI), "")) Then ActualMaxWidth(lngI) = Len(Nz(ReportColumnName(lngI), "")) 'Require each field to occupy at least 10 characters If ActualMaxWidth(lngI) < 10 Then ActualMaxWidth(lngI) = 10 LabelName(lngI) = "lbl" & ColumnName(lngI) TextBoxName(lngI) = "txt" & ColumnName(lngI) TotalBoxName(lngI) = "txtTotal" & ColumnName(lngI) Report_rptFlexCostingLetter.Controls(LabelName(lngI)).Properties("Caption") = ReportColumnName(lngI) Report_rptFlexCostingLetter.Controls(LabelName(lngI)).Properties("Visible") = True MaxWidthNumber(lngI) = ActualMaxWidth(lngI) Report_rptFlexCostingLetter.Controls(TextBoxName(lngI)).Properties("Visible�") = True Report_rptFlexCostingLetter.Controls(TotalBoxName(lngI)).Properties("Visibl�e") = True If lngI <> lngCount Then CRS.MoveNext Next lngI TotalCharacters = 0 For lngI = 1 To lngCount TotalCharacters = TotalCharacters + ActualMaxWidth(lngI) Next lngI For lngI = 1 To lngCount FieldWidth(lngI) = MaxWidthNumber(lngI) * TotalWidthInTwips / TotalCharacters Next lngI LeftNumber(1) = 0 If lngCount >= 2 Then For lngI = 2 To lngCount LeftNumber(lngI) = LeftNumber(lngI - 1) + FieldWidth(lngI - 1) Next lngI End If For lngI = 1 To lngCount Report_rptFlexCostingLetter.Controls(TextBoxName(lngI)).Properties("Width") = Int(FieldWidth(lngI)) Report_rptFlexCostingLetter.Controls(LabelName(lngI)).Properties("Width") = Int(FieldWidth(lngI)) Report_rptFlexCostingLetter.Controls(TotalBoxName(lngI)).Properties("Width"�) = Int(FieldWidth(lngI)) Report_rptFlexCostingLetter.Controls(TextBoxName(lngI)).Properties("Left") = LeftNumber(lngI) Report_rptFlexCostingLetter.Controls(LabelName(lngI)).Properties("Left") = LeftNumber(lngI) Report_rptFlexCostingLetter.Controls(TotalBoxName(lngI)).Properties("Left") = LeftNumber(lngI) Next lngI End If CRS.Close Set CRS = Nothing Set MyDB = Nothing End Sub "jpm" <uh(a)Idetestspam@mars> wrote in message news:ub8lFnd9KHA.5476(a)TK2MSFTNGP06.phx.gbl... > Steve, > My objective is to use one report as a "template" and feed it different > recordsources that have varying numbers of fields with varying field names > which can be displayed for a user to review the information. > > This is what I posted earlier: > > I would like to add the controls and relate the recordsource fields to > them > at run time.(when the report is programmatically loaded) > I'm understanding you to say this isn't possible. . . > > If I stick a sub-form/report on a report object, can I set the source > object to an ADO recordset in a non-ADP database file? > If so, will it automatically display the recordset(create fields for > display > to the user?) > Are there examples of this that anyone can share? > > Much appreciated. > > Jim M. > > "Steve" <notmyemail(a)address.com> wrote in message > news:uM3w9vR9KHA.5476(a)TK2MSFTNGP06.phx.gbl... >> Please post what your database is about and why you want to do what you >> describe here. There are ways to crate a dynamic report you may be able >> to use. >> >> Steve >> santus(a)penn.com >> >> >> "jpm" <uh(a)Idetestspam@mars> wrote in message >> news:e0$kB1E9KHA.3840(a)TK2MSFTNGP02.phx.gbl... >>> Hello, >>> >>> I've not much experience with building reports in MsAccess. Can one >>> dynamicall add fields/controls to reports from a vba module such as that >>> linked to a form? i.e. do processing . . .set a sql string as a >>> report's recordsource, then have the fields of the recordsource added or >>> displayed in the report. I want to do this without having predefined >>> the report content/format, etc. Can someone give me information or >>> point me to information on this? >>> >>> Part of my reason for persuing this data display method is that there >>> doesn't seem to be a grid control for displaying data on a form. So, how >>> about a report that is built dynamically; can I do that? >>> >>> Thanks for any help, >>> >>> Jim M. >>> >>> >>> >> >> > >
From: Duane Hookom on 17 May 2010 21:52
I generally include the Ad-Hoc/query by form applet available at http://www.rogersaccesslibrary.com/forum/dh-query-by-Form_topic12.html. It allows users to select any number of fields and set various criteria etc. Users are a couple clicks from pushing the results to Excel where they can print them or whatever. -- Duane Hookom MS Access MVP "jpm" <uh(a)Idetestspam@mars> wrote in message news:ub8lFnd9KHA.5476(a)TK2MSFTNGP06.phx.gbl... > Steve, > My objective is to use one report as a "template" and feed it different > recordsources that have varying numbers of fields with varying field names > which can be displayed for a user to review the information. > > This is what I posted earlier: > > I would like to add the controls and relate the recordsource fields to > them > at run time.(when the report is programmatically loaded) > I'm understanding you to say this isn't possible. . . > > If I stick a sub-form/report on a report object, can I set the source > object to an ADO recordset in a non-ADP database file? > If so, will it automatically display the recordset(create fields for > display > to the user?) > Are there examples of this that anyone can share? > > Much appreciated. > > Jim M. > > "Steve" <notmyemail(a)address.com> wrote in message > news:uM3w9vR9KHA.5476(a)TK2MSFTNGP06.phx.gbl... >> Please post what your database is about and why you want to do what you >> describe here. There are ways to crate a dynamic report you may be able >> to use. >> >> Steve >> santus(a)penn.com >> >> >> "jpm" <uh(a)Idetestspam@mars> wrote in message >> news:e0$kB1E9KHA.3840(a)TK2MSFTNGP02.phx.gbl... >>> Hello, >>> >>> I've not much experience with building reports in MsAccess. Can one >>> dynamicall add fields/controls to reports from a vba module such as that >>> linked to a form? i.e. do processing . . .set a sql string as a >>> report's recordsource, then have the fields of the recordsource added or >>> displayed in the report. I want to do this without having predefined >>> the report content/format, etc. Can someone give me information or >>> point me to information on this? >>> >>> Part of my reason for persuing this data display method is that there >>> doesn't seem to be a grid control for displaying data on a form. So, how >>> about a report that is built dynamically; can I do that? >>> >>> Thanks for any help, >>> >>> Jim M. >>> >>> >>> >> >> > > |