Calling an adodb recordset in a text box

[Digital Oatmeal]

I am trying to call a function from a text box on a subform that returns a summed total of a single calculated field. I need to reproduce this function in multiple subforms so it is encapsulated as possible. Here's the code:

Function Receipts(ByVal CHEMICAL As String) As ADODB.Recordset
    Dim sql As String
    Dim beginningDate As Date
    Dim endingDate As Date
    Dim chemicalID As String
    
    beginningDate = DateAdd("h", 6, [Forms]![frmProduction]![txtBeginningDate])
    endingDate = DateAdd("h", 30, [Forms]![frmProduction]![txtEndingDate])
    chemicalID = CHEMICAL

sql = "SELECT (Sum(Abs([tblReceiptScaleData]![WeightIn1]-[tblReceiptScaleData]![WeightOut2]+[tblReceiptScaleData]![WeightIn2]-[tblReceiptScaleData]![WeightOut1]))) AS OffloadQty " _
    & "FROM ItemMasterList INNER JOIN (sPULINH INNER JOIN tblReceiptScaleData ON sPULINH.Pono = tblReceiptScaleData.OrderNumber) ON ItemMasterList.SAGEItemKey = sPULINH.Itemkey " _
    & "WHERE (((ItemMasterList.OperationsDescription) = " & chemicalID & ") AND ((tblReceiptScaleData.TimeOut2) >= " & beginningDate & ") AND ((tblReceiptScaleData.TimeOut2) <= " & endingDate & ")); "
Set Receipts = New ADODB.Recordset
Receipts.Open sql, CurrentProject.Connection, , adLockOptimistic
'Receipts = rs("OffloadQty")
   Set Receipts = Nothing

The commented out code is from trying to have the function return a variant and using "rs" as the recordset and opening rs and setting Receipts to rs.

As it is I am getting "Syntax error(missing operator) in query expression" and lists out the WHERE clause of my sql.

With it set up using rs as the recordset and returning a variant it gives me "Run-time error '91': Object variable or With block variable not set."


Thanks ahead of time for any help.

[NeoPa]

Digital Oatmeal:
As it is I am getting "Syntax error(missing operator) in query expression" and lists out the WHERE clause of my sql.

In that case I suggest you post the WHERE clause of your SQL for someone to look at.

[Digital Oatmeal]

Rookie mistake. Now I have this:

SELECT (Sum(Abs([tblReceiptScaleData]![WeightIn1]-[tblReceiptScaleData]![WeightOut2]+[tblReceiptScaleData]![WeightIn2]-[tblReceiptScaleData]![WeightOut1]))) AS OffloadQty
FROM ItemMasterList INNER JOIN (sPULINH INNER JOIN tblReceiptScaleData ON sPULINH.Pono=tblReceiptScaleData.OrderNumber) ON ItemMasterList.SAGEItemKey=sPULINH.Itemkey
WHERE (((ItemMasterList.OperationsDescription) ='BD') And ((tblReceiptScaleData.TimeOut2)>=#3/27/2012 6:00:00 AM#) And ((tblReceiptScaleData.TimeOut2)<=#3/28/2012 6:00:00 AM#));

The sql works...I was missing the "#" signs on the dates.

But now I have "Object variable or With block variable not set" error on the line where the recordset is opened.

[Digital Oatmeal]

SOLVED!!!!!

Here's the code:

Function Receipts(ByVal CHEMICAL As String) As Variant
    Dim sql As String
    Dim beginningDate As Date
    Dim endingDate As Date
    Dim chemicalID As String

    beginningDate = DateAdd("h", 6, [Forms]![frmProduction]![txtBeginningDate])
    endingDate = DateAdd("h", 30, [Forms]![frmProduction]![txtEndingDate])
    chemicalID = CHEMICAL

sql = "SELECT (Sum(Abs([tblReceiptScaleData]![WeightIn1]-[tblReceiptScaleData]![WeightOut2]+[tblReceiptScaleData]![WeightIn2]-[tblReceiptScaleData]![WeightOut1]))) AS OffloadQty " _
    & "FROM ItemMasterList INNER JOIN (sPULINH INNER JOIN tblReceiptScaleData ON sPULINH.Pono=tblReceiptScaleData.OrderNumber) ON ItemMasterList.SAGEItemKey=sPULINH.Itemkey " _
    & "WHERE (((ItemMasterList.OperationsDescription) ='" & chemicalID & "') And ((tblReceiptScaleData.TimeOut2)>=#" & beginningDate & "#) And ((tblReceiptScaleData.TimeOut2)<=#" & endingDate & "#)); "
Dim rs As New ADODB.Recordset
rs.Open sql, CurrentProject.Connection, , adLockOptimistic
    If rs.EOF And rs.BOF Then
        rs.Close
    End If
Receipts = rs("OffloadQty")
   Set rs = Nothing

End Function

[NeoPa]

You solved it all by yourself in the end. Good for you.

Sometimes that can happen when you go to the effort of posting a question. Your brain is somehow stimulated to continue working on it, even when you're doing other things. Telling someone about it can have a similar effect. Very helpful, even if that person has no idea what you're on about I find.

[Digital Oatmeal]

Don't give me all the credit. I did have a little help from my friends.

< SNIP >