The following is a module written by David Ward and it isvery helpfull.
It should solve your predicament. Just read and follow the instructions.
Best of luck.
Robert
Option Explicit
' **************modAccessReports.bas***************
' Yu must reference the access object library
' Copy paste this entire module into a code module
' and save it a a code module template. It can be added
' to any project that requires previewing, printing,
' or emailing of Access reports.
'
' This module is based on
' "ACC: How to Use Automation to Print Microsoft Access Reports"
' Microsoft KB Article ID Q 145707
'
' What I have done is simplify the processes (??),
' and made the code easier to read and use.
' Dave Ward
dc****@xtra.co.nz
' ************************************************** *
Declare Function SetForegroundWindow Lib "User32" _
(ByVal hwnd As Long) As Long
Declare Function ShowWindow Lib "User32" _
(ByVal hwnd As Long, ByVal nCmdShow As Long) As Long
Public Const SW_MAXIMIZE = 3 'Show window maximized
Global Const acNormal = 0 ' -- sends to the printer constant
Global Const acDesign = 1 ' -- not available from VB frontend
Global Const acPreview = 2 ' -- preview window constant
Public Sub PrintAccessReport(dbName As String, _
rptName As String, _
Optional rptFilter As Variant, _
Optional rptWhere As Variant)
On Error GoTo PrintAccessReport_Err
Dim objAccess As Object
Set objAccess = GetObject(DBPath)
With objAccess
' open the database
' .OpenCurrentDatabase filepath:=dbName
' send the report to printer.
.DoCmd.OpenReport rptName, 0, rptFilter, rptWhere
DoEvents ' off it goes to the printer
End With
' tidy up
objAccess.Quit
Set objAccess = Nothing
Exit Sub
PrintAccessReport_Err:
MsgBox Error$(), vbInformation, " Access Database Automation Error"
End Sub
Public Sub PreviewAccessReport(dbName As String, _
rptName As String, _
Optional rptFilter As Variant, _
Optional rptWhere As Variant)
On Error GoTo PreviewAccessReport_Err
Dim SIZE As Variant
Dim hwnd As Long
Dim temp As Long
SIZE = SW_MAXIMIZE
Dim objAccess As Object
Set objAccess = CreateObject("Access.Application")
With objAccess
' open the database
.OpenCurrentDatabase dbName, False
' make it visible
.Visible = True
' maximize the db window size
hwnd = objAccess.hWndAccessApp
temp = SetForegroundWindow(hwnd)
temp = ShowWindow(hwnd, SIZE)
' open the report
.DoCmd.OpenReport rptName, 2, rptFilter, rptWhere
' maximize the report window
' inside the db window
.DoCmd.Maximize
End With
PreviewAccessReport_End:
objAccess.Quit
Set objAccess = Nothing
Exit Sub
PreviewAccessReport_Err:
MsgBox Error$(), vbInformation, " Access Database Automation Error"
Resume PreviewAccessReport_End
End Sub
Public Sub EmailAccessReport(dbName As String, _
rptName As String, _
rptFormat As String, _
rptTo As String, _
rptSubject As String, _
Optional rptCc As String, _
Optional rptBcc As String, _
Optional rptMessage As String, _
Optional rptEdit As Boolean, _
Optional rptTemplate As String)
On Error GoTo EmailAccessReport_Err
Dim objAccess As Object
Set objAccess = CreateObject("Access.Application")
With objAccess
' open the database
.OpenCurrentDatabase filepath:=dbName
' email a report
.DoCmd.SendObject acReport, _
rptName, _
rptFormat, _
rptTo, _
rptCc, _
rptBcc, _
rptSubject, _
rptMessage, _
rptEdit, _
rptTemplate
DoEvents
End With
' tidy up
Set objAccess = Nothing
Exit Sub
EmailAccessReport_Err:
MsgBox Error$(), vbInformation, " Access Database Automation Error"
End Sub
Public Sub SaveReportAsWordDoc(dbName As String, _
rptName As String, _
rptPath As String)
On Error GoTo SaveReportAsWordDoc_Err
Dim objAccess As Object
Set objAccess = CreateObject("Access.Application")
With objAccess
' open the database
.OpenCurrentDatabase dbName, False
.DoCmd.OutputTo acReport, rptName, acFormatRTF, rptPath, -1
End With
SaveReportAsWordDoc_End:
Exit Sub
SaveReportAsWordDoc_Err:
MsgBox Error$(), vbInformation, " Access Database Automation Error"
Resume SaveReportAsWordDoc_End
End Sub
Public Sub SaveReportAsHTML(dbName As String, _
rptName As String, _
rptPath As String)
On Error GoTo SaveReportAsHTML_Err
Dim objAccess As Object
Set objAccess = CreateObject("Access.Application")
With objAccess
' open the database
.OpenCurrentDatabase dbName, False
.DoCmd.OutputTo acReport, rptName, "HTML", rptPath, -1
End With
SaveReportAsHTML_End:
Exit Sub
SaveReportAsHTML_Err:
MsgBox Error$(), vbInformation, " Access Database Automation Error"
Resume SaveReportAsHTML_End
End Sub
Public Sub SaveReportAsExcel(dbName As String, _
rptName As String, _
rptPath As String)
On Error GoTo SaveReportAsExcel_Err
Dim objAccess As Object
Set objAccess = CreateObject("Access.Application")
With objAccess
' open the database
.OpenCurrentDatabase dbName, False
.DoCmd.OutputTo acReport, rptName, acFormatXLS, rptPath, -1
End With
SaveReportAsExcel_End:
Exit Sub
SaveReportAsExcel_Err:
MsgBox Error$(), vbInformation, " Access Database Automation Error"
Resume SaveReportAsExcel_End
End Sub
' ********* Form module procedures
' **Preview Report
'Private Sub cmdPreviewReport_Click()
'Dim db As String
'Dim rptName As String
'db = "C:\...\...\Database\MyDB.mdb"
'rptName = "My Report Name"
'
'PreviewAccessReport db, rptName
'End Sub
' **Print Report
'Private Sub PrintReport_Click()
'Dim db As String
'Dim rptName As String
'db = "C:\...\...\Database\MyDB.mdb"
'rptName = "My Report Name"
'
'PrintAccessReport db, rptName
'End Sub
' **Email Report
'Private Sub cmdEmailReport()
'Dim db As String
'Dim rptName As String
'Dim rptFormat As String
'Dim rptTo As String
'Dim rptSubject As String
'Dim rptMessage As String
'
'db = "C:\...\...\Database\MyDB.mdb"
'rptName = "My Report Name"
'rptFormat = "HTML"
'rptTo = "dc****@xtra.co.nz"
'rptSubject = "Subject Title: " & Date
'rptMessage = "Attached is our latest report for your information."
'
'EmailAccessReport db, rptName, rptFormat, rptTo, rptSubject, , , _
' rptMessage, False
'
'End Sub
Public Sub AccessReportList()
Dim db As String
Dim intChan1 As Long
Dim strResponse As String
db = App.Path & "\RangeLocal.mdb"
On Error GoTo PrintAccessReport_Err
Dim objAccess As Object
Set objAccess = CreateObject("Access.Application")
With objAccess
' open the database
.OpenCurrentDatabase filepath:=db
.Visible = False
intChan1 = DDEInitiate("MSAccess", "RangeLocal")
'request a list of reports in the database
strResponse = DDERequest(intChan1, "ReportList")
MsgBox strResponse
DDETerminate intChan1
End With
' tidy up
Set objAccess = Nothing
Exit Sub
PrintAccessReport_Err:
MsgBox Error$(), vbInformation, " Access Database Automation Error"
End Sub
"Nicola" <bo***@bonzai.co.uk> wrote in message
news:42********@mk-nntp-2.news.uk.tiscali.com...
Hi Everyone,
I am new to programming and would like to know how to
open an access Report from within vb 6. I am trying to write a program
to organise cross stitch threads. I have found out how to use a database
table
but all I want to do now is to click a command button to display this
access report.
Any suggestions please ?????
Thank you in advance
