471,071 Members | 4,765 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,071 software developers and data experts.

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.Execute "Update tblStock Set OnHand = """ & Me.txtTracking & """
Where SKU = " & Me.txtOrderNumber & ";", dbfailonerror
Me.StatusListBox.AddItem "STOCK # " & Me.txtOrderNumber & " 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.
Nov 16 '05 #1
2 5036
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.Value + Me.txtTracking.Value

CurrentDb.Execute "Update tblStock Set OnHand = """ & iNewOnHand & """
Where SKU = " & Me.txtOrderNumber & ";", dbfailonerror
Me.StatusListBox.AddItem "STOCK # " & Me.txtOrderNumber & " 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.Connection
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.txtOrderNumber
& ";"

oRs.Open strsql, oConn

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

CurrentDb.Execute "Update tblStock Set OnHand = """ & iOnHandTotal &
"""
Where SKU = " & Me.txtOrderNumber & ";", dbfailonerror
Me.StatusListBox.AddItem "STOCK # " & Me.txtOrderNumber & " 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

Nov 16 '05 #2
You are 99.9% there. Just change your sql statement a tiny bit.
CurrentDb.Execute "Update tblStock Set OnHand = OnHand+""" &
Me.txtTracking & """ etc.
HTH
Pachydermitis

Nov 16 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by Dave | last post: by
14 posts views Thread by bolidev | last post: by
17 posts views Thread by kalamos | last post: by
6 posts views Thread by HeadScratcher | last post: by
9 posts views Thread by zMisc | last post: by
2 posts views Thread by WU | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.