"Henry Stockbridge" <hs***********@hotmail.com> wrote in message
news:11********************@z14g2000cwz.googlegrou ps.com...
Hi,
My computer just hiccuped, so if this went through already, my
apologies.
I am attempting to have a User access a report in a separate
application based on the value from a combo box, but it fails. Any
help you can lend would be appreciated.
Here is the code...
==========
Private Sub cmdPreviewReport_Click()
Dim rptTitle As String
Dim wsp As Workspace
Dim dbs As Database
Dim dbsAnother As Database
' Return reference to current database.
Set dbs = CurrentDb
' Return reference to default workspace.
Set wsp = DBEngine.Workspaces(0)
' Return reference to the Reports database
Set dbsAnother = wsp.OpenDatabase("\\NETWORKLOCATION\Reports.mdb")
rptTitle = Me.cboReports.Column(0)
DoCmd.OpenReport Me.cboReports, acViewPreview
Set dbs = Nothing
Set dbsAnother = Nothing
End Sub
==========
Henry
The OpenCurrentDatabase method is required to open a report in a separate
application.
Paste the following code into a new Module and save it as
"modOLEOpenReport". Then modify your code thus:
Private Sub cmdPreviewReport_Click()
Dim rptTitle As String
rptTitle = Me.cboReports.Column(0)
OLEOpenReport "\\NETWORKLOCATION\Reports.mdb", rptTitle, ,
acViewPreview
End Sub
See if this works for you.
Microsoft References:
Article 145707: ACC: How to Use Automation to Print Microsoft Access Reports
Article 317113: How To Automate Microsoft Access From Visual Basic .NET
'--------------------<<< Begin Code >>>--------------------
' This function was originally posted under Microsoft Article
' 145707, and has been modified to incorporate the
' method to connect to an external, password-protected
' database as described in Microsoft Article 317113.
Function OLEOpenReport(strDBName As String, _
strRptName As String, _
Optional sDBPassword As Variant, _
Optional ByVal intDisplay As Variant, _
Optional ByVal strFilter As Variant, _
Optional ByVal strWhere As Variant) As Boolean
On Error GoTo OLEOpenReport_Err
Dim objAccess As Object
Dim objDBEngine As DAO.DBEngine
Dim objDB As DAO.Database
' Create new instance of Microsoft Access
Set objAccess = CreateObject("Access.Application")
If Not IsMissing(sDBPassword) Then
' Establish a DAO connection to an external
' database under password protection
Set objDBEngine = objAccess.DBEngine
Set objDB = objDBEngine.OpenDatabase(strDBName, _
False, False, ";PWD=" & sDBPassword)
End If
' Open the external database in the new Access instance
objAccess.OpenCurrentDatabase strDBName, False
If Not IsMissing(sDBPassword) Then
objDB.Close
Set objDB = Nothing
Set objDBEngine = Nothing
End If
If IsMissing(intDisplay) Then intDisplay = acNormal
If IsMissing(strFilter) Then strFilter = ""
If IsMissing(strWhere) Then strWhere = ""
' If view is acViewDesign or acViewPreview then
' maximize the new Access session instance
If intDisplay > 0 Then
objAccess.DoCmd.RunCommand acCmdAppRestore
objAccess.DoCmd.RunCommand acCmdAppMaximize
End If
' Open the report with the specified parameters
objAccess.DoCmd.OpenReport strRptName, _
intDisplay, strFilter, strWhere
' If view is acViewDesign or acViewPreview then
' maximize the report display
If intDisplay > 0 Then objAccess.DoCmd.Maximize
' If view is acViewDesign or acViewPreview then
' wait for the user to close the report
Do While intDisplay > 0 And objAccess.SysCmd( _
acSysCmdGetObjectState, acReport, strRptName) > 0
DoEvents
Loop
' Close new Access session instance
objAccess.Quit acExit
Set objAccess = Nothing
OLEOpenReport = True
OLEOpenReport_End:
Exit Function
OLEOpenReport_Err:
MsgBox error$(), vbInformation, "Automation"
Resume OLEOpenReport_End
End Function
'---------------------<<< End Code >>>---------------------
