pls help me guys! I need help regarding report generation with VBA coding

[Peter Herath]

I need to create an access report uisng VBA code. i want to be able to create a report name and save it and also create labels and text boxes in the report using the vba code and those fields should come from the table in the database. and data of the table should be displayed in the report under each field in the report as well.

im new to access and vba. and i want to get this job done quickly.someone please please help me on this and it would be really greatfull. i hope there'll be someone to help me........

[pks00]

Why do u want to use vba code to do this? Why cant u just design a report using report wizard or something to help you?

[Peter Herath]

well my requirement is to generate customizable reports using cross tab query. i've already developed a form having four combo boxes. two of them filled with two tables data and other two filled with field names of tables. so when a user selects these items from the combo boxes then selected items go as parameters to a Cross tab query and created a recordset from that. i've created a temporary table and insert data into that table using that Recordset(Cross tab query). now i want to create a report from that table. i have a code taken from the Internet that would create a report using vba code. but i've tried it but it gives an error saying that "u must be in design view to create controls". so im stuck at this moment.

so i need some help to create the report using vba code or else can i pushed the records in that table to an Excel sheet and create the report...? if it is possible someone pls help me out......!

[pks00]

Here is sample code that creates a new report and adds one label and textbox
Its a start. Given a report opened in design view, u should be able to set the recordsource and do other things

    Dim rpt As Report
    Dim sRptName As String
 
 
    'Create new report
    Set rpt = CreateReport()
 
    'Store the automatically generated name
    sRptName = rpt.Name
 
    'Open report in design mode
    DoCmd.OpenReport sRptName, acDesign
 
    'Create controls
    CreateReportControl sRptName, acLabel, acDetail, , , 100, 100, 500, 250
    CreateReportControl sRptName, acTextBox, acDetail, , , 100, 400, 500, 250
 
    DoCmd.Close acReport, sRptName, acSaveYes
 
    'Rename report to something more meaningful
    DoCmd.Rename "pksTSDN", acReport, sRptName
 

[Peter Herath]

I used ur sample code, modified it and created a report but it shows all fields/columns in the table in rows. i want to display those fields and its related data in columnar format.so can u tell me how to display related fields and data and how do u call the record source.. the way i called it gave an error saying that " Object Variable or with block variable not set" . Here is the code i cretaed.

----------------------------------------------------------
Public Sub PreviewReport_Click()
 
createtNewReport
 
End Sub
----------------------------------------------------------
 
Public Sub createtNewReport()
On Error GoTo err_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
 
lngTop = 0
lngLeft = 0
 
Dim dbCurr As Database
Dim rsRecordset As Recordset
 
'set report's record source property
'rpt.RecordSource = "X"
 
Set dbCurr = DBEngine.Workspaces(0).Databases(0)
Set rsRecordset = dbCurr.OpenRecordset("X")
 
sRptName = "Report1"
 
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