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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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)
 
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)
Click to expand...


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

. . .
"dbo_PWO_Status.Scenario=" & Me![Scenario]
 
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)
Click to expand...


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

. . .
"dbo_PWO_Status.Scenario=" & Me![Scenario]
Click to expand...
 
Back
Top