Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 13th, 2006, 06:41 PM
Registered User
 
Join Date: Mar 2006
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default Concatenating fields (user input 2 journal)

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]

 
Old March 14th, 2006, 09:05 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old March 14th, 2006, 04:13 PM
Registered User
 
Join Date: Mar 2006
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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]


 
Old March 14th, 2006, 06:00 PM
Registered User
 
Join Date: Mar 2006
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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]


 
Old March 14th, 2006, 11:28 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Hi Tony,

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

Code:
rec("Journal") = rec("Journal") & vbCrLf & "(" & datToday & ")" & rec("Comment")
use

Code:
rec("Journal") = rec("Journal") & vbCrLf & "(" & datToday & ")" & Me.txtComments
Then add:

Code:
Me.txtJournal = rec("Journal")
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

 
Old March 14th, 2006, 11:40 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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

 
Old March 14th, 2006, 11:48 PM
Registered User
 
Join Date: Mar 2006
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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]


 
Old March 15th, 2006, 12:54 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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

 
Old March 15th, 2006, 01:10 AM
Registered User
 
Join Date: Mar 2006
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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]





 
Old March 15th, 2006, 01:34 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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:

Code:
Private Sub cmdJournal_Click()
    Dim db As DAO.Database
    Dim rec As DAO.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 & ")" & Me.txtComments

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

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

    ' clear comments textbox
    Me.txtComments = vbNullString

End Sub
Bob






Similar Threads
Thread Thread Starter Forum Replies Last Post
form labels and input fields alignments Cyber Shiva HTML Code Clinic 1 June 19th, 2007 02:01 AM
Validation to input fields amol_0008 XML 1 May 30th, 2007 04:36 AM
Chapter 12 - text input fields don't allow HTML rdwheless1 BOOK: Beginning PHP5, Apache, and MySQL Web Development ISBN: 978-0-7645-7966-0 1 October 18th, 2006 01:04 PM
hidden input fields and Mozilla panos CSS Cascading Style Sheets 1 February 3rd, 2006 05:45 PM
checking user input hosefo81 Javascript How-To 0 February 16th, 2004 11:59 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.