Sorry for the cross posting in reports segment

My question to all is;
I have a main report that contains 2 other sub reports. All are dependent on seperate record sources. I need to know how to "on open" to change all of the record sources.
For example;
Main report has a record source of qry1 normally, subreport1 has a record source of subqry1 normally and subreport2 has a record source of subqry2 normally.

I need to be able to in very rare cases open these same reports with different record sources. How can I code this to happen.

Can anyone give me a hand here? Thanks in advance to your responses.

Rick

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.

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