Please help: set recordsource of report

[Koen]

Hi all,

I've struggled with the following problem and welcome all a suggestions to
solve this.

I have a form that list records from a table with observations.
The record source for the table depends on from where the form is opened,
but always looks like:

SELECT tblObservation.* FROM tblObservation WHERE ...

Needless to say, it is the where clause that limits the records retrieved
from the backend (the db is split).
Once the form is open, filters can be applied.

What I would like to do, is open a report (rptObservationAndActions) from
the list form a described above.
But it should _only_ retrieve those observations that were preselected and
filtered in the listform.
The difficulty for me is that the report does not only contain data from
tblObservation, but also has a LEFT JOIN to tblAction.
So, the record source of the report is like:

SELECT tblObservation.*, tblAction.*
FROM tblObservation LEFT JOIN tblAction ON tblObservation.Observation_ID =
tblAction.Observation_ID;

For example there are 1000 records in tblObservation.
I do a select that gives me just a preselection of 100 records and after
that I apply some filter to the form that leaves me with a result of 5.
When I do the command 'print report' from the form, it should retrieve and
print only the same 5 records, including the details from tblAction.

How do I manage to set the correct recordsource to the report in this case?

Thanks in advance!

Kind regards, Koen

[Larry Linson]

It's simpler if the Form's RecordSource is SQL (but if it is a saved query,
you can get the SQL from the query's SQL property). You'll have to combine
the criteria in the WHERE with the criteria in the Form's Filter property
and construct the SQL you want for the Report. As long as the Form is open
when you open the Report, you can pick up the information in the Report's
Open event from the Form.

Larry Linson
Microsoft Access MVP

"Koen" <[e-mail]> wrote in message
news:Xn*********************@194.109.133.20...

Hi all,

I've struggled with the following problem and welcome all a suggestions to
solve this.

I have a form that list records from a table with observations.
The record source for the table depends on from where the form is opened,
but always looks like:

SELECT tblObservation.* FROM tblObservation WHERE ...

Needless to say, it is the where clause that limits the records retrieved
from the backend (the db is split).
Once the form is open, filters can be applied.

What I would like to do, is open a report (rptObservationAndActions) from
the list form a described above.
But it should _only_ retrieve those observations that were preselected and
filtered in the listform.
The difficulty for me is that the report does not only contain data from
tblObservation, but also has a LEFT JOIN to tblAction.
So, the record source of the report is like:

SELECT tblObservation.*, tblAction.*
FROM tblObservation LEFT JOIN tblAction ON tblObservation.Observation_ID =
tblAction.Observation_ID;

For example there are 1000 records in tblObservation.
I do a select that gives me just a preselection of 100 records and after
that I apply some filter to the form that leaves me with a result of 5.
When I do the command 'print report' from the form, it should retrieve and
print only the same 5 records, including the details from tblAction.

How do I manage to set the correct recordsource to the report in this case?
Thanks in advance!

Kind regards, Koen

[Salad]

Koen wrote:

Hi all,

I've struggled with the following problem and welcome all a suggestions to
solve this.

I have a form that list records from a table with observations.
The record source for the table depends on from where the form is opened,
but always looks like:

SELECT tblObservation.* FROM tblObservation WHERE ...

Needless to say, it is the where clause that limits the records retrieved
from the backend (the db is split).
Once the form is open, filters can be applied.

What I would like to do, is open a report (rptObservationAndActions) from
the list form a described above.
But it should _only_ retrieve those observations that were preselected and
filtered in the listform.
The difficulty for me is that the report does not only contain data from
tblObservation, but also has a LEFT JOIN to tblAction.
So, the record source of the report is like:

SELECT tblObservation.*, tblAction.*
FROM tblObservation LEFT JOIN tblAction ON tblObservation.Observation_ID =
tblAction.Observation_ID;

For example there are 1000 records in tblObservation.
I do a select that gives me just a preselection of 100 records and after
that I apply some filter to the form that leaves me with a result of 5.
When I do the command 'print report' from the form, it should retrieve and
print only the same 5 records, including the details from tblAction.

How do I manage to set the correct recordsource to the report in this case?

Thanks in advance!

Kind regards, Koen

Have you considered passing a filter?

Docmd.OpenReport "ReportName",,,"whereclause"

Your "whereclause" should be a valid whereclause WITHOUT the word "Where" ExL

Docmd.OpenReport "ReportName",,,"WHERE customerid = 1" XXX BAD NO GOOD

Docmd.OpenReport "ReportName",,,"customerid = 1" 'Good OK

Remember...in filters strings in quotes, dates between #, and numbers has
nothing around it. Ex:
Var = 1
Docmd.OpenReport "ReportName",,,"customerid = " & var

Var = Date()
Docmd.OpenReport "ReportName",,,"Datefld = #" & var & "#"

Var = "Hello"
Docmd.OpenReport "ReportName",,,"customertext = ' " & var & " ' "
remove the spaces between quotes in above example.