How can I put SQL result into variable in VBA

Question

I have example table like below

| ID | Qty |
| -- | ----|
| 1  | 5   |
| 2  | 7   |
| 3  | 8   |
| 4  | 9   |
| 5  | 12  |

How can I pass result of below query into VBA variable to use in next queries (update and insert)?

SELECT example_tab.Qty
FROM example_tab
WHERE ID = 4

In example that test_variable = 9

Answer 1

Is this what your looking for ?

sSQL = "SELECT example_tab.Qty FROM example_tab WHERE ID = 4"

Dim rs As DAO.Recordset     
Set rs = CurrentDB.OpenRecordset(sSQL)

Answer 2

To get a single value into a variable, DLookup can be used:

Dim test_variable As Variant

test_variable = DLookup("[Qty]", "example_tab", "[ID] = 4")