Form values dictate Report Values

[Irie]

Using a button on a form, I use the following code to dictate what records appear in the report, basically whatever records are on the form.

DoCmd.OpenReport "renewalreport", acViewPreview, , "[Renewal]=Forms![Renewalform]![Renewal]"

My problem is that the form is a continuous form and displays several records, but when I use the above code only one record is displayed on the report.

So how do I get the report to display all the records from the report? Is there a way to base a report on a form?

Thnx

 

[dennyryan]

I believe that you would need to pass the same selection criteria to the report as you used in getting your form populated. I know that may not be easy or practical since you're probably using different record sources.

denny

 

[Irie]

Denny,

Thanks for the suggestion. I have thought about what you have said, and have realised that it could work.
But the main problem though, is how do you pass on the information to the report, and the form at the same time?

Does anyone else have any suggestions? Comments?

Thnx

 

[dennyryan]

How is the selection criteria passed to the form? I assume that the user must be entering a value in some control or selecting a value from a combo box such that you get a subset of records to display on the form.

Since you are currently passing selection criteria based on a single field you know where the selection criteria needs to go. The challenge will be to strcuture the selection string using the control values that contain the appropriate criteria.

For Example, if the control named State is how the user limited the records displayed on the form then the VB statements might be:

dim strSQL as String
strSQL = "State= '" & me.State & "'"

DoCmd.OpenReport "renewalreport", acViewPreview, , strSQL


Denny

 

[Irie]

ok, it looks like I had better give the full explanation......

I have a form which is based on a query. The purpose of this form is to see what licences are coming up for renewal.
So when a user launches the renewalform from a mainform, they are prompted to enter the Month and Year they wish to see.
The renewalform will then open displaying the records of the licences that are coming up for renewal that month.

Once these records are shown on the form (continuous form), I have a command button which I want to allow them to then print off a report based on the records that are displayed on the form.

I have used to different pieces of code to launch the report, they both work to the extent that one record will be shown, but nothing else.

DoCmd.OpenReport "renewalreport", acViewPreview, , "[Renewal]=Forms![Renewalform]![Renewal]"

or

Dim strSQL As String
strSQL = "renewaldate= '" & Me.renewaldate& "'"

DoCmd.OpenReport "renewalreport", acViewPreview, , strSQL

Does anyone have any suggestions??
Thnx

[This message has been edited by Irie (edited 08-22-2001).]

 

[disgruntled]

In the report_open() sub in the report itself you can set the recordsouce property. I have a similar project where the queries are different as per the criteria choosen on the form so I set a global variable to the query. But if you just have one you could write it in like so....

Me.RecordSource = "SELECT.......;"

 

[disgruntled]

The original full post is below called "setting recordsource" if I wasn't clear enough.

 

[dennyryan]

You could still conceviably pass the same seelction criteria as used to get the records on the form. it might simplify things if you modified to query used for the report to include Year and Month fields as well (calculated fields).

the selection string might be something like:

strSQL = "Year = " & Forms![MainForm].[Inputyear] & " And Month = " & Forms![MainForm].[InputMonth]

Basically you want to retrieve the selection values from the initial form and then pass those values along in the selection strin on the OpenReports method.

Your report data source would have to contain those selection data elements but that shouldn't be that big a deal.

Denny