Using SQL in code instead of opening 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

 

[Brendan Reynolds]

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

You can assign the SQL to a string variable and pass that to the
OpenRecordset method ...

Dim strSql As String
Dim rsWeight As DAO.Recordset
Dim db As DAO.Recordset

strSql = "SELECT ... etc"
Set db = CurrentDb
Set rsWeight = db.OpenRecordset(strSql, dbOpenDynaset)

There isn't anything intrinsically wrong with using the saved query as you
are doing now, though.

 

[John Spencer]

You could also use the DLookup function instead of declaring a recordset
and using it.

GetTotalWeight = DLookup("TotalWeight","qryTotalWeight")

And your query would be more efficient if you used a where clause
instead of a having clause. So it could be simplified as:

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


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

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

You can assign the SQL to a string variable and pass that to the
OpenRecordset method ...

Dim strSql As String
Dim rsWeight As DAO.Recordset
Dim db As DAO.Recordset

strSql = "SELECT ... etc"
Set db = CurrentDb
Set rsWeight = db.OpenRecordset(strSql, dbOpenDynaset)

There isn't anything intrinsically wrong with using the saved query as
you are doing now, though.