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_Clic k

Dim stDocName As String

stDocName = "Alle leden per Bezorgcategorie "
DoCmd.OpenRepor t stDocName, acPreview

Exit_Knop29_Cli ck:
Exit Sub

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

[Jeff L]

Re: Using one report for different queries

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_Clic k
>
Dim stDocName As String
>
stDocName = "Alle leden per Bezorgcategorie "
DoCmd.OpenRepor t stDocName, acPreview
>
Exit_Knop29_Cli ck:
Exit Sub
>
Can I perhaps add some code that points which query the report should be
based on?
Thanks,
john

[pietlinden@hotmail.com]

Re: Using one report for different queries


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.OpenRepor t "MyReport", acViewPreview, , "[FieldX]= '" &
Forms![MyOpenForm]![txtValue] & "'"

[john]

Re: Using one report for different queries

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" <jleckrone@hotm ail.comschreef in bericht
news:1157051062 .821619.291120@ m73g2000cwd.goo glegroups.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_Clic k
>>
> Dim stDocName As String
>>
> stDocName = "Alle leden per Bezorgcategorie "
> DoCmd.OpenRepor t stDocName, acPreview
>>
>Exit_Knop29_Cl ick:
> Exit Sub
>>
>Can I perhaps add some code that points which query the report should be
>based on?
>Thanks,
>john

>

[salad]

Re: Using one report for different queries

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...with out the word "Where"...a nd 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!W hichSource = "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" <jleckrone@hotm ail.comschreef in bericht
news:1157051062 .821619.291120@ m73g2000cwd.goo glegroups.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_Clic k
>>>
>> Dim stDocName As String
>>>
>> stDocName = "Alle leden per Bezorgcategorie "
>> DoCmd.OpenRepor t 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]

Re: Using one report for different queries

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" <jleckrone@hotm ail.comschreef in bericht
news:1157051062 .821619.291120@ m73g2000cwd.goo glegroups.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_Clic k
>>>
>> Dim stDocName As String
>>>
>> stDocName = "Alle leden per Bezorgcategorie "
>> DoCmd.OpenRepor t stDocName, acPreview
>>>
>>Exit_Knop29_C lick:
>> 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![ControlOnFormNa me] & "'"
Case is = 4
strWhere = "Gender = 'Female'"
Case is = 5
strWhere = "Gender = 'Male'"
End Select
DoCmd.OpenRepor t "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]

Re: Using one report for different queries

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" <jleckrone@hotm ail.comschreef in bericht
news:1157051062 .821619.291120@ m73g2000cwd.goo glegroups.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_Clic k
>
Dim stDocName As String
>
stDocName = "Alle leden per Bezorgcategorie "
DoCmd.OpenRepor t stDocName, acPreview
>
Exit_Knop29_Cli ck:
Exit Sub
>
Can I perhaps add some code that points which query the report should be
based on?
Thanks,
john

[john]

Re: Using one report for different queries

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.OpenRepor t stDocName, acPreview, , sWhere

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

"salad" <oil@vinegar.co mschreef in bericht
news:WVGJg.4043 $bM.2084@newsre ad4.news.pas.ea rthlink.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...with out the word "Where"...a nd 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!W hichSource = "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" <jleckrone@hotm ail.comschreef in bericht
>news:115705106 2.821619.291120 @m73g2000cwd.go oglegroups.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_Clic k
>>>>
>>> Dim stDocName As String
>>>>
>>> stDocName = "Alle leden per Bezorgcategorie "
>>> DoCmd.OpenRepor t 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]

Re: Using one report for different queries

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

"john" <john@test.coms chreef in bericht
news:VuGdnXEM-NlP3GrZRVnygQ@c asema.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.OpenRepor t stDocName, acPreview, , sWhere
>
It gives me a dialog where it shows the M. If I enter an M there the
filter works.
john
>
"salad" <oil@vinegar.co mschreef in bericht
news:WVGJg.4043 $bM.2084@newsre ad4.news.pas.ea rthlink.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...wit hout the word "Where"...a nd 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!W hichSource = "E" Then
>Me.Recordsourc e = "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" <jleckrone@hotm ail.comschreef in bericht
>>news:11570510 62.821619.29112 0@m73g2000cwd.g ooglegroups.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_Clic k
>>>>>
>>>> Dim stDocName As String
>>>>>
>>>> stDocName = "Alle leden per Bezorgcategorie "
>>>> DoCmd.OpenRepor t stDocName, acPreview
>>>>>
>>>>>Exit_Knop2 9_Click:
>>>> Exit Sub
>>>>>
>>>>>Can I perhaps add some code that points which query the report should
>>>>>be
>>>>>based on?
>>>>>Thanks,
>>>>>john
>>>>
>>>

>

[john]

Re: Using one report for different queries

"fredg" <fgutkind@examp le.invalidschre ef 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![ControlOnFormNa me] & "'"
Case is = 4
strWhere = "Gender = 'Female'"
Case is = 5
strWhere = "Gender = 'Male'"
End Select
DoCmd.OpenRepor t "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]

Re: Using one report for different queries

"Jeff L" <jleckrone@hotm ail.comschreef in bericht
news:1157054465 .592348.286920@ m79g2000cwm.goo glegroups.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