Results 1 to 10 of 10

Time Date Stamp to Memo Field requiring update upon addition of new comment in memo field

  1. #1
    3102sseccaa is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Jul 2014
    Posts
    1

    Time Date Stamp to Memo Field requiring update upon addition of new comment in memo field

    I am very new to VBA for Access 2013. Thank you in advance for your assistance.

    I have one simple table with three fields, Auto #; txtComment and txtNewComment.

    I designed a form with the two fields, txtComment and txtNewComment and a control button cmdAppendComment and the caption "Append Comment"

    I have the following code:

    Private Sub cmdAppendComment_Click()
    If (IsNull(txtNewComment.Value)) Then


    MsgBox ("Please enter a comment before clicking on the Append Comment button.")
    Exit Sub
    End If

    If (IsNull(txtComment.Value)) Then
    txtComment.Value = txtNewComment.Value & " ~ " & VBA.DateTime.Date & " ~ " & VBA.DateTime.Time
    Else
    txt.Comment.Value = txtComment.Value & vbNewLine & vbNewLine & txtNewComment.Value & " ~ " & VBA.DateTime.Date & " ~ " & VBA.DateTime.Time
    End If

    txtNewComment.Value = ""
    End Sub

    I enter the first new comment and it updates the Comment field with the time and date stamp.

    I attempt to enter the second comment and I receive an error: Run-time error '424' Object required.

    What am I missing. I found this code on an Access forum and wanted to attempt to duplicate for the sake of learning...I am sure it is simple but would appreciate the help.

    Thank You!

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    So when you hit the Debug option after the error ocurrs, it stops on the following line?
    txtNewComment.Value = ""

    Perhaps you do not have a control named txtNewComment.

    If you type
    Me.txt

    Does txtNewComment show up in intellisense?

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,339
    This is really poor db design. Should be a record for each comment and note and date really should be in separate fields. Could have another field for WhoEnter.

    No code would be required.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,608
    It might be that your installation of Access doesn't recognize the VBA object. Try changing VBA.Datetime.date to just Date(), and VBA.Datetime.time to just time().

    John

  5. #5
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    Quote Originally Posted by John_G View Post
    VBA.Datetime.time to just time().
    Interesting, I think Time() has been deprecated. Perhaps this is where the (well one of the...) issue lies. I believe the Now() function is best for Timestamps.

  6. #6
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,608
    ItsMe -

    Using Now() for timestamp is OK if you are not doing searches for dates - Now() makes searching a lot more complicated.

    If you enter ?Date() = Now() in the immediate pane, you get False, because Date() has no time data whereas Now() does.

    It depends on what the user's needs are.

    John

  7. #7
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    @John_G

    OK, I was thinking more along the lines of
    ?Now()

    rather than
    ?Time()

    Because
    VBA.DateTime.Time may not be available.

  8. #8
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,608
    ItsMe -

    It doesn't make sense to me that MS would remove time() - it's used "all the time" (pun intended!).

    I did a Google search and can find no reference to Time() being deprecated.

    Doesn't mean it's not true - MS has done so many things that defy all logic that it is entirely possible.

    Cheers!

    John

  9. #9
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    Good to know. I was just going from memory and did not bother to Google it. Not sure why I had that stuck in my head. I will attempt to remove it. Thanks for the information.

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,339
    I am not sure Time() ever was a function in Access.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 01-30-2014, 10:55 AM
  2. Excel Import field truncation problem (to Access Memo field)
    By jhrBanker in forum Import/Export Data
    Replies: 6
    Last Post: 07-27-2012, 08:52 AM
  3. Replies: 6
    Last Post: 07-03-2012, 12:27 PM
  4. Replies: 5
    Last Post: 06-19-2012, 10:46 AM
  5. Two Date/Time and Memo Fields
    By Luke in forum Access
    Replies: 3
    Last Post: 08-24-2011, 02:55 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums