Code Examples

Calling a Report Wizard to Create a New Report

acCmdNewObjectReport

You can use the following sample procedure start the Report Wizard in Microsoft Access from an Automation controller:

'***************** Code Start *******************
'From Microsoft Knowledge Base Article Q147816
'----------------------------------------------------------------------
'DECLARATIONS
'----------------------------------------------------------------------

Option Explicit
Dim objAccess as Object

'----------------------------------------------------------------------
'This procedure starts the Report Wizard in Microsoft Access using a
'specified database and table (or query) as the record source. This
'procedure does not close the instance of Microsoft Access because
'objAccess is a module-level variable. To call this procedure, use the
'following syntax:
' CallReportWizard _
' dbname:= _
' "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb",
' sourcetype:="table", sourcename:="Employees"
'----------------------------------------------------------------------

Sub CallReportWizard(dbname As String, sourcetype As String, sourcename As String)
  Dim objtype As Integer
  On Error GoTo CallReportWizard_ErrHandler
  Set objAccess = CreateObject("Access.Application")
  With objAccess
    .Visible = True
    .OpenCurrentDatabase filepath:=dbname
    If LCase(sourcetype) = "table" Then
      objtype = Access.acTable
    Else
      objtype = Access.acQuery
    End If
    .DoCmd.SelectObject objecttype:=objtype, objectname:=sourcename, inDatabaseWindow:=True
    .DoCmd.RunCommand acCmdNewObjectReport
  End With
  Exit Sub

CallReportWizard_ErrHandler:
  If Err <> 2501 Then 'Error did not occur by canceling Report Wizard.
    MsgBox Error$(), , "Call Report Wizard"
  End If
End Sub

'****************** Code End ********************

© 1998 - 2011 Terry Wickenden TKW Design Site developed, maintained and hosted by TKW Design. This site is best viewed at 1024 x 768. Optimised for Firefox.