Syntax error in sql string to update a table from a form using co

[Guest]

Hi,
I have a form from where I need to update a table using code:
The Prefix field is a text field while the scenario field is number.
However I am having difficulty having to contruct the sql statement that
will update the record from the current form. I appreciate any help on this.
Thanks
CODE:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Set db = CurrentDb()

strSQL = "Update dbo_PWO_Status Set " & _
"dbo_PWO_Status.QuoteStatus = Me![QuoteStatus] " & _
"where dbo_PWO_Status.Prefix = """ & Me![Prefix] & """ & And " & _
"dbo_PWO_Status.Scenario= & Me![Scenario]"

Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

 

[Marshall Barton]

I have a form from where I need to update a table using code:
The Prefix field is a text field while the scenario field is number.
However I am having difficulty having to contruct the sql statement that
will update the record from the current form. I appreciate any help on this.
Thanks
CODE:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Set db = CurrentDb()

strSQL = "Update dbo_PWO_Status Set " & _
"dbo_PWO_Status.QuoteStatus = Me![QuoteStatus] " & _
"where dbo_PWO_Status.Prefix = """ & Me![Prefix] & """ & And " & _
"dbo_PWO_Status.Scenario= & Me![Scenario]"

Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

I don't use SQL server, but at least you have a quote in the
wrong place:

. . .
"dbo_PWO_Status.Scenario=" & Me![Scenario]

 

[Guest]

Thanks Marshall for your help. I just found out my mistake. Thanks anyway.
Regards

I have a form from where I need to update a table using code:
The Prefix field is a text field while the scenario field is number.
However I am having difficulty having to contruct the sql statement that
will update the record from the current form. I appreciate any help on this.
Thanks
CODE:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Set db = CurrentDb()

strSQL = "Update dbo_PWO_Status Set " & _
"dbo_PWO_Status.QuoteStatus = Me![QuoteStatus] " & _
"where dbo_PWO_Status.Prefix = """ & Me![Prefix] & """ & And " & _
"dbo_PWO_Status.Scenario= & Me![Scenario]"

Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

I don't use SQL server, but at least you have a quote in the
wrong place:

. . .
"dbo_PWO_Status.Scenario=" & Me![Scenario]