How do I run a Report from a Listbox w/ control source of a table. 1

[jw83]

I have an Access Database that I am trying to list reports that can be run in a Listbox that is linked by the control source to a table. When a report is clicked on I want it to come up with Output To selection. I am using this code to do that: DoCmd.OpenReport List32, acViewPreview

I am also using code to bring in a list. The only problem is it's listing all reports in the database.

Here is that code:
Private Sub Form_Open(Cancel As Integer)
Dim intNumOfReports As Integer
Dim intRptCounter As Integer
Dim str As String

intNumOfReports = CurrentDb.Containers("Reports").Documents.Count
If intNumOfReports <> 0 Then
Me![List32].RowSourceType = "Value List"
For intRptCounter = 0 To intNumOfReports - 1
str = str & CurrentDb.Containers("Reports").Documents(intRptCounter).Name & ";"
Next
Me![List32].RowSource = Left$(str, Len(str) - 1)
End If
End Sub

Thank for any help!

 

[dhookom]

I would not use the report documents since it will include all reports including subreports. Also, may report names are developer friendly, not user friendly eg: rptEmpHrsProjs and srptDayCal.

I typically use a table of reports with the object name (developer friendly) and title (user friendly). My list box displays the title, not object name.

Duane
Hook'D on Access
MS Access MVP

 

[jw83]

Do you have any sample code?

 

[dhookom]

I have a function, BuildWhere(), that builds a where condition that I can use in the DoCmd.OpenReport method.

Private Sub cmdRunReport_Click()
   On Error GoTo cmdRunReport_Click_Error

On Error GoTo Err_cmdRunReport_Click

    Dim stDocName As String
    Dim strWhere As String
    
    If Not IsNull(Me.lboReport) Then
        Select Case Me.lboReport
            Case Else
                strWhere = strWhere & BuildWhere()
                stDocName = Me.lboReport
                DoCmd.OpenReport stDocName, acPreview, , strWhere
        End Select
     Else
        MsgBox "You must select a report", vbOKOnly + vbInformation, "Select Report"
        Me.lboReport.SetFocus
    End If
Exit_cmdRunReport_Click:
    Exit Sub

Err_cmdRunReport_Click:
    Select Case Err.Number
        Case 2501 'ignore
        Case Else
            MsgBox Err.Description
    End Select
    Resume Exit_cmdRunReport_Click

   On Error GoTo 0
   Exit Sub

cmdRunReport_Click_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdRunReport_Click of VBA Document Form_frmReportSelection"
    
End Sub

The Row Source of the lboReport is

SELECT rptRptName, rptTitle, rptDescription
FROM ztblReports
WHERE rptStatus<>0
ORDER BY rptTitle;

Duane
Hook'D on Access
MS Access MVP

 

[jw83]

I can't get it. Is there an easier way to list reports in a table bind the listbox to that table and click the report you want to preview and it automatically run it?

 

[dhookom]

I have given you a report table with field names for you to enter records. I have suggested the Row Source of the list box that displays the reports to your user. I have provide the code from a command button that will open the selected report when clicked.

Where are you having an issue?

Duane
Hook'D on Access
MS Access MVP

 

[jw83]

Where do I put the code? I currently have it in the subform:

Private Sub Form_Open(Cancel As Integer)

The other is in my list box:

Private Sub List32_AfterUpdate()

Sorry I'm not very good with VBA yet...

 

[dhookom]

Start with a form and add your list box based on your table of reports. Then use the command button wizard to write code to open any report in preview mode. Then change the code behind the command button to look like what I have posted for cmdRunReport_Click().

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya jw83 . . .

[blue]The only problem is it's listing all reports in the database.[/blue]

[blue]dhookom[/blue] has already stated:

[blue] ... Also, may report names are developer friendly, not user friendly eg: [purple]rpt[/purple]EmpHrsProjs and [purple]srpt[/purple]DayCal.[/blue]

He's showing that if you preappend some common text ([purple]srpt[/purple]) to your subreport names, you'll be able to filter them out when you pack your listbox.

[blue]Private Sub Form_Open(Cancel As Integer)
   Dim db As DAO.Database, rst As DAO.Recordset
   Dim SQL As String, Pack As String
   
   Set db = CurrentDb
   SQL = "SELECT Name, Type " & _
         "FROM MSysObjects " & _
         "WHERE ([Type]=-32764);"
   Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
   
   If Not rst.BOF Then
      Do Until rst.EOF
         If Left(rst!Name, 4) <> "[purple][b]srpt[/b][/purple]" Then
            If Pack <> "" Then
               Pack = Pack & ";" & rst!Name
            Else
               Pack = rst!Name
            End If
         End If
         
         rst.MoveNext
      Loop
      
      Me![List32].RowSourceType = "Value List"
      Me![List32].RowSource = Pack
   Else
      MsgBox "No Reports!"
   End If
   
   Set rst = Nothing
   Set db = Nothing
   
End Sub[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[jw83]

Ok my problem is know how to set up my table with report names. It currently is like this:
*****************************
Report (column header)

Cumulative Customer Accuracy
Weekly Customer Accuracy
Cum/Weekly Customer Accuracy
*****************************
I don't have two columns. So do I have to rename all of my reports that I have built to start with "rpt"....

 

[dhookom]

You can just type your actual report names into the table. If you have "friendly" report names then you only need one column. I would still use two column that contain the same value if your friendly name is also your actual report name.

You could create and append query if you had 100s of reports and you didn't want to type them manually:

INSERT INTO ztblReports ( rptRptName, rptTitle) SELECT Name, Name  FROM MSysObjects WHERE [Type]=-32764

Duane
Hook'D on Access
MS Access MVP

 

[jw83]

Ok I'm getting closer. Should lboReport be replaced with my table where the reports are housed?

I really appreciate your help! Sorry I don't have a good understanding of this!

 

[dhookom]

lbo" is a standard prefix used in a common naming convention. It is typically used to name a list box. Go back to a previous posting to see how lboReport is used and what its Row Source might look like.

Duane
Hook'D on Access
MS Access MVP

 

[jw83]

Ok it's saying Compile Error: Sub or Function not defined

Here is what I have now...
********************************
Private Sub Command36_Click()
On Error GoTo cmdRunReport_Click_Error

On Error GoTo Err_cmdRunReport_Click

Dim stDocName As String
Dim strWhere As String

If Not IsNull(Me.List37) Then
Select Case Me.List37
Case Else
strWhere = strWhere & BuildWhere()
stDocName = Me.List37
DoCmd.OpenReport stDocName, acPreview, , strWhere
End Select
Else
MsgBox "You must select a report", vbOKOnly + vbInformation, "Select Report"
Me.List37.SetFocus
End If
Exit_cmdRunReport_Click:
Exit Sub

Err_cmdRunReport_Click:
Select Case Err.Number
Case 2501 'ignore
Case Else
MsgBox Err.Description
End Select
Resume Exit_cmdRunReport_Click

On Error GoTo 0
Exit Sub

cmdRunReport_Click_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdRunReport_Click of VBA Document Form_frmReportSelection"

End Sub
***********************************

 

[dhookom]

As I stated earlier, I have a function BuildWhere() that creates a where condition statement. I rarely open a report without applying some type of filter, typically in the where condition of the DoCmd.OpenReport method.

All you need to do is type an apostrophe to the left of the line so it is ignored.

   If Not IsNull(Me.List37) Then
        Select Case Me.List37
            Case Else
'[green]               strWhere = strWhere & BuildWhere()[/green]
                stDocName = Me.List37
                DoCmd.OpenReport stDocName, acPreview, , strWhere
        End Select
     Else

You should also kick your development up a notch and use a naming convention so you don't see control names like List37 and Command36. This makes a great New Year's resolution ;-)

Duane
Hook'D on Access
MS Access MVP

 

[jw83]

Well this about my 2nd time using code in access. I usually use the tools and expression builders. I'm learning. Have any recommended reading on how to layout your names, etc?

I really appreciate your help!!

 

[jw83]

IT WORKS!!!! YAAAAAHHHHHH!!!! Thanks for being patient with me!!!!

 

[dhookom]

Tony Toews has naming conventions.

It's not important which one you use but you should find and stick to one. It makes posting in these help forums much more readable.

Duane
Hook'D on Access
MS Access MVP