Public Function CreateDynamicReport(strSQL As String, sname As String)
Dim db As DAO.Database ' database object
Dim rs As DAO.Recordset ' recordset object
Dim fld As DAO.Field ' recordset field
Dim txtNew As Access.TextBox ' textbox control
Dim lblNew As Access.Label ' label control
Dim rpt As Report ' hold report object
Dim lngTop As Long ' holds top value of control position
Dim lngLeft As Long ' holds left value of controls position
Dim title As String 'holds title of report
Dim imgPath As String
Dim lblCurrentValue As String
imgPath = "C:\Users\svetlio\Documents\Icons for Projects\Backgrounds_Darkgrey.jpg"
'set the title
title = "Äàííè çà èçáðàíèÿ ðàáîòíèê"
' initialise position variables
lngLeft = 0
lngTop = 0
'Create the report
Set rpt = CreateReport
' set properties of the Report
With rpt
.Width = 8500
.RecordSource = strSQL
.Caption = title
.Modal = True
.PopUp = True
.LayoutForPrint = True
.Picture = imgPath
.PictureTiling = True
End With
' Open SQL query as a recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
' Create Label Title
Set lblNew = CreateReportControl(rpt.Name, acLabel, _
acPageHeader, , DLookup("Labels_Msg", "tbl_Labels", "Label_ID = 2") & sname, 0, 0)
lblNew.FontBold = True
lblNew.FontSize = 12
lblNew.SizeToFit
lblNew.ForeColor = vbBlack
' Create corresponding label and text box controls for each field.
For Each fld In rs.Fields
' Create new text box control and size to fit data.
Set txtNew = CreateReportControl(rpt.Name, acTextBox, _
acDetail, , fld.Name, lngLeft + 1500, lngTop)
txtNew.SizeToFit
txtNew.FontBold = True
txtNew.FontSize = 12
txtNew.SizeToFit
txtNew.ForeColor = vbRed
txtNew.BackStyle = Transparent
' Create new label control and size to fit data.
If fld.Name = "Emp_First_Name" Then
Set lblNew = CreateReportControl(rpt.Name, acLabel, acDetail, _
DLookup("Labels_Msg", "tbl_Labels", "Label_ID = 3"), DLookup("Labels_Msg", "tbl_Labels", "Label_ID = 3"), lngLeft, lngTop, 1400, txtNew.Height)
lblNew.SizeToFit
lblNew.ForeColor = vbBlack
End If
If fld.Name = "Emp_Last_Name" Then
Set lblNew = CreateReportControl(rpt.Name, acLabel, acDetail, _
DLookup("Labels_Msg", "tbl_Labels", "Label_ID = 4"), DLookup("Labels_Msg", "tbl_Labels", "Label_ID = 4"), lngLeft, lngTop, 1400, txtNew.Height)
lblNew.SizeToFit
lblNew.ForeColor = vbBlack
End If
' Increment top value for next control
lngTop = lngTop + txtNew.Height + 5
Next
' Create datestamp in Footer
Set lblNew = CreateReportControl(rpt.Name, acLabel, _
acPageFooter, , Date, 0, 0)
lblNew.ForeColor = vbBlack
' Create page numbering on footer
Set txtNew = CreateReportControl(rpt.Name, acTextBox, _
acPageFooter, , "='Page ' & [Page] & ' of ' & [Pages]", rpt.Width - 1000, 0)
txtNew.SizeToFit
' Open new report.
DoCmd.OpenReport rpt.Name, acViewPreview, , , acDialog
'reset all objects
rs.Close
Set rs = Nothing
Set rpt = Nothing
Set db = Nothing
End Function
) :
