Looking for advice on Long Text/Memo fields

[tapir47]

In Access 2016 I have, as part of a bigger system, a customer table and a linked customer memo table whose purpose is to hold a chronological log of contact with the customer.

Entering and storing the data is no problem. The area I need advice on is how to display it in a form. The individual memo entries are of variable size and I want to show the log in the form when I select the customer to enter a new order.

Using a subform doesn't work as the rows in a subform are all fixed size. Using the Shift-F2 approach is clunky to look through the entries that aren't fully shown. I've tried building an unbound long text field by reading through the memo entries and formatting the fields to be date and text with the text indented but that runs into issues with needing to wordwrap text and inserting 'tab' characters to format it.

What I'm after is something like:

10/08/2019 Note text -----------
more text
09/08/2019 Note text
08/08/2019 Note text

Any suggestions?

 

[theDBguy]

Hi. Welcome to AWF! If it's only for display purposes, you might consider using a subreport. Just a thought...

 

[jdraw]

tapir47,

Do you really need a memo/long text field?
Have you considered a tblCustomerNotes with fields
NoteID,CustIdFK, NoteDate, NoteText?
This would allow multiple Notes per Customer. Each note up to 255 chars.
If you needed more you could put line/section numbers into the mix.

 

[tapir47]

Thanks for the input.

jdraw - it needs to be a memo field as the content of an individual note could be quite long. The structure you suggest is what I have but with the NoteText as a memo.

I think that a subreport is the best approach and that gives me what I want in form view but there are two issues I need to resolve:

1 - in layout view, the order form flashes constantly with the status bar showing Calculating... In Form View and Design View its fine.
2 - in form view, there is some weird behaviour with other fields on the form once the subreport is in place which aren't there without the subreport. When another field gets focus, the visible cursor jumps to be about 5 characters into the field. Typing still appears in the correct place. Once the field is completed and you tab beyond it the field is fine as it is when you go back to the field. Weird.

I'll play around some more.

 

[Pat Hartman]

For reports, it's no problem since the vertical space will expand as long as you have can grow set to yes for the control AND the section. However, forms do NOT grow. I would pick a reasonable size height and use a continuous form rather than a DS form. Then teach people how to use shift-F2 to open the zoom box or build your own zoom form. I tend to use the zoom box because i don't have to write any code to support it, it works on ALL text controls AND, if necessary, the user can actually edit in it although I don't recommend that for this application. All the old comments should be locked and if they are, the zoom will respect that. The zoom box will allow edits if the control is locked but won't save them. If this is an issue, you will need to build your own zoom control.

 

[MajP]

I've tried building an unbound long text field by reading through the memo entries and formatting the fields to be date and text with the text indented but that runs into issues with needing to wordwrap text and inserting 'tab' characters to format it.

What I'm after is something like:

10/08/2019 Note text -----------
more text
09/08/2019 Note text
08/08/2019 Note text

Going back to your original. I interpret this to mean you have individual rows for entries with a date field and a memo field. You say you can loop the recordset and concatenate, putting the final result into a large unbound textbox. So I am not following why you cannot get your result properly formatted and need to wordwrap and insert tabs. For readability I would just put a line feed between entries and not indent the second line of text. If you wanted to go that route, it should be very doable.

1/1/2019 Text..................................................................
secondline of text.............................................................

1/2/2019 Text..................................................................
second line of text ...........................................................
third line of text...............................................................

 

[missinglinq]

Here's a Function that can be used to expand a given Control by using the Double-Click event for the Control. You can create a Function to do the zooming, select the desired Controls to be included, then assign the Function to the DoubleClick event of all of the Controls:

In a Standard Module:

Public Function fExpand(ctl As Control) As Byte
 ctl.SetFocus
 DoCmd.RunCommand acCmdZoomBox
End Function

When asked to Save the Module, name it anything except fExpand; having the Function and the Module sharing the same name confuses the Access Gnomes no end!

Then in Form Design View:

• Hold down the <Shift> Key and select (Left Click) each Control to be included
• Go to Properties - Events
• In the appropriate property (OnDblClick, in this case) enter =fExpand([Screen].[ActiveControl])

This will place the Function call in the OnDblClick event for all of the selected Controls, in one fell swoop!

Linq ;0)>

 

[tapir47]

Many thanks for the comments and suggestions. I've decided to revert to the unbounded memo field which I'm populating from the table in VBA.

In the original post I indicated I was after:

10/08/2019 Note text -----------
more text
09/08/2019 Note text
08/08/2019 Note text

Unfortunately the formatting got a bit messed up - what I really want is the "more text" underneath the "Note text" so that the memo text field appears as a column. I can't seem to enter that in the forum editor successfully to show what I mean.

I don't think this is possible but happy to be corrected.

 

[Mark_]

So you want it formatted like below?

10/08/2019 Note text -----------
           more text
09/08/2019 Note text
08/08/2019 Note text

I would select a font that is not proportional so you would only be adding 10 characters on a line break. This can help in character counting also.

 

[tapir47]

Yes, that's what I'm after but there is no line break in the data. The value in the field is stored as "Note text ----------- more text" and depending on the width of the field will wrap at word boundaries as appropriate.

 

[theDBguy]

Yes, that's what I'm after but there is no line break in the data. The value in the field is stored as "Note text ----------- more text" and depending on the width of the field will wrap at word boundaries as appropriate.

Hi. Just a thought but how about using a Listbox?

10/08/2019 | Note text -----------
           | more text
09/08/2019 | Note text
08/08/2019 | Note text

 

[Mark_]

Honestly I think MajP has the better solution. Prepend the date and a CRLF to the text and add a CRLF at the end. You'll end up with

10/08/2019
Note text -----------
more text-----------

09/08/2019
Note text

08/08/2019
Note text

If you use RTF you can even have some visual clues that make some of this stand out more.

 

[jdraw]

I agree with Mark's comments regarding MajP's approach.
But, I think you are trying to make a long text field look like records in a table.
Doable, yes. But a separate, related table may be a better choice. You know the requirements and the user, so the decision is yours.
Good luck.

 

[Pat Hartman]

You are having a formatting problem because you have mushed two data items. The date and the text should be separate fields. Then you will have NO problem getting the affect you want.

Mushing is never good. It doesn't save you anything and it always causes some kind of problem. This is just one variation.

 

[MajP]

Again I am not clear on table structure. I assume you have individual notes with a separate date field. I was assuming something like this for display

Code would be something like

Private Sub Form_Current()
Dim rs As DAO.Recordset
Dim strSql As String
Dim CustId As Long
Dim strOut As String
Const codetag = "<font color=black style='BACKGROUND-COLOR:#FFFF00'>"
Const codeTagEnd = "</font>"

CustId = Me.customerID
strSql = "Select * from TblNotes where FK = " & CustId & " Order by NoteID"
Set rs = CurrentDb.OpenRecordset(strSql)
Do While Not rs.EOF
If strOut = "" Then
strOut = rs!noteDate & ": " & rs!Note
Else
strOut = strOut & vbCrLf & vbCrLf & rs!noteDate & ": " & rs!Note
End If
rs.MoveNext
Loop
Me.TxtLog = strOut
End Sub

 

[Mark_]

@ MajP,

Gotta ask, what was the answer to the question posed on 8/12/2019; Why won't a rocket butter a skill?

For some reason that's struck me as terrible funny!!

 

[MajP]

Dude, i do not have time to ponder that! I am so worried about my belt calculating above the sugar. Not sure if you can even say that on this site.

 

[Pat Hartman]

And here I thought that the notes were at least partially normalized. I guess I was wrong.

When you mush multiple attributes into a single field, you get what you asked for - mush. There is nothing efficient about mushing stuff together. It saves nothing and causes nothing but trouble. Each comment should be a SEPARATE record and there should be at least 5 fields.
SomeUniqueID
FKtoParent
WhoMadeTheComment
DateTime
Comment

 

[tapir47]

That was pretty much what I'd come up with. The date and note fields are separate in the table. I just wanted to see if there was a way of indenting the notes as I think it makes it easier to scan the entries that way.

Guess I'll stay with what I've got.

Thanks all.

 

[MajP]

Private Sub Form_Current()
Dim rs As DAO.Recordset
Dim strSql As String
Dim CustId As Long
Dim strOut As String
Dim firstLine As Boolean

  firstLine = True
  CustId = Me.customerID
  strSql = "Select * from TblNotes where FK = " & CustId & " Order by NoteID"
  Set rs = CurrentDb.OpenRecordset(strSql)
  Do While Not rs.EOF
     If strOut = "" Then
         strOut = IndentLines(rs!noteDate & ": " & rs!Note, 50, 10)
      Else
          strOut = strOut & vbCrLf & vbCrLf & IndentLines(rs!noteDate & ": " & rs!Note, 50, 10)
     End If
     rs.MoveNext
  Loop
Me.txtLog = strOut
End Sub

This pretty much works. Need to play with the indent and width and need to use a proportional font for best results.

Private Function IndentLines(ByVal strIn As String, intWidth As Integer, intIndent As Integer) As String
  Dim I As Integer
  Dim increments As Integer
  Dim rightPart As String
  Dim awords() As String
  Dim firstLine As Boolean
  Dim lineCount As Long
  firstLine = True
  
  awords = Split(strIn, " ")
  
  For I = 0 To UBound(awords)
    lineCount = lineCount + Len(awords(I))
    If lineCount < intWidth Then
       IndentLines = IndentLines & " " & awords(I)
    Else
        IndentLines = IndentLines & vbCrLf & String(intIndent, " ") & awords(I)
       lineCount = intIndent + Len(awords(I))
     End If
  Next I
End Function