DataBase - insert date and time into memo field

Asked By mboren

28-Jan-10 04:17 PM

Using access 2003

Have a status form when staff enters their progress into a memo field

I need the user to be able to enter the date and time of his entry

The end result will be a short cut the will insure the infomation, I just
cannot figure out the code for either a macro or a module that will do it.

28-Jan-10 04:50 PM

If you are storing multiple dates and multiple progress steps in a single memo
field... you are sowing trouble for yourself. It will be very difficult or
impossible to search this big amorphous blob of text with some embedded dates.

I would strongly suggest that you instead create a Table related one-to-many
to this table (whatever it is); it should have a foreign key ID field for the
link, a NoteDate field with a default value of Now() to trap the time that an
entry is made, and a Text or Memo field into which the staff member will enter
their progress.

If you really want one big (essentially unsearchable) memo field you can use
some VBA code to enter the date and time; you will need to tell us what form
event would be appropriate: do you want the field timestamped when the user
opens the form, or moves to a particular record, or sets focus to the notes
textbox, or clicks a button, or what?
--

John W. Vinson [MVP]

29-Jan-10 09:10 AM

Wow, thanks for the response; yeah, know about the lack of search capability,
but this is more like a diary which people enther there  information  to the
bottom of the field; brilliant people but people who cannot seem to remember
to type in the date.

In my ideal world the user will enter a shortcut (ctrl y would be fine) and
wherever the curser is, the date (and username, i did not mention that in the
first message) will be inserted.  Hopefully they will insert it at the left
of a blank line and only use it once, but that will be there problem.

Would love to see what this looks like in VBA, its frustrated me.

thanks again

michael







--
Message posted via http://www.accessmonster.com

29-Jan-10 12:55 PM

My suggested table would automatically fill in the date AND let them enter
their diary. You can display it on a Continuous Form so you can see past
entries easily.


If you insist. Put a Command Button cmdDiary on the form with a Caption of


This will display Diary with the y underlined, and typing Alt-Y on the
keyboard will click the button. In its code put

Private Sub cmdDiary_Click()
Dim Username as String
Username = fOSUserName()
Me!txtMemofield.SetFocus
Me!txtMemofield = Me!txtMemofield & vbCrLf & Date & " " & Username
Me!txtMemofield.SelStart = Len(Me!txtMemofield)
End Sub
'******************** Code Start **************************
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _

Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If ( lngX > 0 ) Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = vbNullString
End If
End Function
'******************** Code End **************************

The fOSUserName code is from http://mvps.org/access/api/api0008.htm (and there
are tons more useful code on that site).
--

John W. Vinson [MVP]

30-Jan-10 12:49 PM

What we have done to facilitate this type of logging is the following:

1) not allow direct logging into the memo field.
2) User double clicks on the field in order to update. When they do
this it opens a new form which shows the current content of the memo
field and another txtbox that they can enter new information into.
3) button on that form that says to add new comments to existing
information.
4) when button is pressed then construct the update to be
oldcomentfield = oldcommentfield & now() & " " &
newtoaddcommentsfield
OR
oldcommentfield = now() & " " & newtoaddcommentsfield & " " &
oldcommentfield
5) form also contains button to exit without any updating.

Ron

29-Jan-10 03:55 PM

works great, it is a work of sheer genius, putting code in a button  and
adding a shortcut to it, amazing.
thank you all very much
i hope you realize you have adopted me
michael




--
Message posted via http://www.accessmonster.com

29-Jan-10 06:28 PM

LOL!!!


--

John W. Vinson [MVP]