Converting SQL from Append query with call to function to SQL string.

Hi

The following came from the SQL generated in a query by the Microsoft Access GUI.  It is an  "Append" query.
It works fine in the Design GUI.
In particular,  I would like to focus on [Cur Inv]   and  the function call GetCurInventory([Stocknum],8401)

INSERT INTO [aaaTempVBATable  - Volatile] ( StockNum, [Cur Inv], [Account Number] )
SELECT [tbl Transactions].StockNum, GetCurInventory([Stocknum],8401) AS TheInv, 8401 AS Expr1
FROM [tbl Transactions]
WHERE ((([tbl Transactions].Source)=8401)) OR ((([tbl Transactions].Destination)=8401));

Select allOpen in new window

When I run this query, he table which receives the data also  receives a value for [Cur Inv].  Per my wishes.


However when I  try to run it in VBA, [Cur Inv] does remains at zero.
The function  call GetCurInventory([Stocknum],8401) does not seem to populate [Cur Inv] when run in VBA.

strInsert = "INSERT INTO [aaaTempVBATable  - Volatile] ( StockNum, [Cur Inv], [Account Number] ) "
strInsert = strInsert & "SELECT [tbl Transactions].StockNum, GetCurInventory([Stocknum],8401) AS TheInv, 8401 AS Expr1 "
strInsert = strInsert & " FROM [tbl Transactions]"
strInsert = strInsert & " WHERE ((([tbl Transactions].Source)=8401)) OR ((([tbl Transactions].Destination)=8401))"
db.Execute (strInsert)

Select allOpen in new window