access reports from vb

[FiRe5tOrM]

i am creating a visual basic project that uses access reports

In my vb project it uses an access database that i want to print out as a access report using a certain query string (the data source will change)

ive managed to print out the report from VB (the first section of coding)
and ive managed to populate the report from within access (in the 2nd section of coding) but how can i have all the coding in just vb please so i can send the current recordsource created in vb to the report so i can the report data i need

Printing the report from VB

Code:--------------------------------------------------------------------------------
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

--------------------------------------------------------------------------------Selecting the data to print in the report in accesses (currently prints all the records)
Code:--------------------------------------------------------------------------------

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

i guess my main problem is i need to refere to an access report from within vb but im struggling to find how to do it. in my program if i write the code in acces for example id only need to use Me.Detail.Controls.Count for examplew to refere to the report in vb but as im not executing the code from access what would i neeed to replace me with in order to refere to that database im guessing its something like
appaccess.reports("allstaffrecs").Controls.Count fore example


i would be very greatful to anyone that could give me any assistance in trying to solve this problem

FiRe5tOrM

 

[Nouba]

you could declare a form module variable like

Dim WithEvents mobjRpt As Access.Report

By selecting mobjRpt in the upper left dropdown list in the code window you'll have access to all public events of the report. After opening the report you have to set the object variable to the report. I. e.

' ...
    Set mobjRpt = .Reports(rptName)
End With

ExitHere:
    Exit Sub
'...

Private Sub mobjRpt_Open(Cancel As Integer)
  ' your event code goes here
End Sub

HTH