Hi guys,
Just need some vba help with creating a report in vba. Spenttoo many hours on this and now time to get some help please.
All I require is the field names listed once(on every pageif possible) across the report as sub headings of the report with all the dataunderneath.
At the moment I’m getting a repeat of the field names and a separatepage for each record and field name.
Just for interest what I'm trying to do is for a user to select rows from a form and dynamically produce a report.
Getting this data is no problem.
Eg This is what I would like........
Report name
Field1.name1 field2.name field3.name field4.name
Data data data data
Data data data data
Data data data data
Etc etc
Current code below. Kept issimple without headers etc to make it easier for you to read.
Appreciate some help.
Regards norm
Dim strSQL As String,db As dao.Database, rs As dao.Recordset, fld As dao.Field, txtNew AsAccess.TextBox
Dim lblNew AsAccess.Label, rpt As Report, lngTop As Long, lngLeft As Long, title As String
lngLeft = 0
lngTop = 0
strSQL ="select * from list"
Set rpt =CreateReport
With rpt
.Width = 8500
.RecordSource= strSQL
.Caption =title
End With
Set db =CurrentDb
Set rs =db.OpenRecordset(strSQL)
'--------------------------------------------------------------------------------------------------
Set lblNew =CreateReportControl(rpt.Name, acLabel, acPageHeader, , "Title", 0, 0)
lblNew.FontBold =True
lblNew.FontSize =12
For Each fld Inrs.Fields
Set txtNew =CreateReportControl(rpt.Name, acTextBox, _
acDetail, ,fld.Name, lngLeft + 1500, lngTop)
txtNew.SizeToFit
Set lblNew =CreateReportControl(rpt.Name, acLabel, acDetail, _
txtNew.Name,fld.Name, lngLeft, lngTop, 1400, txtNew.Height)
lblNew.SizeToFit
lngTop =lngTop + txtNew.Height + 25
Next
'-----------------------------------------------------------------------------------------------------
DoCmd.OpenReportrpt.Name, acViewPreview
rs.Close
Set rs = Nothing
Set rpt = Nothing
Set db = Nothing