Switching From Access 2003 to Access 2007 - Trouble calling a query in VBA (1 Viewer)

BamaColtsFan

Registered User
Joined
Nov 8, 2006
Messages
91
Greetings Revered Access Guru's!

Okay, so we recently "upgraded" from Access 2003 to Access 2007 and now I'm having a problem with two queries called be VBA to send e-mails. The queries both have user prompts in them so specific values can be isolated. In Access 2003, they both got called by VBA and executed perfectly based on the input from the user. Now, since switching to Access 2007, I get the error "Run-time error '3061': Too few parameters. Expected; 1".


I am assuming the problem has to do with the user prompts in the query being called since the debug stops right on the queries. What I don't know is what to do about it. It is essential that the user be able to isolate the data with this prompt.

As always, any suggestions are deeply appreciated!

THNX!
 

Vassago

Administrator
Staff member
Joined
Dec 26, 2002
Messages
4,396
Greetings Revered Access Guru's!

Okay, so we recently "upgraded" from Access 2003 to Access 2007 and now I'm having a problem with two queries called be VBA to send e-mails. The queries both have user prompts in them so specific values can be isolated. In Access 2003, they both got called by VBA and executed perfectly based on the input from the user. Now, since switching to Access 2007, I get the error "Run-time error '3061': Too few parameters. Expected; 1".


I am assuming the problem has to do with the user prompts in the query being called since the debug stops right on the queries. What I don't know is what to do about it. It is essential that the user be able to isolate the data with this prompt.

As always, any suggestions are deeply appreciated!

THNX!
Can you post the code and highlight where the error is occuring? If it's a query not generated via vba, please post that as well so we can see where the parameters are being called.
 

BamaColtsFan

Registered User
Joined
Nov 8, 2006
Messages
91
The actual code that calls the query is very lengthy so I am only posting the offending line. Bassically, any code that calls a select query should do the same thing (I believe, based on my testing). In my case, the select query "MyEmailAddresses" contains a value called LCA Group. In the query criteria is "Like [Enter LCA Group Code for this action:]" which allows the user to specify a LCA Group for the action or to use a wildcard and run the action for all groups. At least that's how it worked before the upgrade. If I take the criteria out of the query, the code executes perfectly. The problem is that I need to allow the user to isolate the group when necessary.

Code:
Set MailList = db.OpenRecordset("MyEmailAddresses")
I know it is usually easier to give advice when you see the entire block of code but it is actually several hundred lines and everything works great except for this one item. If really necessary, I will set up a smaller sample that illustrates the same problem...

THNX!
 

gemma-the-husky

Super Moderator
Staff member
Joined
Sep 12, 2006
Messages
13,873
depends EXACTLY what you are doing, but I think I had the same error the other day, and it took me a long while to sort it (and some suggestrions from Leigh Purvis that finally got me looking at the right thing) - you cant open a recordset if there is a from parameter in it.

ie forms!someform!somefield

you need to change this to read the value from a variable instead.
 

boblarson

Smeghead
Joined
Jan 12, 2001
Messages
32,068
One way is to dynamically create the SQL string in VBA and then open the recordset. The other way is to go through the parameters collection and add them in. I like the build in VBA route as it is simpler in my mind. :)
 

BamaColtsFan

Registered User
Joined
Nov 8, 2006
Messages
91
Okay! Thanks for the suggestions guys but I actually found a solution by proxy! As it happens, the queries I was having trouble with were taking too long to run (lots of data to sort out) so I decided to append to a temp table for processing. Now the queries run with the prompts and the VBA calls from the temp table and everything works great! I think it is related to what Dave said in that the append avoids the "from" call in the actual VBA. And by the way - it runs much faster using the append method!

THNX again!
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom