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 15th, 2006, 01:43 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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

Code:
(3/15/2006)testOne
(3/15/2006)testTwo
(3/15/2006)testThree
Bob

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

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

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

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:

Code:
    If Not IsNull(rec("Journal")) Then
        rec("Journal") = rec("Journal") & vbCrLf & "(" & datToday & ")" & Me.txtComments
    Else
        rec("Journal") = "(" & datToday & ")" & Me.txtComments
    End If
Bob

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

Quote:
quote:Originally posted by Bob Bedell
 Using the above code the output to txtJournal (after three entries in txtComments) looks like:

Code:
(3/15/2006)testOne
(3/15/2006)testTwo
(3/15/2006)testThree
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]








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

Hi Tony

 
Quote:
quote:If I then scroll to Record 2


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

Bob

 
Old March 15th, 2006, 08:44 PM
Registered User
 
Join Date: Mar 2006
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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]
 
Old March 15th, 2006, 10:13 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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.

Code:
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 curent record from hidden
    ' textbox control bound to recordsources 'ID' field.
    strSQL = "SELECT * FROM tblRisks Where ID = " & Me.txtID  'txtID is hidden.

    ' 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 & "(" & datToday & ")" & Me.txtComments
    Else
        rec("Journal") = "(" & datToday & ")" & Me.txtComments
    End If

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

    On Error Resume Next
    Me.txtJournal = rec("Journal")

    Me.txtComments = vbNullString

End Sub

Private Sub txtComments_Enter()
    ' This routine simply ensures that the current record's
    ' primary key value gets written to the table so the
    ' cmdJournal_Click event can use it.
    If (Me.NewRecord) And (Me.Dirty) Then
        DoCmd.RunCommand acCmdSaveRecord
    End If
End Sub
HTH,

Bob

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

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

Code:
strSQL = "SELECT Journal FROM tblRisks Where ID = " & Me.txtID
Bob

 
Old March 16th, 2006, 04:56 PM
Registered User
 
Join Date: Mar 2006
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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]
 
Old March 17th, 2006, 01:10 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Thanks Tony. Glad all is well. :)

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.