Reports in a ComboBox

[kybb1]

Good Afternoon,

How can I capture ALL reports and have them listed in a combo box?

Also, if a new report is added, I would like the combo box updated...can this be done?

Thanks.

 

[dcx693]

Take a look at this thread: automatically populate combo box with available tables using SQL . It discusses tables, but the same technique is applicable to reports.

Basically assign this SQL to the rowsource of your combo box:
SELECT Name FROM MSysObjects WHERE Type=-32764;

Just requery the combo box when a new report is added to the database, or reopen the form that has the combo box with the report names in order to update it.

 

[kybb1]

Thanks DCX...works perfectly....

 

[dcx693]

I did not know that Pat. Thanks for the info.

kybb1, if you want to list your reports in a combo box the "approved" way, take a look at this thread . It shows how to get a list of tables into a combo box, but shouldn't be hard to adapt for reports.

 

[kybb1]

Pat,

I use a naming convention for all my objects. tbl, frm, qry, rpt, etc....

All I wanted to do was capture ALL the reports within the db in a combo box to pick from.

BTW...below is code I've been working on (tinkering with...I'm not the best with VB...self taught and a lot of help from you pro's out there....but if you guys could take a look at it and tell me if there is a better way...it would be greatly appreciated......

What I'm trying to accomplish is SAVE the FORM as a REPORT.

But the form must be closed in order to do so.

Me.txtFormName = Me.cboForms.Column(0)
Me.txtRenameForm = Me.cboForms.Column(0)

strOldFrmName = Me.cboForms.Column(0)
strOldFrmName = Me.txtFormName
strReportName = "rpt" & Mid(strOldFrmName, 4)
strNewFormName = Me.txtRenameForm
strFormName = "frm" & Mid(strNewFormName, 4)

DoCmd.Rename strReportName, acForm, strOldFrmName
DoCmd.SelectObject acForm, strReportName, True
SendKeys "%fa{home}+^{right 2}-{end}", True
DoCmd.Rename strNewFormName, acForm, strReportName

Mucked up as it is...I pick the form from a combo box...rename the form to a (and yes using the naming convention)"report name". Using the SEND KEYS, SAVE AS, then rename the form back to it's original name.

If there is a better way....I'd love to see it.

Thanks....