Change Names In Combo Box

[theSizz]

I have a combo box named cboReports.

This is the SQL in the row source property that is used to populate the combo box with the report names.
SELECT MSysObjects.Name FROM MSysObjects WHERE (((MSysObjects.Name) Not Like "*Sub*") AND ((MSysObjects.Type)=-32764)) ORDER BY MSysObjects.Name;

Using this method I end up with a drop down list that the user views with cryptic names such as rptNtWk and rptCompNetGrp ,etc. etc. Is there a way to populate this list with more representative names of what the reports actually produce? Such as Net Weekly Sales, Compare Group Assets, etc. etc.

Here’s the code that’s in the AfterUpdate Event of the combo box.

Private Sub cboReports_AfterUpdate()

Dim strDocName As String
strDocName = Me![cboReports]

    DoCmd.OpenReport strDocName, acPreview

End Sub

Any help would be appreciated.
Thanks for your time.

 

[Remou]

Not without setting up a table of reports containing a column for the 'friendly' name and a column for the report name.

 

[theSizz]

Hi,Remou

Thanks for your reply. Your solution works fine.

For others who may be interested I am posting an alternative solution that was offered by Bill Mosca.
Using this method it is not necessary to create a separate table.

You can use the report caption to give the report a more readable name. Fill in all your reports' caption property and then call this function from your form's Load event. Pass the combo box as the argument. Call it like this:

Private Sub Form_Load()
    Call GenR8RptList(me.cboReportList)
End Sub

Public Function GenR8RptList(ctrl As Control)
    Dim strRptList
    Dim rpt As Object
    Dim oRpt As Report
    For Each rpt In CurrentProject.AllReports
        If rpt.Name Like "*SUB*" Then
            'skip this one
        Else
            DoCmd.OpenReport rpt.Name, acViewDesign, , , acHidden
            Set oRpt = Reports(rpt.Name)
            strRptList = strRptList & rpt.Name & ";" & oRpt.Caption & ";"
            Set oRpt = Nothing
            DoCmd.Close acReport, rpt.Name
        End If
    Next
    'trim last semi-colon
    If Right(strRptList, 1) = ";" Then
        strRptList = Left(strRptList, Len(strRptList) - 1)
    End If
 
    ctrl.RowSource = strRptList
 
End Function

Change the combo box's (cboReportList) RowSource type to Value List, and leave the Row Source empty. The code will fill in the values. Also set the column count to 2 and column widths to 0";0.5"

There you have it!