使用 VBA 从查询更新表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31249702/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update Table from Query using VBA
提问by Armand Kruger
VBA/Macro newbie needing help with the above mentioned.
VBA/宏新手需要上述方面的帮助。
Table: Inventory
Product Value a Value b
1. Product 1 0 0
2. Product 2 0 0
3. Product 3 0 0
Query: Qry
Product Value VAL
1. Product 1 100 a
2. Product 2 200 a
3. Product 3 300 b
Result of Marco
马可的结果
Table: Inventory
Product Value a Value b
1. Product 1 100 0
2. Product 2 200 0
3. Product 3 0 300
Without changing the schema or thinking of alternative methods: I specifically need a macro (not an update query) to update corresponding products in table.field "Inventory.Value" with a value from query "qry" depending on whether it is in column a or column b as stated in the table.
不改变架构或考虑替代方法：我特别需要一个宏（不是更新查询）来更新 table.field“Inventory.Value”中的相应产品，并使用查询“qry”中的值取决于它是否在 a 列中或如表中所述的 b 列。
I know that there will be an iif statement involved and a insert into but for the life of me I just cannot make it work.
我知道会涉及到一个 iif 语句和一个插入，但对于我的生活，我无法让它发挥作用。
EDIT: I am open to alternative ideas with the same result.
编辑：我对具有相同结果的替代想法持开放态度。
This is a watered down version of the database.
这是数据库的淡化版本。
回答by jhTuppeny
You could try something like this;
你可以尝试这样的事情；
Dim rst As DAO.Recordset
Dim sql As String
sql = <The sql string for your query>
Set rst = CurrentDb.OpenRecordset(sql, dbOpenDynaset)
With rst
Do Until .EOF
If !VAL = "b" Then
sql = "UPDATE Inventory SET [Value b] = " & !Value & " WHERE Product = '" & !Product "' ;"
CurrentDB.Exectute sql
End If
.MoveNext
Loop
End With
回答by Armand Kruger
Thank you for the skeleton guys, it helped tremendously
谢谢大佬们，帮了大忙
This is the Macro (the fields and table names are different to my original post)
这是宏（字段和表名与我原来的帖子不同）
Option Compare Database
Option Explicit
Sub Opdateer()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sql As String
Set dbs = CurrentDb
sql = "SELECT Orders.Warehouse, OrderDetail.Product, OrderDetail.OrderDetailStatus, Sum(OrderDetail.Qty_mt) AS SumOfQty_mt FROM Orders INNER JOIN OrderDetail ON Orders.ID = OrderDetail.OrderNumber GROUP BY Orders.Warehouse, OrderDetail.Product, OrderDetail.OrderDetailStatus;"
Set rst = dbs.OpenRecordset(sql, dbOpenDynaset)
With rst
Do Until rst.EOF
If !OrderDetailStatus = "Allokeer" Then
sql = "UPDATE [InventoryCT] SET [StockAllocated] = " & !SumOfQty_mt & " WHERE [ProductCT] = " & !Product & " ;"
dbs.Execute (sql)
Else
sql = "UPDATE [InventoryCT] SET [StockOpgelaai] = " & !SumOfQty_mt & " WHERE [ProductCT] = " & !Product & " ;"
dbs.Execute (sql)
End If
.MoveNext
Loop
End With
rst.Close
dbs.Close
End Sub
