CODEWITHSUNDEEP
ms-accessvba
Armand Kruger
Armand Kruger

Reputation: 41

Update Table from Query using VBA

VBA/Macro newbie needing help with the above mentioned.

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.

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.

EDIT: I am open to alternative ideas with the same result.

This is a watered down version of the database.

Upvotes: 2

Views: 28369

Answers (2)

Armand Kruger
Armand Kruger

Reputation: 41

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

Upvotes: 2

jhTuppeny
jhTuppeny

Reputation: 970

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

Upvotes: 1

Related Questions