List of reports to preview.

[comer2k]

Hi Guys. Am a bit stuck. Need some help is poss.

I have an unbound combo box in a form that I want to list ALL reports in.

Ive found multiple examples on the site but cant get them to work.

My combo bow is called ComboReports and I have set Row Source Type to Table/Query. In the RowSource box I have input

SELECT [MSysObjects].[Name] FROM MsysObjects WHERE (Left$([Name],1)<>"~") And ([MSysObjects].[Type])=-32764 ORDER BY [MSysObjects].[Name];

This part works fantastic.

The part thats causing an issue is my button. I want it to open the selected report on click but its not working.

My button is called PrintReport and I have the following event procedure for on click

Private Sub PrintReport_Click() 
   If Not IsNull(ComboReports) And ComboReports <> "" Then 
     DoCmd.OpenReport ComboReports, acViewPreview  ' use acNormal to print without preview 
   Else 
     MsgBox ("You Must First Select a Report To Print!") 
     ComboReports.SetFocus 
   End If 
   ComboReports = "" 
End Sub

when I press my button nothing happens. I cant work out whats wrong, could it be something in the form? or is the button event wrong?

Thanks

[patjones]

You can use this to populate the combo box:

Private Sub Form_Open(Cancel As Integer)

Dim oAccess As AccessObject

Me.ComboReports.RowSourceType = "Value List"

For Each oAccess In CurrentProject.AllReports
    Me.ComboReports.AddItem oAccess.Name
Next oAccess

Set oAccess = Nothing

End Sub

To open the report after picking it from the list:

Private Sub ComboReports_AfterUpdate()

DoCmd.OpenReport Me.ComboReports, acViewPreview

End Sub

Let me know how it works.

Pat

[comer2k]

when I put the first string of code in the form rather than the text I get no reports in my combo box.

I also attempted to only use the second string of code with my original box and that brings up an error saying "Charactors found ant the end of SQL statement.

[patjones]

The first set of code, from lines 3 to 11, is meant to be put in the On Open event of your form. The second set of code (line 3) goes in the After Update event of your combo box; this will cause the report to open in a preview as soon as it is selected from the list. This eliminates the need for a command button and the logic that goes into checking whether the user made a selection from the combo box yet.

Pat

[comer2k]

Hi,

Ive put lines 3-11 in the on open event and my reports now show in the combo box. However second part of the code still wont open a preview. Its in the correct box but I dont know why it wont open. Can there be something in the form settings stopping a preview?

[patjones]

Does anything at all happen when you make the selection from the combo box? Not even an error?

[comer2k]

No nothing. The selected report looks like it greys out for a second (as if it confirms its the selection) but after that nothing.

[patjones]

Can you post your code? Thanks.

[comer2k]

Hi

Its

Private Sub ComboReports___AfterUpdate()
DoCmd.OpenReport Me.ComboReports, acViewPreview
End Sub

[patjones]

One thing that sticks out is that you have too many underscores in the subroutine declaration:

Change

Private Sub ComboReports___AfterUpdate()

to

Private Sub ComboReports_AfterUpdate()

Pat

[missinglinq]

There's absolutely nothing wrong with your original code, comer2k! I just copied it and ran it and it works as it should. I would copy the code from the PrintButton's OnClick event, delete the button, then recreate it.

Welcome to Bytes!

Linq ;0)>