letter acting as a merge

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
I'm having trouble seeing your:
-Report Layout
-Record Source Fields
-Code
Could you help us out a little?
 
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.
 
replies in later thread.

--
Duane Hookom
MS Access MVP


seeker53 said:
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?
Click to expand...
Click to expand...
 
Back
Top