I'm attempting to write a procedure to open reports based on a combobox selection. The combobox selection relates the the report name but is not exact. I've run into issues putting spaces in my report names so I've used underscores instead of spaces. While rptSystem_Information looks good behind the scenes, it doesn't look as good as System Information in a combobox. Here's what I've come up with.
I've created a table, one with the pretty combobox names, and in the same row, the actual report names. My thought is to loop through the records until strRpt matches the same row as the report name: I.E. System Information ---rptSystem_Information. I'm missing something. Just not sure what.
Option Compare Database
Public Sub OpenReports(strRpt As String)
'Get the name of the report that corresponds with strRpt from table Reports
Dim rs As DAO.Recordset
Dim db As Database
Dim strSQL As String
Set db = CurrentDb
strSQL = "SELECT Audit_Name FROM [Reports] WHERE Report_Name = """ & strRpt & """"
Set rs = db.OpenRecordset(strSQL)
Do While Not rs.EOF
DoCmd.OpenReport (what do I put here), acViewReport
strRpt is the pretty version of System Information, but I need it to say rptSystem_Information in order to open the report.
As usual your assistance is always appreciated.
Last edited by Pis7ftw; 01-12-14 at 06:01.
Reason: Messed up the strSQL
If I understand correctly, you have a table: [Reports] (not a very wise name!) that associates a display name [Report_Name] to an actual name [Audit_Name] for each report.
You don't need to browse A recordset open on that table. All you need can be stored in the combo box, both the display name and the actual name (let's call it Cbo_Reports): Name: Cbo_Reports Column count: 2 Bound column: 1 RowSource:
SELECT Audit_Name, Report_Name FROM [Reports] ORDER BY Report_Name;
Now you can use:
Private Sub Cbo_Reports_AfterUpdate()
Docmd.OpenReport Me.Cbo_Reports.Value ',,, additional parameters