Calling an adodb recordset in a text box (1 Viewer)

D

Digital Oatmeal

Registered User.
Local time
Today, 06:00
Joined
May 17, 2011
Messages
15
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:

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.
 
D

Digital Oatmeal

Registered User.
Local time
Today, 06:00
Joined
May 17, 2011
Messages
15
Thanks for the reply and I have looked at it in the imediate window before. I even pulled the resulting sql out and placed it in a stand alone query and tried to run it. It doesn't seem to like the "6:00:00" but I cannot figure out how to fix it. Just for giggles, heres the result in the console:

Code: 
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));
 
Last edited:
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:00
Joined
Aug 30, 2003
Messages
36,124
I should have added that date/time values should be surrounded by #, so after concatenation you'd end up with

FieldName = #6:00:00#
 
D

Digital Oatmeal

Registered User.
Local time
Today, 06:00
Joined
May 17, 2011
Messages
15
Rookie mistake. Now I have this:

Code: 
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...

But now I have "Object variable or With block variable not set" error.
 
Last edited:
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:00
Joined
Aug 30, 2003
Messages
36,124
On what line?

How are you using this? I thought it odd to pass a recordset. I'd likely pass the value.
 
D

Digital Oatmeal

Registered User.
Local time
Today, 06:00
Joined
May 17, 2011
Messages
15
1.When I open the recordset

2.I am calling this in a text box on a form.

3.The value is what I'm after too but i'm a hack job programmer that does stupid stuff until it works.

How do you sugest changing the code to return a numeric value?
 
Last edited:
D

Digital Oatmeal

Registered User.
Local time
Today, 06:00
Joined
May 17, 2011
Messages
15
SOLVED!!!!!

Here's the code:
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
 
Last edited:
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:00
Joined
Aug 30, 2003
Messages
36,124
Glad you got it sorted out. That's more the direction I would have gone in. By the way, If the recordset is empty you'll likely get an error here:

Receipts = rs("OffloadQty")

I'd adjust the logic of the preceding test to handle that.
 
D

Digital Oatmeal

Registered User.
Local time
Today, 06:00
Joined
May 17, 2011
Messages
15
pbaldy said:
Glad you got it sorted out. That's more the direction I would have gone in. By the way, If the recordset is empty you'll likely get an error here:

Receipts = rs("OffloadQty")

I'd adjust the logic of the preceding test to handle that.
Click to expand...


When I call the funtion from the text box it is nested in this function that i use for almost everything:
Code: 
Function nnz(testvalue As Variant) As Variant
'Not Numeric return zero
    If Not (IsNumeric(testvalue)) Then
        nnz = 0
    Else
        nnz = testvalue
    End If
End Function

So the text box looks like this:

=nnz(Receipts("BD"))

BUT WAIT...THERE'S MORE...

Assuming I have multiple subforms that use the dates frome the main form but each one represents a different chemical, is there a way to set a global/public/private variable to be set on the subform open event and use that to replace the "BD" that is the "CHEMICAL" in the function? I still need to call it from the text box but that would make the subforms completely encapsulated.

I tried =nnz(Receipts(Me.Chemical)) as the control source but that did not work despite how bad I wanted it to.

There is not a lot of info on the web about trying to make VBA more object oriented.
 
Last edited:
You must log in or register to reply here.

Similar threads

F
Saving all data to an Access Table using ADODB
Replies
3
Views
1,563
fernando.rosales
F
W
MISSING DATA IN EXPORTING CALCULATED QUERYTable FROM ACCESS TO EXCEL USING EXCEL ADO
Replies
1
Views
983
June7
June7
J
Having Query Results Populate in a Drop Down
2
Replies
20
Views
1,600
Jambi46n2
J
K
How do I populate a listbox in Excel with Access Data?
Replies
7
Views
4,877
Cronk
Cronk
T
ADODB Named Pipes not connecting
Replies
2
Views
1,721
Rx_
Rx_

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom