On Error GoTo HandleErrors

'points to the location of the database to open
Dim dbName As String
'name of the report to print
Dim rptName As String
'print type
Dim Preview As Long
'used to story current main recordset
Dim strFilter As String

'doesnt seem needed
'Const acNormal = 0
'Const acPreview = 2

Dim appAccess  As Access.Application
Set appAccess = New Access.Application


dbName = App.Path & "\nhsdirectory.mdb"
rptName = "AllStaffRecs"
Preview = acPreview 'acNormal


With appAccess
    '.OpenCurrentDatabase (App.Path & "\nhsdirectory.mdb")
    .OpenCurrentDatabase filepath:=dbName
    'if user wants to preview print first then show print preview screen
    If Preview = acPreview Then
        'alow the user to see the report
       .Visible = True
       'open report in preview mode
       .DoCmd.OpenReport rptName, acViewPreview
    Else
        'open the report
       .DoCmd.OpenReport rptName
       
    End If
End With

ExitHere:
    Exit Sub

HandleErrors:
    Select Case Err.Number
        Case 2501
            MsgBox "No rows have been selected to print", vbOKOnly, "Print Error"
        Case Else
            MsgBox Err.Description, vbOKOnly, "Print Error"
    End Select
    Resume ExitHere
    
End Sub

Option Compare Database
Option Explicit

' From Access 2002 Desktop Developer's Handbook
' by Litwin, Getz, and Gunderloy (Sybex)
' Copyright 2001.  All rights reserved.

Private Sub Report_Open(Cancel As Integer)
    ' You didn't know how many columns, or what
    ' their names would be, until now.
    ' Fill in the label captions,
    ' and control ControlSources.
    
    Dim intColCount As Integer
    Dim intControlCount As Integer
    Dim i As Integer
    Dim strName As String
    
    On Error Resume Next
    
    Dim rst As ADODB.Recordset
    
    Set rst = New ADODB.Recordset
    
    rst.Open _
    Source:=Me.RecordSource, _
    ActiveConnection:=CurrentProject.Connection, _
    Options:=adCmdTable
    
    intColCount = rst.Fields.Count
    intControlCount = Me.Detail.Controls.Count
    
    If intControlCount < intColCount Then
        intColCount = intControlCount
    End If
    
    ' Fill in information for the necessary controls.
    For i = 1 To intColCount
        strName = rst.Fields(i - 1).Name
        Me.Controls("lblHeader" & i).Caption = strName
        Me.Controls("txtData" & i).ControlSource = strName
        'Me.Controls("txtSum" & i).ControlSource = _
        ' "=Sum([" & strName & "])"
    Next i
    
    ' Hide the extra controls.
    For i = intColCount + 1 To intControlCount
        Me.Controls("txtData" & i).Visible = False
        Me.Controls("lblHeader" & i).Visible = False
        'Me.Controls("txtSum" & i).Visible = False
    Next i
    
    ' Close the recordset.
    rst.Close
End Sub