SQL Update

S

SimonT

Hi Guys,

I am converting a access application over to a SQL BE setup and am having to
convert a number of update queries, I previously had a query to update
product prices and now need to convert to SQL to replace the query, and have
written the code but cannot figure out why its no working, can anyone help
please?

This is my code:

Private Sub cmdUpdate_Click()
Dim rsProducts As DAO.Recordset
Dim rsPrices As DAO.Recordset

Dim strSQLPR As String
Dim strSQLCP As String

Set rsProducts = CurrentDb.OpenRecordset("products", dbOpenDynaset,
dbSeeChanges)
Set rsPrices = CurrentDb.OpenRecordset("tblPrices", dbOpenDynaset,
dbSeeChanges)

strSQLPR = "UPDATE products " & vbCrLf
strSQLPR = strSQLPR & " SET products.price =
[price]+[Forms].frmModify].[txtchange] " & vbCrLf
strSQLPR = strSQLPR & " WHERE
(((products.productID)=[Forms].[frmPriceModify].[productID])); "

DoCmd.RunSQL strSQLPR

strSQLCP = "UPDATE tblPrices " & vbCrLf
strSQLCP = strSQLCP & " SET tblPrices.price =
[price]+[Forms].frmModify].[txtchange] " & vbCrLf
strSQLCP = strSQLCP & " WHERE
(((tblPrices.productID)=[Forms].[frmPriceModify].[productID])); "

DoCmd.RunSQL strSQLCP


[Forms]![frmPriceModify]![price].Requery


rsProducts.Close
rsPrices.Close

Set rsProducts = Nothing
Set rsPrices = Nothing
End Sub

Many thanks
Si
 
T

Tom van Stiphout

On Wed, 31 Mar 2010 10:22:19 +0100, "SimonT"

That's because the server doesn't know what forms!myForm!myField is.
Rather write:
.... & " set myField = " & forms!myForm!myField & " where ..."

If that field is numeric the above is good. If it is text or date,
wrap the value in single-quotes:
.... & " set myField = '" & forms!myForm!myField & "' where ..."

-Tom.
Microsoft Access MVP
 
D

Daryl S

Simon -

I see a missing left square bracket before the first frmModify, and your
WHERE clause needs to have the 'forms' piece outside of the double-quotes so
that it gets resolved before being sent to SQL Server. You do not need the
vbCrLfs appended in your code. I would also add debug.print statements so
you can see the code before running it. Try this (untested):

Private Sub cmdUpdate_Click()
Dim rsProducts As DAO.Recordset
Dim rsPrices As DAO.Recordset

Dim strSQLPR As String
Dim strSQLCP As String

Set rsProducts = CurrentDb.OpenRecordset("products", dbOpenDynaset,
dbSeeChanges)
Set rsPrices = CurrentDb.OpenRecordset("tblPrices", dbOpenDynaset,
dbSeeChanges)

strSQLPR = "UPDATE products "
strSQLPR = strSQLPR & " SET products.price =
[price]+[Forms].[frmModify].[txtchange] "
strSQLPR = strSQLPR & " WHERE
(((products.productID)= '" & [Forms].[frmPriceModify].[productID] & "')); "

Debug.Print strSQLPR
DoCmd.RunSQL strSQLPR

strSQLCP = "UPDATE tblPrices "
strSQLCP = strSQLCP & " SET tblPrices.price =
[price]+[Forms].[frmModify].[txtchange] "
strSQLCP = strSQLCP & " WHERE
(((tblPrices.productID)= '" & [Forms].[frmPriceModify].[productID] & "')); "

Debug.Print strSQLCP
DoCmd.RunSQL strSQLCP


[Forms]![frmPriceModify]![price].Requery


rsProducts.Close
rsPrices.Close

Set rsProducts = Nothing
Set rsPrices = Nothing
End Sub
 
S

SimonT

Thanks for the suggestion guy, however, I keep keeping an error after the
first quote before "[price] error:

Compile error
Expected; end of statement

Regards
Si


Daryl S said:
Simon -

I see a missing left square bracket before the first frmModify, and your
WHERE clause needs to have the 'forms' piece outside of the double-quotes
so
that it gets resolved before being sent to SQL Server. You do not need
the
vbCrLfs appended in your code. I would also add debug.print statements so
you can see the code before running it. Try this (untested):

Private Sub cmdUpdate_Click()
Dim rsProducts As DAO.Recordset
Dim rsPrices As DAO.Recordset

Dim strSQLPR As String
Dim strSQLCP As String

Set rsProducts = CurrentDb.OpenRecordset("products", dbOpenDynaset,
dbSeeChanges)
Set rsPrices = CurrentDb.OpenRecordset("tblPrices", dbOpenDynaset,
dbSeeChanges)

strSQLPR = "UPDATE products "
strSQLPR = strSQLPR & " SET products.price =
[price]+[Forms].[frmModify].[txtchange] "
strSQLPR = strSQLPR & " WHERE
(((products.productID)= '" & [Forms].[frmPriceModify].[productID] & "'));
"

Debug.Print strSQLPR
DoCmd.RunSQL strSQLPR

strSQLCP = "UPDATE tblPrices "
strSQLCP = strSQLCP & " SET tblPrices.price =
[price]+[Forms].[frmModify].[txtchange] "
strSQLCP = strSQLCP & " WHERE
(((tblPrices.productID)= '" & [Forms].[frmPriceModify].[productID] & "'));
"

Debug.Print strSQLCP
DoCmd.RunSQL strSQLCP


[Forms]![frmPriceModify]![price].Requery


rsProducts.Close
rsPrices.Close

Set rsProducts = Nothing
Set rsPrices = Nothing
End Sub

--
Daryl S


SimonT said:
Hi Guys,

I am converting a access application over to a SQL BE setup and am having
to
convert a number of update queries, I previously had a query to update
product prices and now need to convert to SQL to replace the query, and
have
written the code but cannot figure out why its no working, can anyone
help
please?

This is my code:

Private Sub cmdUpdate_Click()
Dim rsProducts As DAO.Recordset
Dim rsPrices As DAO.Recordset

Dim strSQLPR As String
Dim strSQLCP As String

Set rsProducts = CurrentDb.OpenRecordset("products", dbOpenDynaset,
dbSeeChanges)
Set rsPrices = CurrentDb.OpenRecordset("tblPrices", dbOpenDynaset,
dbSeeChanges)

strSQLPR = "UPDATE products " & vbCrLf
strSQLPR = strSQLPR & " SET products.price =
[price]+[Forms].frmModify].[txtchange] " & vbCrLf
strSQLPR = strSQLPR & " WHERE
(((products.productID)=[Forms].[frmPriceModify].[productID])); "

DoCmd.RunSQL strSQLPR

strSQLCP = "UPDATE tblPrices " & vbCrLf
strSQLCP = strSQLCP & " SET tblPrices.price =
[price]+[Forms].frmModify].[txtchange] " & vbCrLf
strSQLCP = strSQLCP & " WHERE
(((tblPrices.productID)=[Forms].[frmPriceModify].[productID])); "

DoCmd.RunSQL strSQLCP


[Forms]![frmPriceModify]![price].Requery


rsProducts.Close
rsPrices.Close

Set rsProducts = Nothing
Set rsPrices = Nothing
End Sub

Many thanks
Si

.
Click to expand...
Click to expand...
 
S

SimonT

Hi Tom & Daryl,

Thanks for the suggestions, have modified the qry, but still nothing
working, current query reads:
Private Sub cmdUpdate_Click()
Dim rsProducts As DAO.Recordset
Dim rsPrices As DAO.Recordset


Dim strSQLPR As String
Dim strSQLCP As String

Set rsProducts = CurrentDb.OpenRecordset("products", dbOpenDynaset,
dbSeeChanges)
Set rsPrices = CurrentDb.OpenRecordset("tblPrices", dbOpenDynaset,
dbSeeChanges)

strSQLPR = "UPDATE products "
strSQLPR = strSQLPR & " SET products.price = [price]" +
"&[Forms]![frmModify]![txtchange]"
strSQLPR = strSQLPR & " WHERE (((products.productID)" =
"&[Forms].[frmPriceModify].[productID] )); "

Debug.Print strSQLPR
DoCmd.RunSQL strSQLPR

strSQLCP = "UPDATE tblPrices "
strSQLCP = strSQLCP & " SET tblPrices.price = [price]" +
"&[Forms]![frmModify]![txtchange] "
strSQLCP = strSQLCP & " WHERE (((tblPrices.productID)" = " &
[Forms].[frmPriceModify].[productID] )); "

Debug.Print strSQLCP
DoCmd.RunSQL strSQLCP


[Forms]![frmPriceModify]![price].Requery


rsProducts.Close
rsPrices.Close

Set rsProducts = Nothing
Set rsPrices = Nothing
End Sub

No error is given, just nothing happens, any further suggestions?

Thanks again
Si


Tom van Stiphout said:
On Wed, 31 Mar 2010 10:22:19 +0100, "SimonT"

That's because the server doesn't know what forms!myForm!myField is.
Rather write:
... & " set myField = " & forms!myForm!myField & " where ..."

If that field is numeric the above is good. If it is text or date,
wrap the value in single-quotes:
... & " set myField = '" & forms!myForm!myField & "' where ..."

-Tom.
Microsoft Access MVP

Hi Guys,

I am converting a access application over to a SQL BE setup and am having
to
convert a number of update queries, I previously had a query to update
product prices and now need to convert to SQL to replace the query, and
have
written the code but cannot figure out why its no working, can anyone help
please?

This is my code:

Private Sub cmdUpdate_Click()
Dim rsProducts As DAO.Recordset
Dim rsPrices As DAO.Recordset

Dim strSQLPR As String
Dim strSQLCP As String

Set rsProducts = CurrentDb.OpenRecordset("products", dbOpenDynaset,
dbSeeChanges)
Set rsPrices = CurrentDb.OpenRecordset("tblPrices", dbOpenDynaset,
dbSeeChanges)

strSQLPR = "UPDATE products " & vbCrLf
strSQLPR = strSQLPR & " SET products.price =
[price]+[Forms].frmModify].[txtchange] " & vbCrLf
strSQLPR = strSQLPR & " WHERE
(((products.productID)=[Forms].[frmPriceModify].[productID])); "

DoCmd.RunSQL strSQLPR

strSQLCP = "UPDATE tblPrices " & vbCrLf
strSQLCP = strSQLCP & " SET tblPrices.price =
[price]+[Forms].frmModify].[txtchange] " & vbCrLf
strSQLCP = strSQLCP & " WHERE
(((tblPrices.productID)=[Forms].[frmPriceModify].[productID])); "

DoCmd.RunSQL strSQLCP


[Forms]![frmPriceModify]![price].Requery


rsProducts.Close
rsPrices.Close

Set rsProducts = Nothing
Set rsPrices = Nothing
End Sub

Many thanks
Si
Click to expand...
Click to expand...
 
S

SimonT

To note, also changed the periods to apostrophe's

SimonT said:
Hi Tom & Daryl,

Thanks for the suggestions, have modified the qry, but still nothing
working, current query reads:
Private Sub cmdUpdate_Click()
Dim rsProducts As DAO.Recordset
Dim rsPrices As DAO.Recordset


Dim strSQLPR As String
Dim strSQLCP As String

Set rsProducts = CurrentDb.OpenRecordset("products", dbOpenDynaset,
dbSeeChanges)
Set rsPrices = CurrentDb.OpenRecordset("tblPrices", dbOpenDynaset,
dbSeeChanges)

strSQLPR = "UPDATE products "
strSQLPR = strSQLPR & " SET products.price = [price]" +
"&[Forms]![frmModify]![txtchange]"
strSQLPR = strSQLPR & " WHERE (((products.productID)" =
"&[Forms].[frmPriceModify].[productID] )); "

Debug.Print strSQLPR
DoCmd.RunSQL strSQLPR

strSQLCP = "UPDATE tblPrices "
strSQLCP = strSQLCP & " SET tblPrices.price = [price]" +
"&[Forms]![frmModify]![txtchange] "
strSQLCP = strSQLCP & " WHERE (((tblPrices.productID)" = " &
[Forms].[frmPriceModify].[productID] )); "

Debug.Print strSQLCP
DoCmd.RunSQL strSQLCP


[Forms]![frmPriceModify]![price].Requery


rsProducts.Close
rsPrices.Close

Set rsProducts = Nothing
Set rsPrices = Nothing
End Sub

No error is given, just nothing happens, any further suggestions?

Thanks again
Si


Tom van Stiphout said:
On Wed, 31 Mar 2010 10:22:19 +0100, "SimonT"

That's because the server doesn't know what forms!myForm!myField is.
Rather write:
... & " set myField = " & forms!myForm!myField & " where ..."

If that field is numeric the above is good. If it is text or date,
wrap the value in single-quotes:
... & " set myField = '" & forms!myForm!myField & "' where ..."

-Tom.
Microsoft Access MVP

Hi Guys,

I am converting a access application over to a SQL BE setup and am having
to
convert a number of update queries, I previously had a query to update
product prices and now need to convert to SQL to replace the query, and
have
written the code but cannot figure out why its no working, can anyone
help
please?

This is my code:

Private Sub cmdUpdate_Click()
Dim rsProducts As DAO.Recordset
Dim rsPrices As DAO.Recordset

Dim strSQLPR As String
Dim strSQLCP As String

Set rsProducts = CurrentDb.OpenRecordset("products", dbOpenDynaset,
dbSeeChanges)
Set rsPrices = CurrentDb.OpenRecordset("tblPrices", dbOpenDynaset,
dbSeeChanges)

strSQLPR = "UPDATE products " & vbCrLf
strSQLPR = strSQLPR & " SET products.price =
[price]+[Forms].frmModify].[txtchange] " & vbCrLf
strSQLPR = strSQLPR & " WHERE
(((products.productID)=[Forms].[frmPriceModify].[productID])); "

DoCmd.RunSQL strSQLPR

strSQLCP = "UPDATE tblPrices " & vbCrLf
strSQLCP = strSQLCP & " SET tblPrices.price =
[price]+[Forms].frmModify].[txtchange] " & vbCrLf
strSQLCP = strSQLCP & " WHERE
(((tblPrices.productID)=[Forms].[frmPriceModify].[productID])); "

DoCmd.RunSQL strSQLCP


[Forms]![frmPriceModify]![price].Requery


rsProducts.Close
rsPrices.Close

Set rsProducts = Nothing
Set rsPrices = Nothing
End Sub

Many thanks
Si
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

strSQLPR = "UPDATE products "
strSQLPR = strSQLPR & " SET products.price = [price] + " & _
[Forms]![frmModify]![txtchange]"
strSQLPR = strSQLPR & " WHERE products.productID= " & _
[Forms].[frmPriceModify].[productID]

Of course if PRODUCTID is a text field then you need to add in quote marks
strSQLPR = strSQLPR & " WHERE products.productID = """ & _
[Forms].[frmPriceModify].[productID] & """"


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Hi Tom & Daryl,

Thanks for the suggestions, have modified the qry, but still nothing
working, current query reads:
Private Sub cmdUpdate_Click()
Dim rsProducts As DAO.Recordset
Dim rsPrices As DAO.Recordset


Dim strSQLPR As String
Dim strSQLCP As String

Set rsProducts = CurrentDb.OpenRecordset("products", dbOpenDynaset,
dbSeeChanges)
Set rsPrices = CurrentDb.OpenRecordset("tblPrices", dbOpenDynaset,
dbSeeChanges)

strSQLPR = "UPDATE products "
strSQLPR = strSQLPR & " SET products.price = [price]" +
"&[Forms]![frmModify]![txtchange]"
strSQLPR = strSQLPR & " WHERE (((products.productID)" =
"&[Forms].[frmPriceModify].[productID] )); "

Debug.Print strSQLPR
DoCmd.RunSQL strSQLPR

strSQLCP = "UPDATE tblPrices "
strSQLCP = strSQLCP & " SET tblPrices.price = [price]" +
"&[Forms]![frmModify]![txtchange] "
strSQLCP = strSQLCP & " WHERE (((tblPrices.productID)" = " &
[Forms].[frmPriceModify].[productID] )); "

Debug.Print strSQLCP
DoCmd.RunSQL strSQLCP


[Forms]![frmPriceModify]![price].Requery


rsProducts.Close
rsPrices.Close

Set rsProducts = Nothing
Set rsPrices = Nothing
End Sub

No error is given, just nothing happens, any further suggestions?

Thanks again
Si


Tom van Stiphout said:
On Wed, 31 Mar 2010 10:22:19 +0100, "SimonT"

That's because the server doesn't know what forms!myForm!myField is.
Rather write:
... & " set myField = " & forms!myForm!myField & " where ..."

If that field is numeric the above is good. If it is text or date,
wrap the value in single-quotes:
... & " set myField = '" & forms!myForm!myField & "' where ..."

-Tom.
Microsoft Access MVP

Hi Guys,

I am converting a access application over to a SQL BE setup and am
having to
convert a number of update queries, I previously had a query to update
product prices and now need to convert to SQL to replace the query,
and have
written the code but cannot figure out why its no working, can anyone
help
please?

This is my code:

Private Sub cmdUpdate_Click()
Dim rsProducts As DAO.Recordset
Dim rsPrices As DAO.Recordset

Dim strSQLPR As String
Dim strSQLCP As String

Set rsProducts = CurrentDb.OpenRecordset("products", dbOpenDynaset,
dbSeeChanges)
Set rsPrices = CurrentDb.OpenRecordset("tblPrices", dbOpenDynaset,
dbSeeChanges)

strSQLPR = "UPDATE products " & vbCrLf
strSQLPR = strSQLPR & " SET products.price =
[price]+[Forms].frmModify].[txtchange] " & vbCrLf
strSQLPR = strSQLPR & " WHERE
(((products.productID)=[Forms].[frmPriceModify].[productID])); "

DoCmd.RunSQL strSQLPR

strSQLCP = "UPDATE tblPrices " & vbCrLf
strSQLCP = strSQLCP & " SET tblPrices.price =
[price]+[Forms].frmModify].[txtchange] " & vbCrLf
strSQLCP = strSQLCP & " WHERE
(((tblPrices.productID)=[Forms].[frmPriceModify].[productID])); "

DoCmd.RunSQL strSQLCP


[Forms]![frmPriceModify]![price].Requery


rsProducts.Close
rsPrices.Close

Set rsProducts = Nothing
Set rsPrices = Nothing
End Sub

Many thanks
Si
Click to expand...
Click to expand...
Click to expand...
 
S

SimonT

Hi John,

Still getting end of statement error, this time after SET txtchange?

Can I confirm also that the underscore is only for readability if it breaks
over a line?

Regards
Si


John Spencer said:
strSQLPR = "UPDATE products "
strSQLPR = strSQLPR & " SET products.price = [price] + " & _
[Forms]![frmModify]![txtchange]"
strSQLPR = strSQLPR & " WHERE products.productID= " & _
[Forms].[frmPriceModify].[productID]

Of course if PRODUCTID is a text field then you need to add in quote marks
strSQLPR = strSQLPR & " WHERE products.productID = """ & _
[Forms].[frmPriceModify].[productID] & """"


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Hi Tom & Daryl,

Thanks for the suggestions, have modified the qry, but still nothing
working, current query reads:
Private Sub cmdUpdate_Click()
Dim rsProducts As DAO.Recordset
Dim rsPrices As DAO.Recordset


Dim strSQLPR As String
Dim strSQLCP As String

Set rsProducts = CurrentDb.OpenRecordset("products", dbOpenDynaset,
dbSeeChanges)
Set rsPrices = CurrentDb.OpenRecordset("tblPrices", dbOpenDynaset,
dbSeeChanges)

strSQLPR = "UPDATE products "
strSQLPR = strSQLPR & " SET products.price = [price]" +
"&[Forms]![frmModify]![txtchange]"
strSQLPR = strSQLPR & " WHERE (((products.productID)" =
"&[Forms].[frmPriceModify].[productID] )); "

Debug.Print strSQLPR
DoCmd.RunSQL strSQLPR

strSQLCP = "UPDATE tblPrices "
strSQLCP = strSQLCP & " SET tblPrices.price = [price]" +
"&[Forms]![frmModify]![txtchange] "
strSQLCP = strSQLCP & " WHERE (((tblPrices.productID)" = " &
[Forms].[frmPriceModify].[productID] )); "

Debug.Print strSQLCP
DoCmd.RunSQL strSQLCP


[Forms]![frmPriceModify]![price].Requery


rsProducts.Close
rsPrices.Close

Set rsProducts = Nothing
Set rsPrices = Nothing
End Sub

No error is given, just nothing happens, any further suggestions?

Thanks again
Si


Tom van Stiphout said:
On Wed, 31 Mar 2010 10:22:19 +0100, "SimonT"

That's because the server doesn't know what forms!myForm!myField is.
Rather write:
... & " set myField = " & forms!myForm!myField & " where ..."

If that field is numeric the above is good. If it is text or date,
wrap the value in single-quotes:
... & " set myField = '" & forms!myForm!myField & "' where ..."

-Tom.
Microsoft Access MVP


Hi Guys,

I am converting a access application over to a SQL BE setup and am
having to
convert a number of update queries, I previously had a query to update
product prices and now need to convert to SQL to replace the query, and
have
written the code but cannot figure out why its no working, can anyone
help
please?

This is my code:

Private Sub cmdUpdate_Click()
Dim rsProducts As DAO.Recordset
Dim rsPrices As DAO.Recordset

Dim strSQLPR As String
Dim strSQLCP As String

Set rsProducts = CurrentDb.OpenRecordset("products", dbOpenDynaset,
dbSeeChanges)
Set rsPrices = CurrentDb.OpenRecordset("tblPrices", dbOpenDynaset,
dbSeeChanges)

strSQLPR = "UPDATE products " & vbCrLf
strSQLPR = strSQLPR & " SET products.price =
[price]+[Forms].frmModify].[txtchange] " & vbCrLf
strSQLPR = strSQLPR & " WHERE
(((products.productID)=[Forms].[frmPriceModify].[productID])); "

DoCmd.RunSQL strSQLPR

strSQLCP = "UPDATE tblPrices " & vbCrLf
strSQLCP = strSQLCP & " SET tblPrices.price =
[price]+[Forms].frmModify].[txtchange] " & vbCrLf
strSQLCP = strSQLCP & " WHERE
(((tblPrices.productID)=[Forms].[frmPriceModify].[productID])); "

DoCmd.RunSQL strSQLCP


[Forms]![frmPriceModify]![price].Requery


rsProducts.Close
rsPrices.Close

Set rsProducts = Nothing
Set rsPrices = Nothing
End Sub

Many thanks
Si
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Seems that I got carried away with the quote marks. You need to delete the
quote mark after [Forms]![frmModify]![txtchange].

The addition of the continuation characters (space + underscore) was to
prevent errors caused by the tendency for the newsgroup to wrap a line and
introduce errors.

strSQLPR = "UPDATE products "
strSQLPR = strSQLPR & " SET products.price = [price] + " & _
[Forms]![frmModify]![txtchange]
strSQLPR = strSQLPR & " WHERE products.productID= " & _
[Forms].[frmPriceModify].[productID]

A good trouble-shooting technique is a to add (temporarily) a line to print
out the SQL string that is generated. This will often let you spot the error
quickly. If not, you can copy the SQL and paste it into a blank query (SQL
view) and attempt to run it. This often shows where the error is and gives a
better description of the error.

Debug.Print strSQLPR 'Print the SQL to the VBA immediate window.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Hi John,

Still getting end of statement error, this time after SET txtchange?

Can I confirm also that the underscore is only for readability if it
breaks over a line?

Regards
Si


John Spencer said:
strSQLPR = "UPDATE products "
strSQLPR = strSQLPR & " SET products.price = [price] + " & _
[Forms]![frmModify]![txtchange]"
strSQLPR = strSQLPR & " WHERE products.productID= " & _
[Forms].[frmPriceModify].[productID]

Of course if PRODUCTID is a text field then you need to add in quote
marks
strSQLPR = strSQLPR & " WHERE products.productID = """ & _
[Forms].[frmPriceModify].[productID] & """"


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Click to expand...
Click to expand...
 
S

SimonT

Hi John,

Sorry for the delay in responding to your kind help. To advise, this is
still not working, every thing I try just fails.

So I am going to try a different approach, by saving the change locally then
run another script to update the SQL DB from that, see if that works, I
cannot even get the data to refresh with this line :

[Forms]![frmPriceModify]![price].Requery

can I ask however, you added the line 'Debug.Print strSQLPR 'Print the SQL
to the VBA immediate window.' how is this suppose to work, as I get nothing
in the immediate window when I click my update button?

Regards
Si

John Spencer said:
Seems that I got carried away with the quote marks. You need to delete
the quote mark after [Forms]![frmModify]![txtchange].

The addition of the continuation characters (space + underscore) was to
prevent errors caused by the tendency for the newsgroup to wrap a line and
introduce errors.

strSQLPR = "UPDATE products "
strSQLPR = strSQLPR & " SET products.price = [price] + " & _
[Forms]![frmModify]![txtchange]
strSQLPR = strSQLPR & " WHERE products.productID= " & _
[Forms].[frmPriceModify].[productID]

A good trouble-shooting technique is a to add (temporarily) a line to
print out the SQL string that is generated. This will often let you spot
the error quickly. If not, you can copy the SQL and paste it into a blank
query (SQL view) and attempt to run it. This often shows where the error
is and gives a better description of the error.

Debug.Print strSQLPR 'Print the SQL to the VBA immediate window.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Hi John,

Still getting end of statement error, this time after SET txtchange?

Can I confirm also that the underscore is only for readability if it
breaks over a line?

Regards
Si


John Spencer said:
strSQLPR = "UPDATE products "
strSQLPR = strSQLPR & " SET products.price = [price] + " & _
[Forms]![frmModify]![txtchange]"
strSQLPR = strSQLPR & " WHERE products.productID= " & _
[Forms].[frmPriceModify].[productID]

Of course if PRODUCTID is a text field then you need to add in quote
marks
strSQLPR = strSQLPR & " WHERE products.productID = """ & _
[Forms].[frmPriceModify].[productID] & """"


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Click to expand...
Click to expand...
Click to expand...
 
B

Bob Barrows

SimonT said:
Hi John,

Sorry for the delay in responding to your kind help. To advise, this
is still not working, every thing I try just fails.

So I am going to try a different approach, by saving the change
locally then run another script to update the SQL DB from that, see
if that works, I cannot even get the data to refresh with this line :

[Forms]![frmPriceModify]![price].Requery

can I ask however, you added the line 'Debug.Print strSQLPR 'Print
the SQL to the VBA immediate window.' how is this suppose to work, as
I get nothing in the immediate window when I click my update button?
Click to expand...
If what you typed above is how you entered it, then you need to remove
the apostrophe at the beginning of the line to uncomment it.
Commented:
'Debug.Print strSQLPR 'Print the SQL to the VBA immediate window.'

Uncommented:
Debug.Print strSQLPR 'Print the SQL to the VBA immediate window.'
 
S

SimonT

Hi Bob,

Thanks for that, I only commented it for the post...can you advise how this
should work?

Regards
Si

Bob Barrows said:
SimonT said:
Hi John,

Sorry for the delay in responding to your kind help. To advise, this
is still not working, every thing I try just fails.

So I am going to try a different approach, by saving the change
locally then run another script to update the SQL DB from that, see
if that works, I cannot even get the data to refresh with this line :

[Forms]![frmPriceModify]![price].Requery

can I ask however, you added the line 'Debug.Print strSQLPR 'Print
the SQL to the VBA immediate window.' how is this suppose to work, as
I get nothing in the immediate window when I click my update button?
Click to expand...
If what you typed above is how you entered it, then you need to remove
the apostrophe at the beginning of the line to uncomment it.
Commented:
'Debug.Print strSQLPR 'Print the SQL to the VBA immediate window.'

Uncommented:
Debug.Print strSQLPR 'Print the SQL to the VBA immediate window.'
Click to expand...
 
B

Bob Barrows

If the immediate window is open before clicking the update button, then
the output from the debug statement should appear. If it isn't, all I
can say is that it has always "just worked" for me. I have never had to
do anything special to make it work.

You might try putting a breakpoint on the preceding line so the module
opens and lets you step through the code.
Hi Bob,

Thanks for that, I only commented it for the post...can you advise
how this should work?

Regards
Si

Bob Barrows said:
SimonT said:
Hi John,

Sorry for the delay in responding to your kind help. To advise, this
is still not working, every thing I try just fails.

So I am going to try a different approach, by saving the change
locally then run another script to update the SQL DB from that, see
if that works, I cannot even get the data to refresh with this line
:

[Forms]![frmPriceModify]![price].Requery

can I ask however, you added the line 'Debug.Print strSQLPR 'Print
the SQL to the VBA immediate window.' how is this suppose to work,
as I get nothing in the immediate window when I click my update
button?
Click to expand...
If what you typed above is how you entered it, then you need to
remove the apostrophe at the beginning of the line to uncomment it.
Commented:
'Debug.Print strSQLPR 'Print the SQL to the VBA immediate window.'

Uncommented:
Debug.Print strSQLPR 'Print the SQL to the VBA immediate window.'
Click to expand...
Click to expand...
 
J

John Spencer

It should print the string in the Immediate window. If it does not then
either the code is not executing at all OR the string is a zero-length string.

If the code is executing then I would carefully check the name(s) of the
variables. I suspect that you don't have OPTION EXPLICIT at the top of the
module. If you don't then it is very easy to have misspelled variable names
and get no error.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Hi Bob,

Thanks for that, I only commented it for the post...can you advise how
this should work?

Regards
Si

Bob Barrows said:
SimonT said:
Hi John,

Sorry for the delay in responding to your kind help. To advise, this
is still not working, every thing I try just fails.

So I am going to try a different approach, by saving the change
locally then run another script to update the SQL DB from that, see
if that works, I cannot even get the data to refresh with this line :

[Forms]![frmPriceModify]![price].Requery

can I ask however, you added the line 'Debug.Print strSQLPR 'Print
the SQL to the VBA immediate window.' how is this suppose to work, as
I get nothing in the immediate window when I click my update button?
Click to expand...
If what you typed above is how you entered it, then you need to remove
the apostrophe at the beginning of the line to uncomment it.
Commented:
'Debug.Print strSQLPR 'Print the SQL to the VBA immediate window.'

Uncommented:
Debug.Print strSQLPR 'Print the SQL to the VBA immediate window.'
Click to expand...
Click to expand...
 
B

Bob Barrows

Ahh, good point. To verify, change the statement to:
Debug.Print "strSQLPR contains '" + strSQLPR + "'"


John said:
It should print the string in the Immediate window. If it does not
then either the code is not executing at all OR the string is a
zero-length string.

If the code is executing then I would carefully check the name(s) of
the variables. I suspect that you don't have OPTION EXPLICIT at the
top of the module. If you don't then it is very easy to have
misspelled variable names and get no error.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Hi Bob,

Thanks for that, I only commented it for the post...can you advise
how this should work?

Regards
Si

Bob Barrows said:
SimonT wrote:
Hi John,

Sorry for the delay in responding to your kind help. To advise,
this is still not working, every thing I try just fails.

So I am going to try a different approach, by saving the change
locally then run another script to update the SQL DB from that, see
if that works, I cannot even get the data to refresh with this
line :

[Forms]![frmPriceModify]![price].Requery

can I ask however, you added the line 'Debug.Print strSQLPR
'Print the SQL to the VBA immediate window.' how is this suppose
to work, as I get nothing in the immediate window when I click my
update button?

If what you typed above is how you entered it, then you need to
remove the apostrophe at the beginning of the line to uncomment it.
Commented:
'Debug.Print strSQLPR 'Print the SQL to the VBA immediate window.'

Uncommented:
Debug.Print strSQLPR 'Print the SQL to the VBA immediate window.'
Click to expand...
Click to expand...
Click to expand...
 

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

Similar Threads

Append Query with VBA and SQL 1
Access 2003 and SQL Server 2005 and DAO recordset 1
Error 3144 - Syntax error in UPDATE Statement 6
Syntax error in Update statement 3
Help...'Variable Not Defined' 3
Update Table Field Value with SQL Query 2
Timer event does not fire 1
Parameterized Pass-thru query to SQL Server 3

Top