Use VBA to CREATE a report (1 Viewer)

NicoleM99

Registered User.
Local time
Today, 13:23
Joined
Mar 7, 2012
Messages
13
Dear Akram,

I have managed to put together a report using VBA, but it only allows the user to print the report form, not export into excel. What are you trying to build exactly?
 

Akram Yadullahi

New member
Local time
Today, 17:53
Joined
Mar 13, 2012
Messages
2
Thanks a lot Nicole! I have an excel worksheet template of the report prepared and have to link all my variables through VBA and populate the printable form of the report as you have mentioned.

Thanks for your help :)

Regards
Akram
 

NicoleM99

Registered User.
Local time
Today, 13:23
Joined
Mar 7, 2012
Messages
13
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
 

Simon_MT

Registered User.
Local time
Today, 12:23
Joined
Feb 26, 2007
Messages
2,177
It looks like the table is not normalised.

If it was you could simply write the individual records for each of the fields using a combi-box.

This would mean that the report would only reflect the fields completed.

Simon
 

NicoleM99

Registered User.
Local time
Today, 13:23
Joined
Mar 7, 2012
Messages
13
Hi Simon,

I'm sorry but I don't understand normalized :)...

There are headings (field names) from 2 separate tables in the comboboxes (on my Form "CustomReport"). They define what the page header labels are in the report ("rptCustom"). I am trying to sort by the left-hand column, but because it changes depending on the choice the user makes, I cannot sort by field name...I need to sort by lblfield1 and/or tbfield1.

Any help you could provide would be very much appreciated...

Nicole :)
 

Attachments

  • example.xls
    524.5 KB · Views: 214

Caqnu

New member
Local time
Today, 13:23
Joined
Feb 25, 2020
Messages
1
Hi Happy YN,

I just come accross this thread today. Some time ago i had posted a sample db here:
DynamicXtabRpt

It's a dynamic report but there are still some limitations. Is it possible that you could use VB codes to conditionally alter the print orientation and number of colomns when required?

Peter


cannot download DynamicXtabRpt . has it been removed?
 

Users who are viewing this thread

Top Bottom