Opening a report in another Database

[gblack]

I'm sure this can't be too tough...

I have a table with the names of Reports in another database. All I want to do is have a form where someone can click on a dropdown box, choose the name of the report and click a button to have the report run.

So far I have the DDB and the Button... I am unclear as to what the event procedure should look like in order for Access to understand that I want it to open a report from a DIFFERENT database.

I'm not sure how to do this... Can someone spell it all out for me?

Thanks a bunch,
Gary

 

[llkhoutx]

Only one database can be used at a time.

I've never tried linking reports, but you might try that.

 

[RoyVidar]

dim acc as access.application
set acc=createobject("access.application")
acc.opencurrentdatabase("c:\yourtest.mdb")
acc.docmd.openreport "rptYourRpt",acviewpreview

The fun stuff begins when you need to close the external db instance too, but if you keep it open until you close this form, then issue acc.quit...

 

[gblack]

Thank you!`

Thanks.... I think that's what I was looking for.
G

 

[Access4All]

Advice on Closing Application

After the acc.quit I would advise :
Set acc = Nothing

This releases the object and is important particularly if a number of reports are to be run in one session, otherwise you could end up with memory issues in Access.

 

[Access4All]

Returning a Cancel value

To my original reason for looking at this post.

Does anyone know how to return a Cancel value back to the calling application.

I use the following code in the Report

Private Sub Report_NoData(Cancel As Integer)
MsgBox "NO DATA", vbExclamation
End Sub

Somehow I need to cancel the report because I get the message "NO DATA" but it still produces a blank report (after the following code)
acc.DoCmd.OpenReport "Tray", acViewPreview

 

[Access4All]

Returning a Cancel Value

Sorry got that wrong I forgot I had a message in two places.
Within the Report in Access.
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
...
With Me.Controls
....If IsNull(.Item(0)) Then
........MsgBox "No Labels to Print"
........Me.Application.Quit
........Exit Sub
....End If
End With
...
End Sub

This works but the application that ran the report doesn't know the application object has been closed.

How can I tell that the application object is closed.

 

[Access4All]

Solution

How to Cancel a Report Which Contains No Records

http://support.microsoft.com/default.aspx?scid=kb;en-us;151587
Within the Report itself :
Sub Report_Open(Cancel As Integer)
....If IsNull(DLookup("<AnyFieldInQuery>", "<QueryName>")) Then
........MsgBox "No Data to Print"
........Cancel = True
....End If

End Sub