Custom Report from combobox selections in Form (1 Viewer)

[NicoleM99]

Dear All,

I am building a personnel/training database and all has been going well until...

I created a form with 8 comboboxes populated with field lists from 2 tables. These are to allow the user to choose which headings appear in their Report. I found a module online, edited the form and report names, and tried to run it but receive various error messages. The form is called CustomReport and the report is called rptCustom. Please help if you can...I am still very new to Access . Thank you, Nicole

Option Compare Database
Option Explicit
Sub CustomReport()
On Error GoTo Err_CustomReport
DoCmd.OpenReport "rptCustom", acDesign

SetReportControls Forms!CustomReport.Combo10.Value, _
Reports!rptCustom.lblField1, Reports!rptCustom.tbfield1
SetReportControls Forms!CustomReport.Combo13.Value, _
Reports!rptCustom.lblField2, Reports!rptCustom.tbfield2
SetReportControls Forms!CustomReport.Combo12.Value, _
Reports!rptCustom.lblField3, Reports!rptCustom.tbfield3
SetReportControls Forms!CustomReport.Combo15.Value, _
Reports!rptCustom.lblField4, Reports!rptCustom.tbfield4

DoCmd.Close acReport, "rptCustom", acSaveYes

DoCmd.OpenReport "rptCustom", acPreview
Exit_CustomReport:
Exit Sub
Err_CustomReport:
MsgBox Err.Description
Resume Exit_CustomReport
End Sub
Private Sub btnCancel_Click()
DoCmd.Close
End Sub
Sub SetReportControls(varFieldName As Variant, conLabel As Control, conTextBox As Control)
If IsNull(varFieldName) Then
conLabel.Caption = " "
conTextBox.ControlSource = ""
conLabel.Caption = varFieldName
conTextBox.ControlSource = varFieldName
End If
End Sub

 

[NicoleM99]

Hello again...

I managed to get this working using the module below, but I have some formatting problems and "Enter Parameter value" messages.

Because I am using data from 2 tables, my headings are coming out as "Personnel Data.Employee Name", "Training Data.Course Descriptions", etc....is there a way to trim the table name in the module?

Any help would be very much appreciated...Nicole

Sub MakeReport()
On Error GoTo Err_MakeReport
DoCmd.OpenReport "rptCustom", acDesign

SetReportControls Forms!CustomReport.Combo10.Value, _
Reports!rptCustom.lblfield1, Reports!rptCustom.tbfield1
SetReportControls Forms!CustomReport.Combo13.Value, _
Reports!rptCustom.lblfield2, Reports!rptCustom.tbfield2
SetReportControls Forms!CustomReport.Combo12.Value, _
Reports!rptCustom.lblfield3, Reports!rptCustom.tbfield3
SetReportControls Forms!CustomReport.Combo17.Value, _
Reports!rptCustom.lblfield4, Reports!rptCustom.tbfield4
SetReportControls Forms!CustomReport.Combo19.Value, _
Reports!rptCustom.lblfield5, Reports!rptCustom.tbfield5
SetReportControls Forms!CustomReport.Combo18.Value, _
Reports!rptCustom.lblfield6, Reports!rptCustom.tbfield6
SetReportControls Forms!CustomReport.Combo16.Value, _
Reports!rptCustom.lblfield7, Reports!rptCustom.tbfield7
SetReportControls Forms!CustomReport.Combo14.Value, _
Reports!rptCustom.lblfield8, Reports!rptCustom.tbfield8

DoCmd.Close acReport, "rptCustom", acSaveYes

DoCmd.OpenReport "rptCustom", acPreview
Exit_MakeReport:
Exit Sub

Err_MakeReport:
MsgBox Err.Description
Resume Exit_MakeReport
End Sub

Sub SetReportControls(varFieldName As Variant, conLabel As Control, conTextBox As Control)
If IsNull(varFieldName) Then
conLabel.Caption = " "
conTextBox.ControlSource = ""
Else
conLabel.Caption = varFieldName
conTextBox.ControlSource = varFieldName
End If
End Sub

Private Sub btnCancel_Click()

DoCmd.Close
End Sub

Private Sub btnMakeReport_Click()

MakeReport
End Sub