Custom Report from Form (1 Viewer)

[NicoleM99]

Dear All,

I am building a personnel/training database and has been going well until I attempted to create a form which allows the user to create custom reports. It contains 8 comboboxes populated with field lists from 2 tables. These are to allow the user to choose which headings appear in their Report. Has anyone encountered this? any ideas? I'm still quite new at Access but I'm fairly competent in Excel (including modules, macros, etc).

Any help would be much appreciated...thank you, Nicole .

 

[sxschech]

If I understand the question, you would put text boxes on the report that refer to the comboboxes on the form.

In the properties window of the report text box

Control Source =forms!formname.comboboxname

formname would be the name of your form
combobox name would be the name of your combobox on the form

 

[NicoleM99]

Thank you for your response ...

That doesn't work so well...what it gives me is the field heading (repeatedly) rather than the actual data under that heading. ex. the Field List from my Personnel Data table is: Employee Name, Address, Phone, Town, Province, etc. What I need is the data not the headings. I have managed to get it working using a module (pls see below), but there are formatting problems and "Enter Parameter Value" errors for some of the sections. How are your skills with macros ?

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.lblfield1, Reports!rptCustom.tbfield5
SetReportControls Forms!CustomReport.Combo18.Value, _
Reports!rptCustom.lblfield2, Reports!rptCustom.tbfield6
SetReportControls Forms!CustomReport.Combo16.Value, _
Reports!rptCustom.lblfield3, Reports!rptCustom.tbfield7
SetReportControls Forms!CustomReport.Combo14.Value, _
Reports!rptCustom.lblfield4, 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

 

[sxschech]

Sorry my suggestion did not work for you. I haven't used macros. I think maybe I'm unclear on what you were trying to do as I thought you wanted a customized header, but sounds like it is more than that.

 

[NicoleM99]

Thank you for trying to help out ...I've got the report working now, just a small formatting glitch left to figure out.