Solved

Memo field update

Posted on 2013-01-28
9
509 Views
Last Modified: 2013-01-28
Hi Experts,
I have added a user name and time stamp to a memo field as an "after update" event.  The code works when something is added, but also is fired when something is deleted, which I don't want. Could someone please modify the attached code so that it is only fired when something is added?
Thanks experts!
Private Sub Notes_AfterUpdate()
   
    Me.Notes = Me.Notes & " (added by " & fOSUserName & " on " & Now() & ")" & vbCrLf
    
End Sub

Open in new window

0
Comment
Question by:hbatt
  • 4
  • 3
  • 2
9 Comments
 
LVL 77

Expert Comment

by:peter57r
Comment Utility
What you are asking is pretty well impossible unless you are saying that you have specific separate procedures for adding content and deleteing content.

There is no way to tell whether content was changed, added or deleted unless you get involved in some very sophisticated character matching - all you can tell is that something was done to the text.
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
what version of access are you using?
0
 
LVL 77

Expert Comment

by:peter57r
Comment Utility
I suppose that if you did something like...

Me.Notes = Me.Notes & " (added by " & fOSUserName & " on " & Now() & ") ***" & vbCrLf

then you could test for the presence of *** in the last, say, 7 characters and if found , do not append the username and timestamp.
0
 

Author Comment

by:hbatt
Comment Utility
I am using Access 2007
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
0
 

Author Comment

by:hbatt
Comment Utility
I find that the ColumnHistory method also appends the code with the user name and date when data is being deleted, so unless I am wrong, I don't believe this is a viable solution. I haven't tried Peter57's character matching solution, but if this is the optimal way of handling this issue, what would the code look like?
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
can you try using

if len(me.notes).oldvalue > len(me.notes).value  then  'this means that something was deleted
0
 

Author Comment

by:hbatt
Comment Utility
Capricorn1,

With a slight modification, this works.  (The .value expression triggered an error)

Thanks!
Private Sub Notes_AfterUpdate()
    If Len(Me.Notes.OldValue) > Len(Me.Notes) Then 'this means that something was deleted
        Exit Sub
    Else

    Me.Notes = Me.Notes & " (added by " & fOSUserName & " on " & Now() & ")" & vbCrLf
    
    End If
End Sub

Open in new window

0
 

Author Comment

by:hbatt
Comment Utility
One change to correct a bug when deleting:
Private Sub Notes_AfterUpdate()
   
    If Len(Me.Notes.OldValue) > Len(Me.Notes.Value) Then 'this means that something was deleted
    
        Exit Sub
    Else
 If Me.Dirty Then Me.Dirty = False
    Me.Notes = Me.Notes & " (added by " & fOSUserName & " on " & Now() & ")" & vbCrLf
    
    End If
   
End Sub

Open in new window

0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now