Calling Function From query error 3061

[Cintury]

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(parame ter)). 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

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
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
WHERE (((tblInventory.Distribution_completed)=False));
 

My Function

Option Compare Database
 
 
Public Function totFdPoundage(fcat As Integer) As Integer
 
Dim strsql As String
Dim rsfh As Recordset
Dim lbsContainer As Integer
On Error GoTo verbose_warning
 
 
 
 
 
Select Case fcat
Case 0:
 
   strsql = "SELECT Bakery FROM tblFoodDonations WHERE " & _
            "tblFoodDonations.FoodDonationsID = qryCurr_Inv.Food_Donations_ID"
    Set rsfh = CurrentDb.OpenRecordset(strsql)
    lbsContainer = rsfh("Bakery")
 
 
    totFdPoundage = lbsContainer
 
Case 1:
 
    strsql = "SELECT Dairy FROM tblFoodDonations WHERE " & _
             "tblFoodDonations.FoodDonationsID = qryCurr_Inv.Food_Donations_ID"
    Set rsfh = CurrentDb.OpenRecordset(strsql)
    lbsContainer = rsfh("Diary")
 
 
    totFdPoundage = lbsContainer
 
 
Case 2:
 
    strsql = "SELECT [tblFoodDonations].[Meat] FROM [tblFoodDonations] WHERE " & _
             "[tblFoodDonations].[FoodDonationsID] = [qryCurr_Inv].[Food_Donations_ID]"
 
 
    Debug.Print strsql
    Set rsfh = CurrentDb.OpenRecordset(strsql)
    lbsContainer = rsfh("Meat")
 
 
    totFdPoundage = lbsContainer
 
 
Case 3:
 
    strsql = "SELECT Fruit FROM tblFoodDonations WHERE " & _
             "tblFoodDonations.FoodDonationsID = qryCurr_Inv.Food_Donations_ID"
    Set rsfh = CurrentDb.OpenRecordset(strsql)
    lbsContainer = rsfh("Fruit")
 
 
    totFdPoundage = lbsContainer
 
 
Case 4:
 
    strsql = "SELECT Vegetable FROM tblFoodDonations WHERE " & _
             "tblFoodDonations.FoodDonationsID = qryCurr_Inv.Food_Donations_ID"
    Set rsfh = CurrentDb.OpenRecordset(strsql)
    lbsContainer = rsfh("Vegetable")
 
 
    totFdPoundage = lbsContainer
 
 
Case 5:
 
    strsql = "SELECT Prepared FROM tblFoodDonations WHERE " & _
             "tblFoodDonations.FoodDonationsID = qryCurr_Inv.Food_Donations_ID"
    Set rsfh = CurrentDb.OpenRecordset(strsql)
    lbsContainer = rsfh("Prepared")
 
 
    totFdPoundage = lbsContainer
 
 
Case 6:
 
    strsql = "SELECT Non-Perishable FROM tblFoodDonations WHERE " & _
             "tblFoodDonations.FoodDonationsID = qryCurr_Inv.Food_Donations_ID"
    Set rsfh = CurrentDb.OpenRecordset(strsql)
    lbsContainer = rsfh("Juice")
 
 
    totFdPoundage = lbsContainer
 
 
Case 7:
 
    strsql = "SELECT Non-Food FROM tblFoodDonations WHERE " & _
             "tblFoodDonations.FoodDonationsID = qryCurr_Inv.Food_Donations_ID"
    Set rsfh = CurrentDb.OpenRecordset(strsql)
    lbsContainer = rsfh("Non-Perishable")
 
 
    totFdPoundage = lbsContainer
 
 
Case 8:
 
    strsql = "SELECT  FROM tblFoodDonations WHERE " & _
             "tblFoodDonations.FoodDonationsID = qryCurr_Inv.Food_Donations_ID"
    Set rsfh = CurrentDb.OpenRecordset(strsql)
    lbsContainer = rsfh("Non-Food")
 
 
    totFdPoundage = lbsContainer
 
 
End Select
 
verbose_warning:
   msgbox "Error Number: " & Err.Number & vbCrLf & "Problem Description: " & Err.Description & " " & vbCrLf & "Help Context: " & Err.HelpContext
   Exit Function
 
 
rsfh.Close
Set rsfh = Nothing
 
 
End Function
 

[NeoPa]

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.

[ChipR]

What does this do?
lbsContainer = rsfh("Bakery")

[ChipR]

Case 8 is missing a parameter.
strsql = "SELECT FROM ...

[Cintury]

@ChipR
According to my reading on recordsets, it should take the value of that field and place it in the lbsContainer variable.

[ChipR]

Sorry, but this is bothering me. The purpose of Select Case is to make your life easier.

strSQL = "Select "
Select Case fcat 
  Case 0
    strSQL = strSQL & "Bakery "
  Case 1
    strSQL = strSQL & "Dairy "
  Case 2
    strSQL = strSQL & "Fruit "
  ...
  Case Else
    MsgBox "Invalid Category!"
End Select
strSQL = strSQL & "FROM tblFoodDonations WHERE " & _ 
             "tblFoodDonations.FoodDonationsID = qryCurr_Inv.Food_Donations_ID"
Set rsfh = CurrentDb.OpenRecordset(strSQL) 
...

That being said, this seems like a job for DLookup.

[Cintury]

@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?

Public Function totLBS(fdreceipt As String, fcat As String) As Long
 
 
Dim numLbsContainer As Long
Dim strCriteria As String
 
strCriteria = "FoodDonationsID =" & fdreceipt
numLbsContainer = DLookup(fcat, "tblFoodDonations", strCriteria)
 
totLBS = numLbsContainer
End Function

When Debugging the fcat is "Meat" which is the correct string for the category I want to look up in the tblFoodDonation s category.

The strCriteria in my test case comes out to "FoodDonationsI D=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!

[Cintury]

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

Public Function totLBS(fdreceipt As String, fcat As String) As Long
 
 
Dim numLbsContainer As Long
Dim strCriteria As String
 
strCriteria = "[FoodDonationsID] =" & "'" & fdreceipt & "'"
numLbsContainer = DLookup("[" & fcat & "]", "tblFoodDonations", strCriteria)
 
totLBS = numLbsContainer
End Function
 
 

I appreciate all the help I received from this forum, special thanks to ChipR & NeoPa.

[NeoPa]

@Cintury
Is the field you want returned called [fcat] or [Meat]. If the former, then you would need the following instead :

numLbsContainer = DLookup("[fcat]", "tblFoodDonations", strCriteria)