ordering lines within a memo field by date/time

[kleaves]

I have a memo field which records the events that happen on a form. So for example when a user changes the assignment from one user to the next, a row is written to the memo field indicating date, time, windows logon and then a brief description

Basically I end up with :

07/12/06 11:16:34 kleaves : Assigned user changed from Joe Bloggs to John Doe

At present every new row is added to the memo field at the end, so the most recent action is at the bottom.

How can I sort this so that most recent is at the top.

 

[DaveJHPT]

Add each new row at the beginning rather than at the end.

Dave

 

[Dreamweaver]

Instead of adding like me![memo] = me![memo] & vbcrlf & new info

try adding Me![Memo] = New Info & vbcrlf & Me![Memo]

I don't think you can sort the contents but somebody may prove me wrong there LOL
you may be able to correct the old datas sort though.

the only thing I can think of is coding a sort that would Look for dates in the text string then re-write the field data using the datas that way it would All be sorted the way you want it .

as a thought you could use an array to help with the lines esp if you've used a vbcr or vbcrlf but I'm not that good with arrays LOL

mick

 

[kleaves]

Dave

Sorry for being such a novice !!! When you say add each row at the beginning rather than at the end. How can I do this. Here is my Code:

Private Sub Submitt_Record_Click()
On Error GoTo Err_Submitt_Record_Click
Dim curNewValue As String
Dim curOriginalValue As String
Dim strMsg As String
Dim curDate As String
Dim curTime As String

curDate = Date
curTime = Time()
'curOriginalValue = ""
'curNewValue = ""


If ((IsNull([Assigned to:]) <> 1) And ([Assigned to:] <> "")) Then
' curOriginalValue = [Assigned to:].OldValue
If (IsNull([Assigned to:].OldValue)) Then
curOriginalValue = ""
Else
curOriginalValue = [Assigned to:].OldValue
End If
If (IsNull([Assigned to:])) Then
[Assigned to:] = ""
End If

curNewValue = [Assigned to:]
If (curOriginalValue = "") Then
' first time a field has been assigned
' strMsg = curDate + " " + curTime + ": 'Assigned to' set to " + curNewValue
strMsg = Now() & " " & Environ("UserName") & ": 'Assigned to' set to " & curNewValue
If (IsNull([Change History:])) Then
[Change History:] = strMsg
Else
[Change History:] = [Change History:] + Chr(13) + Chr(10) + strMsg
End If
Else
If (curOriginalValue <> curNewValue) Then
strMsg = Now() & " " & Environ("UserName") & ": 'Assigned to' changed from " + curOriginalValue + " to " + curNewValue
If (IsNull([Change History:])) Then
[Change History:] = strMsg
Else
[Change History:] = [Change History:] + Chr(13) + Chr(10) + strMsg
End If
End If
End If
End If

'curOriginalValue = ""
'curNewValue = ""
If (IsNull([Status:]) <> 1) Then
If (IsNull([Status:].OldValue)) Then
curOriginalValue = ""
Else
curOriginalValue = [Status:].OldValue
End If

curNewValue = [Status:]
If (curOriginalValue = "") Then
strMsg = Now() & " " & Environ("UserName") & ": 'Status' set to " + curNewValue
If (IsNull([Change History:]) <> 1) Then
[Change History:] = [Change History:] + Chr(13) + Chr(10) + strMsg
Else
[Change History:] = strMsg
End If
Else
If (curOriginalValue <> curNewValue) Then
strMsg = Now() & " " & Environ("UserName") & ": 'Status' changed from " + curOriginalValue + " to " + curNewValue
If (IsNull([Change History:]) <> 1) Then
[Change History:] = [Change History:] + Chr(13) + Chr(10) + strMsg
Else
[Change History:] = strMsg
End If
End If
End If
End If

findNewID

DoCmd.GoToRecord , , acNext

Exit_Submitt_Record_Click:
Exit Sub

Err_Submitt_Record_Click:
MsgBox Err.Description
Resume Exit_Submitt_Record_Click

End Sub

 

[kleaves]

Dreamweaver

I understand your response, as I already do your suggestion on another memo field. However, in this memo field, I didn't think I can specify the order as simple as that because of the code in place. Please see my response to Dave I submitted just before this.

Kleaves

 

[gemma-the-husky]

its the bit that does the changehistory i think

instead of

If (IsNull([Change History:])) Then
[Change History:] = strMsg
Else
[Change History:] = [Change History:] + Chr(13) + Chr(10) + strMsg
End If

put the strmsg first, so
[Change History:] = strmsg & vbcrlf & [Change History:]

note that vbcrlf is a constant consisting of chr(13) and chr(10), and is a bit easier to type in regularly, as well as automatically being syntax-checked - ie it will capitalize if spelt properly, and will cause compile errors if spelt incorrectly.

 

[kleaves]

Thanks, that done the trick. Now it makes sense !!!

Everyones help has been much appreciated

 

[Dreamweaver]

Dreamweaver

I understand your response, as I already do your suggestion on another memo field. However, in this memo field, I didn't think I can specify the order as simple as that because of the code in place. Please see my response to Dave I submitted just before this.

Kleaves

The Other suggestion was a way you could correct the OLD data only it would be complex and require a loop through the table then maybe creating an array of each Memo field the sort of stuff that gives most sane people headaches but as I said only a suggetion.

If it was me I would just leave as is.

Didn't mean to confuse ya if I did sorry

mick

 

[kleaves]

Mick

Thanks for the suggestion, but I think I'll go without the headache !!

Thanks for your help

Kleaves