Error 3061 Too few parameters Expected 1

[Seth Schrock]

This one really has me puzzled. Normally I know how to deal with this type of error, but this time it is defying all my troubleshooting knowledge.

I have a query def (qryCustomerCreditScore). In my VBA code, I'm opening a recordset based on this query that sums some of the fields, counts another one, and then performs a custom function based on the last one. It is when I open this recordset that I get the Too few parameters error. So, as I normally do, I set a breakpoint and then used the immediate window to pull my SQL string from the VBA, copied and pasted it into a query def and then ran it. No error was returned. However, no records were returned. So I decided to run qryCustomerCreditScore(I'm still in Break mode) and it didn't return any records either. I then stopped my code and re-ran qryCustomerCreditScoreand it now returns records. So I run my query def based on my VBA SQL string and it works fine. I try to run it again through the code and it errors out again. While still in break mode I run qryCustomerCreditScoreand no records are returned.

I have never seen this or read about it online. Normally I would say that I got a field name typed incorrectly, but that doesn't seem to be the case this time since it all works fine when not being ran through code. I don't know if this matters, but my tables are stored on SQL Server connected through ODBC drivers (DSN-less connection).

Here is the code for qryCustomerCreditScore:

SELECT CustID_pk
, QuoteID_fk
, GetIndividualCS([CustID_pk]) AS CreditScore
, CustFN
, CustLN
, Income
, CollNum
, CollAmt
, ITIN
FROM tblCustomers
WHERE QuoteID_fk=[Forms]![frmConsumerMortgageCalculator]![QuoteID]
 

Here is the VBA code up to the line that errors out (line 18):

Private Sub SetValues()
On Error GoTo Error_Handler
 
Dim db As DAO.Database
Dim strQuote As String
Dim rstQuote As DAO.Recordset
Dim strCust As String
Dim rstCust As DAO.Recordset
 
 
Set db = CurrentDb
strQuote = "SELECT * FROM tblQuotes WHERE QuoteID = " & lngQuoteID
Set rstQuote = db.OpenRecordset(strQuote, dbOpenDynaset, dbSeeChanges)
 
strCust = "SELECT QuoteID_fk, Sum(Income) AS LnIncome, Sum(CollNum) AS LnCollNum, " & _
          "Sum(CollAmt) AS LnCollAmt, Count(ITIN) AS IsITIN, GetLoanCS([QuoteID_fk]) AS CreditScore " & _
          "FROM qryCustomerCreditScore GROUP BY QuoteID_fk"
Set rstCust = db.OpenRecordset(strCust, dbOpenDynaset, dbSeeChanges)

The strCust string concatenates out to

SELECT QuoteID_fk
, Sum(Income) AS LnIncome
, Sum(CollNum) AS LnCollNum
, Sum(CollAmt) AS LnCollAmt
, Count(ITIN) AS IsITIN
, GetLoanCS([QuoteID_fk]) AS CreditScore 
FROM qryCustomerCreditScore 
GROUP BY QuoteID_fk
 

I can't think of any other details that would be necessary, but like I said, I don't know what to look at right now. If I have missed something, just ask and I will provide it.

[twinnyfo]

Seth,

Check line 3 of your first block of code and line 16 of your second block.

Block 1, Line 3 says:

GetIndividualCS([CustID_pk]) AS CreditScore

Block 2, Line 16 says:

GetLoanCS([QuoteID_fk]) AS CreditScore

Since the only query referenced is qryCustomerCreditScore, you may be confusing your SQL string.

Also, if this does not resolve it, have you considered calculating out the Credit Score after the record(s) is returned? Not sure if that would make a difference....

Cheers!

[twinnyfo]

AND!!! I just realized that you were using the "Forms!" reference as a parameter in your query. When you use queries in such a manner (I've run across this a few times), you can't query that query in another query (if you at all followed this). You would have to find another way to limit the values.

[Seth Schrock]

I changed the alias in qryCustomerCreditScore to be IndCreditScore so that field is now

GetIndividualCS([CustID_pk]) AS IndCreditScore

I still get the error.

For which one are you suggesting that I calculate the credit score after the record is returned? For the GetIndividualCS function, it is taking up to three different credit scores from a related table and using special code to choose one of them for a specific person. GetLoanCS then takes the credit scores selected for each person and makes a selection from one of them.

I do have to have the query def because I use it in other situations as well, but I could try to combine my two queries into one for my VBA code. I'll let you know how this works.

[Seth Schrock]

I don't know for sure, but I would be surprised if I hadn't queried another query that used the Forms! reference. I'll give it a shot though. Maybe my idea to combine the two queries would fix it.

[twinnyfo]

A couple ways to use Form values without the Forms! reference is to use either global variables (which still requires you to create a tiny funciton that returns the value of the variable) or TempVars, which acts like a global variable, but can be used as is.... I am still not comfortable using Temp Vars, although I have found that it does work.

I think either one would work in these situations.

Hope this hepps!

[Seth Schrock]

Well, I got it to work. First I combined it and left the Forms! reference in it and it still errored out. Then I ended up using a variable to pass the value I needed to the query so that my query was using the actual number instead of a reference and it worked.

I am curious as to why it doesn't work to use VBA to query a query def that has a Forms! reference in it or create a SQL string in VBA that has the Forms! reference. It seems strange.

Anyway, thanks twinnyfo for leading me in the right direction. I don't think that I would have considered that.

[twinnyfo]

Seth,

Without my own pounding my head against a brick wall with the same error in the same situation, I would never have known. I forget where I found this soultion, but no one was able to provide a "why". Maybe the reference is just too far removed from itself--I don't have a clue.

But, I am glad we gould get you to a solution!