Update Statement SQL - VBA (access) (1 Viewer)

p4nny

New member
Local time
Today, 17:16
Joined
Mar 16, 2020
Messages
15
Hello,

I keep getting a syntax error on the following update statement. All of the fields are spelt correctly.

Code:
Private Sub Command133_Click()
CurrentDb.Execute "UPDATE tblPlannedRoles" _
& "SET [2020] = '" & Me.rtxt2020 & "', [2021] = '" & Me.rtxt2021 & "', [2022] = '" & Me.rtxt2022 & "', [2023] = '" & Me.rtxt2023 & "', [2024] = '" & Me.rtxt2024 & "', [2025] = '" & Me.rtxt2025 & "', [2026] = '" & Me.rtxt2026 & "', [2027] = '" & Me.rtxt2027 & "', [2028] = '" & Me.rtxt2028 & "', [2029] = '" & Me.rtxt2029 & "', [2030] = '" & Me.rtxt2030 & "'" _
& "WHERE [Role Title] = '" & Me.roletitle & "' AND [Country] = '" & Me.Country & "' AND [Employer] = '" & Me.Employer & "' AND [Sub Function] = '" & Me.subfunction & "' AND [Job Family] = '" & Me.[JobFamily] & "' AND [Grade / Level] = '" & Me.grade & "'"
End Sub

appreciate any help!

Thanks
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:16
Joined
Oct 29, 2018
Messages
21,358
Hi. First thing I noticed is you're missing a space between the table name and the SET statement. Your SQL looks like this:

UPDATE tblPlannedRolesSET [2020] =...
 

Micron

AWF VIP
Local time
Today, 13:16
Joined
Oct 20, 2018
Messages
3,476
Create a variable for the sql. Debug.Print the variable before trying to execute. Look at the print out - the problem may be obvious. If it is here, I missed it. If you don't see the issue, copy the sql to a new query in sql view and try switching to datasheet view. If it balks, the sql view may show you where the issue is.
EDIT:
Nice catch DBG.
p4nny - My suggestion is something you should adopt as a trouble shooting method from now on.
 

plog

Banishment Pending
Local time
Today, 12:16
Joined
May 11, 2011
Messages
11,611
Your table(s) are not set up properly. Access isn't a spreadsheet; databases should accomodate data vertically (with more rows) and not horizontally(with more columns). Also, when you store values in field names ([2020], [2021], etc.) it means you need a new table, not more columns. The process of setting up tables in a database is called normalization (https://en.wikipedia.org/wiki/Database_normalization), read that link and then thru a few tutorials.

This next advice is academic because you really need to stop whatever you are working on fix your tables, but when you are building an SQL string in VBA and you run into trouble with it, you need to find out exactly what that string contains. Build your SQL in a string (strSQL = "UPDATE tblPlannedRoles...", then do a Debug.Print to find out what exactly its building. If you can't eyeball the error, you then throw that SQL into a query object and see what error it throws there.

Again, though, fix your tables.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:16
Joined
May 7, 2009
Messages
19,169
actually two:

add space (" ") at the beginning and end of this line:

& "SET [2020] = '" & ....
 

Users who are viewing this thread

Top Bottom