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

J

Jack

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)
 
M

Marshall Barton

Jack said:
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]
 
J

Jack

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

Marshall Barton said:
Jack said:
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]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top