ProgrammingMSAccess.com

The ProgrammingMSAccess.COM Site

A pair of VBA procedures that demonstrates how to use an ado recordset as the source for a report.  change str2 so that it points to the report that you want to use.  a complete working sample with the associated report is available in programming microsoft access version 2002. 

Sub ReportBasedOnADORecordset()
Dim cnn1 As ADODB.Connection
Dim rst1 As ADODB.Recordset
Dim str2 As String
Dim rpt1 As Access.Report
Dim bol1 As Boolean

'Open the Connection object
Set cnn1 = New ADODB.Connection
cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\PMA Samples\Northwind.mdb;"

'Obtain a criterion for rst1 WHERE clause, and
'construct Select statement
str1 = InputBox("Enter the first letter for a CustomerID", _
"Programming Microsoft Access Version 2002", "A")
str1 = "SELECT * FROM Customers WHERE Left(CustomerID,1) " & _
"= '" & str1 & "'"

'Open the ADO Recordset object
Set rst1 = New ADODB.Recordset
rst1.ActiveConnection = cnn1
rst1.Open str1, , adOpenKeyset, adLockOptimistic, _
adCmdText

'Link Customers if not in AllTables
MsgBox "Click OK to start compiling data for report. " & _
"Please be patient.", vbInformation, _
"Programming Microsoft Access Version 2002"
DoCmd.Echo False
bol1 = IsLinked("Customers")
If bol1 = False Then
DoCmd.TransferDatabase acLink, "Microsoft Access", _
"C:\PMA Samples\Northwind.mdb", _
acTable, "Customers", "Customers", False
End If

'Assign Source property of ADO recordset to RecordSource
'property for the report, and save the report
str2 = "rptMailingLabels"
DoCmd.OpenReport str2, acViewDesign
Set rpt1 = Reports(str2)
rpt1.RecordSource = rst1.Source
DoCmd.Close , , acSaveYes

'Open report for viewing
DoCmd.OpenReport str2, acViewPreview
DoCmd.Echo True

'Clean up objects and links
rst1.Close
cnn1.Close
Set rst1 = Nothing
Set cnn1 = Nothing
If bol1 = False Then DoCmd.DeleteObject acTable, "Customers"

End Sub

Function IsLinked(str1 As String) As Boolean
Dim obj1 As Access.AccessObject

'Returns True if filename is in AllTables
For Each obj1 In CurrentData.AllTables
If obj1.Name = str1 Then
IsLinked = True
Exit Function
End If
Next obj1

End Function

Want to understand  Microsoft Access 2000/2002/2003 so that you can program it to do more tasks like this?  Get Programming Microsoft Office Access 2003 by Rick Dobson from Microsoft Press.  Learn more about the book by clicking here.

Copyright 2003 CAB, Inc. All rights reserved. Republication or redistribution of CAB, Inc. content, including by framing or similar means, is expressly prohibited without the prior written consent of CAB, Inc. CAB, Inc. shall not be liable for any errors in the content, or for any actions taken in reliance thereon.