468,511 Members | 1,634 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,511 developers. It's quick & easy.

Please help: set recordsource of report

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
Nov 12 '05 #1
2 2724
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" <no@spam.nl> 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

Nov 12 '05 #2
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.

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Jayjay | last post: by
1 post views Thread by George Kandaz | last post: by
3 posts views Thread by Patrik | last post: by
3 posts views Thread by Don | last post: by
reply views Thread by NPC403 | last post: by
1 post views Thread by fmendoza | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.