Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2012
    Posts
    79

    Unanswered: Procedure assistance.

    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.


    Code:
    Option Compare Database
    Option Explicit
    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
    rs.MoveNext
    Loop
    
    End Sub
    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 07:01. Reason: Messed up the strSQL
    Version: Access 2010

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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:
    Code:
    SELECT Audit_Name, Report_Name FROM [Reports] ORDER BY Report_Name;
    Now you can use:
    Code:
    Private Sub Cbo_Reports_AfterUpdate()
    
        Docmd.OpenReport Me.Cbo_Reports.Value ',,, additional parameters
    
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Nov 2012
    Posts
    79
    Rock on! That's much easier than writing a procedure for it. And I've changed the name of the table, thanks for the catch!
    Version: Access 2010

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •