Update Query syntax issues in VBA

[Doozer1979]

IHello all,

I have the following bit of code that i want to insert the value from txtGroupID into a filed called GroupID in a table called AlertsID. I only want it to update the table when another the AlertID field in the table matches the value in a txt box called txtAlertID. The save button is located on the same form level as the text boxes.

the VB editor returns the error " expected: end of statement"


Private Sub cmdSaveAlert_Click()
DoCmd.Save , "frmAlertsAM"
DoCmd.RunSQL "UPDATE AlertsAM SET GroupID = me.txtGroupID.value" _
& WHERE(AlertsAM.AlertAMID) = Me.txtAlertAMID;"

End Sub

Your help is greatly appreciated

 

[chergh]

Your missing quote marks before the where statement, remember to put a space in there as well.

 

[KenHigg]

DoCmd.RunSQL "UPDATE AlertsAM SET GroupID = '" & me.txtGroupID.value & "' WHERE(AlertsAM.AlertAMID) = '" & Me.txtAlertAMID & "';"

???

 

[Alc]

Perhaps

DoCmd.Save , "frmAlertsAM"
DoCmd.RunSQL "UPDATE AlertsAM SET GroupID = " & me.txtGroupID.value  _
& " WHERE(AlertsAM.AlertAMID) = " & Me.txtAlertAMID& ";"

 

[tehNellie]

strSQL = "UPDATE AlertsAM SET GroupID = " &  me.txtGroupID.value & _
" WHERE(AlertsAM.AlertAMID) =" &  Me.txtAlertAMID & ";"

your controls or variables need to be outside the quotes, because they both appear to be numeric values I've not wrapped them in quotes, personally I use:

WHERE tblSomeTable.name = " & chr(34) & strSomeName & Chr(34)

When it comes to wrapping quotes around strings just to get away from having to decipher "'" when I come back to the code at a later date.

 

[Doozer1979]

Thanks for all of your prompt responses.


I get a runtime error of 3464 "data-type mismatch" when i try to run your code ken?

tehnellie: I don't understand the strSQL bit at the beginning. I know it means String Sql but i don't know how to utilise that in VBA


Again thank you. I'm very much a novice at VBA

 

[Alc]

What does your string now say?
The error message seems to suggest you're trying to use a numeric value as a string, or vice-versa.

 

[KenHigg]

DoCmd.RunSQL "UPDATE AlertsAM SET GroupID = " & me.txtGroupID.value & " WHERE(AlertsAM.AlertAMID) = " & Me.txtAlertAMID & ";"

This should work if txtGroupID and AlertAMID are numeric data types... Just loose the single quotes for numerics....

 

[Doozer1979]

Thanks Al ot Ken, and everybody else for your help

 

[KenHigg]

Tip: If you step through this via a breakpoint and examine the sql string you should be able to spot these types of errors

Another cheat is to put it (the sql string) in a msgbox right before the line it errors in and you can look at there

 

[tehNellie]

tehnellie: I don't understand the strSQL bit at the beginning. I know it means String Sql but i don't know how to utilise that in VBA

oops, I stick my sql into strings and use the string in the execute command. i.e

Docmd.runsql strSQL