CODEWITHSUNDEEP
ms-accessvbareportms-access-2016subform
CrazyCloud
CrazyCloud

Reputation: 11

MS Access 2016: Set Report's RecordSource to get data from a Subform

Is there some sort of work-around that could make this possible? Or could anyone offer just some general advice on my situation below? I tried to set the record source through VBA but had no luck.

My situation:

I have a main form with a subform contained within, and I want the subform to be purely for data entry (Data Entry = Yes). Upon clicking a button, the user is sent from the main form to a report (print preview only). I want the source for this report to be the subform the users are entering data into, but I don't have this option from the report's property sheet itself (no forms), and I also was unable to set it through VBA. Here's my code, it's one line so I highly doubt it's the problem. 

Private Sub Report_Load()

    Reports![P18003 SDR Report].RecordSource = "P18003 SDR Subform"

End Sub

Previously, to work-around this I had a parameter query that waited for the user to enter data into an unbound textbox from the main form, and an after update trigger on that textbox would load any data relevant to that parameter (my employer and I miscommunicated the requirements).

It turns out though that I don't need to load any older data, and I run into problems with my current parameter query in the event that the user does input a parameter which loads old data - that old data is transferred to the report in addition to the new data they've just entered. This is the main problem with the method I am currently using, and it trashes almost all functionality with this form and report. The events that a user would need to enter a parameter which queries older data are few and far between, but it's not a functional product unless I can get the subform to be connected to the report directly. There's likely something obvious I'm missing here (or at least, I hope there is).

Thanks in advance for taking the time to read this and for any help you may offer.

Upvotes: 0

Views: 1865

Answers (1)

Strawberryshrub
Strawberryshrub

Reputation: 3399

you can either send the recordsource to the report itself in the OpenArgs by the open report event, after the click event in the subform

Private Sub btnSubform_Click()

Dim strSQL as String
strSQL = "SELECT * FROM SubformQuery WHERE ID = " & CurrentSubfromRecordID
Docmd.OpenReport, acViewReport, , , , strSQL

End Sub

and then in the Report.

Private Sub Report_Open(Cancel As Integer)
Me.recordsource = Me.OpenArgs
End Sub

Or you can make a paramter Query and set this Query criteria as record source for the report. So the parameter in the query is pointing to the current selected row in the subform. Like this:

--- At the Criteria in the Query designer:
--- RecordSource for the Report:
Forms![FormMain]![YourSubform]![ID]

Now every time the reports obens he gets the ID from the current selected record of your subform.

Upvotes: 0

Related Questions