07-18-2003, 04:52 AM
Join Date: Jul 2003
Access report via vb
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
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
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
'.OpenCurrentDatabase (App.Path & "\nhsdirectory.mdb")
'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
'open the report
Select Case Err.Number
MsgBox "No rows have been selected to print", vbOKOnly, "Print Error"
MsgBox Err.Description, vbOKOnly, "Print Error"
Selecting the data to print in the report in accesses (currently prints all the records)
Option Compare Database
' 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
intColCount = rst.Fields.Count
intControlCount = Me.Detail.Controls.Count
If intControlCount < intColCount Then
intColCount = intControlCount
' 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 & "])"
' 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
' Close the recordset.