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

[Peter Herath]

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
 
 

[nico5038]

For dynamic reports based on a crosstable query with variable fields I use the following preparation and VBA code.
To start, doing this you need to place the fields "coded" in the report.
The column headings should be called "lblCol1", "lblCol2", "lblCol3", etc.
The "detail" fields should be called "Col1", "Col2", "Col3", etc.

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 this could differ for you.

Make sure that the number of Columns is not bigger as the number placed. The programcode has no protection against that !

The OpenReport code:

 
Private Sub Report_Open(Cancel As Integer)
Dim intI As Integer
 
Dim rs As Recordset
 
Set rs = CurrentDb.OpenRecordset(Me.RecordSource)
 
'Place headers
For intI = 3 To rs.Fields.Count - 1
Me("lblCol" & intI - 1).Caption = rs.Fields(intI).Name
Next intI
 
'Place correct controlsource
For intI = 3 To rs.Fields.Count - 1
Me("Col" & intI - 1).ControlSource = rs.Fields(intI).Name
Next intI
 
'Place Total field
Me.ColTotal.ControlSource = "=SUM([" & rs.Fields(2).Name & "])"
 
End Sub
 

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.

Nic;o)