Open a report using a form's recordsetclone as the report data source (1 Viewer)

[LPurvis]

Immediately before your report opening line.

Me.Dirty = False
DoCmd.OpenReport "Report1", acViewReport

 

[Lukael]

I'm sorry to say, but this doesn't work. actually before posting anywhere I tried same with Me.Save, Me.Requery, Docmd.Runcommand accmdSaveRecord & accmdSave, even Me.RecordSource=Forms.Results.RecordsetClone doesn't work. Test it in my sample and you'll see. Do you have any other ideas ?

 

[LPurvis]

So in the example you posted here, inserting that line into the procedure doesn't work for you? You've tried it in the example?

 

[Lukael]

Yes It doesn't work. In my DB and in this Sample file I posted.

 

[LPurvis]

Hi

So, with the code as, in the attached example, being:

 Private Sub Command7_Click()
Me.Dirty = False
DoCmd.OpenReport "Report1", acViewReport
End Sub

If you enter example data a in the image below:

And then click the Report

You don't see that? (The last entered value added?)

 

[Lukael]

I really don't get It, really. I doesn't work for me, take a look. Now very strange thing occured - when I opened your posted sample directly from Winrar, code worked, but once I downloaded to computer, code works as you'll see in printscreen :banghead:

EDIT - It tried again. Now It doesn't work any way around.

EDIT AGAIN - It works when you add new records with same data in 1st row. Try adding new records with completely different text from all other previous records. A very strange occurence

 

[LPurvis]

But... in your example image... you're not entering the text which qualifies it for inclusion in the requested search. (I mentioned this a long while ago.)

In my example above, I searched for Paul, got the matching result and then entered three more Pauls.
Once saved, they appear on the report *because* they match the criteria.
If you're entering random characters that no longer match the search, then they are excluded from the report by definition of the source.

The RecordSource of a form isn't whatever happens to be displayed on that form right now. It's a property (usually a SQL statement, table or query name) which determines what that form will show.

You're currently comparing apples and oranges.

Cheers

 

[Lukael]

I thought we understood each other - I didn't mention anything about entering same values, otherwise I would explicitly tell you that. I told that I need to see current records on report, that's It. And I know It can be done, actually It works when you add these lines before opening report:

DoCmd.OpenForm "Results", acDesign
DoCmd.OpenForm "Results", acNormal

But I don't want that, It flickers the form, normally. I'm sure there is a away this can be achieved, my best suspicion is to find a way with RecordsetClone.

 

[LPurvis]

OK then....

>> I thought we understood each other
That's why I'd made the comment earlier on. Assuming understanding is the mother of all..., well... it's not good.

>> I didn't mention anything about entering same values, otherwise I would explicitly tell you that.
Again, that's a one-sided assumption. Because you didn't mention that the values were the same, you assumed I accepted that they were different.
It could be argued because you didn't assert that they were different, that I assumed they weren't different. See my point?

I, on the other hand, asked the question - and I did so because this is fundamentally how RecordSources work. They do not relate to what is displayed on a form (that's a form's Recordset). They are a description of what WILL be shown on a form when its opening request is made.

>> And I know It can be done, actually It works when you add these lines before opening report:
Again, that's throwing ideas at the problem hoping something will stick.
It's much more important to understand what is going on and how data requests work.
I'll explain what that "solution" is actually doing:

The first line opens the form in Design view, thus removing the temporarily assigned RecordSource. When you then open the form again in Form view, it is using its default RecordSource i.e. the entire "Table1".
The report isn't showing just the newly added rows, it's showing the entire table.

The situation you're in right now is that you want:
• To perform a search, get results in a form which limits displayed rows to that search.
• Add rows which do not conform to that RecordSource (so that if the search were performed again - it would NOT show those... THIS is what I confirmed with you earlier WAS happening. :-s)
• Open a report based on that same passed RecordSource, but ignore the fact that some recent rows don't match that recordset - and instead include them, even though they could be entirely random.

Does that highlight the problem?

Are there solutions (workarounds)? Yes of course.
I can think of two obvious ones off the bat.
Firstly, iterate the Result form's rows and build a new criteria from the entered values.
Secondly, use an unbound report. (Far from standard, but this is going against the grain for data requests.)

See example for both options.

Cheers

 

[Lukael]

Yes, communication was obviously a problem. I'm an intemediate VBA programmer, and It was hard for me to explain this problem as english is not my native language.

I knew there has to be some way with RecordSetClone, since I have done pretty much same thing with exporting to Excel, using CopyFromRecordset property. Unfortunally, I couldn't get this worked in Access only.

Thanks for all responses and provided code, you helped me a lot. Your code works, but "Unbound Report" has problems with null's - that's when searching produces empty recordsource. I'll get this fixed.


Thanks Again !!!

 

[LPurvis]

Hi

No probs with language issues, I can only imagine how hard it is.
Thankfully my biggest issues are spelling properties BackColor instead of BackColour. (And allowing for illogical date formats )

But yes, it's important to list requirements as clearly as possible.

Using the RecordsetClone... yes, but that's only part of the solution.
Accessing the RecordsetClone is trivial. But in an example like CopyFromRecordset that's a method specifically designed to insert Recordset (any Recordset) into a spreadsheet. But otherwise you have to come up with a method. Forms can bind to recordsets, as can list controls - but not reports. Hence it's only half the battle, if that.

Anyway, happy to help, but the issue with the unbound report... (you do still have it in Preview mode and not Report/Layout view? As it depends upon the Format event being raise.)
If you can describe the exact issue, I can look at that.
(The core concept of unbound reports is shown in my examples page.)

Cheers

 

[arnelgp]

its not the recordsourceclone, its the recordsource.
you can't set the recourdsource to a recordset in report.

if you want to filter your report, just as the form does, use the form's filter as your filter

Private Sub Report_Open(Cancel As Integer)
Dim frm As Form
On Error Resume Next
Set frm = Forms![frmListNonHREmployees].Form
Me.RecordSource = frm.RecordSource
If frm.FilterOn=True
Me.Filter = frm.Filter
Me.FilterOn = True
End If
Set frm = Nothing
End Sub

 

[LPurvis]

Hi. I'm afraid we're a ways past that.
I've explained the limitations of using the RecordSource already. That was what the OP was already using and wondering why rows that didn't match that weren't appearing. Hence the requirement to use the RecordsetClone (or in the case I chose, a Recordset.Clone).
What you've offered is, more or less, what the OP already had. (And was failing.)

I believe the distinction between them is now clearly understood by the OP.

Cheers