What I did was import the excel files into tables in Access, then I created a Form with 7 comboboxes populated with the headings of my tables (ex. Name, address, ph#, manager, job title, training course, duration, etc). Then I put this code behind the Form command button ("Make Report") allowing the user to create a custom report. I still haven't figured out how to format the report using VBA...I need it to always sort by the lefthand column (lblfield1/tbfield1). If you find a way to do this, please let me know. Ciao! Nicole
Private Sub
Command39_Click()
*your command button name*
On Error GoTo Err_MakeReport
DoCmd.OpenReport "
rptCustom", acDesign
*your report name (create it first)*
SetReportControls Forms!CustomReport.
Combo10.Value, _
*combobox names*
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
SetReportControls Forms!CustomReport.
Combo17.Value, _
Reports!rptCustom.lblfield5, Reports!rptCustom.tbfield5
SetReportControls Forms!CustomReport.
Combo19.Value, _
Reports!rptCustom.lblfield6, Reports!rptCustom.tbfield6
SetReportControls Forms!CustomReport.
Combo18.Value, _
Reports!rptCustom.lblfield7, Reports!rptCustom.tbfield7
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