在VBA代码中运行更新查询 [英] Run Update query within VBA code
问题描述
我正在尝试使用Access编写一个小型清单程序,但是我所掌握的知识不多,到目前为止我所做的一切都是通过谷歌搜索来完成的.我已经设法使代码使用未绑定的表单存储数据,现在我想通过以未绑定的形式输入的数据为另一个表更新表的某些特定字段.有问题的2个表格是交易"和库存".库存有3个字段,零件编号,位置和数量.我希望从库存移出的位置扣除库存数量,然后将其添加到库存位置.
I am trying to make a small inventory program using Access but I don't have much knowledge, whatever I have done so far is by googling stuff. I Have managed to make code to store data using unbound forms, now I want to update some particular fields of a table by data entered in an unbound form for another table. The 2 tables in question are Transaction and Stock. Stock has 3 fields PartNo, Location and Qty. I want the QTY in stock to be deducted from the location where stock is moving out of, and to be added to where it is going to.
表Stock
Stock_PartNo Stock_Location Stock_Qty
2288 SAWRH001 85
2288 SAWRH002 54
3214 SAWRH003 544
4567 SAWRH001 32
5555 SAWRH002 128
5555 SAWRH005 874
5678 SAWRH002 321
6544 SAWRH004 465
6666 SAWRH003 45
6666 SAWRH004 87
7777 SAWRH003 365
7890 SAWRH002 352
8765 SAWRH005 57
8888 SAWRH004 54
9999 SAWRH005 21
这是我的未绑定表格代码:
Here is my code for an unbound form:
Private Sub Command39_Click()
Dim db As Database, rsCust As Recordset
Set db = CurrentDb
Set rsCust = db.OpenRecordset("Transaction", DB_OPEN_DYNASET)
rsCust.AddNew
rsCust("Trans_PartNo") = Me!Combo52
rsCust("Trans_Desc") = Me!Text19
rsCust("Trans_Disp") = Me!Text21
rsCust("Trans_Recv") = Me!Text23
rsCust("Trans_Qty") = Me!Text25
rsCust("Trans_Date") = Me!Text29
rsCust.Update
MsgBox "Material transfer information has been updated"
Call ClearControls
rsCust.Close
db.Close
End Sub
此数据将存储在一个名为Transaction的表中,该表只是从一个地方移到另一个地方的记录历史记录.我想要的是应该使用此表格来更新表库存,因此,如果部件号2288从SAWRH001转移到SAWRH005,则它应该自动更新表库存.据我了解,我需要嵌入一个SQL查询,但是我不知道如何在VBA中实现.
This data would be stored in a table called Transaction which is just a record history of what was moved from one place to another. What I want is that table Stock should be updated using this form, so if PartNo 2288 is transferred from SAWRH001 to SAWRH005 then it should update table Stock automatically. As far as I understand I need to embed a SQL query but I don't know how to do that in VBA.
推荐答案
带有参数:
Dim db As Database
Dim qdf As QueryDef
Set db = CurrentDb
sSQL = "UPDATE Stock SET Stock_Qty = Stock_Qty + [p1] " & _
" WHERE Stock_PartNo = [p2] AND Stock_Location = [p3]"
''Temporary query
Set qdf = db.CreateQueryDef("", sSQL)
''No need to worry about quotes etc
qdf.Parameters("p2") = Me!Combo52
''Subtract
qdf.Parameters("p1") = Me.Text25 * -1
qdf.Parameters("p3") = Me.From
qdf.Execute dbFailOnError
''Add
qdf.Parameters("p1") = Me.Text25
qdf.Parameters("p3") = Me.To
qdf.Execute dbFailOnError
这篇关于在VBA代码中运行更新查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!