Combining Long Text Fields

[Jon Harris]

Afternoon All,

Please forgive me if this is blindingly obvious but wondered if anyone could point a newcomer me in the right direction.

I have a basic database where one of the fields is for job update notes (long text). Currently I allow that to be opened and edited by users.

I'm now being asked to make the existing notes fixed and a new text box that allows users to add a new note to the existing. I've been googling for a while and have my Access 2016 Bible in one hand but am going around in any decreasing circles.

Any issues thoughts as how to best achieve this in an as simple as way as possible?

many thanks in advance!

 

[sneuberg]

It looks like you can just lock the existing text box on the form and then concatenate to it from another unbound text box with code like:

Me.JobUpdateNotes = Me.JobUpdateNotes & Me.NewNotes
Me.NewNotes = Null

You can see this in the attached test database.

 

[Jon Harris]

Thanks for the quick reply Steve. That's kind of what I was thinking. I'll have a look at your example when I get in this evening as 1/ I can't get it to work if I use a button to call the code or place it in an event 2/ secondly I get my head around how it will then store the updated note if unbound. School boy stuff I'm sure!

Many thanks

 

[sneuberg]

T 1/ I can't get it to work if I use a button to call the code or place it in an event

I don't understand what you are saying or asking here.

2/ secondly I get my head around how it will then store the updated note if unbound. School boy stuff I'm sure!

The additional note is typed into the unbound textbox by the user. When the button is clicked that information is tacked on to the end of the existing note and stored there. Of course you could bind the additional note textbox to a table field and have it stored separately too.

 

[Minty]

I would always add notes of this type to a separate table linked back to the parent record.

You can add an EmployeeID and date/time stamp to each record, and you have a proper history of the notes.

 

[missinglinq]

Here's a step-by-step I ran up a few years ago for some students who were working in a field where notes, once entered, couldn't be deleted/edited, but instead had to have corrective notes added, if mistakes were made.

***********
​

In this example, there are two Memo Field Textboxes, but only one is Bound, since the other one is simply a temporary holding area. The Bound Memo Field Textbox, named CommentsField, here, must have its Locked Property set to Yes/True, so that all data entry has to be done through the temporary Textbox.

TempDataBox is Unbound, and in the Property Pane its Visible Property must be set originally set to 'No.' I place mine side by side with the CommentsField Textbox so that the user can refer to what's currently in the CommentsField Textbox while entering new notes.

Once again, the CommentsField Textbox is Bound to the Form's underlying Table/Query, and its Locked Property is set to 'Yes.'

Place a Command Button on the Form. Name it IndirectDataInput and in the Properties Pane set its Caption to "Add New Data"

Now use one of these two code:

For IndirectDataEntry Without TimeStamp

Private Sub IndirectDataInput_Click()
If IndirectDataInput.Caption = "Add New Data" Then
   TempDataBox.Visible = True
   TempDataBox.SetFocus
   IndirectDataInput.Caption = "Commit Data"
 
Else
   IndirectDataInput.Caption = "Add New Data"
   If IsNull(Me.CommentsField) Then
      If Len(Me.TempDataBox) > 0 Then
        Me.CommentsField = Me.TempDataBox
        Me.TempDataBox = ""
        TempDataBox.Visible = False
      Else
        TempDataBox.Visible = False
      End If
    Else
      If Len(Me.TempDataBox) > 0 Then
       Me.CommentsField = Me.CommentsField & vbNewLine & Me.TempDataBox
       Me.TempDataBox = ""
       TempDataBox.Visible = False
      Else
       TempDataBox.Visible = False
      End If
      
    End If
End If
End Sub

IndirectDataEntry With TimeStamp

Private Sub IndirectDataInput_Click()
If IndirectDataInput.Caption = "Add New Data" Then
   TempDataBox.Visible = True
   TempDataBox.SetFocus
   IndirectDataInput.Caption = "Commit Data"
Else
   IndirectDataInput.Caption = "Add New Data"
   If IsNull(Me.CommentsField) Then
      If Len(Me.TempDataBox) > 0 Then
        Me.CommentsField = Now() & "  " & Me.TempDataBox
        Me.TempDataBox = ""
        TempDataBox.Visible = False
      Else
        TempDataBox.Visible = False
      End If
    Else
      If Len(Me.TempDataBox) > 0 Then
       Me.CommentsField = Me.CommentsField & vbNewLine & Now() & "  " & Me.TempDataBox
       Me.TempDataBox = ""
       TempDataBox.Visible = False
 
      Else
       TempDataBox.Visible = False
      End If
      
    End If
End If
End Sub

So, you click on the Command Button, the temporary Textbox appears, you enter your new data, click the Button again, the data is added to the Memo Field Textbox, and the temporary Textbox disappears.

You'll have to replace CommentsField, in the code above, with the actual name of the Textbox on your Form that is Bound to your Memo Field.

Linq ;0)>

 

[Jon Harris]

Evening All,

Many, many thanks for all your quick and detail replies, I've a lot go through and play with now which is a great help.

Thanks again for helping this newcomer!

Regards,

Jon.

 

[gemma-the-husky]

there are two basic methods

one, as missinglinq just said, is to append a new note to the end of a read only notes record.

the other, is to maintain a sub table of notes, and then concatenate them in order to present them.

The latter way is more flexible, but also involves more programming. I generally do the first.

 

[moke123]

we use a slightly different method and i thought i'd share it just for idea purposes. We have a running sheet we keep notes with which records the case number, date, staff making entry , and the notes. I use an unbound textbox for entry and editing, and a sub report to display the notes. I use a sub report in order to take advantage of the ability to hide duplicate entries such as 2 notes made on the same day. I use some hidden textboxes on the main form. The crucial one is OptBox which defaults to 1 for new entries and 2 for edits. I then use a select case under the submit button for either an insert or update procedure.