creating an activity log

[pepper42 via AccessMonster.com]

I have an unbound text box where new notes are typed and a command button
with the code that posts date, time, and new notes to the locked memo field
on click.
However, each time new notes are added, the previous data is just overridden
by the last entry instead of appending to the memo field. (I got the code
from -
http://www.accessmonster.com/Uwe/Forum.aspx/access-forms/12725/Memo-field)

The sad thing is that I had it working in a test database at one point and
was overridden. Any help would be appreciated.

 

[Guest]

Use this when you update the memo field.
[unbound text box] & " - " & [YourMemoField]
You could also add the date-time like this --
Format(Now(), "hh:nn mm/dd/yyyy") & " - " & [unbound text box] & " - " &
[YourMemoField]

 

[Guest]

I’m not familiar with a control button performing a date/time stamp, but you
can try another method (much in line with Karl Dewey’s explanation) by
creating an Event Procedure for the unbound text control in your data entry
form. In design view, open the property sheet and under “After Updateâ€,
click on the first drop-down button to highlight Event Procedure. Then click
on the send button next to the first, and you will be able to enter your
event procedure (See below).

Assuming your unbound comment control is named “CommentUnboundâ€, and your
Memo control is named “Memo†remembering that the control names might be
different than the actual field names, at your discretion, here is a
suggested event procedure:

---------------------------------------------------------------------
Private Sub CommentUnbound_AfterUpdate()

Me.Memo = vbNewLine & vbNewLine & Format(Now(), “m-d-yy h:nn am/pm. â€) &
Me.CommentUnbound &†“& Me.Memo

Me.CommentUnbound = Null

DoCmd.GotoControl “CommentUnboundâ€

End Sub
--------------------------------------------------------------------

The last line is not critical. The “DoCmd.GoToControl “CommentUnboundâ€
allows the cursor to remain in the unbound control for another entry. The
procedure above allows for a double line feed so that the next entry into the
Memo control (field) will appear two lines above the previous entry for easy
reviewing. Your comments therefore appear in descending order with the
latest at the top.

Locking the memo control (field) and keying in text to an unbound control is
not always desirable, as staff can more easily type and review the comments
if they type directly into the Memo control (field). There are also many
times that the user will need to edit the text he/she enters, particularly if
the text is long. Your method doesn’t allow corrections or edits if the memo
field is locked. A worker may review the text entered into the Memo and
decide minutes later that the information needs to be modified. The only
recourse is to make another entry explaining a correction or addition to a
previous entry. It could be tiring to whoever has to read all the
notes/comments in the Memo field.

Also, there is a way to capture the name of the person logging in to the
network and if you have a field in your table such as “LastUpdatedBy†or
“Modifier†,etc., you could add that name to your Date/Time stamp so that you
have a date and time and worker’s name who adds comments to the Memo.
Explaining hwo to perform this task, however, requires a longer explanation
that doesn’t address your immediate needs.

Hope this helps.

 

[pepper42 via AccessMonster.com]

Thank you! it works perfect. I believe the issue was not with the code, but
where I had the event procedure occuring. The event now posts the date/time,
currentuser and new comments without overridding the previous posting to the
memo field.

However, what would need to change in the code to have the new notes posted
in ascending order?

As far as the advice on the locked comments field - I agree. There is
another way to edit the comments from a form that has some additional
requirements/permissions before editing.

 

[Guest]

Re: Event Procedure for Ascending Order (vs. Descending Order).

Hi Pepper42.
For clarification and the benefit of other readers, please excuse my
repeating and/or rewording some text that I used in my last message.

In form design view, highlight your unbound control and open the property
sheet. Click the Event tab and place your cursor on the line, “After
Updateâ€. When your cursor is on that line, you will see two control buttons
on the far right side of that line. Click the first button and highlight the
words, “Event Procedure†on the drop-down list. Then click the second button
(ellipsis…) and you will be taken to the Event Procedure screen. Add your
Event Procedure between the two existing lines:

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - -
Private Sub CommentUnbound After Update ()

ADD YOUR EVENT PROCEDURE HERE (See Below)

End Sub
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - -

Assuming your unbound comment control is named “CommentUnboundâ€, and your
Memo control is named “Memoâ€, remembering that the control names might be
different than the actual field names (at your discretion), here is a
suggested event procedure:

------------------------------------ ASCENDING ORDER
-------------------------------

Private Sub CommentUnbound_AfterUpdate()

Me.Memo = Me.Memo & vbNewLine & vbNewLine & Format(Now(), “m-d-yy h:nn
am/pm. â€) & Me.CommentUnbound '(Expression all on one line)

Me.CommentUnbound = Null

DoCmd.GotoControl “CommentUnboundâ€

End Sub

 

[pepper42 via AccessMonster.com]

Thank you! I can tell now what the code is doing and how to rearrange the
order of commands in other events that I have. Luckily, I am now in a work
environment where once this database is up and running, I can spend some time
learning to code properly instead of hacking about .