Update query syntax error (maybe ?) (1 Viewer)

HelpMoses76

Member
Local time
Today, 10:45
Joined
Sep 17, 2020
Messages
45
Private Sub cmdUpdate_Click()

Update BR - DETAILS
Set [BR-DETAILS].CostCentre = Me.txtCostCentre.Value

End Sub

I am getting an error that says sub or function not defined . It highlights the word Update. I wonder what I am doing wrong.

Moses
 

plog

Banishment Pending
Local time
Today, 09:45
Joined
May 11, 2011
Messages
10,296
A Sub is written in the VBA language. A query is written in SQL. You have tried to put SQL striaght inside VBA and the VBA interpreter has no idea what you are talking about because it doesn't speak SQL.

You need to use a VBA function that can pass SQL to the SQL engine and have it do the query. That is achieved with a DoCmd.RunSQL:


Check out that link it will give an example.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:45
Joined
Oct 29, 2018
Messages
14,050
Hi Moses. You can also try it this way.
Code:
Dim strSQL As String
strSQL = "UPDATE [BR - DETAILS] SET CostCentre = '" & Me.txtCostCentre & "'"
CurrentDb.Execute strSQL, dbFailOnError
Hope that helps...
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:45
Joined
Feb 19, 2002
Messages
31,026
Of course if your form is bound, you might not need code at all. Bound forms automatically update the tables to which their recordsources are bound.
 

HelpMoses76

Member
Local time
Today, 10:45
Joined
Sep 17, 2020
Messages
45
Hi Moses. You can also try it this way.
Code:
Dim strSQL As String
strSQL = "UPDATE [BR - DETAILS] SET CostCentre = '" & Me.txtCostCentre & "'"
CurrentDb.Execute strSQL, dbFailOnError
Hope that helps...

Can I modify this to update it based on an auto number field ?

This is what I tried .

strSQL = "UPDATE [BR-DETAILS] SET CostCentre = '" & Me.txtCostCentre & "' , AssetName = '" & Me.txtAsset & "' WHERE ID = ' " & Me.List248.Column(21) & " ' "
CurrentDb.Execute strSQL, dbFailOnError

Get a datatype mimatch error.
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:45
Joined
Sep 21, 2011
Messages
7,723
Can I modify this to update it based on an auto number field ?

This is what I tried .

strSQL = "UPDATE [BR-DETAILS] SET CostCentre = '" & Me.txtCostCentre & "' , AssetName = '" & Me.txtAsset & "' WHERE ID = ' " & Me.List248.Column(21) & " ' "
CurrentDb.Execute strSQL, dbFailOnError

Get a datatype mimatch error.
That will be because you have surrounded the value for the ID field with single quotes, which Access expects to be a string.?
Remove the single quotes as I expect ID to be numeric.

Same would apply if any of the other fields are numeric.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:45
Joined
Feb 19, 2013
Messages
12,895
ID's are usually numeric in which case you don't use the single quotes.
 

HelpMoses76

Member
Local time
Today, 10:45
Joined
Sep 17, 2020
Messages
45
Can I modify this to update it based on an auto number field ?

This is what I tried .

strSQL = "UPDATE [BR-DETAILS] SET CostCentre = '" & Me.txtCostCentre & "' , AssetName = '" & Me.txtAsset & "' WHERE ID = ' " & Me.List248.Column(21) & " ' "
CurrentDb.Execute strSQL, dbFailOnError

Get a datatype mimatch error.
No luck ;-(
 

HelpMoses76

Member
Local time
Today, 10:45
Joined
Sep 17, 2020
Messages
45
That will be because you have surrounded the value for the ID field with single quotes, which Access expects to be a string.?
Remove the single quotes as I expect ID to be numeric.

Same would apply if any of the other fields are numeric.
ID's are usually numeric in which case you don't use the single quotes.
no luck even after removing the quotes ;-(
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:45
Joined
Sep 21, 2011
Messages
7,723
You only remove the quotes for anything which is not a string?
Show the database properties for those fields in design view.

Another way is add Debug.Print strSQL AFTER setting the variable and copy and paste the result from the immediate window back here. Comment out the Execute in the meantime.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:45
Joined
Oct 29, 2018
Messages
14,050
Can I modify this to update it based on an auto number field ?

This is what I tried .

strSQL = "UPDATE [BR-DETAILS] SET CostCentre = '" & Me.txtCostCentre & "' , AssetName = '" & Me.txtAsset & "' WHERE ID = ' " & Me.List248.Column(21) & " ' "
CurrentDb.Execute strSQL, dbFailOnError

Get a datatype mimatch error.
Which field is an Autonumber field?
 

HelpMoses76

Member
Local time
Today, 10:45
Joined
Sep 17, 2020
Messages
45
This fixed it . Than you for your help Gasman and CJ London.

strSQL = "UPDATE [BR-DETAILS] SET CostCentre = '" & Me.txtCostCentre & "' , AssetName = '" & Me.txtAsset & "' WHERE ([BR-DETAILS].ID) = " & Me.List248.Column(21) & " "
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:45
Joined
Feb 19, 2013
Messages
12,895
column 21? List248? sounds like you have many more issues that just this one.

'no luck' doesn't tell use what you actually used. Copy and paste the unlucky code so we can see what you actually tried

"' WHERE ID = ' " & Me.List248.Column(21) & " ' "

this bit of code has spaces either side of the single quote
 

HelpMoses76

Member
Local time
Today, 10:45
Joined
Sep 17, 2020
Messages
45
column 21? List248? sounds like you have many more issues that just this one.

'no luck' doesn't tell use what you actually used. Copy and paste the unlucky code so we can see what you actually tried

"' WHERE ID = ' " & Me.List248.Column(21) & " ' "

this bit of code has spaces either side of the single quote

This is the string that fixed it .


strSQL = "UPDATE [BR-DETAILS] SET CostCentre = '" & Me.txtCostCentre & "' , AssetName = '" & Me.txtAsset & "' WHERE ([BR-DETAILS].ID) = " & Me.List248.Column(21) & " "
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:45
Joined
Sep 21, 2011
Messages
7,723
FWIW the last
Code:
& " "
is not required

If you Debug.Print your sql you will find your errors so much quicker. That goes for any criteria strings as well.
 

Users who are viewing this thread

Top Bottom