Can I print a report with two differentsets of data?

K

Kelvin Beaton

Hi have a report that is based on this query:
SELECT tbl_ClientRecord.ClientID, tbl_ClientRecord.Address, IIf([city] Is
Null,"",StrConv([City],3) & ", " & StrConv([State],1) & " " & [zip]) AS
CityStateZip, tbl_ClientRecord.PrintTNDenialLetter,
StrConv([BeneficiaryFirstName] & " " & [BeneficiaryLastName],3) AS
BeneficiaryFullName, "Dear Parent or Guardian of " &
(StrConv([BeneficiaryFirstName] & " " & [BeneficiaryLastName],3)) & ":" AS
DearFullName
FROM tbl_ClientRecord INNER JOIN tbl_Beneficiaries ON
tbl_ClientRecord.ClientID = tbl_Beneficiaries.ClientID
WHERE (((tbl_ClientRecord.PrintTNDenialLetter)=Yes))
ORDER BY tbl_ClientRecord.Address;

The report prints find when I want to print based on the query above, but
can I use the same report and print just the current client record?

I thought this code would print the corrent record, but it prints all the
records marked "Yes" as in the query above:
Dim stDocName As String
Dim strWhere As String
stDocName = "rpt_PrintTennCareTermination-Letter"
DoCmd.OpenReport stDocName, acPreview
strWhere = "[ClientID] = " & Me.[ClientID]
DoCmd.OpenReport stDocName, acPreview, "", strWhere

If this code worked it would only work if the records was marked to be
printed... "WHERE (((tbl_ClientRecord.PrintTNDenialLetter)=Yes))"

How can I use one report to print using differents sets of data?

Thanks

Kelvin
 
K

Klatuu

Remove all the WHERE criteria from the query.
Use the WHERE argument of the OpenReport method to do your filtering.
 
K

Kelvin Beaton

Can you give me a small example of what that statement would look like?
Thanks

Kelvin


Klatuu said:
Remove all the WHERE criteria from the query.
Use the WHERE argument of the OpenReport method to do your filtering.
--
Dave Hargis, Microsoft Access MVP


Kelvin Beaton said:
Hi have a report that is based on this query:
SELECT tbl_ClientRecord.ClientID, tbl_ClientRecord.Address, IIf([city] Is
Null,"",StrConv([City],3) & ", " & StrConv([State],1) & " " & [zip]) AS
CityStateZip, tbl_ClientRecord.PrintTNDenialLetter,
StrConv([BeneficiaryFirstName] & " " & [BeneficiaryLastName],3) AS
BeneficiaryFullName, "Dear Parent or Guardian of " &
(StrConv([BeneficiaryFirstName] & " " & [BeneficiaryLastName],3)) & ":"
AS
DearFullName
FROM tbl_ClientRecord INNER JOIN tbl_Beneficiaries ON
tbl_ClientRecord.ClientID = tbl_Beneficiaries.ClientID
WHERE (((tbl_ClientRecord.PrintTNDenialLetter)=Yes))
ORDER BY tbl_ClientRecord.Address;

The report prints find when I want to print based on the query above, but
can I use the same report and print just the current client record?

I thought this code would print the corrent record, but it prints all the
records marked "Yes" as in the query above:
Dim stDocName As String
Dim strWhere As String
stDocName = "rpt_PrintTennCareTermination-Letter"
DoCmd.OpenReport stDocName, acPreview
strWhere = "[ClientID] = " & Me.[ClientID]
DoCmd.OpenReport stDocName, acPreview, "", strWhere

If this code worked it would only work if the records was marked to be
printed... "WHERE (((tbl_ClientRecord.PrintTNDenialLetter)=Yes))"

How can I use one report to print using differents sets of data?

Thanks

Kelvin
 
K

Klatuu

Look in VBA Help at the OpenReport method. It as an example there.
If you don't understand it, post back.
--
Dave Hargis, Microsoft Access MVP


Kelvin Beaton said:
Can you give me a small example of what that statement would look like?
Thanks

Kelvin


Klatuu said:
Remove all the WHERE criteria from the query.
Use the WHERE argument of the OpenReport method to do your filtering.
--
Dave Hargis, Microsoft Access MVP


Kelvin Beaton said:
Hi have a report that is based on this query:
SELECT tbl_ClientRecord.ClientID, tbl_ClientRecord.Address, IIf([city] Is
Null,"",StrConv([City],3) & ", " & StrConv([State],1) & " " & [zip]) AS
CityStateZip, tbl_ClientRecord.PrintTNDenialLetter,
StrConv([BeneficiaryFirstName] & " " & [BeneficiaryLastName],3) AS
BeneficiaryFullName, "Dear Parent or Guardian of " &
(StrConv([BeneficiaryFirstName] & " " & [BeneficiaryLastName],3)) & ":"
AS
DearFullName
FROM tbl_ClientRecord INNER JOIN tbl_Beneficiaries ON
tbl_ClientRecord.ClientID = tbl_Beneficiaries.ClientID
WHERE (((tbl_ClientRecord.PrintTNDenialLetter)=Yes))
ORDER BY tbl_ClientRecord.Address;

The report prints find when I want to print based on the query above, but
can I use the same report and print just the current client record?

I thought this code would print the corrent record, but it prints all the
records marked "Yes" as in the query above:
Dim stDocName As String
Dim strWhere As String
stDocName = "rpt_PrintTennCareTermination-Letter"
DoCmd.OpenReport stDocName, acPreview
strWhere = "[ClientID] = " & Me.[ClientID]
DoCmd.OpenReport stDocName, acPreview, "", strWhere

If this code worked it would only work if the records was marked to be
printed... "WHERE (((tbl_ClientRecord.PrintTNDenialLetter)=Yes))"

How can I use one report to print using differents sets of data?

Thanks

Kelvin
 
K

Kelvin Beaton

Thanks Dave for your help so far...

I'd like to take this a step further.

I have the report menu working (see code below).

What I would like to do now is pass it a parameter.



This is the code behind my print button:

Dim stDocName As String

stDocName = Me.cmboReportList

DoCmd.OpenReport stDocName, acPreview



I've added a field called "Condition" to the table "tbl_List_of_reports"



strWhere = "[tables].[tbl_List_of_reports].[condition would go here]"

DoCmd.OpenReport stDocName, acPreview, "", strWhere



So I would end up with something like this:

Dim stDocName As String

Dim strWhere As String

strWhere = "[tables].[tbl_List_of_reports].[condition]"

stDocName = Me.cmboReportList

DoCmd.OpenReport stDocName, acPreview, stWhere



Is this close at all?



Thanks for your help!



Kelvin



+++++++++++++++++++++++++++++++++++++++++++++++++++++

Here is a sample process for creating the menu that I'm using.



Create a table with your report all names. Call it "tbl_List_of_reports"

Create a form named "Reports"

Add a combo box to the form based on the "tbl_List_of_reports" table

Name the Combo Box "Report_Selected"

Using the command button wizard add a print report button.

Select any one of your reports to print. (this is temporary)



Edit the VB code behind that button to reflect the name of the combo box

change the following

stDocName = "you report name"

to

stDocName = me.Report_Selected

+++++++++++++++++++++++++++++++++++++++++++++++++++++




Klatuu said:
Look in VBA Help at the OpenReport method. It as an example there.
If you don't understand it, post back.
--
Dave Hargis, Microsoft Access MVP


Kelvin Beaton said:
Can you give me a small example of what that statement would look like?
Thanks

Kelvin


Klatuu said:
Remove all the WHERE criteria from the query.
Use the WHERE argument of the OpenReport method to do your filtering.
--
Dave Hargis, Microsoft Access MVP


:

Hi have a report that is based on this query:
SELECT tbl_ClientRecord.ClientID, tbl_ClientRecord.Address, IIf([city]
Is
Null,"",StrConv([City],3) & ", " & StrConv([State],1) & " " & [zip])
AS
CityStateZip, tbl_ClientRecord.PrintTNDenialLetter,
StrConv([BeneficiaryFirstName] & " " & [BeneficiaryLastName],3) AS
BeneficiaryFullName, "Dear Parent or Guardian of " &
(StrConv([BeneficiaryFirstName] & " " & [BeneficiaryLastName],3)) &
":"
AS
DearFullName
FROM tbl_ClientRecord INNER JOIN tbl_Beneficiaries ON
tbl_ClientRecord.ClientID = tbl_Beneficiaries.ClientID
WHERE (((tbl_ClientRecord.PrintTNDenialLetter)=Yes))
ORDER BY tbl_ClientRecord.Address;

The report prints find when I want to print based on the query above,
but
can I use the same report and print just the current client record?

I thought this code would print the corrent record, but it prints all
the
records marked "Yes" as in the query above:
Dim stDocName As String
Dim strWhere As String
stDocName = "rpt_PrintTennCareTermination-Letter"
DoCmd.OpenReport stDocName, acPreview
strWhere = "[ClientID] = " & Me.[ClientID]
DoCmd.OpenReport stDocName, acPreview, "", strWhere

If this code worked it would only work if the records was marked to be
printed... "WHERE (((tbl_ClientRecord.PrintTNDenialLetter)=Yes))"

How can I use one report to print using differents sets of data?

Thanks

Kelvin
 
K

Klatuu

No, not even close, sorry.

strWhere = "PrintTNDenialLetter = -1"
DoCmd.OpenReport stDocName, acPreview, "", strWhere

The Where argument is the same as an SQL WHERE without the word WHERE.
also = yes will not work. Yes is not a constant for -1, but True is;
however, SQL doesn't understand constants, you have to use the actual value.
--
Dave Hargis, Microsoft Access MVP


Kelvin Beaton said:
Thanks Dave for your help so far...

I'd like to take this a step further.

I have the report menu working (see code below).

What I would like to do now is pass it a parameter.



This is the code behind my print button:

Dim stDocName As String

stDocName = Me.cmboReportList

DoCmd.OpenReport stDocName, acPreview



I've added a field called "Condition" to the table "tbl_List_of_reports"



strWhere = "[tables].[tbl_List_of_reports].[condition would go here]"

DoCmd.OpenReport stDocName, acPreview, "", strWhere



So I would end up with something like this:

Dim stDocName As String

Dim strWhere As String

strWhere = "[tables].[tbl_List_of_reports].[condition]"

stDocName = Me.cmboReportList

DoCmd.OpenReport stDocName, acPreview, stWhere



Is this close at all?



Thanks for your help!



Kelvin



+++++++++++++++++++++++++++++++++++++++++++++++++++++

Here is a sample process for creating the menu that I'm using.



Create a table with your report all names. Call it "tbl_List_of_reports"

Create a form named "Reports"

Add a combo box to the form based on the "tbl_List_of_reports" table

Name the Combo Box "Report_Selected"

Using the command button wizard add a print report button.

Select any one of your reports to print. (this is temporary)



Edit the VB code behind that button to reflect the name of the combo box

change the following

stDocName = "you report name"

to

stDocName = me.Report_Selected

+++++++++++++++++++++++++++++++++++++++++++++++++++++




Klatuu said:
Look in VBA Help at the OpenReport method. It as an example there.
If you don't understand it, post back.
--
Dave Hargis, Microsoft Access MVP


Kelvin Beaton said:
Can you give me a small example of what that statement would look like?
Thanks

Kelvin


Remove all the WHERE criteria from the query.
Use the WHERE argument of the OpenReport method to do your filtering.
--
Dave Hargis, Microsoft Access MVP


:

Hi have a report that is based on this query:
SELECT tbl_ClientRecord.ClientID, tbl_ClientRecord.Address, IIf([city]
Is
Null,"",StrConv([City],3) & ", " & StrConv([State],1) & " " & [zip])
AS
CityStateZip, tbl_ClientRecord.PrintTNDenialLetter,
StrConv([BeneficiaryFirstName] & " " & [BeneficiaryLastName],3) AS
BeneficiaryFullName, "Dear Parent or Guardian of " &
(StrConv([BeneficiaryFirstName] & " " & [BeneficiaryLastName],3)) &
":"
AS
DearFullName
FROM tbl_ClientRecord INNER JOIN tbl_Beneficiaries ON
tbl_ClientRecord.ClientID = tbl_Beneficiaries.ClientID
WHERE (((tbl_ClientRecord.PrintTNDenialLetter)=Yes))
ORDER BY tbl_ClientRecord.Address;

The report prints find when I want to print based on the query above,
but
can I use the same report and print just the current client record?

I thought this code would print the corrent record, but it prints all
the
records marked "Yes" as in the query above:
Dim stDocName As String
Dim strWhere As String
stDocName = "rpt_PrintTennCareTermination-Letter"
DoCmd.OpenReport stDocName, acPreview
strWhere = "[ClientID] = " & Me.[ClientID]
DoCmd.OpenReport stDocName, acPreview, "", strWhere

If this code worked it would only work if the records was marked to be
printed... "WHERE (((tbl_ClientRecord.PrintTNDenialLetter)=Yes))"

How can I use one report to print using differents sets of data?

Thanks

Kelvin
 
K

Kelvin Beaton

Thanks, I'll give this a try!

Kelvin

Klatuu said:
No, not even close, sorry.

strWhere = "PrintTNDenialLetter = -1"
DoCmd.OpenReport stDocName, acPreview, "", strWhere

The Where argument is the same as an SQL WHERE without the word WHERE.
also = yes will not work. Yes is not a constant for -1, but True is;
however, SQL doesn't understand constants, you have to use the actual
value.
--
Dave Hargis, Microsoft Access MVP


Kelvin Beaton said:
Thanks Dave for your help so far...

I'd like to take this a step further.

I have the report menu working (see code below).

What I would like to do now is pass it a parameter.



This is the code behind my print button:

Dim stDocName As String

stDocName = Me.cmboReportList

DoCmd.OpenReport stDocName, acPreview



I've added a field called "Condition" to the table "tbl_List_of_reports"



strWhere = "[tables].[tbl_List_of_reports].[condition would go here]"

DoCmd.OpenReport stDocName, acPreview, "", strWhere



So I would end up with something like this:

Dim stDocName As String

Dim strWhere As String

strWhere = "[tables].[tbl_List_of_reports].[condition]"

stDocName = Me.cmboReportList

DoCmd.OpenReport stDocName, acPreview, stWhere



Is this close at all?



Thanks for your help!



Kelvin



+++++++++++++++++++++++++++++++++++++++++++++++++++++

Here is a sample process for creating the menu that I'm using.



Create a table with your report all names. Call it "tbl_List_of_reports"

Create a form named "Reports"

Add a combo box to the form based on the "tbl_List_of_reports" table

Name the Combo Box "Report_Selected"

Using the command button wizard add a print report button.

Select any one of your reports to print. (this is temporary)



Edit the VB code behind that button to reflect the name of the combo box

change the following

stDocName = "you report name"

to

stDocName = me.Report_Selected

+++++++++++++++++++++++++++++++++++++++++++++++++++++




Klatuu said:
Look in VBA Help at the OpenReport method. It as an example there.
If you don't understand it, post back.
--
Dave Hargis, Microsoft Access MVP


:


Can you give me a small example of what that statement would look
like?
Thanks

Kelvin


Remove all the WHERE criteria from the query.
Use the WHERE argument of the OpenReport method to do your
filtering.
--
Dave Hargis, Microsoft Access MVP


:

Hi have a report that is based on this query:
SELECT tbl_ClientRecord.ClientID, tbl_ClientRecord.Address,
IIf([city]
Is
Null,"",StrConv([City],3) & ", " & StrConv([State],1) & " " &
[zip])
AS
CityStateZip, tbl_ClientRecord.PrintTNDenialLetter,
StrConv([BeneficiaryFirstName] & " " & [BeneficiaryLastName],3) AS
BeneficiaryFullName, "Dear Parent or Guardian of " &
(StrConv([BeneficiaryFirstName] & " " & [BeneficiaryLastName],3)) &
":"
AS
DearFullName
FROM tbl_ClientRecord INNER JOIN tbl_Beneficiaries ON
tbl_ClientRecord.ClientID = tbl_Beneficiaries.ClientID
WHERE (((tbl_ClientRecord.PrintTNDenialLetter)=Yes))
ORDER BY tbl_ClientRecord.Address;

The report prints find when I want to print based on the query
above,
but
can I use the same report and print just the current client record?

I thought this code would print the corrent record, but it prints
all
the
records marked "Yes" as in the query above:
Dim stDocName As String
Dim strWhere As String
stDocName = "rpt_PrintTennCareTermination-Letter"
DoCmd.OpenReport stDocName, acPreview
strWhere = "[ClientID] = " & Me.[ClientID]
DoCmd.OpenReport stDocName, acPreview, "", strWhere

If this code worked it would only work if the records was marked to
be
printed... "WHERE (((tbl_ClientRecord.PrintTNDenialLetter)=Yes))"

How can I use one report to print using differents sets of data?

Thanks

Kelvin
 
N

Nats

Please would you be able to help me?

I need to know how to link data from 2 different tables, to create a third
table or report, but so that there is a link between the data from the 2
different databases.

Table 1
Field 1 Field 2 Field 3
Field 4 Field 5
(Employee No.) (Employee Name) (Detials 1) (Details 2)
(Details 3)
1 Sally Rose
58 James Paul
74 Lucy Car
15 Kristy Scott


TABLE 2

Field 1 Field 2 Field 3 Field 4
Field 5
(Shop no.) (Shop) (Detials 1) (Detials 2)
(Details 3)
500 Waitrose
475 M&S
100 Tesco
205 Sainsbury


If I type in say ‘Sally Rose’ It will come up with all the shops ‘Sally
Rose’ would go to. i.e. Waitrose and Tesco (and details of these shops will
come up (as in data for say Waitrose & Tesco in fields ‘details 1, 2, and 3’
on the table 2).

Then either as a separate table (or the same however you think best). I want
to be able to do the same thing only other way around – so I can type in
‘Waitrose’ and it will bring up all the names of employees that shop at
Waitrose, and their details (as in data for that person in fields ‘details 1,
details 2 and details 3’ in table 1).

Note: The table I have used as examples, are just examples and in the real
database have more data but the principle is the same.

I look forward to hearing from you in the near future.

Thank you in advance for you help.

Klatuu said:
No, not even close, sorry.

strWhere = "PrintTNDenialLetter = -1"
DoCmd.OpenReport stDocName, acPreview, "", strWhere

The Where argument is the same as an SQL WHERE without the word WHERE.
also = yes will not work. Yes is not a constant for -1, but True is;
however, SQL doesn't understand constants, you have to use the actual value.
--
Dave Hargis, Microsoft Access MVP


Kelvin Beaton said:
Thanks Dave for your help so far...

I'd like to take this a step further.

I have the report menu working (see code below).

What I would like to do now is pass it a parameter.



This is the code behind my print button:

Dim stDocName As String

stDocName = Me.cmboReportList

DoCmd.OpenReport stDocName, acPreview



I've added a field called "Condition" to the table "tbl_List_of_reports"



strWhere = "[tables].[tbl_List_of_reports].[condition would go here]"

DoCmd.OpenReport stDocName, acPreview, "", strWhere



So I would end up with something like this:

Dim stDocName As String

Dim strWhere As String

strWhere = "[tables].[tbl_List_of_reports].[condition]"

stDocName = Me.cmboReportList

DoCmd.OpenReport stDocName, acPreview, stWhere



Is this close at all?



Thanks for your help!



Kelvin



+++++++++++++++++++++++++++++++++++++++++++++++++++++

Here is a sample process for creating the menu that I'm using.



Create a table with your report all names. Call it "tbl_List_of_reports"

Create a form named "Reports"

Add a combo box to the form based on the "tbl_List_of_reports" table

Name the Combo Box "Report_Selected"

Using the command button wizard add a print report button.

Select any one of your reports to print. (this is temporary)



Edit the VB code behind that button to reflect the name of the combo box

change the following

stDocName = "you report name"

to

stDocName = me.Report_Selected

+++++++++++++++++++++++++++++++++++++++++++++++++++++




Klatuu said:
Look in VBA Help at the OpenReport method. It as an example there.
If you don't understand it, post back.
--
Dave Hargis, Microsoft Access MVP


:


Can you give me a small example of what that statement would look like?
Thanks

Kelvin


Remove all the WHERE criteria from the query.
Use the WHERE argument of the OpenReport method to do your filtering.
--
Dave Hargis, Microsoft Access MVP


:

Hi have a report that is based on this query:
SELECT tbl_ClientRecord.ClientID, tbl_ClientRecord.Address, IIf([city]
Is
Null,"",StrConv([City],3) & ", " & StrConv([State],1) & " " & [zip])
AS
CityStateZip, tbl_ClientRecord.PrintTNDenialLetter,
StrConv([BeneficiaryFirstName] & " " & [BeneficiaryLastName],3) AS
BeneficiaryFullName, "Dear Parent or Guardian of " &
(StrConv([BeneficiaryFirstName] & " " & [BeneficiaryLastName],3)) &
":"
AS
DearFullName
FROM tbl_ClientRecord INNER JOIN tbl_Beneficiaries ON
tbl_ClientRecord.ClientID = tbl_Beneficiaries.ClientID
WHERE (((tbl_ClientRecord.PrintTNDenialLetter)=Yes))
ORDER BY tbl_ClientRecord.Address;

The report prints find when I want to print based on the query above,
but
can I use the same report and print just the current client record?

I thought this code would print the corrent record, but it prints all
the
records marked "Yes" as in the query above:
Dim stDocName As String
Dim strWhere As String
stDocName = "rpt_PrintTennCareTermination-Letter"
DoCmd.OpenReport stDocName, acPreview
strWhere = "[ClientID] = " & Me.[ClientID]
DoCmd.OpenReport stDocName, acPreview, "", strWhere

If this code worked it would only work if the records was marked to be
printed... "WHERE (((tbl_ClientRecord.PrintTNDenialLetter)=Yes))"

How can I use one report to print using differents sets of data?

Thanks

Kelvin
 
N

Nat

Please would you be able to help me?

Table 1

Field 1 Field 2 Field 3 Field 4
Field 5
(Employee No.) (Employee Name) (Details 1) (Details 2)
(Details 3)
47 Sally Rose
58 Mary Scott
100 Lucy Car
15 James Top


TABLE 2

Field 1 Field 2 Field 3 Field 4
Field 5
(Shop no.) (Shop) (Details 1) (Details 2)
(Details 3)
200 Waitrose
202 Tesco
205 Sainsburys
805 Budgens
600


I need to know how to link data from 2 different tables, to create a third
table, but so that there is a link between the data from the 2 different
databases.

So just for example I want a table (or query) however you see best to
achieve this, that does the following:



If I type in say ‘Sally Rose’ It will come up with all the shops ‘Sally
Rose’ would go to. i.e. Waitrose and Tesco (and details of these shops will
come up (as in data for say Waitrose & Tesco in fields ‘details 1, 2, and 3’
on the table 2).

Then either as a separate table (or the same however you think best). I want
to be able to do the same thing only other way around – so I can type in
‘Waitrose’ and it will bring up all the names of employees that shop at
Waitrose, and their details (as in data for that person in fields ‘details 1,
details 2 and details 3’ in table 1).

Note: The table I have used as examples, are just examples and in the real
database have more data but the principle is the same.

Thank you in advance for your help.




Klatuu said:
No, not even close, sorry.

strWhere = "PrintTNDenialLetter = -1"
DoCmd.OpenReport stDocName, acPreview, "", strWhere

The Where argument is the same as an SQL WHERE without the word WHERE.
also = yes will not work. Yes is not a constant for -1, but True is;
however, SQL doesn't understand constants, you have to use the actual value.
--
Dave Hargis, Microsoft Access MVP


Kelvin Beaton said:
Thanks Dave for your help so far...

I'd like to take this a step further.

I have the report menu working (see code below).

What I would like to do now is pass it a parameter.



This is the code behind my print button:

Dim stDocName As String

stDocName = Me.cmboReportList

DoCmd.OpenReport stDocName, acPreview



I've added a field called "Condition" to the table "tbl_List_of_reports"



strWhere = "[tables].[tbl_List_of_reports].[condition would go here]"

DoCmd.OpenReport stDocName, acPreview, "", strWhere



So I would end up with something like this:

Dim stDocName As String

Dim strWhere As String

strWhere = "[tables].[tbl_List_of_reports].[condition]"

stDocName = Me.cmboReportList

DoCmd.OpenReport stDocName, acPreview, stWhere



Is this close at all?



Thanks for your help!



Kelvin



+++++++++++++++++++++++++++++++++++++++++++++++++++++

Here is a sample process for creating the menu that I'm using.



Create a table with your report all names. Call it "tbl_List_of_reports"

Create a form named "Reports"

Add a combo box to the form based on the "tbl_List_of_reports" table

Name the Combo Box "Report_Selected"

Using the command button wizard add a print report button.

Select any one of your reports to print. (this is temporary)



Edit the VB code behind that button to reflect the name of the combo box

change the following

stDocName = "you report name"

to

stDocName = me.Report_Selected

+++++++++++++++++++++++++++++++++++++++++++++++++++++




Klatuu said:
Look in VBA Help at the OpenReport method. It as an example there.
If you don't understand it, post back.
--
Dave Hargis, Microsoft Access MVP


:


Can you give me a small example of what that statement would look like?
Thanks

Kelvin


Remove all the WHERE criteria from the query.
Use the WHERE argument of the OpenReport method to do your filtering.
--
Dave Hargis, Microsoft Access MVP


:

Hi have a report that is based on this query:
SELECT tbl_ClientRecord.ClientID, tbl_ClientRecord.Address, IIf([city]
Is
Null,"",StrConv([City],3) & ", " & StrConv([State],1) & " " & [zip])
AS
CityStateZip, tbl_ClientRecord.PrintTNDenialLetter,
StrConv([BeneficiaryFirstName] & " " & [BeneficiaryLastName],3) AS
BeneficiaryFullName, "Dear Parent or Guardian of " &
(StrConv([BeneficiaryFirstName] & " " & [BeneficiaryLastName],3)) &
":"
AS
DearFullName
FROM tbl_ClientRecord INNER JOIN tbl_Beneficiaries ON
tbl_ClientRecord.ClientID = tbl_Beneficiaries.ClientID
WHERE (((tbl_ClientRecord.PrintTNDenialLetter)=Yes))
ORDER BY tbl_ClientRecord.Address;

The report prints find when I want to print based on the query above,
but
can I use the same report and print just the current client record?

I thought this code would print the corrent record, but it prints all
the
records marked "Yes" as in the query above:
Dim stDocName As String
Dim strWhere As String
stDocName = "rpt_PrintTennCareTermination-Letter"
DoCmd.OpenReport stDocName, acPreview
strWhere = "[ClientID] = " & Me.[ClientID]
DoCmd.OpenReport stDocName, acPreview, "", strWhere

If this code worked it would only work if the records was marked to be
printed... "WHERE (((tbl_ClientRecord.PrintTNDenialLetter)=Yes))"

How can I use one report to print using differents sets of data?

Thanks

Kelvin
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top