Opening a report using combo box?

[dolano]

Is it possible to open a report using a combo box. At the moment all my reports are opened using cmd buttons. This is just a question of curiosity, I will continue to look into it myself of course.

 

[jkl0]

You could put your report names in a value list of your combo box and then use just one command button to open your reports with the following code.

Private Sub YourButtonName_Click()
On Error GoTo Err_YourButtonName_Click

    Dim stDocName As String

    stDocName = """ & Me.[ComboBoxName].Column(0) & """

    If Me.[ComboBoxName].Column(0) = "" Then
        Exit Sub
    End If

    DoCmd.OpenReport stDocName, acViewPreview

Exit_YourButtonName_Click:
    Exit Sub

Err_YourButtonName_Click:
    MsgBox Err.Description
    Resume Exit_YourButtonName_Click
    
End Sub

Hope this helps.

 

[dolano]

thank you that was a useful piece of code, I went a different route but will use that code somewhere else down the line

 

[dolano]

There is a way in which you can use a combo box to select and also open any report. you can utilise the snapshotviewer which is a built in activeX control. (However you may not like using activex, so, I dunno; move on). Here is how to achieve it.

you need this code in the form load event:

Private Sub Form Load()
Dim obj As AccessObject, dbs As Object
Dim strlist As String
Set dbs  = Application.CurrentProject
For Each obj In dbs.AllReports
 strlist = strlist & obj.Name & ";"
Next obj
cboReports.RowSourceType = "Value List"
cboReports.RowSource = strlist

This will populate the combo box with all reports if you just want some of them remove the 'AccessObject' and 'Object' part of the code and manually enter the name of the report.

The next stage is to put the snapshot viewer into your form. To do this click on more tools and select snapshotviewer control 11.0. Size the viewer to ur liking.

Next, put this piece of code into the On Change event of the combo box

Private Sub cboReports_Change()
DoCmd.OutputTo acOutputReport, cboReports, acFormatSNP, Application.CurrentProject.Path & "\temp.snp" 
SnapshotViewer1.SnapshotPath = Application.CurrentProject,Path & "\temp.snp"
End Sub

Figure out the code for yourself. and any property settings that need to be done should be pretty obvious.

Good luck.

 

[ansentry]

To fill you combo box with all the reports in your database, put this in the row source:

SELECT [Name] FROM MsysObjects WHERE (([Type]=-32764) And ([Name] Not Like "~*") And ([Name] Not Like "MSys*")) ORDER BY [Name];

This:

SnapshotViewer1.SnapshotPath = Application.CurrentProject,Path & "temp.snp"

Should be:

SnapshotViewer1.SnapshotPath = Application.CurrentProject.Path & "[B]\temp.snp[/B]"

In my opinion this code should be in the AfterUpdate event of the combo not the OnChange.

In the previous code posted:

stDocName = """ & Me.[ComboBoxName].Column(0) & """

Should be:

stDocName = Me.[ComboBoxName]

 

[Keith Nichols]

I haven't tried this but I think you could use a case select in the combo box after update event to determine which entry was selected and then use this as the stDocName in the run report command.

Dim stDocName As String

...

Case "your report name"
    stDocName = "your report name"
    
....

DoCmd.OpenReport stDocName, acPreview

Of course, this would be very longwinded if you have alot of reprots you would like to select from.

 

[Rich]

In the previous code posted:

stDocName = """ & Me.[ComboBoxName].Column(0) & """

Should be:

stDocName = Me.[ComboBoxName]

Actually it should be Me.ComboName or Me![ComboName]

 

[dolano]

For Each obj In dbs.AllReports
strlist = strlist & obj.Name & ";"
Next obj

Thanks John A. but the above code does fill the combo box with all reports in the db. Thanks for pointing out the syntax error on the path name of \temp.snp.

Also,


Code:

SnapshotViewer1.SnapshotPath = Application.CurrentProject.Path & "\temp.snp"


In my opinion this code should be in the AfterUpdate event of the combo not the OnChange.

I did this and for all the difference it makes. All it does as far as I could determine is instead of having to reselect the report you are just after lookinig at you just click it again, why this is useful is beyond me. but thanks for the input.

 

[dolano]

For Each obj In dbs.AllReports
strlist = strlist & obj.Name & ";"
Next obj

Thanks John A. but the above code does fill the combo box with all reports in the db. Thanks for pointing out the syntax error on the path name of \temp.snp.

Also,

Code:

SnapshotViewer1.SnapshotPath = Application.CurrentProject.Path & "\temp.snp"


In my opinion this code should be in the AfterUpdate event of the combo not the OnChange.

I did this and for all the difference it makes. All it does as far as I could determine is instead of having to reselect the report you are just after lookinig at you just click it again, why this is useful is beyond me. but thanks for the input.