Access report - Recordsetclone as. Recordsource

0
votes

I have a form, which has button for report. I want to set Report .Recordsource to whatever on screen is, so basically I need .RecordsetClone of form send to Report. Here is what I tried, but It doesn't work:

Me.Recordsource= Forms!Myform.RecordsetClone

I get an invalid argument on that. Any ideas how to solve this ?

EDIT:

I tried this too - this button is placed on form which has records and opens Report :

Private Sub cmdOpenReport_Click()

DoCmd.OpenReport "MyReport", acViewReport
   Reports![MyReport].RecordSource = Me.RecordSource
Reports![MyReport].Filter = Me.Filter
Reports![MyReport].FilterOn = True

End Sub
2
ms-accessvba
Recordsource wants a string (query or SQL). You can try Set Me.Recordset = Forms!Myform.RecordsetClone but I'm not sure if this works. – Andre
@Andre, when pasting this in Report_Open event, I got error: "This feature is only available in an ADP". Looks like It doesn't work. – LuckyLuke82

2 Answers

0
votes

You can't do that, but you may get away with:

Me.RecordSource = Forms!Myform.RecordSource

though that will not include a filter applied to the form. However, the Filter can be copied the same way, and then:

Me.Filter = Forms!Myform.Filter  
Me.FilterOn = True

while sorting must be specified in the report the usual way.

Proof of concept

Private Sub Report_Open(Cancel As Integer)

    If MsgBox("Mod 2?", vbQuestion + vbYesNo, "Select") = vbYes Then
        Me.RecordSource = "Select * From TestTable Where Id Mod 2 = 0"
    End If

End Sub
0
votes

Gustav, this is correct answer. I have opened another thread for that, but I wasn't aware of what is wrong and where. Sorry for crossposting. here is link to my thread: Access Report - show current recordsource of another form

Dim strWhere As String

        Me.Dirty = False

        With Me.Recordset.Clone
            Do Until .EOF
                strWhere = strWhere & "," & !ID
                .MoveNext
            Loop
        End With
        strWhere = Mid(strWhere, 2)
        DoCmd.OpenReport "MyReport", acViewReport, WhereCondition:="ID In (" & strWhere & ")

Moderators can delete one of the threads, I can't do that.