Hi All,

I was wondering if anyone could help me out here.

I have designed a customer database and each customer has their own notes section.

I have a unbound text box which people will add notes to which will automatically place the notes typed in the notes section. This will time stamp the notes and show the user who placed the notes on as well.

I have the following code being used on this unbound text box.

Private Sub txtMemoAdd_AfterUpdate()
Me.YourRealMemoField = Me.YourRealMemoField & vbCrLf & Environ("username") & "-----" & Now() & vbCrLf & Me.txtMemoAdd
Me.txtMemoAdd = ""
End Sub

What i want to try an do is every time a new note is added i want to display it in descending order so the newest note is always on top.

Does anyone know how to do this?

Thanks in advance

The simplest way of doing this would be to create a separate table for your notes and tie it to your customer table. This way each customer can have multiple notes. You could have a date field in the notes field that is set to the current date as the default. There was just a thread on this same thing not that long ago. Here's the link to it http://www.UtterAccess.com/forum/Textbox-A...g-t1981239.html

I posted code in that thread, which will do what you're after. The preferred way is to normally to have a separate table for your notes unless the users specifically want it this way, which is sometimes the case.

Okay i have looked at the link on what you have put but this does not help me out on what i want to do.

At current there are historic notes where all new notes are added on the bottom of the memo field.

Instead of being added to the bottom of the memo field i want them adding to the top.

I know this is possible when dealing with SQL code but not sure how to do it with VBA code.

Any help would be appreciated.

The code that I posted does exactly that. The notes are in a single field. When a new entry is created, it is dated today and placed at the top. As far as being able to do it with SQL. You would be able to do that if you had a separate table for your notes. You would just simply do a sort descending on your date field.

Okay am reading your code on what you have put and to be fare i am fairly new to VBA code but this does not make sense to me.

I have 1 simple line of code that does a job for me but instead of adding notes to the bottom i want them at the top for mainly as a preference for the user so its more user friendly.

Your suggesting to write this which just goes right over my head. As it does not do what i require in keeping the original line of code of what i had. IE. date and time stamping all new notes with the PC name that did it.

Dim strComments As String
Dim dtDate As Date
Dim strAddComments As String
Dim strExistingComments As String
Dim intID As Integer
Dim intRecCount As Integer
dtDate = Date

If IsNull(Me.txtNewComments) Then
MsgBox "There are not comments to enter", vbInformation
Exit Sub
End If

intID = Me.ID
intRecCount = DCount("*", "Customers", "[ID]=" & intID & " AND [Comments] Is Not Null")
strComments = dtDate & " " & strUser & " " & Me.txtNewComments
If intRecCount < 1 Then 'No existing comments
strAddComments = strComments
Else 'Add to comments
strExistingComments = DLookup("Comments", "Customers", "[ID]=" & intID)
strAddComments = strExistingComments & vbCrLf & strComments
End If

'Update the comments
CurrentDb.Execute "UPDATE Customers SET Comments = """ & strAddComments & """" & _
"WHERE ID= " & intID




Isn't there an easier way to do this as i don't want to start creating all sort of tables and linking them into each individual customer.

I have 1 notes section in the customer table where this is a memo field and i would like to keep it that way i dont see the benefit of creating another table just for notes when i already have this in the customer table.

The code that I provided is intended for just a single memo field in the customers table. Let's break the code down.



These are modified slightly from the original. The user was taken out and the newest comments will appear first.

Simplest just to change



to

Okay i have managed to get it working after a bit of tweaking on my test database.

What i need to do now is instead of date being placed i need it to be date and time and i also need to add onto this so the PC Name is added onto the same line of the date and time.

Also what i need to do is add space in between each note so it is not crowed when there are a list of notes.

Any idea's on how to make these amendments?

What's the code you're currently using?

This is code that i have manged to get working but needs tweaking before its ideal for what i need.

Private Sub Command9_Click()
Dim strComments As String
Dim dtDate As Date
Dim strAddComments As String
Dim strExistingComments As String
Dim intID As Integer
Dim intRecCount As Integer
dtDate = Date


If IsNull(Me.txtNewComments) Then
MsgBox "There are not comments to enter", vbInformation
Exit Sub
End If


intID = Me.ID

intRecCount = DCount("*", "Customers", "[ID]=" & intID & " AND [Comments] Is Not Null")

strComments = dtDate & " - " & Me.txtNewComments
If intRecCount < 1 Then
strAddComments = strComments
Else
strExistingComments = DLookup("Comments", "Customers", "[ID]=" & intID)
strAddComments = strComments & vbCrLf & strExistingComments
End If

CurrentDb.Execute "UPDATE Customers SET Comments = """ & strAddComments & """" & _
"WHERE ID= " & intID
End Sub

Did you try the approach I suggested, which doesn't need an Update query?

If you're determined to use the Update query, change



to



If you want more space between comments, use more than one vbCrLf in a row (as in & vbCrLf & vbCrLf & vbCrLf)

Realistically, though, this is not a recomended approach. If you have a need such as this, each comment should be a separate record in a related table. You're violating database normalization rules!

Just going with Doug's example, you could just modify it to be like this.



That's all you would need.

Cheers thats great i now have that working but its doing something strange but not sure why.

Its doing the following;

Username @ Date / Time then doing Date again

Do you know why its putting another Date at the end?

It's because you're adding the date in the following line of code:

Okay i think i have a slight problem so maybe you guys can help as you have been fantastic so far.

When i put a test note on to see what it does it doesn't work but when i put another note on it then kicks in and works.

Do you know why?

For example;

The first note pops up as the following;

- Date
Test Notes


When i try this again it then goes above the notes but does the following;

- Username @ Date / Time : Date
Test Notes


Any idea's?

Okay tried your code but it didn't work. I think i have solved the problem with the following code.

"didn't work" isn't much to go on. What was the problem?

Glad you got it working though...