Concat to the existing field by line feed

[DevAccess]

Hello

I am trying to update audit trail when there is multiselect status update to few types of records, and I want to update the status ="Archived" and also want to update audit trail of each record which has audit trail field. but I am getting error missing operator.

sUser = UserNameWindows
currenttime = Format(Now(), "dd/mm/yyyy hh:mm:ss")
SqlQuery = "UPDATE tbl_AllRequests " & _
" SET [Status]= 'Archived' " & _
",[History] = concat([History], 'Archived on " & currenttime & " by '" & sUser & "'&')'&" & _
" WHERE [ID] in (" & selectedIDs & ")"

DoCmd.RunSQL SqlQuery, True

Please help how to make this work, I want to append new action to the existing audit trail of the each records whose status has been update to archived and this new appending line should be appended at new line.

 

[pbaldy]

This may help:

http://www.baldyweb.com/ImmediateWindow.htm

Might help to see the function too.

 

[DevAccess]

This may help:

http://www.baldyweb.com/ImmediateWindow.htm

Might help to see the function too.

Thanks but my query is how to append value to existing field using SQL vba.

 

[pbaldy]

I assumed the function did that. In general:

Field = Field & "New stuff"

In VBA you can include vbCrLf for a new line.

 

[DevAccess]

I assumed the function did that. In general:

Field = Field & "New stuff"

In VBA you can include vbCrLf for a new line.

I am still getting error on second line when i am concating existing field value, what is wrong..

SqlQuery = "UPDATE tbl_AllRequests " & _
" SET [Status]= 'Archived' " & _

", [History] = [History] & CHR(13) & CHR(10) & 'Archived on' " + currenttime + " ' by ' & '" + sUser + "' " & _

" WHERE [ID] in (" & selectedIDs & ")"

 

[MarkK]

...still getting error...

What error are you getting?

 

[DevAccess]

What error are you getting?

", [History] = [History] & CHR(13) & CHR(10) & 'Archived on' " + currenttime + " ' by ' & '" + sUser + "' " & _

error no 3075

syntax missing operator
Full line of code

sUser = UserNameWindows
'    MsgBox sUser
    currenttime = Format(Now(), "dd/mm/yyyy hh:mm:ss")
SqlQuery = "UPDATE tbl_AllRequests " & _
" SET [Status]= 'Archived' " & _
", [History] = [History] & CHR(13) & CHR(10) & 'Archived on' " & currenttime & " ' by ' & '" + sUser + "' " & _
" WHERE [ID] in (" & selectedIDs & ")"

 

[pbaldy]

I'll get out of the way.

 

[MarkK]

Paul, you are not in the way. Would you prefer if I stay out of a thread like this?

Dev, have you printed the final SQL as per Paul's link in post #2? What is the result of that?

 

[DevAccess]

Paul, you are not in the way. Would you prefer if I stay out of a thread like this?

Dev, have you printed the final SQL as per Paul's link in post #2? What is the result of that?

Thanks Paul and Mark and all

I figured out what was issue, below works fine for me

", [History] = [History] & CHR(13) & CHR(10) & 'Archived on ' & #" & currenttime & "# & ' by ' & '" + sUser + "'" & _

 

[DevAccess]

Thanks Paul and Mark and all

I figured out what was issue, below works fine for me

", [History] = [History] & CHR(13) & CHR(10) & 'Archived on ' & #" & currenttime & "# & ' by ' & '" + sUser + "'" & _

since currenttime is date value we have to bound them with "#"

 

[Galaxiom]

since currenttime is date value we have to bound them with "#"

But the date is formatted as text inside the History field.

I have no experience building an audit trail but I would be inclined to take a different approach. Why not store the archive in a related table as multiple records? The date stored as a date in a field makes all actions searchable by date. The type of action stored as a field would also be searchable.

As a bonus the related table would nut suffer the problem of the accumulation of text in the concatenated audit exceeding the 255 character capacity of a text field. Memo fields do sacrifice some retrieval capacities and are a more complex structure inside the file making them more prone to corruption then a text record.

It is possible to display the related records in a subform such that they look like they are all in one textbox on the main form. Or concatenate them all into a textbox on the form or report.