writing to an Access memo field using VBA

[JohnH33]

Hi Everyone,
I'm new to VBA programming and am having difficulty writing data to a memo field in Access using VBA. I have a very simple DB with a fieldname/ fieldtype list: [ID] = autocount; [descriptor] = memo; [data ] = memo; [recordnum] = integer; [desc-number] = integer. I want to add some extra text to [descriptor] and write it back into the field. The test code below allows me to write to one of the integer boxes and the merged text appears in the Msgbox but I nothing writes into the [descriptor] field. The same code writes the same thing to every record if I insert " 'test' " into SQLquery2 instead of the mergetext variable. What am I doing wrong?

thanks,
John H

Public Sub test1()

Dim C1, mergedcode As Integer
Dim mergetext, newtext, SQLquery1, SQLquery2 As String
C1 = 1
mergedcode = -6
While C1 < 10
newtext = DLookup("[descriptor]", "working-tbl-copy", "[ID] = " & C1)
mergetext = newtext & C1
MsgBox (mergetext)
SQLquery1 = "UPDATE [working-tbl-copy] SET [working-tbl-copy].[desc-number] = " & mergedcode & " WHERE [working-tbl-copy].[ID] = " & C1
SQLquery2 = "UPDATE [working-tbl-copy] SET [working-tbl-copy].[descriptor] = " & mergetext & " WHERE [working-tbl-copy].[ID] = " & C1
DoCmd.SetWarnings False
DoCmd.RunSQL SQLquery1
DoCmd.RunSQL SQLquery2
DoCmd.SetWarnings True
C1 = C1 + 1
Wend

End Sub

 

[pbaldy]

In your test, you properly surrounded the text value with single quotes. In your actual code, you didn't. Try

SQLquery2 = "UPDATE [working-tbl-copy] SET [working-tbl-copy].[descriptor] = '" & mergetext & "' WHERE [working-tbl-copy].[ID] = " & C1

 

[JohnH33]

perfect. thanks very much. John H

 

[pbaldy]

Happy to help, and welcome to the site by the way!