I have a Memorandum field on a form where I would like to automatically enter the date and time when I click in the form. I tried to use the "NOW" expression in the On Click properties field but I am doing something incorrectly because it does not work. Thank you for any help you are able to offer. Norm
Enter Date On Click
- Thread starter admsvcs
- Start date
- Local time
- Today, 15:06
- Joined
- Aug 30, 2003
- Messages
- 36,157
That won't work there. You'd need code in the event:
http://www.baldyweb.com/FirstVBA.htm
The code might look like:
Me.Memorandum = Now()
http://www.baldyweb.com/FirstVBA.htm
The code might look like:
Me.Memorandum = Now()
You suggestion worked perfectly. Thank you! The string produced remains highlighted after the expression executes. I tried to click at the end of the string to have an insertion point for additional text and I am not able to do so. Is there something else I need to do? Norm
Hi admsvcs
I had exactly the same query as you which I originally got round by using a temporary field to enter my data in and then pasting in into the memo field with the current Users ID and date/time stamp, rather a convoluted solution but it did the job. Having looked at what was suggested here I re-visited the idea and came up with the following.
First the idea doesn't work as listed because Now() returns a date data type and not a string, using Date$ and Time$ would be one solution, or using the Format() function to format Now() as a string.
Secondly, using on click is not good as if you try to edit the text by clicking in the box it will run the script again.
Thirdly, using Me.Memorandum = Now() would replace any text already in the memo field with the current date/time, which may or may not be what you want to acheive.
My solution is as follows where :-
MaintNotes is the name of my memo field
I have used the 'On Enter' event so that the script only runs every time you enter the memo field not every time you click it (you could of course also use 'Double Click' if you only wanted a date/Time stamp sometimes.
Getuser() is a function of mine that returns the name of the currently logged in user
Private Sub MaintNotes_Enter()
Dim CUser, MN As String
MN = MaintNotes
CUser = GetUser()
Me.MaintNotes = CUser + Format(Now(), " dd/mm/yyyy hh:mm") + Chr(13) + Chr(10) + Chr(10) + MN
End Sub
This then saves any current content in the memo field in variable MN, gets the current users name from GetUser() and then creates a stamp consisting of
Users Name, Date (UK formatted) and Time, Carriage Return and 2 line feeds
and lastly adds the saved "Old" text to the end.
I then insert my "new" notes below the stamp and above the previous notes.
This means that the latest note is always at the top of the memo, obviously you can change these options and the order in which they are added to suit your purpose.
Hope this helps.
Regards
Rick-K
I had exactly the same query as you which I originally got round by using a temporary field to enter my data in and then pasting in into the memo field with the current Users ID and date/time stamp, rather a convoluted solution but it did the job. Having looked at what was suggested here I re-visited the idea and came up with the following.
First the idea doesn't work as listed because Now() returns a date data type and not a string, using Date$ and Time$ would be one solution, or using the Format() function to format Now() as a string.
Secondly, using on click is not good as if you try to edit the text by clicking in the box it will run the script again.
Thirdly, using Me.Memorandum = Now() would replace any text already in the memo field with the current date/time, which may or may not be what you want to acheive.
My solution is as follows where :-
MaintNotes is the name of my memo field
I have used the 'On Enter' event so that the script only runs every time you enter the memo field not every time you click it (you could of course also use 'Double Click' if you only wanted a date/Time stamp sometimes.
Getuser() is a function of mine that returns the name of the currently logged in user
Private Sub MaintNotes_Enter()
Dim CUser, MN As String
MN = MaintNotes
CUser = GetUser()
Me.MaintNotes = CUser + Format(Now(), " dd/mm/yyyy hh:mm") + Chr(13) + Chr(10) + Chr(10) + MN
End Sub
This then saves any current content in the memo field in variable MN, gets the current users name from GetUser() and then creates a stamp consisting of
Users Name, Date (UK formatted) and Time, Carriage Return and 2 line feeds
and lastly adds the saved "Old" text to the end.
I then insert my "new" notes below the stamp and above the previous notes.
This means that the latest note is always at the top of the memo, obviously you can change these options and the order in which they are added to suit your purpose.
Hope this helps.
Regards
Rick-K
Rick,
I am glad you found my inquiry! Thank you!!
You are light years ahead of me. I would never have known that the result for Now() is a data type and not a string, but your remark now makes perfect sense.
You experiences were the same as mine.
I modified your code, without first understanding each element, and I got the result that I expected...it did not work.
Private Sub Memorandum_Enter()
Dim CUser, MN As String
MN = Memorandum
CUser = GetUser()
Me.Memorandum= CUser + Format(Now(), " dd/mm/yyyy hh:mm") + Chr(13) + Chr(10) + Chr(10) + MN
End Sub
I got the error message that GetUser was not recognized. I don't know what CUser is either and Googled for same with no results. What would you recommend for a VBA resource for someone starting from the beginning?. What you have done looks like exactly what I need. Thank you for taking the time to help!!!!
Norm
I am glad you found my inquiry! Thank you!!
You are light years ahead of me. I would never have known that the result for Now() is a data type and not a string, but your remark now makes perfect sense.
You experiences were the same as mine.
I modified your code, without first understanding each element, and I got the result that I expected...it did not work.
Private Sub Memorandum_Enter()
Dim CUser, MN As String
MN = Memorandum
CUser = GetUser()
Me.Memorandum= CUser + Format(Now(), " dd/mm/yyyy hh:mm") + Chr(13) + Chr(10) + Chr(10) + MN
End Sub
I got the error message that GetUser was not recognized. I don't know what CUser is either and Googled for same with no results. What would you recommend for a VBA resource for someone starting from the beginning?. What you have done looks like exactly what I need. Thank you for taking the time to help!!!!
Norm
Hi
sorry, I obviously confused things by sending my procedure as is although some of it will not be of use to you . The Getuser() function is one of my own modules which gets the name of the user logged into the database so that this is recorded along with the date and time. Unless you have a similar function in your own database you will not be able to do that part of it, CUser is simply a variable to hold this users name (abbreviation for Current User)
You can just ignore GetUser and CUser parts, the important bit is
Dim CUser, MN As String
MN = Memorandum
Me.Memorandum= Format(Now(), " dd/mm/yyyy hh:mm") + Chr(13) + Chr(10) + Chr(10) + MN
MN is just a temporary variable to store any contecnt from the memo field, you could call it pretty much anything you like
The Format() function just changes the format of the Date and Time produced by the Now() function and also changes it to a string data type rather than date data type.
Depending on where you are, you may also want to change or omit the part in quotes as this simply formats the date and time as per the British way of doing things eg 14/07/2011 17:40, assuming you are in the States, you would probably want "mm-dd-yyyy hh:mm" (07-14-2011 17:40) or something like that.
The Chr(13) and Chr(10) are simply a way of adding the ASCII codes for Carriage Return and Line Feed onto the end of the string so that when you then add the old contents of the Memo field (stored in MN) there is blank line between the date and time and any previous text.
If you wanted to add the comments after any previous text rather than before then you would put MN first, followed by the return/line feed codes and finally the date and time. You can do this by changing the line to :-
Me.Memorandum= MN + Chr(13) + Chr(10) + Chr(10) + Format(Now(), " dd/mm/yyyy hh:mm")
As to a good staring point for learning vba, I started by using the Access wizard to convert all my Macros to VB and then looked at the code produced and worked out what it did.
Hope I haven't confused you too much
Rick
sorry, I obviously confused things by sending my procedure as is although some of it will not be of use to you . The Getuser() function is one of my own modules which gets the name of the user logged into the database so that this is recorded along with the date and time. Unless you have a similar function in your own database you will not be able to do that part of it, CUser is simply a variable to hold this users name (abbreviation for Current User)
You can just ignore GetUser and CUser parts, the important bit is
Dim CUser, MN As String
MN = Memorandum
Me.Memorandum= Format(Now(), " dd/mm/yyyy hh:mm") + Chr(13) + Chr(10) + Chr(10) + MN
MN is just a temporary variable to store any contecnt from the memo field, you could call it pretty much anything you like
The Format() function just changes the format of the Date and Time produced by the Now() function and also changes it to a string data type rather than date data type.
Depending on where you are, you may also want to change or omit the part in quotes as this simply formats the date and time as per the British way of doing things eg 14/07/2011 17:40, assuming you are in the States, you would probably want "mm-dd-yyyy hh:mm" (07-14-2011 17:40) or something like that.
The Chr(13) and Chr(10) are simply a way of adding the ASCII codes for Carriage Return and Line Feed onto the end of the string so that when you then add the old contents of the Memo field (stored in MN) there is blank line between the date and time and any previous text.
If you wanted to add the comments after any previous text rather than before then you would put MN first, followed by the return/line feed codes and finally the date and time. You can do this by changing the line to :-
Me.Memorandum= MN + Chr(13) + Chr(10) + Chr(10) + Format(Now(), " dd/mm/yyyy hh:mm")
As to a good staring point for learning vba, I started by using the Access wizard to convert all my Macros to VB and then looked at the code produced and worked out what it did.
Hope I haven't confused you too much
Rick
Thanks Bob, you spotted my deliberate error too 
.
I put the IIF() in as well because otherwise he may get an error if Memorandum is Null
Also, obviously it should be Memorandum not MaintNotes, that's the name of the field in my database doh!!!
Rick
.I put the IIF() in as well because otherwise he may get an error if Memorandum is Null
Also, obviously it should be Memorandum not MaintNotes, that's the name of the field in my database doh!!!
Rick
.Gentlemen, we are almost there. I have attached a screenshot of the code. Here is what happens. When I click in the Memorandum field, the date and time is inserted and highlighted. If I move the cursor to the end of the string, I can then type a memo. If I leave the Memorandum field and go to some other field in my form and then come back to the Memorandum field, a new date and time is inserted ERASING the prior date, time and memo. Norm
Absolutely...
Private Sub Memorandum_Enter()
Dim CUser, MN As String
MN = IIf(Me.Memorandum <> Null, Me.Memorandum, "")
Me.Memorandum = Format(Now(), " m/d/yyyy hh:mm") + Chr(13) + Chr(10) + Chr(10) + MN
End Sub
Norm
Private Sub Memorandum_Enter()
Dim CUser, MN As String
MN = IIf(Me.Memorandum <> Null, Me.Memorandum, "")
Me.Memorandum = Format(Now(), " m/d/yyyy hh:mm") + Chr(13) + Chr(10) + Chr(10) + MN
End Sub
Norm
Hi, I'm pretty sure it doesn't need to be saved in between, I think the error is in my typing, there should be a space between the quotes at the end of the Iif() function or alternativly use Bobs much neater MN = Nz(memorandum, " ")
Sadly I can't test it at the moment as Apple have not brought out an iPhone Access app yet
Sadly I can't test it at the moment as Apple have not brought out an iPhone Access app yet
