Solved

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

Posted on 2014-01-21
31
1,213 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
  • 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
 

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 - Access MVP) earned 500 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 84
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 84
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

863 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now