' Open dummy query to invoke NewObjectAutoReport command on it Public Sub CreateAutoReport(strSQL As String)ĬurrentDb.QueryDefs("qryDummy").SQL = strSQL
MICROSOFT ACCESS REPORTING CODE
This query is used by the code but the resulting report will not be based on the query as this would invalidate the report when the query was next changed. You will first need to create a query and call it "qryDummy". This code is used for dynamic report creation using the AutoReport command. The report query has two rowheader columns and a Total column, therefor the first field is effectively column 4 (count starts at 0 so I used intI=3) but it could differ for you.ĭynamic report creation via AutoReport command Set rs = CurrentDb.OpenRecordset(Me.RecordSource) Private Sub Report_Open(Cancel As Integer) The code needed for the open report event is: The program code has no protection against that. Make sure that the number of Columns is not bigger than the number placed. The report query has two rowheader columns and a Total column, therefore the first field is effectively column 4 (count starts at 0 so I used intI=3) but this could differ for you. The "detail" fields should be called "Col1", "Col2", "Col3", etc. The column headings should be called "lblCol1", "lblCol2", "lblCol3", etc. To start, doing this you need to place the fields "coded" in the report. Making the columnheader and detaildata flexible is possible, but needs some VBA code in the OpenReport event. Then the dynamic filling becomes very easy. As I like to have control over the layout, thus I have the lay-out designed first with "coded" controls.
This code is especially "tuned" for crosstab queries. But thats another article.ĭynamic report designed to be used with Crosstab Queries There is no facility in this code to validate the SQL query as it is assumed this is done elsewhere. To create that SQL SELECT statement you can set up a form to allow the user to select options to build the query. To call this function you simply need to pass a String parameter of a SQL statement to it as per the following.ĬreateDynamicReport "SELECT * FROM TableName" You can play around with the layout of the report using the lngTop and lngLeft variables. At which point they will be prompted to save it. This report will not be saved until the user saves it or tries to close it. Set lblNew = CreateReportControl(rpt.Name, acLabel, acDetail, _ ' Create new label control and size to fit data.
' Create new text box control and size to fit data. ' Create corresponding label and text box controls for each field. Set lblNew = CreateReportControl(rpt.Name, acLabel, _ Function CreateDynamicReport(strSQL As String)ĭim rs As DAO.Recordset ' recordset objectĭim txtNew As Access.TextBox ' textbox controlĭim lblNew As Access.Label ' label controlĭim lngTop As Long ' holds top value of control positionĭim lngLeft As Long ' holds left value of controls positionĭim title As String 'holds title of report
0 kommentar(er)
