Prev: Trying to not count duplicates in a report
Next: Creating a Total from a Sub Report
From: mboren on 28 Jan 2010 16:17
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
can't figure out the code for either a macro or a module that will do it.

From: John W. Vinson on 28 Jan 2010 16:50
On Thu, 28 Jan 2010 21:17:33 GMT, "mboren" <u57845(a)uwe> wrote:

>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
>can't figure out the code for either a macro or a module that will do it.

If you're storing multiple dates and multiple progress steps in a single memo
field... you're 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'll 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]
From: mboren via AccessMonster.com on 29 Jan 2010 09:10
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 can't 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 didn't 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






John W. Vinson wrote:
>>Using access 2003
>>
>[quoted text clipped - 4 lines]
>>The end result will be a short cut the will insure the infomation, I just
>>can't figure out the code for either a macro or a module that will do it.
>
>If you're storing multiple dates and multiple progress steps in a single memo
>field... you're 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'll 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?

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

From: John W. Vinson on 29 Jan 2010 12:55
On Fri, 29 Jan 2010 14:10:07 GMT, "mboren via AccessMonster.com" <u57845(a)uwe>
wrote:

>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 can't seem to remember
>to type in the date.

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.

>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 didn't 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.

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

"Diar&y"

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 _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

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]
From: Ron2006 on 29 Jan 2010 12:57
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
 |  Next  |  Last
Pages: 1 2
Prev: Trying to not count duplicates in a report
Next: Creating a Total from a Sub Report