By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,476 Members | 1,553 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,476 IT Pros & Developers. It's quick & easy.

Calling an adodb recordset in a text box

P: 4
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:

Expand|Select|Wrap|Line Numbers
  1. Function Receipts(ByVal CHEMICAL As String) As ADODB.Recordset
  2.     Dim sql As String
  3.     Dim beginningDate As Date
  4.     Dim endingDate As Date
  5.     Dim chemicalID As String
  6.  
  7.     beginningDate = DateAdd("h", 6, [Forms]![frmProduction]![txtBeginningDate])
  8.     endingDate = DateAdd("h", 30, [Forms]![frmProduction]![txtEndingDate])
  9.     chemicalID = CHEMICAL
  10.  
  11. sql = "SELECT (Sum(Abs([tblReceiptScaleData]![WeightIn1]-[tblReceiptScaleData]![WeightOut2]+[tblReceiptScaleData]![WeightIn2]-[tblReceiptScaleData]![WeightOut1]))) AS OffloadQty " _
  12.     & "FROM ItemMasterList INNER JOIN (sPULINH INNER JOIN tblReceiptScaleData ON sPULINH.Pono = tblReceiptScaleData.OrderNumber) ON ItemMasterList.SAGEItemKey = sPULINH.Itemkey " _
  13.     & "WHERE (((ItemMasterList.OperationsDescription) = " & chemicalID & ") AND ((tblReceiptScaleData.TimeOut2) >= " & beginningDate & ") AND ((tblReceiptScaleData.TimeOut2) <= " & endingDate & ")); "
  14. Set Receipts = New ADODB.Recordset
  15. Receipts.Open sql, CurrentProject.Connection, , adLockOptimistic
  16. 'Receipts = rs("OffloadQty")
  17.    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.
Attached Files
File Type: txt vba-Receipts.txt (1.1 KB, 161 views)
Mar 27 '12 #1
Share this Question
Share on Google+
5 Replies


NeoPa
Expert Mod 15k+
P: 31,186
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.
Mar 27 '12 #2

P: 4
Rookie mistake. Now I have this:
Expand|Select|Wrap|Line Numbers
  1. SELECT (Sum(Abs([tblReceiptScaleData]![WeightIn1]-[tblReceiptScaleData]![WeightOut2]+[tblReceiptScaleData]![WeightIn2]-[tblReceiptScaleData]![WeightOut1]))) AS OffloadQty
  2. FROM ItemMasterList INNER JOIN (sPULINH INNER JOIN tblReceiptScaleData ON sPULINH.Pono=tblReceiptScaleData.OrderNumber) ON ItemMasterList.SAGEItemKey=sPULINH.Itemkey
  3. 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.
Mar 27 '12 #3

P: 4
SOLVED!!!!!

Here's the code:
Expand|Select|Wrap|Line Numbers
  1. Function Receipts(ByVal CHEMICAL As String) As Variant
  2.     Dim sql As String
  3.     Dim beginningDate As Date
  4.     Dim endingDate As Date
  5.     Dim chemicalID As String
  6.  
  7.     beginningDate = DateAdd("h", 6, [Forms]![frmProduction]![txtBeginningDate])
  8.     endingDate = DateAdd("h", 30, [Forms]![frmProduction]![txtEndingDate])
  9.     chemicalID = CHEMICAL
  10.  
  11. sql = "SELECT (Sum(Abs([tblReceiptScaleData]![WeightIn1]-[tblReceiptScaleData]![WeightOut2]+[tblReceiptScaleData]![WeightIn2]-[tblReceiptScaleData]![WeightOut1]))) AS OffloadQty " _
  12.     & "FROM ItemMasterList INNER JOIN (sPULINH INNER JOIN tblReceiptScaleData ON sPULINH.Pono=tblReceiptScaleData.OrderNumber) ON ItemMasterList.SAGEItemKey=sPULINH.Itemkey " _
  13.     & "WHERE (((ItemMasterList.OperationsDescription) ='" & chemicalID & "') And ((tblReceiptScaleData.TimeOut2)>=#" & beginningDate & "#) And ((tblReceiptScaleData.TimeOut2)<=#" & endingDate & "#)); "
  14. Dim rs As New ADODB.Recordset
  15. rs.Open sql, CurrentProject.Connection, , adLockOptimistic
  16.     If rs.EOF And rs.BOF Then
  17.         rs.Close
  18.     End If
  19. Receipts = rs("OffloadQty")
  20.    Set rs = Nothing
  21.  
  22. End Function
Mar 27 '12 #4

NeoPa
Expert Mod 15k+
P: 31,186
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.
Mar 27 '12 #5

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

< SNIP >
Mar 31 '12 #6

Post your reply

Sign in to post your reply or Sign up for a free account.