Solved

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

Posted on 2014-01-21
31
1,177 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

705 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

16 Experts available now in Live!

Get 1:1 Help Now