Solved: username & date a comment in access

Is it possible to have a comment field in a form so that after a comment is made an after update procedure would insert the username and date on the front end of the comment? Something like "username, 1/16/11: Comment......"

If new comments are made then it would be added and a new username and date would be added so that all the comments would be tracked along with username and date?

That way if multiple managers make a comment it would denote who made them and not just whoever did the last comment as I have it now.

My field name is MGR_Comments

Thanks, Joe

 

Joe, I am sure you know that this is possible, most things are with VBA.
I would suggest a CommentInput Field, where the AfterUpdate event procedure says.
me.MGR_Comments = Date() & " - " & me.CommentInput & vbNewLine & MGR_Comments
You can add in the user, but I am not sure how you are identifying them.
This vba code keeps the latest inputs at the beginning of the comment.

 

I am currently doing this:

Private Sub MGR_Comments_AfterUpdate()
If Not IsNull(Me.MGR_Comments) And Me.MGR_Comments <> "" Then
Me.MGRCom = True
Me.MGR_Signed = Environ("username")
Me.MGR_Signed_Date = Now
Else: Me.MGRCom = False
Me.MGR_Signed = ""
Me.MGR_Signed_Date = ""
End If
Me.Refresh
End Sub

But it occured to me that I could eliminate my "Comment By" and "CommentDate" fields if I can just get the username and date to populate right in the comment. With the current code also I have it so that the username and date = " " if the comment is deleted, this would no longer be required If the username and date is populated in the comment.

Here is a copy of the DB and I hope you can read it I am using office 2010 on my home laptop but I think it is still saving in 2002-2003 format

 

It opens up OK, my only problem is if the only field is the Comment field and a manager adds to it his addition is going to be at the bottom. It will be almost impossible to add the user and date to the front of that line. It is very easy to add it to the end of the line however.
If you have an input field field for Adding Comments then you could do it.

 

I'm not sure what you mean, would that be a seperate input form/field that would add to the comment field everytime a comment is made? That would be ok. If this is overly complicated then it would be ok to have the user and date at the end.

 

Can you show me an example of using an input field to add a comment. Would it be an unbound field that writes to the MGR_Comments field???

 

Joe, I have added the input fields to the table in place of the "signed" and "date" fields, but I have only put the Manager's one on the form.
You enter the comment in to the Input field, the VBA addes it to the front of the Comment field and then clears the input field ready for another entry. That way the only overhead in the table is maintaining a blank field.

What are the Comment Check boxes for?

 

The comment check boxes are there because my boss wants to be able to look at the summary form, frmNNNList and see if comments have been made and by what department. It makes the radio buttons true or false on frmNNFList. At first I tried to do this by making labels visable or not, but since they were not bound to the record it didn't work so I came up with this scheme. Maybe there is a better way???

 

Wow, that's some good stuff OBP, you have taught me so much.

A couple of questions:
why is it that when I hit the tab key it goes to the next record? Is there a way so that the tabbing just continues on the same form. I notice that any time I click in the input box, even if I don't make a comment and tab out it goes to the next record. Maybe it needs to setfocus to something??

Thanks, Joe

 

Joe, the field that I added is the last in the Tab order, move it up the order and it will not move to the next record.
You can also set the Form's "Cycle" to "Current Record" instead of all records.

 

OK I got the tab thingy working right and cycle set to currend record. When I make a comment and press enter the comment input field clears but the comment does not show unless I click refresh, what can I add to the code to refresh the form as part of the procedure? Me.Refresh does not seem to do it.

 

You should not have to do anything, it was immediate on the version that I posted.

 

Sorry OBP it is not showing when I hit enter unless I refresh the form, or if I make a comment and hit tab then it shows. I redownloaded the one you posed again and it is the same way, I wonder if it different because I am running the 2002-2003 version on my 2010 version laptop???

Anyway I have tried to write code to make the form refresh and I cant do it, I don't know what I am doing wrong. I have tried: Me.Refresh, Forms("frmNewNonFunded").Refresh, Forms!frmRemarksInput.,etc,etc and I cannot get it to refresh like it does when I click on the refresh button on the toolbar.

Any ideas?

 

Well you could use me.requery, but it will then go back to record 1.
So you could store the ID number that you are on prior to the requery and then find that record again after the requery.
You can use a Find Combo for the VBA code.

 

I don't quite get it, isn't there some line of code I can put at the end of the procedure that will refresh the form?