Access数据库培训
网站公告
·Access快速平台QQ群号:277422564    ·Access快速开发平台下载地址及教程    ·欢迎添加微信交流账号:Accessoft7    ·如何快速搜索本站文章|示例|资料!    
您的位置: 首页 > 技术文章 > Access数据库-报表

用VBA创建动态报表

时 间:2017-10-28 21:21:18
作 者:Big Young   ID:252  城市:襄阳
摘 要:用VBA由用户定义的SQL SELECT语句创建动态报表。
正 文:

用VBA由用户定义的SQL Select语句创建动态报表:
Function CreateDynamicReport(strSQL 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
 
     'set the title
     title = "Title for the Report"
 
     ' 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
     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, , "Title", 0, 0)
     lblNew.FontBold = True
     lblNew.FontSize = 12
     lblNew.SizeToFit
 
     ' 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
 
         ' Create new label control and size to fit data.
         Set lblNew = CreateReportControl(rpt.Name, acLabel, acDetail, _
         txtNew.Name, fld.Name, lngLeft, lngTop, 1400, txtNew.Height)
         lblNew.SizeToFit        
 
         ' Increment top value for next control
         lngTop = lngTop + txtNew.Height + 25
     Next
 
     ' Create datestamp in Footer
     Set lblNew = CreateReportControl(rpt.Name, acLabel, _
     acPageFooter, , Now(), 0, 0)
 
     ' 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
 
     'reset all objects
     rs.Close
     Set rs = Nothing
     Set rpt = Nothing
     Set db = Nothing
 
End Function


Access软件网官方交流QQ群 (群号:115120150)       access源码网店

最新评论 查看更多评论(0)

发表评论您的评论将提升作者分享的动力!快来评论一下吧!

用户名:
密 码:
内 容:
 

常见问答

技术分类

相关资源

关于我们 | 服务条款 | 在线投稿 | 友情链接 | 网站统计 | 网站帮助