Update Table from Query using VBA

Question

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.

Two questions: 1) Are you using a Macro or Data Macro? Data Macros are new in Access 2010 and not created in the Macros section, but from the Table. 2) Can you use a Macro in conjunction with an update query or is there some reason you can't use update queries? — Don Jewett
– Don Jewett, Commented Jul 6, 2015 at 19:03
This is probably going to show my ignorance but I haven't heard of Data Macro's before so I will definitely look into that. Up to now I've only used classic vba macro's. I also don't have the knowledge to pull this update query off, assuming that there will be sql involved. — Armand Kruger
– Armand Kruger, Commented Jul 7, 2015 at 6:02

Armand Kruger · Accepted Answer · 2015-07-08 06:57:06Z

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

jhTuppeny · Accepted Answer · 2015-07-07 08:06:31Z

1

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

answered Jul 7, 2015 at 8:06

jhTuppeny

9901 gold badge12 silver badges16 bronze badges

4 Comments

Armand Kruger Over a year ago

I'm getting Syntax errors: Is it okay if the sql string goes over 3 lines? dis CurrentDb stay the same or do I have to put in the Name of my DB?

jhTuppeny Over a year ago

You can split the code over three lines providing the SQL statement stays the same. CurrentDB stays as is

Armand Kruger Over a year ago

It refuses to go beyond the "sql =" bit

jhTuppeny Over a year ago

Can you post the code that you're trying? Clearly there's a problem with your SQL string - you're not including the "<" characters are you?

Collectives™ on Stack Overflow

Update Table from Query using VBA

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related