Hi All,

'Tis my first time posting so I hope I use the right etiquette!

BUSINESS PROBLEM
I work in a Project Management environment and am trying to build a Risk Register for the Project Team.
Users can select a Risk from the Register and track movement by progressively adding comments which are appended to a read-only journal.
As such, the journal provides a permanent audit trail of user comments, actions, changes etc.

SYSTEM PROBLEM
Risks are stored in tblRisks (which contains two memo-type fields - 'Comments' and 'Journal').
Users enter data to tblRisks via frmRisks (txtComments). frmRisks also contains read-only txtJournal.
I want the User to
1. type comments into txtComments,
2. click cmdJournal and then have
2.1 The Comments concatenate to 'Journal' (for viewing at txtJournal)
2.2 The comments at txtComment then being purged post-concatenation.

WHAT HAVE I TRIED SO FAR?
I'm a slave to Wrox's "Beginning Access 2002 VBA" ISBN 0-7645-4402-0.
I've also ploughed through 22 pages of the Access forum on this site.
Finally, I've tried writing the following code which is attached to frmRisks (i.e. as a Form Module).

No result. No error message. Nothing.

================
Private Sub cmdJournal_Click()

Dim db As Database
Dim rec As Recordset
Dim datToday As Date

Set db = CurrentDb()
Set rec = db.OpenRecordset("tblRisks", dbOpenTable)
datToday = Date

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

'make changes to the current record in the copy buffer
rec("Journal") = rec("Journal") & vbCrLf & "(" & datToday & ")" & rec("Comment")

'save the contents of the copy buffer to disk
rec.Update

UpdateJournal = True
End Sub
================

Does anyone have any thoughts?
With grateful thanks,

Tony Adams
(Melbourne, Australia)
[email protected]

Hi,

   When you open the recordset, you want to limit it to the current record. You have opened a recordset that contains all the records in the table "tblRisks". You need to either limit the recordset to the current record, or loop through the recordset until you match the current PK.

   Also, when you do the update, you need to specify the field you want to update, and with what. So capture the new journal value like:

stNewJournal = rec("Journal") & vbCrLf & "(" & datToday & ")" & rec("Comment")

Then something like:

rec("Journal") = stNewJournal
Rec.Update

I am not sure of the syntax for the record update in DAO since I do mostly ADO.

I know there are some posters who can get you all the way there.

HTH



mmcdonal

Thanks for the feedback - it didn't solve my problem but it makes my code look a lot neater :)

By the way, my Wrox bible "Beginning Access 2002 VBA" (p237) says that "Whenever you create a Recordset object, the first row of the recordset becomes the current record."

I used that as the rationale for creating a recordset containing all records in tblRisks - I could quickly create the object, view the Risk that is displayed at the frmRisks and then close the object.

I'm interested to know if there's a nicer way to limit the recordset to a single record.

With thanks again,

Tony Adams
(Melbourne, Australia)
[email protected]

Here's an article from Microsoft Support
http://support.microsoft.com/default...;en-us;Q197592

Also, an excellent discussion string that's worth ploughing through
http://www.access-programmers.co.uk/...ht=audit+trail

I haven't worked through the solution yet, but there's comprehensive discussion, code samples etc - the string goes back 4 years...

Fingers crossed,

Tony Adams
(Melbourne, Australia)
[email protected]

Hi Tony,

You simply aren't pulling the new comment value from the TextBox control. Instead of:

use

Then add:

to display the new value of your Journal field on your form.

I don't understand what purpose the Comment field is serving in your table though. Do you want to save each individual comment in your table as well? As it stands, you aren't writing to that field at all. It isn't being used. And if you did choose to store each individual comment, your Journal field would end up containing a tremendous amount of redundant data given your current table design. Each comment would be stored in the comment field of its own record, and in the Journal field of every other record to follow. Just not clear on what data you want to persist.

Bob

Also, don't know how helpful you'll find the Audit Trail literature you referenced in your situation. Audit trail functionality typically automates documentation of changes to individual records (last modified, by whom, old value, new vaule, etc.). That information is unique for each record, and is stored in a field of the record, or as a discrete record in an audit record table. At the moment, you're simply concatenating strings, and storing the result in a single field of a single record.

Bob

Thanks Bob,

The purpose of txtComments is simply to capture the user's incremental comment for that Risk.

Once the comment is added to txtJournal (i.e. as part of the history log), then I would look to purge the value in txtComment.

In essence, txtComment is only used to capture input and is then cleared. I will use txtJournal to store the single history of all comments in one field for each Risk.

With thanks again,

Tony Adams
(Melbourne, Australia)
[email protected]

Hi Tony,

I understand how you are using the textbox control txtComments. I was asking about the Comments memo field in your table. That's the element in your design that isn't being used as your code currently stands. Were you intending to save the value entered into txtComments into the field Comments?

Bob

Hi Bob,
Thanks again for the response.

I think the Comments field in tblRisks is redundant - I don't intend storing any value in there beyond the life of the Function.

As an aside, I tried your code snippet which replaced '...rec("Comment")' with '...Me.txtComments', but received an error message "Compile error: Invalid use of Me keyword"

MS Access help screen indicates a couple of possible causes - the most likely is that the code module was a standard module rather than a class module.
MS Access help says "If you copied the code in question from a class module, you have to replace the Me keyword with the specific object or form name to preserve the original reference".

I'll have a tinker and let you know (for interest's sake) what happens.
Any pearls of wisdom along this line?

All the best,

Tony Adams
(Melbourne, Australia)
[email protected]

Hi Tony,

The Me keyword is simply an abbreviated reference to the form that contains the control txtComments. Its an abbreviation of:

Forms!frmRisks!txtComments

Assuming you have a form named frmRisks with a textbox named txtComments, a textbox named txtJournal, and a command button named cmdJournal, use the following as the click event procedure (pasted in teh form module) for cmdJournal:

Bob