Macro-shortcut to insert Date and Time (1 Viewer)

[Valery]

Would anyone know how to write a macro and assign it to a shortcut key to enter the date and time in the following format. It will be entered in a form's text box.

5-Sep-13/2:15;

It is entered often and would save a great deal of time and be more accurate if "semi-automated"

Thank you for your kindness and support.

PS: looked on websites but only offers IIF statements that enter the date automatically which would not be suitable for this purpose.

 

[Miranda1]

You may not need a macro to do that. I think you can just set the input mask of the form (or the underlying field) to something like: 90\->L<LL\-90/00:00";";;

That way, people using your form will be forced to enter the data in the format you want (in the above example: (d)d-mmm-yy/(h)h:mm; By the way: if you plan to use your date/time values for calculations, you're probably better off using one of the built-in date-time formats that Access offers. That way, the database will actually recognize your input as a date-time.

 

[namliam]

Why not make a button you can click that puts the desired timestamp into the text box?

 

[Valery]

No - this date/time is entered in a memo field as part of notes that are taken - daily and a lot. They are not needed to be sorted or queried upon... so not in an official Date/Time field. I did find out - after much research!! - that CTRL + colon will enter the date, and CTRL SHIFT + colon will enter the time and that is faster then before BUT is not entered in the format we want.

 

[namliam]

And the difference between a memo and a text box is?

something along the ways of
Me.YourMemoField = Me.YourMemoField & Format(Now(), "DD-MMM-YYYY/HH:mm")

 

[Miranda1]

I don't know of any way to run VBA-code using a keyboard short-cut, but that may be me ;-)

What you could try is add an after update event to your memo field that appends your date/time bit at the end of the text in the field. The code would automatically run each time the memo field is updated. Something like this:

Private Sub memo_AfterUpdate()
Dim Output As String

'Assumes your memo field is called memo
'Assigns the value of the memo field to the variable Output
Output = Me!memo.Value

'Appends the date/time at the end of the text in the memo field
Output = Output & " " & Format(Now(), "dd-mmm-yy") & "/" & Format(Now(), "hh:nn") & ";"

'puts text and date/time back in the memo field
Me!memo.Value = Output

End Sub

Is that of any use?

 

[Valery]

Not really but sure can use this somewhere else!

The field will contain text from users - comments to which they must begin with a date and time. Here is an example of the field content:

5-Sep-13/2:15; Left MSG with wife. Will call back. 7-Sep-13/3:45; Not well. Will require surgery. Monique has set appt with Dr. Laslow. 9-Sep-13/1:30 N/A; 3:45 N/A; 18-Sep-13/2:45 Still in ICU.

Hope this gives you an idea. I don't see anything being helpful except an assigned key like Alt 5 or Shift W... whatever combo that doesn't disturb Access...

The last thing we want is to create another field or two for date and time! There is no space for it and much of this comment text is deleted and replace by other text once it has been taken care of or is no longer relevant.

Thank you so much for assisting.

 

[Miranda1]

Ok, I see the problem...

If you have room on your form for a button, you could add the code to the on-click event for the button instead of the update event of the memo field. You could then give the button a caption like "&insert" (this underlines the letter i, indicating that alt+i is the shortcut key for the button). That would give you the option of either clicking the button to run the code, or pressing alt+i.

If that doesn't do the trick, you'll probably have to figure something out with key-press events.

 

[Valery]

Ok, I see the problem...

If you have room on your form for a button, you could add the code to the on-click event for the button instead of the update event of the memo field. You could then give the button a caption like "&insert" (this underlines the letter i, indicating that alt+i is the shortcut key for the button). That would give you the option of either clicking the button to run the code, or pressing alt+i.

OK that sounds good - what is the exact coding for that button? To insert and format the date? and this would go in the OnClick as an event procedure, right? Because it can't go into AfterEvent of the memo field - user will put in that date/time anywhere in the text while they are typing...



If that doesn't do the trick, you'll probably have to figure something out with key-press events.

 

[Miranda1]

When you're in design view, select the button and use the right-click menu to open the properties for the button.

Go to the event tab and select the On Click event. This will open the Choose builder window. In there, choose code builder. Paste the code in my above post between the provided start and end line (minus the sub and end sub statements).

I haven't tried it, but I'm fairly sure it should do the trick.

 

[namliam]

And the difference between a memo and a text box is?

something along the ways of
Me.YourMemoField = Me.YourMemoField & Format(Now(), "DD-MMM-YYYY/HH:mm")

why the whole swapping thingy, this did the same inside a button

 

[Valery]

It works Miranda! Thank you!

 

[Lochwood]

I don't know of any way to run VBA-code using a keyboard short-cut, but that may be me ;-)

What you could try is add an after update event to your memo field that appends your date/time bit at the end of the text in the field. The code would automatically run each time the memo field is updated. Something like this:

Private Sub memo_AfterUpdate()
Dim Output As String

'Assumes your memo field is called memo
'Assigns the value of the memo field to the variable Output
Output = Me!memo.Value

'Appends the date/time at the end of the text in the memo field
Output = Output & " " & Format(Now(), "dd-mmm-yy") & "/" & Format(Now(), "hh:nn") & ";"

'puts text and date/time back in the memo field
Me!memo.Value = Output

End Sub

Is that of any use?

Hi,
I know this is an old thread but hopefully you can still help.. I have managed to get the code working and inserting date and time when i add text to my notes field.. i would also like to have the username of the person entering the text EG: Date Time User. the userdata can be taken from a loaded login form that is hidden so all i need is to direct the code to the form and field. Hope this makes sense.

 

[isladogs]

Several ways of doing this.
For example use, Environ("UserName")

Output = Output & " " & Format(Now(), "dd-mmm-yy") & "/" & Format(Now(), "hh:nn") & " " & Environ("UserName")  & ";"

 

[Lochwood]

Ridders,

Works perfectly thanks you