423,850 Members | 1,074 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,850 IT Pros & Developers. It's quick & easy.

Calling Function From query error 3061

Cintury
P: 81
The problem is I have a function that I've created and stored in a module. I call it as an expression (e.g. total: Function(parameter)). I'm receiving the error 3061: too few parameters, expected 1. Now the query and database are already open so I'm not entirely sure of any connection strings I may need. I think part of the problem may be that the parameter I am using to call the function is part of the query. I am not sure how to come by this value before hand. I may need to split this into a two-stage query.
Advice greatly appreciated!

MS-Access SQL Query
Expand|Select|Wrap|Line Numbers
  1. SELECT tblInventory.InventoryID, tblInventory.Food_Donations_ID, tblFoodDonor.[Donor Company], tblProductBrand.Brand_Name, tblFood_Category.Food_Category_ID, tblFood_Category.Food_Cat_Description, tblInventory!Num_Of_Containers*tblInventory!Weight_Per_Container AS Total_Poundage_From_Inv, totFdPoundage([tblInventory]![Food_Category_ID]) AS Total_Poundage_From_FD
  2. FROM tblProductBrand INNER JOIN ((tblFood_Category INNER JOIN tblInventory ON tblFood_Category.Food_Category_ID = tblInventory.Food_Cat_ID) INNER JOIN (tblFoodDonor INNER JOIN tblFoodDonations ON tblFoodDonor.DonorID = tblFoodDonations.DonorID) ON tblInventory.InventoryID = tblFoodDonations.InventoryID) ON tblProductBrand.Brand_ID = tblInventory.Product_Brand_ID
  3. WHERE (((tblInventory.Distribution_completed)=False));
  4.  
My Function
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3.  
  4. Public Function totFdPoundage(fcat As Integer) As Integer
  5.  
  6. Dim strsql As String
  7. Dim rsfh As Recordset
  8. Dim lbsContainer As Integer
  9. On Error GoTo verbose_warning
  10.  
  11.  
  12.  
  13.  
  14.  
  15. Select Case fcat
  16. Case 0:
  17.  
  18.    strsql = "SELECT Bakery FROM tblFoodDonations WHERE " & _
  19.             "tblFoodDonations.FoodDonationsID = qryCurr_Inv.Food_Donations_ID"
  20.     Set rsfh = CurrentDb.OpenRecordset(strsql)
  21.     lbsContainer = rsfh("Bakery")
  22.  
  23.  
  24.     totFdPoundage = lbsContainer
  25.  
  26. Case 1:
  27.  
  28.     strsql = "SELECT Dairy FROM tblFoodDonations WHERE " & _
  29.              "tblFoodDonations.FoodDonationsID = qryCurr_Inv.Food_Donations_ID"
  30.     Set rsfh = CurrentDb.OpenRecordset(strsql)
  31.     lbsContainer = rsfh("Diary")
  32.  
  33.  
  34.     totFdPoundage = lbsContainer
  35.  
  36.  
  37. Case 2:
  38.  
  39.     strsql = "SELECT [tblFoodDonations].[Meat] FROM [tblFoodDonations] WHERE " & _
  40.              "[tblFoodDonations].[FoodDonationsID] = [qryCurr_Inv].[Food_Donations_ID]"
  41.  
  42.  
  43.     Debug.Print strsql
  44.     Set rsfh = CurrentDb.OpenRecordset(strsql)
  45.     lbsContainer = rsfh("Meat")
  46.  
  47.  
  48.     totFdPoundage = lbsContainer
  49.  
  50.  
  51. Case 3:
  52.  
  53.     strsql = "SELECT Fruit FROM tblFoodDonations WHERE " & _
  54.              "tblFoodDonations.FoodDonationsID = qryCurr_Inv.Food_Donations_ID"
  55.     Set rsfh = CurrentDb.OpenRecordset(strsql)
  56.     lbsContainer = rsfh("Fruit")
  57.  
  58.  
  59.     totFdPoundage = lbsContainer
  60.  
  61.  
  62. Case 4:
  63.  
  64.     strsql = "SELECT Vegetable FROM tblFoodDonations WHERE " & _
  65.              "tblFoodDonations.FoodDonationsID = qryCurr_Inv.Food_Donations_ID"
  66.     Set rsfh = CurrentDb.OpenRecordset(strsql)
  67.     lbsContainer = rsfh("Vegetable")
  68.  
  69.  
  70.     totFdPoundage = lbsContainer
  71.  
  72.  
  73. Case 5:
  74.  
  75.     strsql = "SELECT Prepared FROM tblFoodDonations WHERE " & _
  76.              "tblFoodDonations.FoodDonationsID = qryCurr_Inv.Food_Donations_ID"
  77.     Set rsfh = CurrentDb.OpenRecordset(strsql)
  78.     lbsContainer = rsfh("Prepared")
  79.  
  80.  
  81.     totFdPoundage = lbsContainer
  82.  
  83.  
  84. Case 6:
  85.  
  86.     strsql = "SELECT Non-Perishable FROM tblFoodDonations WHERE " & _
  87.              "tblFoodDonations.FoodDonationsID = qryCurr_Inv.Food_Donations_ID"
  88.     Set rsfh = CurrentDb.OpenRecordset(strsql)
  89.     lbsContainer = rsfh("Juice")
  90.  
  91.  
  92.     totFdPoundage = lbsContainer
  93.  
  94.  
  95. Case 7:
  96.  
  97.     strsql = "SELECT Non-Food FROM tblFoodDonations WHERE " & _
  98.              "tblFoodDonations.FoodDonationsID = qryCurr_Inv.Food_Donations_ID"
  99.     Set rsfh = CurrentDb.OpenRecordset(strsql)
  100.     lbsContainer = rsfh("Non-Perishable")
  101.  
  102.  
  103.     totFdPoundage = lbsContainer
  104.  
  105.  
  106. Case 8:
  107.  
  108.     strsql = "SELECT  FROM tblFoodDonations WHERE " & _
  109.              "tblFoodDonations.FoodDonationsID = qryCurr_Inv.Food_Donations_ID"
  110.     Set rsfh = CurrentDb.OpenRecordset(strsql)
  111.     lbsContainer = rsfh("Non-Food")
  112.  
  113.  
  114.     totFdPoundage = lbsContainer
  115.  
  116.  
  117. End Select
  118.  
  119. verbose_warning:
  120.    msgbox "Error Number: " & Err.Number & vbCrLf & "Problem Description: " & Err.Description & " " & vbCrLf & "Help Context: " & Err.HelpContext
  121.    Exit Function
  122.  
  123.  
  124. rsfh.Close
  125. Set rsfh = Nothing
  126.  
  127.  
  128. End Function
  129.  
Oct 5 '09 #1

✓ answered by ChipR

Sorry, but this is bothering me. The purpose of Select Case is to make your life easier.
Expand|Select|Wrap|Line Numbers
  1. strSQL = "Select "
  2. Select Case fcat 
  3.   Case 0
  4.     strSQL = strSQL & "Bakery "
  5.   Case 1
  6.     strSQL = strSQL & "Dairy "
  7.   Case 2
  8.     strSQL = strSQL & "Fruit "
  9.   ...
  10.   Case Else
  11.     MsgBox "Invalid Category!"
  12. End Select
  13. strSQL = strSQL & "FROM tblFoodDonations WHERE " & _ 
  14.              "tblFoodDonations.FoodDonationsID = qryCurr_Inv.Food_Donations_ID"
  15. Set rsfh = CurrentDb.OpenRecordset(strSQL) 
  16. ...
That being said, this seems like a job for DLookup.

Share this Question
Share on Google+
8 Replies


NeoPa
Expert Mod 15k+
P: 31,121
No definitive answers, but I'd consider running the query without the function call to start with and see what shows. The parameter is defined as an Integer, so there must be no missing records, and the field passed must also be Integer (It is most usual for ID type fields to be defined as Longs).

See where this gets you. You may need to progress from there to debugging your actual code. Do it first without the query. See Debugging in VBA for help with debugging.
Oct 5 '09 #2

Expert 100+
P: 1,287
What does this do?
lbsContainer = rsfh("Bakery")
Oct 5 '09 #3

Expert 100+
P: 1,287
Case 8 is missing a parameter.
strsql = "SELECT FROM ...
Oct 5 '09 #4

Cintury
P: 81
@ChipR
According to my reading on recordsets, it should take the value of that field and place it in the lbsContainer variable.
Oct 5 '09 #5

Expert 100+
P: 1,287
Sorry, but this is bothering me. The purpose of Select Case is to make your life easier.
Expand|Select|Wrap|Line Numbers
  1. strSQL = "Select "
  2. Select Case fcat 
  3.   Case 0
  4.     strSQL = strSQL & "Bakery "
  5.   Case 1
  6.     strSQL = strSQL & "Dairy "
  7.   Case 2
  8.     strSQL = strSQL & "Fruit "
  9.   ...
  10.   Case Else
  11.     MsgBox "Invalid Category!"
  12. End Select
  13. strSQL = strSQL & "FROM tblFoodDonations WHERE " & _ 
  14.              "tblFoodDonations.FoodDonationsID = qryCurr_Inv.Food_Donations_ID"
  15. Set rsfh = CurrentDb.OpenRecordset(strSQL) 
  16. ...
That being said, this seems like a job for DLookup.
Oct 5 '09 #6

Cintury
P: 81
@ChipR
Ok, I've written up a DLookup function and it seems to be correctly formatted yet it gives me a data type mismatch for the criteria section. Can you let me know if I am giving the correct parameters?
Expand|Select|Wrap|Line Numbers
  1. Public Function totLBS(fdreceipt As String, fcat As String) As Long
  2.  
  3.  
  4. Dim numLbsContainer As Long
  5. Dim strCriteria As String
  6.  
  7. strCriteria = "FoodDonationsID =" & fdreceipt
  8. numLbsContainer = DLookup(fcat, "tblFoodDonations", strCriteria)
  9.  
  10. totLBS = numLbsContainer
  11. End Function
When Debugging the fcat is "Meat" which is the correct string for the category I want to look up in the tblFoodDonations category.

The strCriteria in my test case comes out to "FoodDonationsID=80012" which appears to be the correct format when going by the link you gave me.

I think the problem is alllllmost solved.
Thanks for all the help!
Oct 6 '09 #7

Cintury
P: 81
Nevermind, I figured out where my formatting was wrong. I'll repost the code below in case anyone else has DLookup trouble.

Expand|Select|Wrap|Line Numbers
  1. Public Function totLBS(fdreceipt As String, fcat As String) As Long
  2.  
  3.  
  4. Dim numLbsContainer As Long
  5. Dim strCriteria As String
  6.  
  7. strCriteria = "[FoodDonationsID] =" & "'" & fdreceipt & "'"
  8. numLbsContainer = DLookup("[" & fcat & "]", "tblFoodDonations", strCriteria)
  9.  
  10. totLBS = numLbsContainer
  11. End Function
  12.  
  13.  
I appreciate all the help I received from this forum, special thanks to ChipR & NeoPa.
Oct 6 '09 #8

NeoPa
Expert Mod 15k+
P: 31,121
@Cintury
Is the field you want returned called [fcat] or [Meat]. If the former, then you would need the following instead :
Expand|Select|Wrap|Line Numbers
  1. numLbsContainer = DLookup("[fcat]", "tblFoodDonations", strCriteria)
Oct 6 '09 #9

Post your reply

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