Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Obtain error 'Too few parameters. Expected 3.' when using OpenRecordset command

Posted on 2014-01-21
31
Medium Priority
?
1,437 Views
Last Modified: 2014-05-11
I obtain the error
'Too few parameters. Expected 3.'
when executing the following command
CurrentDb.OpenRecordset("SelectChampionEmailQry", adOpenDynamic, adLockReadOnly) in the VBA code in MS Access 2010
0
Comment
Question by:shanthi_joseph
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 10
  • 2
  • +1
31 Comments
 
LVL 75
ID: 39799012
Can you post the SQL for SelectChampionEmailQry ?
0
 

Author Comment

by:shanthi_joseph
ID: 39799026
Here is the SQL for SelectChampionEmailQry :

SELECT DISTINCT  Champion2 as Champion FROM SelectChampion2EmailQry
UNION SELECT DISTINCT  Champion3  as Champion FROM SelectChampion3EmailQry
UNION SELECT DISTINCT Champion4  as Champion FROM SelectChampion4EmailQry;
0
 
LVL 75
ID: 39799049
Well, ok but ... that's just a bunch of other queries.
In those queries, are you referencing a Form control?
If so, post the SQL for those queries.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:shanthi_joseph
ID: 39799056
Here is the SQL for the SelectChampion2EmailQry:

SELECT DISTINCT tblRegister.QSIAssignChampion2 AS Champion2, tblRegister.QSIID
FROM tblRegister INNER JOIN tblPlants ON tblRegister.Site = tblPlants.PlantName
WHERE (((tblRegister.QSIAssignChampion2) Is Not Null And (tblRegister.QSIAssignChampion2)<>"-") AND ((tblRegister.QSICreatedTimestamp)<[Forms]![MainSwitchboard]![PeriodTo]) AND ((tblPlants.RegionCode) Like [Forms]![MainSwitchboard]![SelectRegion]) AND ((tblPlants.CellCode) Like [Forms]![MainSwitchboard]![SelectCell]) AND ((tblRegister.QSIStage)<9))
ORDER BY tblRegister.QSIAssignChampion2;

It does reference the Form controls
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 2000 total points
ID: 39799062
ok ... good.
Every place you have a Form reference like this in any of the queries:

 [Forms]![MainSwitchboard]![SelectRegion]

Do this - wrap in the Eval() function like so:

Eval("[Forms]![MainSwitchboard]![SelectRegion]")

... including the double quotes.
0
 

Author Comment

by:shanthi_joseph
ID: 39799085
I obtain the error: UNKNOWN after changing all the Form references

Here are the modified queries:

SELECT DISTINCT  Champion2 as Champion FROM SelectChampion2EmailQry
UNION SELECT DISTINCT  Champion3  as Champion FROM SelectChampion3EmailQry
UNION SELECT DISTINCT Champion4  as Champion FROM SelectChampion4EmailQry;

SelectChampion2EmailQry
----------------------------------
SELECT DISTINCT tblRegister.QSIAssignChampion2
FROM tblRegister INNER JOIN tblPlants ON tblRegister.Site = tblPlants.PlantName
WHERE (((tblRegister.QSIAssignChampion2) Is Not Null And (tblRegister.QSIAssignChampion2)<>"-") AND ((tblPlants.RegionCode) Like [Forms]![MainSwitchboard]![SelectRegion]) AND ((tblPlants.CellCode) Like [Forms]![MainSwitchboard]![SelectCell]) AND ((tblRegister.QSIStage)<10) AND ((tblRegister.Site) Like [Forms]![MainSwitchboard]![SelectPlant]))
ORDER BY tblRegister.QSIAssignChampion2;

SelectChampion3EmailQry
----------------------------------
SELECT DISTINCT tblRegister.QSIAssignChampion3 AS Champion3, tblRegister.QSIID
FROM tblRegister INNER JOIN tblPlants ON tblRegister.Site = tblPlants.PlantName
WHERE (((tblRegister.QSIAssignChampion3) Is Not Null And (tblRegister.QSIAssignChampion3)<>"-") AND ((tblRegister.QSICreatedTimestamp)<Eval("[Forms]![MainSwitchboard]![PeriodTo]")) AND ((tblPlants.RegionCode) Like Eval("[Forms]![MainSwitchboard]![SelectRegion]")) AND ((tblPlants.CellCode) Like Eval("[Forms]![MainSwitchboard]![SelectCell]")) AND ((tblRegister.QSIStage)<9))
ORDER BY tblRegister.QSIAssignChampion3;

SelectChampion4EmailQry
----------------------------------
SELECT DISTINCT tblRegister.QSIAssignChampion4 AS Champion4, tblRegister.QSIID
FROM tblRegister INNER JOIN tblPlants ON tblRegister.Site = tblPlants.PlantName
WHERE (((tblRegister.QSIAssignChampion4) Is Not Null And (tblRegister.QSIAssignChampion4)<>"-") AND ((tblRegister.QSICreatedTimestamp)<Eval("[Forms]![MainSwitchboard]![PeriodTo]")) AND ((tblPlants.RegionCode) Like Eval("[Forms]![MainSwitchboard]![SelectRegion]")) AND ((tblPlants.CellCode) Like Eval("[Forms]![MainSwitchboard]![SelectCell]")) AND ((tblRegister.QSIStage)<9))
ORDER BY tblRegister.QSIAssignChampion4;
0
 

Author Comment

by:shanthi_joseph
ID: 39799087
Sorry, here is the correct SelectChampion2EmailQry:

SELECT DISTINCT tblRegister.QSIAssignChampion2 AS Champion2, tblRegister.QSIID
FROM tblRegister INNER JOIN tblPlants ON tblRegister.Site = tblPlants.PlantName
WHERE (((tblRegister.QSIAssignChampion2) Is Not Null And (tblRegister.QSIAssignChampion2)<>"-") AND ((tblRegister.QSICreatedTimestamp)<Eval("[Forms]![MainSwitchboard]![PeriodTo]")) AND ((tblPlants.RegionCode) Like Eval("[Forms]![MainSwitchboard]![SelectRegion]")) AND ((tblPlants.CellCode) Like Eval("[Forms]![MainSwitchboard]![SelectCell]")) AND ((tblRegister.QSIStage)<9))
ORDER BY tblRegister.QSIAssignChampion2;
0
 
LVL 75
ID: 39799094
OK ... let's break it down.
Instead of running the entire Union query, try running each query separately first - see what you get. Is just one query causing the issue for example?

Be *sure* of course the Form(s) is Open :-)

mx
0
 

Author Comment

by:shanthi_joseph
ID: 39799098
I obtain the error "UNKNOWN" for all the queries. I also get the error when I try to open the form.
0
 
LVL 75
ID: 39799123
The code in the original post ... where/when is that code executing?

For now try changing
CurrentDb.OpenRecordset("SelectChampionEmailQry", adOpenDynamic, adLockReadOnly)

to

CurrentDb.OpenRecordset("SelectChampionEmailQry"), dbFailOnError

mx
0
 
LVL 85
ID: 39799577
You don't need double quotes around the form reference:

Eval("[Forms]![MainSwitchboard]![PeriodTo]"))

Should be

Eval([Forms]![MainSwitchboard]![PeriodTo]))
0
 
LVL 75
ID: 39800615
I'm pretty sure you do need the Double quotes.  Eval() requires a String argument and I don't think the Forms reference by itself evaluates to a String.
I've always used double quote in this case and it's always worked.

I think something else is going on ...

mx
0
 
LVL 85
ID: 39801479
I'm curious if the EVAL function is evaluating the value of PeriodTo (which would be correct) or evaluation the string "[Forms]![MainSwitchboard]![PeriodTo]" (which would not be correct).

I don't use EVAL, so don't really know.
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 39801607
Personally, I generally declare those form references as parameters.  

They will probably need to be declared in the original queries and also in the main query.
0
 
LVL 75
ID: 39801631
Eval() avoids all of that ..
0
 

Author Comment

by:shanthi_joseph
ID: 39801709
It is working correctly when I use
Set rsEmailChampions = CurrentDb.OpenRecordset("SelectChampionEmailQry")

I have used Eval with double quotes. eg. Eval("[Forms]![MainSwitchboard]![SelectRegion]"))
0
 
LVL 75
ID: 39801743
I think your issue was using adOpenDynamic, adLockReadOnly.

The recordset is going to be Read Only anyway because it's a Union query.

adOpenDynamic - I've never used that - not sure what it's for.

Anyway, I would say you are good to go ...

mx
0
 
LVL 75
ID: 39801745
btw ...
The reason for the Too Few Parameters error in this case (and others) is because ... JET, which does not 'see' your Forms  (in this case  Forms!SomeFormName.SomeControlName).   Consequently, it does not know what Forms!SomeFormName.SomeControlName is, resulting in the Parameter error message ... just like if you were running a SELECT query, and misspelled a Form or Field name used in a criteria expression.
JET is involved of course in the OpenRecordset command ...
0
 

Author Comment

by:shanthi_joseph
ID: 39801844
I needed to restart MS Access for the changes to work correctly. Yesterday, the changes did not work, but today, when I logged in and started MS Access, it worked correctly.
0
 
LVL 75
ID: 39802190
Interesting ... not sure why that was ... but hey ...

mx
0
 

Author Comment

by:shanthi_joseph
ID: 40033824
I do not require a solution to this problem.
0
 

Author Comment

by:shanthi_joseph
ID: 40033909
I've requested that this question be closed as follows:

Accepted answer: 0 points for shanthi_joseph's comment #a40033824

for the following reason:

I do not require a solution to this problem.
0
 
LVL 75
ID: 40033910
A working solution was given ...

mx
0
 

Author Comment

by:shanthi_joseph
ID: 40041328
I've requested that this question be closed as follows:

Accepted answer: 0 points for shanthi_joseph's comment #a39801844

for the following reason:

I have selected the solution.
0
 
LVL 75
ID: 40041329
???
0
 
LVL 75
ID: 40056499
?
0
 
LVL 75
ID: 40057493
thx
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question