Update syntax

[N J]

Hi,

I have code below that is used to add a new number to the txtTracking Field
based on what the user puts into the text box (txtTracking). What I would
like it to do is add whatever is already in the OnHand field, to what the
user has put into the txtTracking text box, to replace the number in OnHand

So ... OnHand = 20, user inputs 15 and the event is run, OnHand value is now
35
Any help would be greatly appreciated, thank you.


Private Sub cmdOK_Click()
Dim strsql As String

CurrentDb.Execu te "Update tblStock Set OnHand = """ & Me.txtTracking & """
Where SKU = " & Me.txtOrderNumb er & ";", dbfailonerror
Me.StatusListBo x.AddItem "STOCK # " & Me.txtOrderNumb er & " MARKED DOWN QTY
OF " & Me.txtTracking & "", 0
txtOrderNumber. SetFocus
End Sub

--

Kind Regards,
Customer Services,
Blue Lagoon Products

http://www.fastminimoto.co.uk

P.S. For us to answer your emails efficiently, please always include the
original message. Thank you.

[jkearns@gmail.com]

Re: Update syntax

You need to query for the first value, add it to the second, and then
run your update command. You can accomplish this in a couple of ways.

1) Have OnHand appear in a textbox in the Form somewhere (you can make
this invisible) and then it would be (assuming the name of the text box
is txtOnHand:

Private Sub cmdOK_Click()
Dim strsql As String
Dim iNewOnHand as Integer

iNewOnHand = Me.txtOnHand.Va lue + Me.txtTracking. Value

CurrentDb.Execu te "Update tblStock Set OnHand = """ & iNewOnHand & """
Where SKU = " & Me.txtOrderNumb er & ";", dbfailonerror
Me.StatusListBo x.AddItem "STOCK # " & Me.txtOrderNumb er & " MARKED DOWN
QTY
OF " & Me.txtTracking & "", 0
txtOrderNumber. SetFocus
End Sub

2) Or the other way to do it would be to query for it in the code,
then add it, then update it:

Private Sub cmdOK_Click()
Dim oRs as ADODB.Recordset
Dim oConn as ADODB.Connectio n
Set oRs = New ADODB.Recordset
Set oConn = CurrentProject. Connection
Dim strsql As String
Dim iOnHandTotal as Integer

strsql = "SELECT OnHand FROM tblStock Where SKU = " & Me.txtOrderNumb er
& ";"

oRs.Open strsql, oConn

iOnHandTotal = oRs("OnHand") + Me.txtTracking

CurrentDb.Execu te "Update tblStock Set OnHand = """ & iOnHandTotal &
"""
Where SKU = " & Me.txtOrderNumb er & ";", dbfailonerror
Me.StatusListBo x.AddItem "STOCK # " & Me.txtOrderNumb er & " MARKED DOWN
QTY
OF " & Me.txtTracking & "", 0
txtOrderNumber. SetFocus

oRs.Close
oConn.Close
Set oRs = Nothing
Set oConn = Nothing

End Sub

Hope this helps.

JK

[Pachydermitis]

Re: Update syntax

You are 99.9% there. Just change your sql statement a tiny bit.
CurrentDb.Execu te "Update tblStock Set OnHand = OnHand+""" &
Me.txtTracking & """ etc.
HTH
Pachydermitis