Set record source,controls and display data in a report using VBA Code

[kosala1981]

I have created a report using sample codes taken by the forum. one problem is that the report displays the field/column names in the table in columnar format(field names display in rows.) but i want to be able to display filed names and its relevant data in tabular format in the repor but the code itself just displays only the fields in rows and another problem is that, to set the record source. the way i set the record source property of the report gives an error saying that "Object variable or With block variable not set". so here's the sample code of my dynamic report. can u guys help me out to sove this small problem. thanx.

------------------------------------------------------
Public Sub PreviewReport_Click()

createtNewReport

End Sub
------------------------------------------------------

Public Sub createtNewReport()

Dim txtNew As TextBox
Dim lblNew As Label

Dim rpt As Report
Dim sRptName As String
Dim fldData As Field
Dim lngTop As Long
Dim lngLeft As Long
Dim dbCurr As Database
Dim rsRecordset As Recordset

lngTop = 0
lngLeft = 0


-----------------------
'set report's record source propery 
'rpt.RecordSource = "X"
'it gives an error saying that"Object variable or With block variable not set" 
'with out this part the report's being created. 
----------------

Set dbCurr = DBEngine.Workspaces(0).Databases(0)
Set rsRecordset = dbCurr.OpenRecordset("X")


sRptName = "ICTA_PMIS_REPORT"

DoCmd.OpenReport sRptName, acViewDesign

For Each fldData In rsRecordset.Fields

'create controls
Set txtNew = CreateReportControl(sRptName, acTextBox, acDetail, , fldData.Name, lngLeft + 1500, lngTop)
txtNew.SizeToFit


Set lblNew = CreateReportControl(sRptName, acLabel, acDetail, txtNew.Name, fldData.Name, lngLeft, lngTop, 1400, txtNew.Height)
      lblNew.SizeToFit


'Increment top calue for next control
lngTop = lngTop + txtNew.Height + 25

Next

DoCmd.Close acReport, sRptName, acSaveYes


'------------------------------------------------
exit_createtNewReport:
Exit Sub

err_createtNewReport:
MsgBox Err.Description
Resume exit_createtNewReport

End Sub

 

[lameid]

You should be able to use something like the following to set the record source.

Reports!ICTA_PMIS_REPORT.Report.RecordSource = "Recordsource"


By tabular do you mean like the results of a crosstab query? If so, I would use a Cross tab query and then use code to open the recordset of the query and step through its fields collection to use as a base to add controls.