SQL String failing 1

[policechiefwiggum]

Hi All,

I thought i'd cracked everything on the tool i'm building but something strange has started to happen.

As with my previous posts i'm self taught at this Access / VBA / SQL malarkey!

Long story short when i click my save button on my form it runs a long SQL query. This writes the value of about 30 fields to the database (the fields consits of check boxes and text fields.)

Then it takes the value of the "new_update" field and combines it with the value of the "old_update" field.
this then writes the new value to the [OldUpdate] field in the database.

like so:

Dim Username As String
Dim Clock As Date
Dim AllUpdate As String
Dim OldNewUpdateSQL
Dim AddNewUpdate As String
AddNewUpdate = Me.new_update.Value
'MsgBox (Username)

Clock = Now()
AllUpdate = Clock & "  " & Username & vbCrLf & "" & Me.new_update.Value & vbCrLf & vbCrLf & Me.prev_update.Value

which then executes:

Set ConnectDatabase = CurrentProject.Connection
Set DatabaseSQL = New ADODB.Command
OldNewUpdateSQL = "UPDATE [projects] SET [OldUpdate] = '" & AllUpdate & "' WHERE [UATID] = " & uatproject & ""
'MsgBox (OldNewUpdateSQL)
DatabaseSQL.ActiveConnection = ConnectDatabase
DatabaseSQL.CommandText = OldNewUpdateSQL
DatabaseSQL.Execute

Note: Username if pulled from OpenArgs further up the code.
This used to work but now gives a Syntax error.

I guess my question is, is there anyway to time and date stamp the new update and tack it on to the old update without having to string them all together?

when i remove the last part of my "AllUpdate" string its works perfectly, so i'm assuming that too much data is trying to be passed to the DB

Thanks
Police Chief Wiggum

 

[PHV]

You may try to replace this:
& AllUpdate &
with this:

 & Replace(AllUpdate, "'", "''") &

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[policechiefwiggum]

PHV, that seems to have worked perfectly, thanks very much )