Passing recordset between froms, reports

I have a form which will generate a report for mailing. The form opens a recordset with all the fields needed. I'm wandering how can i pass this recordset to the report, so that i don't need to open the same recordset once again.

Similarly, sometimes i also want to pass recordset between forms (no main/sub form relation), how can i do this?

Another little question, when i open a form in datasheet view, it's always very big. How can i limit its size when opening?

Thanks!

EDIT:

To be clearer, say i have "FORM", when the user hit a button on it, "Report" will be open. I want "Report" to use the recordset that i开发者_运维百科s already created(opened) in "FORM".

Also on "FORM", there is a textbox, filled by users, i want also to show it on the "Report".

EDIT2:

I tired but cannot passe the recordset, nor populating a field in my report from a textbox on my form, very annoying ..

You should be able to accomplish this by simply passing the correct SQL statement to your report. This can be done using the Opening Arguments.

Assuming that you really do want the identical set of records on your report as you see on your form, what you need to do depends on how you have opened your form, or how you are filtering your form. You could indeed use an identical DAO Recordset object and set your reports Recordset object to a copy or clone of the Form's Recordset object. However, this might not be necessary to get the results your look for.

Solution #1
If your form uses a query or SQL statement you can use this solution.

Code on your form:

DoCmd.OpenReport "rptReportName", acViewPreview, , , acWindowNormal, Me.RecordSource

Code on your report:

Private Sub Report_Open(Cancel As Integer)
    Me.RecordSource = Nz(Me.OpenArgs, "")
End Sub

Solution #2
Use this solution if your form is using the form's filter property to filter down to the correct set of records. I'm assuming you then want to pass that filter condition on to the report. You'll need to configure the report so that it uses the same RecordSource as your Form (or it must at least contain the table/fields that will be included in your filter statement). The problem is that passing the recordsource of your report to your form doesn't pass any filter that you might have set on the form.

DoCmd.OpenReport "rptReportName", acViewPreview, , Nz(Me.Filter, ""), acWindowNormal

As a final note, it is not possible to set a Report's recordset property. You can assign a Recordsource as I've already shown (a recordsource is a tablename, a queryname, or an SQL statement) but you cannot use the Recordset property unless the database is an Access Data Project, which I don't recommend using at all.

Edit1
It wasn't clear from the original post what problem the OP was trying to solve. I incorrectly assumed he was having trouble getting the same records to show on his report as what he has on his form. It appears that rather the OP is concerned about making two trips to the server to retrieve records.

Because you cannot set the Recordset value on an Access report, your best option might be to create a local Access table and simply use it as a temp table. I don't know what size your recordset typically is. If it's quite large (5000+ records) this solution may not be a good idea. One problem I can think of is that it will cause your front-end database application file to bloat over time unless you have the file setup to run Compact and Repair on close.

I think the worry about two trips to the server is unwarranted.

If you were using a Jet/ACE back end, Jet would cache the data locally, and there wouldn't be a re-retrieval unless the data had changed (in which case I think you'd probably want the up-to-date data, no?).

With a server database, the server itself is likely to cache the results, particularly if the SQL statement used is identical in both cases.

This looks to me like a case of premature optimization.

Well If I have understand from your question you need to manipulate the data in a table by knowing/unknowing the record set number.
If that is then you need to reform the mentality of how to access data in a table, because to promote the record set number in your list is not the quite right way, usually we promote the data and the record numbers are hidden.
So when you read your table try to pass your fields into variables for later use or pass them directly to your list view.
The way of accessing the table to get each data it comes from another process which always varied.
But even when you want to keep the record numbers for later use try to declare a name as public variable as ArrayList() then when you read from table you may use the 'Variable'.add(RecordNumber) .
So when you need to access a particular number take the reading line number from your list view by calling VariableName(ListViewLineNumber) Please inform me if this solution comes closely to you issue solution.