VBA Module help "No value given for one or more required parameters"

[legrev]

Hi! I'm quite new to Access coding, I hope someone can help me with this problem. Below is my function and I keep getting the error "No value given for one or more required parameters".. Hope someone could help me out..

Thank you very much in advance!

Function GetCountryTotal(Country As String) As Double
On Error GoTo Err_handler
Dim Cn As New ADODB.Connection
Dim Cmd As New ADODB.Command
Dim Rs As New ADODB.Recordset
Dim AmountSum As Double

teststring = Country
strquote = Chr$(34)
AmountSum = 0
Set Cn = CurrentProject.Connection
With Cmd
.ActiveConnection = Cn

.CommandText = " SELECT DISTINCT Sum(IIf([TblPledge].[PledgeAmountUSD]=0 Or [TblPledge].[PledgeAmountUSD] Is Null,IIf([TblPledge].[PledgeExchangeRate]>0,[TblPledge].[PledgeAmount]/[TblPledge].[PledgeExchangeRate],0),[TblPledge].[PledgeAmountUSD])) AS USDAmountSum, TblAllotment.AllotmentCountry " _
& " FROM TblPledge INNER JOIN TblAllotment ON TblPledge.PledgeID = TblAllotment.AllotmentPledgeID " _
& " WHERE (((TblPledge.PledgeID) In (select TblPledge.PledgeID from CountryofImplementationQuery))) AND TblAllotment.AllotmentCountry = '" & Country & "' " _
& " GROUP BY TblAllotment.AllotmentCountry"

.CommandType = adCmdText
Set Rs = .Execute
With Rs
.MoveLast
AmountSum = .Fields(0)
End With

.ActiveConnection.Close
End With

GetCountryTotal = AmountSum

Err_handler:
MsgBox Err.Description
Exit Function
End Function

 

[SkipVought]

Hi,

Your problem is with this statement...

GetCountryTotal = AmountSum

1) GetCountryTotal REQUIRES an argument
2) GetCountryTotal RETURNS a value

The use of it shoud be something like

MyTotal = GetCountryTotal("Denmark")

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[cmmrfrds]

Right after this statement display the SQL that was created.

.CommandType = adCmdText
Debug.Print .CommandText

Do a "Control G" and copy and paste the SQL.

 

[legrev]

thanks guys!!!

here's the sql

SELECT DISTINCT Sum(IIf([TblPledge].[PledgeAmountUSD]=0 Or [TblPledge].[PledgeAmountUSD] Is Null,IIf([TblPledge].[PledgeExchangeRate]>0,[TblPledge].[PledgeAmount]/[TblPledge].[PledgeExchangeRate],0),[TblPledge].[PledgeAmountUSD])) AS USDAmountSum, TblAllotment.AllotmentCountry FROM TblPledge INNER JOIN TblAllotment ON TblPledge.PledgeID = TblAllotment.AllotmentPledgeID WHERE (((TblPledge.PledgeID) In (select TblPledge.PledgeID from CountryofImplementationQuery))) AND TblAllotment.AllotmentCountry = 'AFGHANISTAN' GROUP BY TblAllotment.AllotmentCountry

 

[cmmrfrds]

Set Rs = .Execute
What happens at this point if the query does not return a recordset? There won't be a valid reference for the following. Check for EOF before continuing.

If Rs.EOF or Rs.BOF then
msgbox "no records returned"
exit function
end if
With Rs
.MoveLast
AmountSum = .Fields(0)
End With

 

[legrev]

Hi!

It goes to the err_handler right after rs= .execute.

 

[cmmrfrds]

What happens if you take the SQL statement that you pasted in and run it from the QBE? There appears to be something wrong with the SQL.

 

[legrev]

Hi! Yes, there is something wrong with the sql... The sql contains a select from a query that has parameters and it seems that it is not possible to have that. I took out that query, made some work-arounds and it worked...

Thanks anyway guys for your help!!!!