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.

Expand|Select|Wrap|Line Numbers

 
------------------------------------------------------

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:

Expand|Select|Wrap|Line Numbers

  
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)

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

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

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about