Run-time error '3061': Too few parameters. Expected 2. 3

[zahara666]

I get that error with the below code:

Set rst = dbs.OpenRecordset("InvoiceQuery&quot

strSQL = "SELECT Int1Table.InvoiceNumber, ClientTable.ClientName, ClientTable.ClientContact, ClientTable.Address1, ClientTable.Address2, ClientTable.City, ClientTable.Zip, Int1Table.FileNum, Int1Table.LastName, Int1Table.FirstName, Int1Table.LOS, Int1Table.Rate, Int1Table.Surcharge, Int1Table.Total, Int1Table.Invoiced, Int1Table.Received, Int1Table.ClientCode FROM ClientTable INNER JOIN Int1Table ON ClientTable.ClientCode = Int1Table.ClientCode WHERE (((Int1Table.Invoiced)=False) AND ((Int1Table.Received) Between [Forms]![InvoiceForm]![FromDate] And [Forms]![InvoiceForm]![ToDate]) AND ((Int1Table.ClientCode)="

For Each varItem In ctl.ItemsSelected
strSQL = strSQL & "'" & ctl.ItemData(varItem) & "' Or ([Int1Table].[ClientCode])="
Next varItem

strSQL = Left$(strSQL, Len(strSQL) - 30) & &quot) ORDER BY Int1Table.ClientCode"

Any ideas???

Thanks for your help!!

Julie

 

[RickSpr]

1. Do you realize that if the InvoiceForm is not open, the two references to it will be treated as parameters?
2. Check that all your column names are spelled correctly, and that you have qualified them with the correct table name.
3. Why did you include the "Set rst =" statement? It seems to be unrelated to the SQL statement you're building, but then you didn't show any execution of that SQL statement. Is the error occurring on the Set statement? Rick Sprague

 

[zahara666]

1. The InvoiceForm is open while this is being run
2. All table names and field names are correct, I have checked and double checked this.
3. The error is occuring in the set rst = line, and that SQL statement is the InvoiceQuery that I am referencing.

Thanks,
J.

 

[RickSpr]

Ah! Then the code that sets strSQL is irrelevant.

Apparently, the stored query InvoiceQuery contains 2 parameters, and you failed to set them prior to opening the recordset. To learn the parameter names, simply open the query from the Database Window. Access will ask you for values for the parameters, one at a time.

If you discover that the "parameters" are actually references to form fields (in some other form than InvoiceForm, which you said is open), the problem is that InvoiceQuery expects that other form to be open when the query is opened.

If you don't want to open the other form, or if the parameters don't refer to a form, then you need to provide the parameters in your code before you run the query. To do so, you use the QueryDef object, set the parameter values using its Parameters collection, and then open the recordset from the QueryDef:

    Dim db As Database
    Dim qdf As QueryDef
    Dim rst As Recordset

    Set db = CurrentDb()
    Set qdf = db.QueryDefs("InvoiceQuery")
    qdf.Parameters(&quot;<1st parameter name>&quot;) = <value>
    qdf.Parameters(&quot;<2nd parameter name>&quot;) = <value>
    Set rst = qdf.OpenRecordset

Note: When you use DAO objects, such as Database, QueryDef, and Recordset, you should Set them to Nothing before exiting your procedure. There is a bug in DAO that sometimes fails to release these objects implicitly when they go out of scope, and it can cause Access to refuse to shut down. Rick Sprague

 

[zahara666]

Thanks for the information you provided. You are right in saying that the query is asking for 2 parameters. The 2 paremeters are a date range and that is inputed by the user in the InvoiceForm - which is open when this function begins running, which is why I can't understand why it is STILL expecting those two parameters in the set rst = line..

I'll look into this in more detail, but if you have some idea, please don't hesitate to share.

Thanks for your help,
Julie

 

[RickSpr]

If you open the query from the database window while the InvoiceForm is open, does it extract the parameters from the form, or does it prompt for the parameter values?

Whate I'm thinking is that when you open the query via OpenRecordset, it doesn't scan for parameters. That scan may only happen when you open it in the UI. If that's the problem, my earlier solution will still work for you. Rick Sprague

 

[zahara666]

Yes, I was thinking the same logic that when you open it via recordset, it ignores what has been entered in the form. I will give your code a try.

Thanks very much for your help.

Julie

 

[zahara666]

Rick,

I did exactly as you said and followed your above code, but it is STILL giving me the same error message... Any ideas?

J.

 

[RickSpr]

What are the names of the parameters? They should be [Forms]![InvoiceForm]![FromDate] and [Forms]![InvoiceForm]![ToDate]. Is that correct? And there are controls named FromDate and ToDate on the form? And InvoiceQuery's parameters window doesn't show any other parameters?

Here's another idea: Is InvoiceQuery based on yet another query, which might have parameters? That could cause this. Rick Sprague

 

[zahara666]

Hi Rick,

I typed in the parameters just the way you suggested. With the names being: [Forms]![InvoiceForm]![FromDate] and [Forms]![InvoiceForm]![ToDate] .. But I am still getting that error. When I check the VBA code, it takes the date values and puts them into the parameters, but for some reason it isn't referencing it into the Query.

No, invoice query is not based on any other query..

Now I am getting frustrated.

Any ideas?

Thanks a lot for your help,
Julie

 

[jebry]

Hi Julie!

If you at some point try to run the SQL you showed in your original post, you will need to make the following change:

((Int1Table.Received) Between [Forms]![InvoiceForm]![FromDate] And [Forms]![InvoiceForm]![ToDate])

to

((Int1Table.Received) Between #" & [Forms]![InvoiceForm]![FromDate] & "# And #" & [Forms]![InvoiceForm]![ToDate]) "# etc.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[zahara666]

Jeff,

That didn't work.. Same error message.

Julie

 

[jebry]

Hi!

Maybe you can post all of the code, or send me a zipped copy of your db. I'll help if I can.

Jeff Bridgham
bridgham@purdue.edu

 

[Rohdem]

The problem is you cannot open a query-based recordset that gets parameters from a form. I ran into this problem many times until I found this wonderful piece of code here on TT!! (My apologies to the TT member who gave me the code, because I cannot remember their name)


After searching through my books I found an explaination of and a work-around for this problem. The problem is when a query is opened in VBA any conditional parameters cannot be resolved as they are when the query is opened by Access. The work-around is as follows any referrenced forms must be open:

Dim db As Database
Dim qdf As QueryDef
Dim prm As Parameter
Dim rst As Recordset

Set db = CurrentDB()
Set qdf = db.QueryDefs("qrySomeQuery&quot
For Each prm in qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = gdf.OpenRecordset(dbOpenDynaset)

The above code example taken from Microsoft Access 95 Developer's Handbook Second Edition by Litwin, Getz, Gilbert and Reddick. Mike Rohde
"I don't have a god complex, god has a me complex!"

 

[Rohdem]

Sorry, that last line should be:

Set rst = qdf.OpenRecordset(dbOpenDynaset)
NOT
Set rst = gdf.OpenRecordset(dbOpenDynaset)
Mike Rohde
"I don't have a god complex, god has a me complex!"