Pass variable between form and report (objects) 1

[ponderdj]

Hello,

I've been trying to read about variable scope, and have come to the conclusion that forms are objects and variables declared within them do not exist outside of them, i.e. on another form.

I'm trying to have a command button open a report, and on open set it's recordset equal to the form's recordset. I'm trying to do it by setting a varible to a DAO.Recordset in the form, then trying to set the report's recordset to that variable on open.

I've tried a couple of different ways of passing the variable from the form into a module and then back to the report with no luck.

Any ideas?

 

[MajP]

Both the form and the report modules are class modules like any class module. On the report at the top put the following, you have basically created a custom property on the report.

public rsTheFormsRecordset as dao.recordset

from the form that opens the report
Open the report then

reports("theReportsName").rsTheFormsRecordset = Me.Recordset

 

[ponderdj]

This is on the form that opens the report

Private Sub Cmd_Print_FP_Main_Click()

    DoCmd.OpenReport "Rpt_FP_Main", acViewPreview
    Reports("Rpt_FP_Main").rst9 = Me.Recordset

End Sub

I put this in the On_Open for the report:

Private Sub Report_Open(Cancel As Integer)
Public rst9 As DAO.Recordset

Report.Filter = Form_Frm_FP_Main.Filter
Report.FilterOn = True

End Sub

And at that line (public rsTheFormsRecordset as dao.recordset) I get the error:
"Invalid attribute in sub or function"
-------------------------------------------------
I've read that you can declare a public variable in a module, set a form's recordset to that public variable, and then set a different form's(or report in this case) recordset equal to that public variable.

 

[ponderdj]

Oh I see! It's because I declared rst9 as public in the private proceedure. Ok, I now moved it to the top of the report section, but I get and error at this line:

Reports("Rpt_FP_Main").rst9 = Me.Recordset

Saying: "Obejct variable or with block variable not set."

But I do have "Public rst9 As DAO.Recordset" at the top of Rpt_FP_main. Hmm..

 

[MajP]

Sorry,
I was typing fast. I need to set the object.
Set Reports("Rpt_FP_Main").rst9 = me.recordset

Also to avoid a long discussion about form classes and the forms collection. If the name of your report is "FP_Main" then the index is "FP_Main". If the name is "Rpt_FP_Main", then you are okay.

 

[ponderdj]

Ok,

I can get the code to run now, but the report opens with an empty recordsource because it's publc variable, rst9 doesn't get set until after it is opened and goes through it's on open process. If I try to set it's recordsource = rst9 during the on open, the problem arises that rst9 isn't set yet. So rst9 gets set too late, and if I try to set it before the report opens I get "application or object defined error". I think I might try to set the variable in a module, any ideas?

 

[ponderdj]

Well, this article is making me think it's not possbile:

http://support.microsoft.com/?scid=kb;en-us;287437&spid=2509&sid=global

To clarify, I want to set the recordset (or recordsource?) of the report to be the same as the form that opens it without requerying the same query (that the form queried to set it's recordsource) again.

Someone please correct me if I'm wrong.

Thanks

 

[PHV]

In a standard code module declarations section:
Public rst9 As DAO.Recordset

In your form:
Private Sub Cmd_Print_FP_Main_Click()
Set rst9 = Me.Recordset
DoCmd.OpenReport "Rpt_FP_Main", acViewPreview
End Sub

In the Open event procedure of the report:
Set Me.Recordset = rst9

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ponderdj]

PHV,

Thanks for responding...

Using your exact method, I get this error:
"Compile Error: Method or Data Member not found."
In the open event proceedure of the report on the line:
Set Me.Recordset = rst9

Is there anything I have to do differently in the standard code module declarations section? In a Module called mod_SetRst I have:

Option Compare Database
Public rst9 As DAO.Recordset

Is there anything I have to do to get it to "fire"?

Thanks.

 

[MajP]

Here is a clean way to do it:

Private Sub cmdOpenRpt_Click()
DoCmd.OpenReport "rptTest", acViewDesign
With Reports("rptTest")
.Filter = Me.Filter
.OrderBy = Me.OrderBy
.OrderByOn = False
.FilterOn = False
If Me.OrderByOn Then .OrderByOn = True
If Me.FilterOn Then .FilterOn = True
End With
DoCmd.OpenReport "rptTest", acPreview
End Sub

 

[ponderdj]

MajP,

Will that set the report's recordset = to the form's recordset? That's what I'm trying to do, without having the report run the query again when it opens.

Thanks

 

[MajP]

No, but basically whatever you see on the form will be the same as the report. I only have A2k so I do not know about later versions, but as far as I know you can only set the Redcordsource of a form which is a string, I do not think there is a way to assign a recordset object. Why do you not want it to rerun the query? I did this with a command button, so I could leave the form and report both open. If I make a change, filter, or sort in the report, and click the command button, you see the changes on the report.

 

[ponderdj]

The goal of this all is to not rerun the query because it takes awhile. Since the form has already queried the table, and has the information set in it's recordsource, and when I set a variable = to it's recordset I can see it has the data on the form, I should be able to transfer that to a report without rerunning the query (which takes awhile), right?

 

[MajP]

I might be wrong because PHV hinted that you could do this and he is usually right. However, in A2k I can retrieve and set a forms recordset, but I can only retrieve and set the recordsource of a report which is only a string. That is why I beleive you get this error

Using your exact method, I get this error:
"Compile Error: Method or Data Member not found."
In the open event proceedure of the report on the line:
Set Me.Recordset = rst9

If the query takes a long time to open maybe you want to save it to a temp table, and have the report pull from the temp table.

 

[ponderdj]

Thanks, MajP.

I'm going to wait because I want to see what PHV has to say, but if nothing else I'll have to use a temp table... it should work, I just want to know if the way I was looking for is possible.

Once again, Thanks

 

[PHV]

What is your version of access ?
Works for me in ac2003.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ponderdj]

I'm using 2000. Guess that could be the problem?

 

[MajP]

Take a look at this:

http://support.microsoft.com/?scid=kb;en-us;287437&spid=2509&sid=global

It can be done in 2000 with a little trickery.

 

[MajP]

This works:
In a module at the top after options

global glblRecordset as dao.recordset

On the form:

Private Sub cmdOpenRpt_Click()
Set glblRecordset = Me.Recordset
DoCmd.OpenReport "rptTest", acPreview
End Sub

On the report:

Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = glblRecordset.Name
End Sub

 

[MajP]

Ponderdj,
I gave you a star for teaching me something, I never tried. Now, I am curious if the report runs faster. My guess is that it will not. Loading a report is often much more involved than a form. For example if you have sections that are to be kept together, the report retireves the data, tries to populate a page, sees it can not fit all the data on a page, and must start a new page. Grouping also has to be done ahead of time. There are many things that can help optimize a query. Check out this site for references on doing that.