MS Access SQL Update Table (1 Viewer)

[Malcolm17]

Hi,

I have a table called PLUs, I need to update the value of the field called Desc from an unbound field on a form.

I am trying the following line of code and cannot see what is wrong with it, please can you help?

<DoCmd.RunSQL "UPDATE PLUs SET Desc = Forms.EPOSControl.Starter1Description WHERE Code = '1118'">
or
<DoCmd.RunSQL "UPDATE PLUs SET Desc = Forms.EPOSControl.Starter1Description WHERE Code = Forms.EPOSControl.Start1Code">

The table is called PLUs
The form is called EPOSControl
The field to update from the form is called Starter1Description, it is on an unbound Form and unbound Text Box
The field to update in the table is called Desc (Stored as Text)
The field to select which row to update is called Code (Stored as Text)

I can manually update it in the table or by using an update query, so I believe it is my line of code that is wrong, but I can't see where.

Many thanks,

Malcolm

 

[pbaldy]

You have to concatenate the variable portions. Here's a FAQ:

Building SQL in VBA

 

[Malcolm17]

Hi,

I keep looking at this an cannot work it out, I have used code which I use in another database to do something similar, however I cannot get it to work, please could someone be so kind to look at this for me and put me out of my misery.

Many thanks,

Malcolm

 

[CJ_London]

Desc is a reserved word and as such needs to be enclosed in square brackets.

you need to reference the value of your controls separately in your string

"UPDATE PLUs SET [Desc] = '" & Starter1Description & "' WHERE Code = '" & Start1Code & "'"

 

[pbaldy]

DoCmd.RunSQL "UPDATE PLUs SET [Desc] = '" & Forms.EPOSControl.Starter1Description & "' WHERE Code = '1118'"

 

[Malcolm17]

Thank you for all your help, this has helped my problem, I didn't know that Desc was a reserved word - every day is a school day!

Malcolm

 

[CJ_London]

here is a list for most reserved words

List of reserved words in Access - Microsoft 365 Apps

Lists the reserved words that you should not use in field, object, and variable names. It is not practical to provide a list of all reserved words, such as built-in function names or Access user-defined names.

docs.microsoft.com