Memo Field Display Problem

[Bob]

All,

The memo field not displaying completely is again causing headaches.
Please read on.

Access Ver: XP
Windows Ver: XP
LAN Envoronment

Loosing characters after 255.

A report and embedded sub-reports are previewed when a form's command
button is clicked. The form is unbound and its controls are
responsible for the reports and their respective contents. Individual
controls on the form are linked to various lookup tables.

To print preview the report, a command button is clicked. The command
button's code:

With DoCmd
.OpenReport stDocName, acViewPreview
.RunCommand acCmdZoom100
.Maximize
End With

Of interest: If I place a stop at the command button that starts the
reporting process and use Len() to determine the number of characters
in the memo field's control, it returns 255 -even though the table for
this record has a much higher count.

The sub-report's control with the problem, txtBody4, is unbound and
its value is set with the code below. An example of the problem can
be demonstrated by selecting an initial denial reason of 1. The
control txtBody4 is being truncated to 255 characters - it is a memo
field in a lookup table. The properties of this control: format is
blank, decimal = Auto, Can grow/shrink = yes, font Times New Roman 12
pt, and no italic.

The sub-reports code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim frm As Form_frmGUI
Dim frmDenial As Form_frmDenial
Dim intDenialRsn As Integer
' 03/07/08 RO: Added CRLF on case 1 of case statement
On Error GoTo Detail_Format_Error

Set frm = Form_frmGUI
Set frmDenial = Form_frmDenial
intDenialRsn = frmDenial.cboDenialReason

With Me
Select Case intDenialRsn
'Fill in Text based on what is chosen in combo drop-down
box
Case 1
'Total Denial of Medical Necessity or Benefit
Coverage Limitation
.txtBody4 = "The clinical rationale is " &
frmDenial.txtExplain2 & vbCrLf & vbCrLf

If frm.imgFEPLogo.Visible Then
.txtBody4 = .txtBody4 _
& "Please contact your personal physician for
alternative treatment options. " _
& "See page " & frmDenial.cboEOCPage & " of
the " & frmDenial.txtYear _
& " Blue Cross Blue Shield Benefit Plan
Brochure " _
& "which states " & Chr(34) &
frmDenial.cboEOCDescription & Chr(34) & vbCrLf '& vbCrLf
End If
Case 2
'Modification of Services
If frm.imgFEPLogo.Visible Then
.txtBody4 = "Please contact your personal
physician for alternative treatment options. " _
& "See page " & frmDenial.cboEOCPage & " of
the " & frmDenial.txtYear _
& " Blue Cross Blue Shield Benefit Plan
Brochure " _
& "which states " & Chr(34) &
frmDenial.cboEOCDescription & Chr(34) & vbCrLf
End If
Case 3
'Elegibility or Out of Network
If frm.imgFEPLogo.Visible Then
.txtBody4 = "Please contact your personal
physician for alternative treatment options. " _
& "See page " & frmDenial.cboEOCPage & " of
the " & frmDenial.txtYear _
& " Blue Cross Blue Shield Benefit Plan
Brochure " _
& "which states " & Chr(34) &
frmDenial.cboEOCDescription & Chr(34) & vbCrLf
End If
Case 4
'Benefit Exclusion
If frmDenial.imgFEPLogo.Visible Then
.txtBody4 = "Please contact your personal
physician for alternative treatment options. " _
& "See page " & frmDenial.cboEOCPage & " of
the " & frmDenial.txtYear _
& " Blue Cross Blue Shield Benefit Plan
Brochure " _
& "which states " & Chr(34) &
frmDenial.cboEOCDescription & Chr(34) & vbCrLf
End If
Case 5
'Partial Denial, PSR
.txtBody4 = "The clinical rationale is " &
frmDenial.txtExplain2 & vbCrLf & vbCrLf

If frm.imgFEPLogo.Visible Then
.txtBody4 = .txtBody4 _
& "See page " & frmDenial.cboEOCPage & " of
the " & frmDenial.txtYear _
& " Blue Cross Blue Shield Benefit Plan
Brochure " _
& "which states " & Chr(34) &
frmDenial.cboEOCDescription & Chr(34) & vbCrLf
End If
Case 6
'Partial Denial, InPatient
.txtBody4 = "The clinical rationale is " &
frmDenial.txtExplain2 & vbCrLf & vbCrLf

If frm.imgFEPLogo.Visible Then
.txtBody4 = .txtBody4 _
& "Please contact your personal physician for
alternative treatment options. " _
& "See page " & frmDenial.cboEOCPage & " of
the " & frmDenial.txtYear _
& " Blue Cross Blue Shield Benefit Plan
Brochure " _
& "which states " & Chr(34) &
frmDenial.cboEOCDescription & Chr(34) & vbCrLf
End If
Case 7
'Partial Denial, Lower Level Care
.txtBody4 = "The clinical rationale is " &
frmDenial.txtExplain2 & vbCrLf & vbCrLf

If frm.imgFEPLogo.Visible Then
.txtBody4 = .txtBody4 _
& "Please contact your personal physician for
alternative treatment options. " _
& "See page " & frmDenial.cboEOCPage & " of
the " & frmDenial.txtYear _
& " Blue Cross Blue Shield Benefit Plan
Brochure " _
& "which states " & Chr(34) &
frmDenial.cboEOCDescription & Chr(34) & vbCrLf
End If
Case 8
'Partial Denial, Reconsideration
.txtBody4 = "The clinical rationale is " &
frmDenial.txtExplain2 & vbCrLf & vbCrLf

If frm.imgFEPLogo.Visible Then
.txtBody4 = .txtBody4 _
& "Please contact your personal physician for
alternative treatment options. " _
& "See page " & frmDenial.cboEOCPage & " of
the " & frmDenial.txtYear _
& " Blue Cross Blue Shield Benefit Plan
Brochure " _
& "which states " & Chr(34) &
frmDenial.cboEOCDescription & Chr(34) & vbCrLf & vbCrLf
End If
End Select

If frmDenial.imgBlueShieldCALogo.Visible Then
.txtBody5 = "You may obtain a free of charge copy of the "
& frmDenial.txtGuideline & "" _
& " on which the denial decision was based, upon request,
" _
& "by calling the customer/member service phone number
listed at the top of this letter. "
End If
End With

ExitHere:
Exit Sub

Detail_Format_Error:

Select Case Err.Number
Case Else
MsgBox "Error " & Err.Number & " (" & Err.Description & ")
" _
& vbCrLf & "in procedure Detail_Format of VBA Document
Report_rsubDenial3"
End Select
Resume ExitHere

End Sub


Thanks for looking at this problem.

Bob

 

[Allen Browne]

Bob, see:
Truncation of Memo fields
at:
http://allenbrowne.com/ser-63.html

In general, the cause is anyhing in the report / query that does any
aggregation or formatting on the memo.

 

[Bob]

Bob, see:
    Truncation of Memo fields
at:
   http://allenbrowne.com/ser-63.html

In general, the cause is anyhing in the report / query that does any
aggregation or formatting on the memo.

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.




























- Show quoted text -

Allen,

I added an autonumber to the lookup table and made the AN the bound
field on the form. The offending bug/subreport now runs a groupby
query using FIRST on the momo field and uses the AN as criteria, which
gathers the memo text. The report now works.

Thanks for your comments.

Bob