Using one report for different queries

[john]

I have a report to print envelopes. The report is based on a query. Now I
need to make 10 more queries to make different selections of addresses.
Every query has the same output fields as the already existing query. I know
I can copy the report and base it on another query but then I would have to
make 10 extra reports. How can I use just one report for all of the queries?
At the moment I use the button wizard in my forms to make buttons that start
the particular reports. Resulting code looks like this:

Private Sub Knop34_Click()
On Error GoTo Err_Knop29_Click

Dim stDocName As String

stDocName = "Alle leden per Bezorgcategorie"
DoCmd.OpenReport stDocName, acPreview

Exit_Knop29_Click:
Exit Sub

Can I perhaps add some code that points which query the report should be
based on?
Thanks,
john

[Jeff L]

What is different about your 10 queries? It seems to me like you
should be able to do all of this with one query, one report, and
perhaps a filter.

john wrote:

I have a report to print envelopes. The report is based on a query. Now I
need to make 10 more queries to make different selections of addresses.
Every query has the same output fields as the already existing query. I know
I can copy the report and base it on another query but then I would have to
make 10 extra reports. How can I use just one report for all of the queries?
At the moment I use the button wizard in my forms to make buttons that start
the particular reports. Resulting code looks like this:

Private Sub Knop34_Click()
On Error GoTo Err_Knop29_Click

Dim stDocName As String

stDocName = "Alle leden per Bezorgcategorie"
DoCmd.OpenReport stDocName, acPreview

Exit_Knop29_Click:
Exit Sub

Can I perhaps add some code that points which query the report should be
based on?
Thanks,
john

[pietlinden]

Jeff L wrote:

What is different about your 10 queries? It seems to me like you
should be able to do all of this with one query, one report, and
perhaps a filter.

If you look at the OpenReport method, you will see that one of the
arguments is "Where Condition", which is any valid Where clause (minus
the WHERE keyword).
DoCmd.OpenReport "MyReport", acViewPreview, , "[FieldX]= '" &
Forms![MyOpenForm]![txtValue] & "'"

[john]

The differences between the queries are the selection criteria. For
instance:
1. All members
2. This year's new members
3. Members of a specific zipcode area
4. Female members
5. This year's male members
etc.
I don't know how to put this in one query or report.
john

"Jeff L" <jl*******@hotmail.comschreef in bericht
news:11**********************@m73g2000cwd.googlegr oups.com...

What is different about your 10 queries? It seems to me like you
should be able to do all of this with one query, one report, and
perhaps a filter.

john wrote:

>I have a report to print envelopes. The report is based on a query. Now I
need to make 10 more queries to make different selections of addresses.
Every query has the same output fields as the already existing query. I
know
I can copy the report and base it on another query but then I would have
to
make 10 extra reports. How can I use just one report for all of the
queries?
At the moment I use the button wizard in my forms to make buttons that
start
the particular reports. Resulting code looks like this:

Private Sub Knop34_Click()
On Error GoTo Err_Knop29_Click

Dim stDocName As String

stDocName = "Alle leden per Bezorgcategorie"
DoCmd.OpenReport stDocName, acPreview

Exit_Knop29_Click:
Exit Sub

Can I perhaps add some code that points which query the report should be
based on?
Thanks,
john

[salad]

john wrote:

The differences between the queries are the selection criteria. For
instance:
1. All members
2. This year's new members
3. Members of a specific zipcode area
4. Female members
5. This year's male members
etc.
I don't know how to put this in one query or report.
john

Let's say you have a query like this
Select Emp.* From Employee
This will select all employees

Select Emp.* From Employee Where Sex = "M"
This will select all employees flagges as males

Prior to opening the report you should create/store the "Where clause"
of the SQL (the part that filters the returned records) to a
variable...without the word "Where"...and pass that when opeing the
report. Ex:
"Sex = ""M""
is valid since it does not include the predicate Where.

Now, if you really needed to change the recordsource, you could do that
too. Let's say you had an Employee table and a customer table and the
report could come from any of those sources. I think in the newer
versions of Access you can pass an argument to a report, in Acess97 you
can't...so I'll use the A97 method. Let's say you had 2 queries; EmpQry
and CustQry. The report defaults to using CustQry. In the form
(RptForm) that calls the report, create a hidden field (WhichSource)
that stores either a "C" or "E" in it. THen in the OnOpen event you can
have code similar to this.

If Forms!RptForm!WhichSource = "E" Then
Me.Recordsource = "EmpQry"
Endif
Since the default is for customers, this will change the recordsource to
the Employee query. The fields in the report should be the name in both
queries.

Let's say a field in Customers is CustomerID. How do you use the same
field from the Employee table? Simple, drag the EmployeeID field to a
column. Then in front of it, add the word CustomerID with a colon. Ex:
CustomerID : EmployeeID
Now when you reference the column, it will be referenced using
CustomerID. IOW, you created an alias for the field.

"Jeff L" <jl*******@hotmail.comschreef in bericht
news:11**********************@m73g2000cwd.googlegr oups.com...

>>What is different about your 10 queries? It seems to me like you
should be able to do all of this with one query, one report, and
perhaps a filter.

john wrote:

>>>I have a report to print envelopes. The report is based on a query. Now I
need to make 10 more queries to make different selections of addresses.
Every query has the same output fields as the already existing query. I
know
I can copy the report and base it on another query but then I would have
to
make 10 extra reports. How can I use just one report for all of the
queries?
At the moment I use the button wizard in my forms to make buttons that
start
the particular reports. Resulting code looks like this:

Private Sub Knop34_Click()
On Error GoTo Err_Knop29_Click

Dim stDocName As String

stDocName = "Alle leden per Bezorgcategorie"
DoCmd.OpenReport stDocName, acPreview

Exit_Knop29_Click:
Exit Sub

Can I perhaps add some code that points which query the report should be
based on?
Thanks,
john

[fredg]

On Thu, 31 Aug 2006 21:19:59 +0200, john wrote:

The differences between the queries are the selection criteria. For
instance:
1. All members
2. This year's new members
3. Members of a specific zipcode area
4. Female members
5. This year's male members
etc.
I don't know how to put this in one query or report.
john

"Jeff L" <jl*******@hotmail.comschreef in bericht
news:11**********************@m73g2000cwd.googlegr oups.com...

>What is different about your 10 queries? It seems to me like you
should be able to do all of this with one query, one report, and
perhaps a filter.

john wrote:

>>I have a report to print envelopes. The report is based on a query. Now I
need to make 10 more queries to make different selections of addresses.
Every query has the same output fields as the already existing query. I
know
I can copy the report and base it on another query but then I would have
to
make 10 extra reports. How can I use just one report for all of the
queries?
At the moment I use the button wizard in my forms to make buttons that
start
the particular reports. Resulting code looks like this:

Private Sub Knop34_Click()
On Error GoTo Err_Knop29_Click

Dim stDocName As String

stDocName = "Alle leden per Bezorgcategorie"
DoCmd.OpenReport stDocName, acPreview

Exit_Knop29_Click:
Exit Sub

Can I perhaps add some code that points which query the report should be
based on?
Thanks,
john

How are you currently selecting which report to run?
To use just one report (which is the correct method) and yet return
different sets of data ....
Let's say you have a form with an option group to select which report
data you want.

1 All Members
2 New Members
3 ZipCode
4 Female
5 Male

You would also have an unbound text control on the form (to enter the
wanted ZipCode in).

Add a command button to the form.
Code it's Click event something like:
Dim strWhere as string
Select Case OptionGroupName
Case is = 2
strWhere = "NewMember = -1"
Case is = 3
strWhere = "[ZipCode] = '" & Me![ControlOnFormName] & "'"
Case is = 4
strWhere = "Gender = 'Female'"
Case is = 5
strWhere = "Gender = 'Male'"
End Select
DoCmd.OpenReport "ReportName", acViewPreview, , strWhere

The above is just a generalized method. I have no idea how you
determine, in your database, who is a new or old member, or how you
store the data of who is male or female. If Option 1 is selected, all
the records will be returned.
You can adapt the above to your actual database.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[Jeff L]

Ok. It sounds like your first one, All Members is the most
comprehensive list. So basically, the other queries you made are All
Members query but with some criteria done to it to get a smaller list.
Correct?

You should be able to take the All Members query and make that the data
source of your report. Now when the report is opened, you pass it some
criteria depending on which list you want as the other person's post
suggested.
john wrote:

The differences between the queries are the selection criteria. For
instance:
1. All members
2. This year's new members
3. Members of a specific zipcode area
4. Female members
5. This year's male members
etc.
I don't know how to put this in one query or report.
john

"Jeff L" <jl*******@hotmail.comschreef in bericht
news:11**********************@m73g2000cwd.googlegr oups.com...

What is different about your 10 queries? It seems to me like you
should be able to do all of this with one query, one report, and
perhaps a filter.

john wrote:

I have a report to print envelopes. The report is based on a query. Now I
need to make 10 more queries to make different selections of addresses.
Every query has the same output fields as the already existing query. I
know
I can copy the report and base it on another query but then I would have
to
make 10 extra reports. How can I use just one report for all of the
queries?
At the moment I use the button wizard in my forms to make buttons that
start
the particular reports. Resulting code looks like this:

Private Sub Knop34_Click()
On Error GoTo Err_Knop29_Click

Dim stDocName As String

stDocName = "Alle leden per Bezorgcategorie"
DoCmd.OpenReport stDocName, acPreview

Exit_Knop29_Click:
Exit Sub

Can I perhaps add some code that points which query the report should be
based on?
Thanks,
john

[john]

Thanks.

I fiddled with the filter and all kinds of different quotations and copies
from the sql query but I can't get it to work. This is one the things I
tried and partly works:

Dim stDocName As String
Dim stWhere As String

stDocName = "All male members"
stWhere = "[MF] = M"
DoCmd.OpenReport stDocName, acPreview, , sWhere

It gives me a dialog where it shows the M. If I enter an M there the filter
works.
john

"salad" <oi*@vinegar.comschreef in bericht
news:WV****************@newsread4.news.pas.earthli nk.net...

john wrote:

>The differences between the queries are the selection criteria. For
instance:
1. All members
2. This year's new members
3. Members of a specific zipcode area
4. Female members
5. This year's male members
etc.
I don't know how to put this in one query or report.
john

Let's say you have a query like this
Select Emp.* From Employee
This will select all employees

Select Emp.* From Employee Where Sex = "M"
This will select all employees flagges as males

Prior to opening the report you should create/store the "Where clause" of
the SQL (the part that filters the returned records) to a
variable...without the word "Where"...and pass that when opeing the
report. Ex:
"Sex = ""M""
is valid since it does not include the predicate Where.

Now, if you really needed to change the recordsource, you could do that
too. Let's say you had an Employee table and a customer table and the
report could come from any of those sources. I think in the newer
versions of Access you can pass an argument to a report, in Acess97 you
can't...so I'll use the A97 method. Let's say you had 2 queries; EmpQry
and CustQry. The report defaults to using CustQry. In the form (RptForm)
that calls the report, create a hidden field (WhichSource) that stores
either a "C" or "E" in it. THen in the OnOpen event you can have code
similar to this.

If Forms!RptForm!WhichSource = "E" Then
Me.Recordsource = "EmpQry"
Endif
Since the default is for customers, this will change the recordsource to
the Employee query. The fields in the report should be the name in both
queries.

Let's say a field in Customers is CustomerID. How do you use the same
field from the Employee table? Simple, drag the EmployeeID field to a
column. Then in front of it, add the word CustomerID with a colon. Ex:
CustomerID : EmployeeID
Now when you reference the column, it will be referenced using CustomerID.
IOW, you created an alias for the field.

>"Jeff L" <jl*******@hotmail.comschreef in bericht
news:11**********************@m73g2000cwd.googleg roups.com...

>>>What is different about your 10 queries? It seems to me like you
should be able to do all of this with one query, one report, and
perhaps a filter.

john wrote:

I have a report to print envelopes. The report is based on a query. Now
I
need to make 10 more queries to make different selections of addresses.
Every query has the same output fields as the already existing query. I
know
I can copy the report and base it on another query but then I would have
to
make 10 extra reports. How can I use just one report for all of the
queries?
At the moment I use the button wizard in my forms to make buttons that
start
the particular reports. Resulting code looks like this:

Private Sub Knop34_Click()
On Error GoTo Err_Knop29_Click

Dim stDocName As String

stDocName = "Alle leden per Bezorgcategorie"
DoCmd.OpenReport stDocName, acPreview

Exit_Knop29_Click:
Exit Sub

Can I perhaps add some code that points which query the report should be
based on?
Thanks,
john

[john]

Got it working now with:
stWhere = "MF = 'M'"
john

"john" <jo**@test.comschreef in bericht
news:Vu********************@casema.nl...

Thanks.

I fiddled with the filter and all kinds of different quotations and copies
from the sql query but I can't get it to work. This is one the things I
tried and partly works:

Dim stDocName As String
Dim stWhere As String

stDocName = "All male members"
stWhere = "[MF] = M"
DoCmd.OpenReport stDocName, acPreview, , sWhere

It gives me a dialog where it shows the M. If I enter an M there the
filter works.
john

"salad" <oi*@vinegar.comschreef in bericht
news:WV****************@newsread4.news.pas.earthli nk.net...

>john wrote:

>>The differences between the queries are the selection criteria. For
instance:
1. All members
2. This year's new members
3. Members of a specific zipcode area
4. Female members
5. This year's male members
etc.
I don't know how to put this in one query or report.
john

Let's say you have a query like this
Select Emp.* From Employee
This will select all employees

Select Emp.* From Employee Where Sex = "M"
This will select all employees flagges as males

Prior to opening the report you should create/store the "Where clause" of
the SQL (the part that filters the returned records) to a
variable...without the word "Where"...and pass that when opeing the
report. Ex:
"Sex = ""M""
is valid since it does not include the predicate Where.

Now, if you really needed to change the recordsource, you could do that
too. Let's say you had an Employee table and a customer table and the
report could come from any of those sources. I think in the newer
versions of Access you can pass an argument to a report, in Acess97 you
can't...so I'll use the A97 method. Let's say you had 2 queries; EmpQry
and CustQry. The report defaults to using CustQry. In the form
(RptForm) that calls the report, create a hidden field (WhichSource) that
stores either a "C" or "E" in it. THen in the OnOpen event you can have
code similar to this.

If Forms!RptForm!WhichSource = "E" Then
Me.Recordsource = "EmpQry"
Endif
Since the default is for customers, this will change the recordsource to
the Employee query. The fields in the report should be the name in both
queries.

Let's say a field in Customers is CustomerID. How do you use the same
field from the Employee table? Simple, drag the EmployeeID field to a
column. Then in front of it, add the word CustomerID with a colon. Ex:
CustomerID : EmployeeID
Now when you reference the column, it will be referenced using
CustomerID. IOW, you created an alias for the field.

>>"Jeff L" <jl*******@hotmail.comschreef in bericht
news:11**********************@m73g2000cwd.google groups.com...

What is different about your 10 queries? It seems to me like you
should be able to do all of this with one query, one report, and
perhaps a filter.

john wrote:

>I have a report to print envelopes. The report is based on a query. Now
>I
>need to make 10 more queries to make different selections of addresses.
>Every query has the same output fields as the already existing query. I
>know
>I can copy the report and base it on another query but then I would
>have to
>make 10 extra reports. How can I use just one report for all of the
>queries?
>At the moment I use the button wizard in my forms to make buttons that
>start
>the particular reports. Resulting code looks like this:
>
>Private Sub Knop34_Click()
>On Error GoTo Err_Knop29_Click
>
Dim stDocName As String
>
stDocName = "Alle leden per Bezorgcategorie"
DoCmd.OpenReport stDocName, acPreview
>
>Exit_Knop29_Click:
Exit Sub
>
>Can I perhaps add some code that points which query the report should
>be
>based on?
>Thanks,
>john

[john]

"fredg" <fg******@example.invalidschreef in bericht

How are you currently selecting which report to run?

By pushing a button (one button for every report).

Add a command button to the form.
Code it's Click event something like:
Dim strWhere as string
Select Case OptionGroupName
Case is = 2
strWhere = "NewMember = -1"
Case is = 3
strWhere = "[ZipCode] = '" & Me![ControlOnFormName] & "'"
Case is = 4
strWhere = "Gender = 'Female'"
Case is = 5
strWhere = "Gender = 'Male'"
End Select
DoCmd.OpenReport "ReportName", acViewPreview, , strWhere

The above is just a generalized method. I have no idea how you
determine, in your database, who is a new or old member, or how you
store the data of who is male or female. If Option 1 is selected, all
the records will be returned.
You can adapt the above to your actual database.

Good idea! Thanks.
john

[john]

"Jeff L" <jl*******@hotmail.comschreef in bericht
news:11**********************@m79g2000cwm.googlegr oups.com...

Ok. It sounds like your first one, All Members is the most
comprehensive list. So basically, the other queries you made are All
Members query but with some criteria done to it to get a smaller list.
Correct?

Yes.

You should be able to take the All Members query and make that the data
source of your report. Now when the report is opened, you pass it some
criteria depending on which list you want as the other person's post
suggested.

Thanks...
.... to all for your great help! :-)
john