Using SQL in VBA instead of query

[dhstein]

I needed a function to provide a value, so I created a query that would
return a single row and opened the query as a recordset to get the value.
What syntax would I use to put the SQL directly in the code and avoid the
step of creating the query? Thanks for any help you can provide.


SQL here:

SELECT tblProductSKU.ProductShortSKU,
Sum([tblWarehouseLocation].[WarehouseLocationMultiplier]*[tblWarehouseLocation].[WarehouseLocationQty]) AS TotalWeight
FROM tblProductSKU INNER JOIN tblWarehouseLocation ON
tblProductSKU.ProductSKU = tblWarehouseLocation.WarehouseLocationSKU
GROUP BY tblProductSKU.ProductShortSKU
HAVING (((tblProductSKU.ProductShortSKU)=GetVariable("ShortSKU")));


Function here:

Private Function GetTotalWeight(SKU As String)

Dim rsWeight As DAO.Recordset
MsgBox SKU

ShortSKU = SKU

Set rsWeight = CurrentDb.OpenRecordset("qryTotalWeight", dbOpenDynaset)
rsWeight.MoveFirst

GetTotalWeight = rsWeight!TotalWeight
rsWeight.Close
End Function

 

[Clifford Bass]

Hi,

Just copy and paste the text, as a string, into your code. You can use
the & and the continuation character (the underscore [_]) to make it easier
to see. Any quote symbols ("), within the SQL will have to be doubled up.
And you do not need the semicolon at the end.

Set rsWeight = CurrentDb.OpenRecordset( _
"SELECT tblProductSKU.ProductShortSKU, Sum(" & _
"[tblWarehouseLocation].[WarehouseLocationMultiplier] * " & _
"[tblWarehouseLocation].[WarehouseLocationQty]) AS " & _
"TotalWeight " & _
"FROM tblProductSKU INNER JOIN tblWarehouseLocation ON " & _
"tblProductSKU.ProductSKU = " & _
"tblWarehouseLocation.WarehouseLocationSKU " & _
"GROUP BY tblProductSKU.ProductShortSKU " & _
"HAVING (((tblProductSKU.ProductShortSKU)=GetVariable(" & _
"""ShortSKU"")))", _
dbOpenDynaset)

Note the addition of the space before the quote at the end of most
lines to make sure that there is appropriate separation of tokens within the
SQL. If I got it all correct, the above should work.

Hope that helps,

Clifford Bass