Open a report using a form's recordsetclone as the report data source

[Magster]

Hi all,

I want to open a report using an open form's recordsetclone. I know how to get the recordsetclone in VBA - I just don't know how to open the report with the form's data.

I am assuming that I either pass the recordset to the report or reference the open form's recordsetclone from the report. This report is used exclusively from this form.

Code examples would be greatly appreciated!

THX!

 

[Magster]

Well, through a bit more digging, I was able to call the report and use the form's recordsetclone from the report as follows:

from the form in the button on-click event I call the report:
DoCmd.OpenReport "listNonHREmployees", acViewPreview

In the report module itself, I set the recordsource to the form's recordsetclone:
Private Sub Report_Open(Cancel As Integer)
On Error Resume Next
Me.RecordSource = Forms.frmListNonHREmployees.RecordsetClone
End Sub

This works almost... I used the filter button to drill down to a small subset of data and then I clicked the preview report button. I was anticipating that the subset of data would be in the report, but instead I had all records.

I guess I'm back to how do I pass the recordsetclone from the form which in my test, doesn't contain all records?

 

[LPurvis]

The method you've employed isn't what you think it is.
Have you removed the report's RecordSource from design view?
How does it open when you comment out your report's Opening code?
Blank? No rows?
Now restore the Open event procedure - but do not suppress error handling (i.e. leave "On Error Resume Next" commented out).
You should then receive an error (I'd imagine "Type Mismatch").

Assigning the Recordsource property (which accepts a string value to be a locally evaluatable table name, querydef or SQL statement) sees the report load the data based on that - running the required query to do so.
Passing a Recordset object will not see the same (in-memory) data used for the report.
Only assigning the recordset property will do that
Set Me.Recordset = Forms!frmListNonHREmployees.RecordsetClone
and that will fail in an MDB (or ACCDB).

The data is being reloaded by the report. The source isn't shared.
Just as if you attempted to use
Me.RecordSource = Forms.frmListNonHREmployees.RecordsetClone.Name
this is similarly reloading the data - for the "Name" property of a DAO recordset returns the source statement that was used to open the recordset.
That string would be used by the report to open a new resultset and bind the report to it.

If your aim isn't one of saving data fetching overhead but one of filtering, then that's far easier.
Whatever filter you've applied to the form just needs to be applied to the report too.
For example

DoCmd.OpenReport "listNonHREmployees", acViewPreview, , Me.Filter

Alternatively a query which both the form and report are based on which gathers its criteria from a common source.

Cheers.

 

[Magster]

Thank you! It workds great! I had to do a bit of renaming because of combo boxes in the form and matching text boxes in the report but that was minor.

I really appreciate that you took the time to explain... you saved me a lot of time and I can use this concept in a lot of places!

 

[LPurvis]

No worries.

 

[Lukael]

Hi guys,

I seee this is an old thread, but I'm facing same problems. Any way to post some example code, I'm not folowing your answers exactly ?

 

[LPurvis]

Hi

To be honest, it was barely clear what the OP was wanting based on the initial question . Can you describe exactly what it is you're trying to achieve? (And, it'd really be best offered as a new question thread. By all means post a link to that thread here.)

Cheers

 

[Lukael]

Sure, here It is. I googled all over, and your thread is only one I found so far:

stackoverflow.com/questions/36631021/access-report-show-current-recordsource-of-another-form/36634451#36634451


I haven't post a lot on this forum so far, that's why I couldn't post entire link (http is mising..)

I really hope you have answer to this one, I'm struggling with It.

EDIT: I can create a new thread here, but I'm not sure about cross-posting policy you have here...

 

[LPurvis]

Hmm... I'm not registered at Stack Overflow (as far as I can recall, I might be, but I barely even post here anymore, let alone starting elsewhere!)

Your question is essentiall:
>> When I add some record on this form and click for report, this records is not being displayed.
Yes?

One of the things to highlight is:
>> Requery doesn't work, I allready tried that before. Requery removes last saved record from screen, and Report still doesn't show correctly

That's massive. You're saying that the record added isn't committed?
What happens when you simply close the form? Does it open again with the new record? Sounds more like something is preventing the row from being committed, hence no matter what you do it won't appear on the report.
A Requery is slight overkill, but it should have done enough.

Cheers

 

[Lukael]

No, let me explain a little bit more....I have a "search" form - based on criteria I enter in Textboxes here,,, a form with results is opened - with recordsource in continous form as I searched. So recordsource is allready defined here.

Now, let's say I search for something that isn't in table - after opening "results" form, It's recordsource is ofcourse empty....Now I'm starting to adding some records in this continous form... When finished I want to print those newly added records, BUT - Report shows only last added record !

If I click on Report button immediately after "results" form is opened (without adding new records), the Report shows correctly.

 

[LPurvis]

It then just sounds like the new rows you're adding don't fall within the confines of the criteria you've specified.
The classic would be a missing foreign key which is normally entered automatically when opened as a subform in a data entry form.
Examine the table, looking at the newly added rows. Do they comply?

Cheers

 

[Lukael]

Examine the table, looking at the newly added rows. Do they comply?

I have opened "results" form. It has allready data in It. Now I add new record and save record. Then I open table where It should be, and It is. Is this what you meant ?

 

[LPurvis]

Yes the new row is there in the table, but does it contain all the field values that it would need to be included in that same search result?
If you open the search form again and perform the exact same search, are the new rows you added last time present? (That they vanished after a requery suggests that they'd not be.) That would be the problem.

Cheers

 

[Lukael]

Yes the new row is there in the table, but does it contain all the field values that it would need to be included in that same search result?

Yes, all rows are inserted.

If you open the search form again and perform the exact same search, are the new rows you added last time present? (That they vanished after a requery suggests that they'd not be.) That would be the problem.

Yes, when I perform search again for newly added record, "results" form shows this record.

 

[LPurvis]

So, when you're adding new rows to this continuous form - and you requery that form, the new rows disappear.
But if you open the form and perform the exact same search, the new rows are now present?
Beginning to feel a look would be required.

Cheers

 

[Lukael]

Ok, I will post a sample later and you'll see what I'm talking about. I just have to come home again.

 

[LPurvis]

Home is where the heart is.... (and the work office for some :-s)

Will look out for the example later.

Cheers

 

[Lukael]

Ok, I'm done, here is sample. Open form "Search" and type "Paul" (or whatever in Table1 is). Then "Results" form will open in this record. Click on report button - you'll notice this record is in report too.

After that, type some new records in form "Results", and then click on Report button. You'll notice that report doesn't update, although records are allready saved in Table1.

I hope you know answer to this

P.S.: Some of us have work office at home too

 

[LPurvis]

Hi

Yep, as far as I can see it's simply a committing issue. (What can I say... some people struggle with commitment.)
In the example provided, consider if you add three new rows. All of which match the criteria for the current search.
When opening the report, you'll see two of the newly added rows, but not the third - because it's not been committed yet.
Committing can occur in a number of ways. Explicitly through a save command, switching focus between parent and subform, and by navigating to another row.

So in the above example, if, before clicking to launch the report, you navigate back or forward a row from that you've just entered, it is committed, and so also shown in the report.

The earlier mentioned Requery should have sorted that issue for you (as I mentioned, a Requery is a bit heavy handed for this requirement - but would work.)
I'd suggest a simple save
Me.Dirty = False
or a Refresh if you want to make sure the buffer is purged before the report launches.
Me.Refresh
(You can flush the cache by other means, but that'll do.)

Cheers

 

[Lukael]

Ok, seems like you understand It. Where should I put this dirty=false statement, can you show in my sample ?