By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,124 Members | 2,208 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,124 IT Pros & Developers. It's quick & easy.

TimeStamping a memo field

P: 4
I saw this in a version of Access 2003, but I can't remember how to "code" it. A programmer before me, had code inserted into the memo field to list the user name and time stamp the entry (with the time as well) when having many users accessing the same files, to know what had been communicated with a customer, by whom and when. I can't remember the code and everything I've tried so far, doesn't get me the result I want. Does anyone know how to write this code?
Nov 29 '06 #1
Share this Question
Share on Google+
12 Replies


missinglinq
Expert 2.5K+
P: 3,532
Private Sub MyMemoField_GotFocus()
Me.MyMemoField = MeMyMemoField.Value & " " & CurrentUser() & " @ " & Now() & " : "
End Sub
Nov 29 '06 #2

NeoPa
Expert Mod 15k+
P: 31,616
It may be better to add this code to the AfterUpdate event rather than every time focus is got.
Nov 30 '06 #3

P: 4
I saw this in a version of Access 2003, but I can't remember how to "code" it. A programmer before me, had code inserted into the memo field to list the user name and time stamp the entry (with the time as well) when having many users accessing the same files, to know what had been communicated with a customer, by whom and when. I can't remember the code and everything I've tried so far, doesn't get me the result I want. Does anyone know how to write this code?

THANK YOU!!! It works very well on "got focus". Namistay!
Jan 4 '07 #4

Expert 5K+
P: 8,434
THANK YOU!!! It works very well on "got focus". Namistay!
Be warned, though - if the user clicks on another control then back to the memo field, their details may be added twice.
Jan 4 '07 #5

missinglinq
Expert 2.5K+
P: 3,532
Where you put it depends on exactly what you're trying to do. If simply time/user stamping where the integrety of the dta doesn't matter, i.e where you have no legal reason to be able to reasonably guarrantee when an entry was made, it's not as important as, say on medical records. That's what I deal with exclusively, so that's how I do it. The user also has to click on a button to go into the memo field to enter data. Editing can't be done on the field. Corrections to data can only be made by the user making a new entry and stating that it is a correction to an earlier entry.
Jan 4 '07 #6

P: 4
Where you put it depends on exactly what you're trying to do. If simply time/user stamping where the integrety of the dta doesn't matter, i.e where you have no legal reason to be able to reasonably guarrantee when an entry was made, it's not as important as, say on medical records. That's what I deal with exclusively, so that's how I do it. The user also has to click on a button to go into the memo field to enter data. Editing can't be done on the field. Corrections to data can only be made by the user making a new entry and stating that it is a correction to an earlier entry.

This is for our information only, when we talked to joe customer, who said what when. What I'm experiencing now, is the next time I go back to the memo field, instead of starting on a new line, the user information, date, etc, comes right after the previous data. How do I write the code for enter key behavior?
Jan 5 '07 #7

Expert 5K+
P: 8,434
This is for our information only, when we talked to joe customer, who said what when. What I'm experiencing now, is the next time I go back to the memo field, instead of starting on a new line, the user information, date, etc, comes right after the previous data. How do I write the code for enter key behavior?
Here's a modified copy of misinglinq's code...
Expand|Select|Wrap|Line Numbers
  1. Private Sub MyMemoField_GotFocus()
  2.   Me.MyMemoField.Value = MeMyMemoField.Value & vbNewLine & CurrentUser() & " @ " & Now() & " : "
  3.   ' You might also want to add this line...
  4.   Me.MyMemoField.SelStart = Len(Me.MyMemofield.Value)
  5. End Sub
Note that this is from a VB6 viewpoint, and may not be entirely correct.
Jan 5 '07 #8

missinglinq
Expert 2.5K+
P: 3,532
The VB6 code works fine here, Killer, but needs two small adjustments.

First, as written, if the memo field is empty the code places a blank line then the time stamp on the second line. So to tweak that we need to check for Null and omit the vbNewLine if the memo filed is blank.

Secondly, the SelStart function bombs out and throws an Overflow Error (Error 6 I think) if the memo field is over half "full." That's because the function takes an Integer as an arguement (max 32767) and a memo field can have approximately 65k characters. The only way I've ever been able to get around this is to use the much dreaded, much maligned SendKeys and mimic <Control> + <End>. I've never experienced any of the reported problems using SendKeys, but try to avoid using it. If anyone has a better hack for this I'd love to hear it!

So we now have:
Expand|Select|Wrap|Line Numbers
  1. Private Sub MyMemoField_GotFocus()
  2.    If IsNull(Me.MyMemoField.Value) Then
  3.       Me.MyMemoField.Value = Me.MyMemoField.Value & CurrentUser() & " @ " & Now() & " : "
  4.    Else: Me.MyMemoField.Value = Me.MyMemoField.Value & vbNewLine & CurrentUser() & " @ " & Now() & " : "
  5.    End If
  6.  
  7.  If Not IsNull(Me.MyMemoField.Value) Then
  8.       If Len(Me.MyMemoField.Value) < 32767 Then
  9.           Me.MyMemoField.SelStart = Len(Me.MyMemoField.Value)
  10.       Else
  11.          SendKeys "^{END}"
  12.      End If
  13.  End If
  14.  
  15.  End Sub
  16.  
Jan 6 '07 #9

Expert 5K+
P: 8,434
Thanks for the pointers, missinglinq. Two points I'd like to make in response.

First, I'd guess you could get around the integer problem by using something like a SendMessage API call.

Second, you could also use an IIF() to produce the vbNewLine or an empty string depending on whether the field is populated. Something like...
Expand|Select|Wrap|Line Numbers
  1.   With Me.MyMemoField
  2.     .Value = .Value & IIF(IsNull(.Value), "", vbNewLine) & CurrentUser() & " @ " & Now() & " : "
  3.     ' Then move the cursor to the end, of course.
  4.   End With
Jan 6 '07 #10

missinglinq
Expert 2.5K+
P: 3,532
Like my signature says, There's ALWAYS more than one way to skin a cat!

Have a great weekend, Killer!
Jan 6 '07 #11

P: 4
Here's a modified copy of misinglinq's code...
Expand|Select|Wrap|Line Numbers
  1. Private Sub MyMemoField_GotFocus()
  2.   Me.MyMemoField.Value = MeMyMemoField.Value & vbNewLine & CurrentUser() & " @ " & Now() & " : "
  3.   ' You might also want to add this line...
  4.   Me.MyMemoField.SelStart = Len(Me.MyMemofield.Value)
  5. End Sub
Note that this is from a VB6 viewpoint, and may not be entirely correct.
PERFECT! This is exactly what I had in mind! You guys are the greatest! :)
Jan 8 '07 #12

Expert 5K+
P: 8,434
PERFECT! This is exactly what I had in mind! You guys are the greatest! :)
Glad we could help.

However, I recommend you read the rest of the discussion as we covered some potential pitfalls of this technique. For example, setting SelStart can fail if the text is too long.
Jan 8 '07 #13

Post your reply

Sign in to post your reply or Sign up for a free account.