report acting as mail merge

G

Guest

Following is a previous thread with code. This works but makes all letters
the same address as the first. How do I get it to do the same functionality
for each record.

here is the code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim strsql As String
Dim strsql2 As String

Set db = CurrentDb
'strsql2 = "SELECT a.related_pers_entity_id, b.pers_entity_id,
b.first_name, b.last_name,"
'strsql2 = strsql2 & " c.addr_line1_txt, c.addr_line2_txt, c.city_txt,
c.state_code, c.zip_num"
'strsql2 = strsql2 & " FROM ((dbo_tblrelated_person_map a, dbo_tblcase
d, dbo_tblperson b, dbo_tbladdress c,dbo_fcs_DischargeLetterStatus e"
'strsql2 = strsql2 & " WHERE (((d.case_end_date) Between " &
[Forms]![Main]![BeginDate] & " And " & [Forms]![Main]![EndDate] & ") AND
((e.DischargeLetterDate) Is Null) AND"
'strsql2 = strsql2 & " b.pers_entity_type_code = c.entity_type_code and
b.pers_entity_id = c.entity_id and d.cons_entity_type_code =
b.pers_entity_type_code and "
'strsql2 = strsql2 & "d.cons_entity_id = b.pers_entity_id and
d.case_sysid *= e.CaseID and b.pers_entity_id *= a.pers_entity_id and "
'strsql2 = strsql2 & "((c.primary_flg)=True) AND
((d.case_disposition_code) Is Not Null)) GROUP BY a.related_pers_entity_id,
b.pers_entity_id, "
'strsql2 = strsql2 & "b.first_name, b.last_name, c.addr_line1_txt,
c.addr_line2_txt, c.city_txt, c.state_code, c.zip_num,
b.pers_entity_type_code"
'strsql2 = strsql2 & " HAVING (((b.pers_entity_type_code)='C')) ORDER BY
b.last_name"

Set rs = db.OpenRecordset("qryDischargeLetter_wa")

If Not IsNull(rs!related_pers_entity_id) And rs!related_pers_entity_id
<> rs!pers_entity_id Then
strsql = "select a.first_name, a.last_name"
strsql = strsql & " from dbo_tblperson a"
strsql = strsql & " where a.pers_entity_id = '" &
rs!related_pers_entity_id & "'"
Set rs1 = db.OpenRecordset(strsql)
Me!Text2 = Trim(rs1!first_name) & " " & rs1!last_name
Me.Text4 = Trim(rs!addr_line2_txt)
Me.Text3 = Trim(rs!addr_line1_txt)
Me.Text5 = Trim(rs!city_txt & ", " & rs!state_code & " " & rs!zip_num)
Me.Text18.Visible = True
Me.Text18 = "Concerning -- " & rs!first_name & " " & rs!last_name
'Me.Text18.Visible = False
rs.MoveNext
rs1.Close
Else
Me.Text18.Visible = False
Me.Text2 = Trim(rs!first_name) & " " & rs!last_name
Me.Text4 = Trim(rs!addr_line2_txt)
Me.Text3 = Trim(rs!addr_line1_txt)
Me.Text5 = Trim(rs!city_txt & ", " & rs!state_code & " " & rs!zip_num)
rs.MoveNext
End If
End Sub

I am not sure how to present the layout to you. I copied all the objects on
the report but they would not paste into this post.
 
D

Duane Hookom

To show your layout, you would just type into your reply like:
John Smith
123 Oak Street
City, State Zip

"Conerning your child " & [Child1]

(repeat for each record)

Tables
Table1
Field1a
Field1b
Field1c
....

Table2
Field2a
Field2b
Field2c
....

I don't understand the need to create recordsets in your report. Your
solution will not work as proposed. I would set the record source of the
report to whatever data is needed.

--
Duane Hookom
MS Access MVP
--

seeker53 said:
Following is a previous thread with code. This works but makes all
letters
the same address as the first. How do I get it to do the same
functionality
for each record.

here is the code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim strsql As String
Dim strsql2 As String

Set db = CurrentDb
'strsql2 = "SELECT a.related_pers_entity_id, b.pers_entity_id,
b.first_name, b.last_name,"
'strsql2 = strsql2 & " c.addr_line1_txt, c.addr_line2_txt, c.city_txt,
c.state_code, c.zip_num"
'strsql2 = strsql2 & " FROM ((dbo_tblrelated_person_map a, dbo_tblcase
d, dbo_tblperson b, dbo_tbladdress c,dbo_fcs_DischargeLetterStatus e"
'strsql2 = strsql2 & " WHERE (((d.case_end_date) Between " &
[Forms]![Main]![BeginDate] & " And " & [Forms]![Main]![EndDate] & ") AND
((e.DischargeLetterDate) Is Null) AND"
'strsql2 = strsql2 & " b.pers_entity_type_code = c.entity_type_code and
b.pers_entity_id = c.entity_id and d.cons_entity_type_code =
b.pers_entity_type_code and "
'strsql2 = strsql2 & "d.cons_entity_id = b.pers_entity_id and
d.case_sysid *= e.CaseID and b.pers_entity_id *= a.pers_entity_id and "
'strsql2 = strsql2 & "((c.primary_flg)=True) AND
((d.case_disposition_code) Is Not Null)) GROUP BY
a.related_pers_entity_id,
b.pers_entity_id, "
'strsql2 = strsql2 & "b.first_name, b.last_name, c.addr_line1_txt,
c.addr_line2_txt, c.city_txt, c.state_code, c.zip_num,
b.pers_entity_type_code"
'strsql2 = strsql2 & " HAVING (((b.pers_entity_type_code)='C')) ORDER
BY
b.last_name"

Set rs = db.OpenRecordset("qryDischargeLetter_wa")

If Not IsNull(rs!related_pers_entity_id) And rs!related_pers_entity_id
<> rs!pers_entity_id Then
strsql = "select a.first_name, a.last_name"
strsql = strsql & " from dbo_tblperson a"
strsql = strsql & " where a.pers_entity_id = '" &
rs!related_pers_entity_id & "'"
Set rs1 = db.OpenRecordset(strsql)
Me!Text2 = Trim(rs1!first_name) & " " & rs1!last_name
Me.Text4 = Trim(rs!addr_line2_txt)
Me.Text3 = Trim(rs!addr_line1_txt)
Me.Text5 = Trim(rs!city_txt & ", " & rs!state_code & " " &
rs!zip_num)
Me.Text18.Visible = True
Me.Text18 = "Concerning -- " & rs!first_name & " " & rs!last_name
'Me.Text18.Visible = False
rs.MoveNext
rs1.Close
Else
Me.Text18.Visible = False
Me.Text2 = Trim(rs!first_name) & " " & rs!last_name
Me.Text4 = Trim(rs!addr_line2_txt)
Me.Text3 = Trim(rs!addr_line1_txt)
Me.Text5 = Trim(rs!city_txt & ", " & rs!state_code & " " &
rs!zip_num)
rs.MoveNext
End If
End Sub

I am not sure how to present the layout to you. I copied all the objects
on
the report but they would not paste into this post.
Click to expand...
 
G

Guest

ok one of the recordsets is used to gain the first and last name of the
responsible party. This information for the responsible party and consumer
are in the same tblperson therefore I need to capture the responsible party
ID from the record source and then put that in a query to find out what first
name last name is of responsible party.

Duane Hookom said:
To show your layout, you would just type into your reply like:
John Smith
123 Oak Street
City, State Zip

"Conerning your child " & [Child1]

(repeat for each record)

Tables
Table1
Field1a
Field1b
Field1c
....

Table2
Field2a
Field2b
Field2c
....

I don't understand the need to create recordsets in your report. Your
solution will not work as proposed. I would set the record source of the
report to whatever data is needed.

--
Duane Hookom
MS Access MVP
--

seeker53 said:
Following is a previous thread with code. This works but makes all
letters
the same address as the first. How do I get it to do the same
functionality
for each record.

here is the code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim strsql As String
Dim strsql2 As String

Set db = CurrentDb
'strsql2 = "SELECT a.related_pers_entity_id, b.pers_entity_id,
b.first_name, b.last_name,"
'strsql2 = strsql2 & " c.addr_line1_txt, c.addr_line2_txt, c.city_txt,
c.state_code, c.zip_num"
'strsql2 = strsql2 & " FROM ((dbo_tblrelated_person_map a, dbo_tblcase
d, dbo_tblperson b, dbo_tbladdress c,dbo_fcs_DischargeLetterStatus e"
'strsql2 = strsql2 & " WHERE (((d.case_end_date) Between " &
[Forms]![Main]![BeginDate] & " And " & [Forms]![Main]![EndDate] & ") AND
((e.DischargeLetterDate) Is Null) AND"
'strsql2 = strsql2 & " b.pers_entity_type_code = c.entity_type_code and
b.pers_entity_id = c.entity_id and d.cons_entity_type_code =
b.pers_entity_type_code and "
'strsql2 = strsql2 & "d.cons_entity_id = b.pers_entity_id and
d.case_sysid *= e.CaseID and b.pers_entity_id *= a.pers_entity_id and "
'strsql2 = strsql2 & "((c.primary_flg)=True) AND
((d.case_disposition_code) Is Not Null)) GROUP BY
a.related_pers_entity_id,
b.pers_entity_id, "
'strsql2 = strsql2 & "b.first_name, b.last_name, c.addr_line1_txt,
c.addr_line2_txt, c.city_txt, c.state_code, c.zip_num,
b.pers_entity_type_code"
'strsql2 = strsql2 & " HAVING (((b.pers_entity_type_code)='C')) ORDER
BY
b.last_name"

Set rs = db.OpenRecordset("qryDischargeLetter_wa")

If Not IsNull(rs!related_pers_entity_id) And rs!related_pers_entity_id
<> rs!pers_entity_id Then
strsql = "select a.first_name, a.last_name"
strsql = strsql & " from dbo_tblperson a"
strsql = strsql & " where a.pers_entity_id = '" &
rs!related_pers_entity_id & "'"
Set rs1 = db.OpenRecordset(strsql)
Me!Text2 = Trim(rs1!first_name) & " " & rs1!last_name
Me.Text4 = Trim(rs!addr_line2_txt)
Me.Text3 = Trim(rs!addr_line1_txt)
Me.Text5 = Trim(rs!city_txt & ", " & rs!state_code & " " &
rs!zip_num)
Me.Text18.Visible = True
Me.Text18 = "Concerning -- " & rs!first_name & " " & rs!last_name
'Me.Text18.Visible = False
rs.MoveNext
rs1.Close
Else
Me.Text18.Visible = False
Me.Text2 = Trim(rs!first_name) & " " & rs!last_name
Me.Text4 = Trim(rs!addr_line2_txt)
Me.Text3 = Trim(rs!addr_line1_txt)
Me.Text5 = Trim(rs!city_txt & ", " & rs!state_code & " " &
rs!zip_num)
rs.MoveNext
End If
End Sub

I am not sure how to present the layout to you. I copied all the objects
on
the report but they would not paste into this post.


Duane Hookom said:
I'm having trouble seeing your:
-Report Layout
-Record Source Fields
-Code
Could you help us out a little?

--
Duane Hookom
MS Access MVP


I have code in the format of the detail section to populate name address
city
state and zip. It works just fine until I go to the second letter and
the
same info is on each letter. It seems like that it starts at the
beginning
of the recordset each time a new letter is formatted. Need guidance.
Thanks.
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

And your table structure is....?

--
Duane Hookom
MS Access MVP


seeker53 said:
ok one of the recordsets is used to gain the first and last name of the
responsible party. This information for the responsible party and
consumer
are in the same tblperson therefore I need to capture the responsible
party
ID from the record source and then put that in a query to find out what
first
name last name is of responsible party.

Duane Hookom said:
To show your layout, you would just type into your reply like:
John Smith
123 Oak Street
City, State Zip

"Conerning your child " & [Child1]

(repeat for each record)

Tables
Table1
Field1a
Field1b
Field1c
....

Table2
Field2a
Field2b
Field2c
....

I don't understand the need to create recordsets in your report. Your
solution will not work as proposed. I would set the record source of the
report to whatever data is needed.

--
Duane Hookom
MS Access MVP
--

seeker53 said:
Following is a previous thread with code. This works but makes all
letters
the same address as the first. How do I get it to do the same
functionality
for each record.

here is the code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim strsql As String
Dim strsql2 As String

Set db = CurrentDb
'strsql2 = "SELECT a.related_pers_entity_id, b.pers_entity_id,
b.first_name, b.last_name,"
'strsql2 = strsql2 & " c.addr_line1_txt, c.addr_line2_txt,
c.city_txt,
c.state_code, c.zip_num"
'strsql2 = strsql2 & " FROM ((dbo_tblrelated_person_map a,
dbo_tblcase
d, dbo_tblperson b, dbo_tbladdress c,dbo_fcs_DischargeLetterStatus e"
'strsql2 = strsql2 & " WHERE (((d.case_end_date) Between " &
[Forms]![Main]![BeginDate] & " And " & [Forms]![Main]![EndDate] & ")
AND
((e.DischargeLetterDate) Is Null) AND"
'strsql2 = strsql2 & " b.pers_entity_type_code = c.entity_type_code
and
b.pers_entity_id = c.entity_id and d.cons_entity_type_code =
b.pers_entity_type_code and "
'strsql2 = strsql2 & "d.cons_entity_id = b.pers_entity_id and
d.case_sysid *= e.CaseID and b.pers_entity_id *= a.pers_entity_id and "
'strsql2 = strsql2 & "((c.primary_flg)=True) AND
((d.case_disposition_code) Is Not Null)) GROUP BY
a.related_pers_entity_id,
b.pers_entity_id, "
'strsql2 = strsql2 & "b.first_name, b.last_name, c.addr_line1_txt,
c.addr_line2_txt, c.city_txt, c.state_code, c.zip_num,
b.pers_entity_type_code"
'strsql2 = strsql2 & " HAVING (((b.pers_entity_type_code)='C'))
ORDER
BY
b.last_name"

Set rs = db.OpenRecordset("qryDischargeLetter_wa")

If Not IsNull(rs!related_pers_entity_id) And
rs!related_pers_entity_id
<> rs!pers_entity_id Then
strsql = "select a.first_name, a.last_name"
strsql = strsql & " from dbo_tblperson a"
strsql = strsql & " where a.pers_entity_id = '" &
rs!related_pers_entity_id & "'"
Set rs1 = db.OpenRecordset(strsql)
Me!Text2 = Trim(rs1!first_name) & " " & rs1!last_name
Me.Text4 = Trim(rs!addr_line2_txt)
Me.Text3 = Trim(rs!addr_line1_txt)
Me.Text5 = Trim(rs!city_txt & ", " & rs!state_code & " " &
rs!zip_num)
Me.Text18.Visible = True
Me.Text18 = "Concerning -- " & rs!first_name & " " &
rs!last_name
'Me.Text18.Visible = False
rs.MoveNext
rs1.Close
Else
Me.Text18.Visible = False
Me.Text2 = Trim(rs!first_name) & " " & rs!last_name
Me.Text4 = Trim(rs!addr_line2_txt)
Me.Text3 = Trim(rs!addr_line1_txt)
Me.Text5 = Trim(rs!city_txt & ", " & rs!state_code & " " &
rs!zip_num)
rs.MoveNext
End If
End Sub

I am not sure how to present the layout to you. I copied all the
objects
on
the report but they would not paste into this post.


:

I'm having trouble seeing your:
-Report Layout
-Record Source Fields
-Code
Could you help us out a little?

--
Duane Hookom
MS Access MVP


I have code in the format of the detail section to populate name
address
city
state and zip. It works just fine until I go to the second letter
and
the
same info is on each letter. It seems like that it starts at the
beginning
of the recordset each time a new letter is formatted. Need
guidance.
Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Top