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

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
shanthi_josephAsked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Can you post the SQL for SelectChampionEmailQry ?
0
 
shanthi_josephAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
 
shanthi_josephAuthor Commented:
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
 
shanthi_josephAuthor Commented:
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
 
shanthi_josephAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
 
shanthi_josephAuthor Commented:
I obtain the error "UNKNOWN" for all the queries. I also get the error when I try to open the form.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You don't need double quotes around the form reference:

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

Should be

Eval([Forms]![MainSwitchboard]![PeriodTo]))
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
Dale FyeCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Eval() avoids all of that ..
0
 
shanthi_josephAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
 
shanthi_josephAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Interesting ... not sure why that was ... but hey ...

mx
0
 
shanthi_josephAuthor Commented:
I do not require a solution to this problem.
0
 
shanthi_josephAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
A working solution was given ...

mx
0
 
shanthi_josephAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
???
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
?
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
thx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.