Just a basic OpenRecordset. I tried dbOpenDynaset too...
Code:
'Calculate the total number of units
Dim rsSum As DAO.Recordset
Dim lngSum As Long
lngSum = 0
Set rsSum = CurrentDb.OpenRecordset("qryInvView", dbOpenSnapshot)
With rsSum
If .EOF = False Then
.MoveFirst
While .EOF = False
lngSum = lngSum + ![CountUnits]
.MoveNext
Wend
End If
Me.txtUnitCount.Value = lngSum
End With
rsSum.Close
Set rsSum = Nothing
In case you are curious, here is the SQL from the query object named in the DAO. THis SQL works fine when I double click the query in the Nav Pane. Within this SQL is another query object that is a Totals query.
Code:
SELECT tblPurchOrd.PurchOrdPK, tblPurchOrd.CustFK, tblPurchOrd.PO, qryProdDescrInParam.Product, qryProdDescrInParam.CountUnits, qryProdDescrInParam.SpeciesPK, qryProdDescrInParam.GradePK, qryProdDescrInParam.DimensionPK, qryProdDescrInParam.CurePK
FROM tblPurchOrd INNER JOIN qryProdDescrInParam ON tblPurchOrd.PurchOrdPK = qryProdDescrInParam.PurchOrdFK
WHERE (((tblPurchOrd.PurchOrdPK)=IIf([Forms]![frmInventory]![cmbPO]<>"",[Forms]![frmInventory]![cmbPO],[tblPurchOrd].[PurchOrdPK])) AND ((tblPurchOrd.CustFK)=[Forms]![frmInventory]![cmbCust]) AND ((qryProdDescrInParam.SpeciesPK)=IIf([Forms]![frmInventory]![cmbSpecies]<>"",[Forms]![frmInventory]![cmbSpecies],[qryProdDescrInParam].[SpeciesPK])) AND ((qryProdDescrInParam.GradePK)=IIf([Forms]![frmInventory]![cmbGrade]<>"",[Forms]![frmInventory]![cmbGrade],[qryProdDescrInParam].[GradePK])) AND ((qryProdDescrInParam.DimensionPK)=IIf([Forms]![frmInventory]![cmbDim]<>"",[Forms]![frmInventory]![cmbDim],[qryProdDescrInParam].[DimensionPK])) AND ((qryProdDescrInParam.CurePK)=IIf([Forms]![frmInventory]![cmbCure]<>"",[Forms]![frmInventory]![cmbCure],[qryProdDescrInParam].[CurePK])));