Using the above code the output to txtJournal (after three entries in txtComments) looks like:

Bob

As far as your table design goes, all you need is a single memo field named 'Journal'. Your table will never contain more than one, single field record. The maximum number of characters you can store in a memo field is 65,535 when entering data through the user interface; 1 gigabyte of character storage when entering data programmatically. Since you're doing the later, you shouldn't have any storage issues.

Bob

Since the 'Journal' field will be null the first time you run the app, you can avoid starting the journal entry with a blank line by using:

Bob

Hi Bob,

Yep, it's coming together. Many thanks for your tips.
Just one thing that I want to work out...the concatenated value at txtJournal now carries over to each subsequent Risk in tblRisks, so long as the application session remains open. The only way to 'clear' the carried over text string is to close the application and reopen.

So, the code looks like this for Record 1:
(3/15/2006)testOne
(3/15/2006)testTwo
(3/15/2006)testThree
If I then scroll to Record 2 and enter "testFour" at txtComment, then the txtJoural string for Record 2 is:
(3/15/2006)testOne
(3/15/2006)testTwo
(3/15/2006)testThree
(3/15/2006)testFour

The only way to get a correct outcome is to add a comment for Record 1, close the application, reopen and add a comment for Record 2.

The outcome is then correct:
Record 1
(3/15/2006)testOne
(3/15/2006)testTwo
(3/15/2006)testThree

Record 2
(3/15/2006)testFour

Other than that, 'tis a beautiful thang!

Cheers,

Tony Adams
(Melbourne, Australia)
[email protected]

Hi Tony

 

Don't follow. What is record 2? Is frmRisks bound to some table other than tblRisks?

Bob

Hi Bob,

Sorry, I mean where I add an additional record to tblRisks.
tblRisks holds one record for each Risk that the Project identifies. During the course of the Project, new Risks are identified and added to the Risk Register.

I want to ensure that the comments added to say Risk 1 are exclusive to that record in tblRisks and not to any other records.

BTW frmRisks is only bound to tblRisks.

Cheers, Tony

Tony Adams
(Melbourne, Australia)
[email protected]

Hi Tony,

This sould get you a little further. Place a textbox on the form that is bound to tblRisks primary key field. You can hide it if you like. I'm just using an autonumber field named 'ID' bound to a hidden textbox named 'txtID' as an example.

I revised the code to open a recordset based on the primary key value of the current record. This will enable you to associate comments with the currently displayed record.

I have txtComments as an unbound field, so I added an OnEnter event for txtComments that ensures the primary key value of a new record is saved to tblRisks before the cmdJournal_Click routine tries to use that value.

HTH,

Bob

Your SQL statement doesn't need to retrieve the whole record either. All you need is the Journal field. So you could use:

Bob

My thanks to Bob Bedell for sterling help - a really good outcome!

Here's a summary for anyone that wants to use it.
This allows the user to input free form comments and then have them appended to a 'journal'.
============================
[u]Pre-requisites:</u>
 

• tblRisk (including ID, Journal)
• frmRisk (including txtID, txtComment, txtJournal, cmdJournal)

[u]Code:</u>
Private Sub cmdJournal_Click()
    Dim db As DAO.Database
    Dim rec As DAO.Recordset
    Dim datToday As Date
    Dim strSQL As String

    Set db = CurrentDb()

    'Retrieve primary key value of current record from hidden textbox
    'control bound to the recordsource ID field. txtID is hidden
    strSQL = "SELECT Journal FROM tblRisks Where ID = " & Me.txtID

    'Open updateable recordset using SQL string as source argument.
    'Recordset contains only the current record
    Set rec = db.OpenRecordset(strSQL, dbOpenDynaset)
    datToday = Date

    'copy the current record to the copy buffer
    rec.Edit

    'make changes to the current record in the copy buffer
    If Not IsNull(rec("Journal")) Then
        rec("Journal") = rec("Journal") & vbCrLf & vbLf & "(" & datToday & ") " & Me.txtComment
    Else
        rec("Journal") = "(" & datToday & ") " & Me.txtComment
    End If
    'save the contents of the copy buffer to disk
    rec.Update

    On Error Resume Next

    'display new Journal value
    Me.txtJournal = rec("Journal")

    'clear Comments textbox
    Me.txtComment = vbNullString

   End Sub


Private Sub txtComment_Enter()
'This routine simply ensures that the current record's primary key value
'gets written to the table so that the cmdJournal_Click event can use it.

If (Me.NewRecord) And (Me.Dirty) Then
    DoCmd.RunCommand acCmdSaveRecord
End If
End Sub
=====================

Bob,
I'm happy to email you a sample of the Risk Register so you can see the end result - send me your address if you like.
All the best,

Tony Adams
(Melbourne, Australia)
[email protected]

Thanks Tony. Glad all is well. :)

Bob