Update query syntax error (maybe ?)

[HelpMoses76]

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]

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:

DoCmd.RunSQL method (Access)

Office VBA reference topic

docs.microsoft.com

Check out that link it will give an example.

 

[theDBguy]

Hi Moses. You can also try it this way.

Dim strSQL As String
strSQL = "UPDATE [BR - DETAILS] SET CostCentre = '" & Me.txtCostCentre & "'"
CurrentDb.Execute strSQL, dbFailOnError

Hope that helps...

 

[Pat Hartman]

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]

Hi Moses. You can also try it this way.

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]

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]

ID's are usually numeric in which case you don't use the single quotes.

 

[HelpMoses76]

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]

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]

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]

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]

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) & " "

 

[HelpMoses76]

Which field is an Autonumber field?

ID field ..which is in List248.column(21)

 

[CJ_London]

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]

Which field is an Autonumber field?

Thank you DBguy.

 

[HelpMoses76]

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]

FWIW the last

& " "

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.

 

[theDBguy]

Thank you DBguy.

Hi. You're welcome. Glad to hear you got it sorted out. Good luck with your project.