Solved

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

Posted on 2014-01-21
31
1,362 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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 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 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

623 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