Too Few Parameters in OpenRecordset

[DoveArrow]

When trying to run the following code, I get the error message "Too
few parameters. Expected 2."

Dim db As Database (I've also tried Dim db As DAO.Database, just in
case)
Dim rs As Recordset (I've also tried Dim rs As DAO.Recordset, just in
case)
Dim strSQL As String

strSQL = "SELECT jtblMajorSpecialization.SpecializationCode,
jtblMajorSpecialization.SpecializationRequirement,
jtblMajorSpecialization.[Specialization Description],
jtblMajorSpecialization.SpecializationTypeID FROM tblMajorRequirement
RIGHT JOIN jtblMajorSpecialization ON
tblMajorRequirement.MajorRequirement =
jtblMajorSpecialization.MajorRequirement WHERE
(((jtblMajorSpecialization.SpecializationTypeID)=1 Or
(jtblMajorSpecialization.SpecializationTypeID)=2) AND
((tblMajorRequirement.CatalogYear)=[Forms]![frmProgramChange]!
[CatalogYear]) AND ((tblMajorRequirement.Program)=[Forms]!
[frmProgramChange]![PrimaryMajor]));"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

Now I have tried creating a select query based off of my SQL
statement, and the query runs just fine. However, even if I try to use
my query as the recordset (see below), I still get the same error
message.

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("qselMajorSpecialization", dbOpenDynaset)

If someone can please explain what is happening here, I would very
much appreciate it. Thanks.

 

[Roger Carlson]

Why? Well, it is important to remember that Access is not a single program,
but has separate pieces. There is Access the Graphical User Interface
(GUI) that allows you to create tables, queries, form, reports and such.
Then there is the Jet database engine, which is the SQL interpreter. And
lastly, there is Visual Basic for Applications (VBA) which can include a
variety of components, DAO being one of them.



The Access GUI and SQL work pretty well together. That's why the you can
open it directly in Access.



However, VBA (and particularly DAO) needs some additional information in
order to read the text boxes on the form. You could do this
programmatically by reading the Parameter Collection, but a simpler method
is to simply supply the VALUE of the textbox, rather than a reference to it.
Something like this:



strSQL = "SELECT jtblMajorSpecialization.SpecializationCode,
....
((tblMajorRequirement.CatalogYear)='" & [Forms]![frmProgramChange]!
[CatalogYear] & "') AND ((tblMajorRequirement.Program)= '" & [Forms]!
[frmProgramChange]![PrimaryMajor] & "'));"


The above assumes that CatalogYear and Primary Major are text fields.


On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "TooManyParameters.mdb" which explains the problem in more
detail.



--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

When trying to run the following code, I get the error message "Too
few parameters. Expected 2."

Dim db As Database (I've also tried Dim db As DAO.Database, just in
case)
Dim rs As Recordset (I've also tried Dim rs As DAO.Recordset, just in
case)
Dim strSQL As String

strSQL = "SELECT jtblMajorSpecialization.SpecializationCode,
jtblMajorSpecialization.SpecializationRequirement,
jtblMajorSpecialization.[Specialization Description],
jtblMajorSpecialization.SpecializationTypeID FROM tblMajorRequirement
RIGHT JOIN jtblMajorSpecialization ON
tblMajorRequirement.MajorRequirement =
jtblMajorSpecialization.MajorRequirement WHERE
(((jtblMajorSpecialization.SpecializationTypeID)=1 Or
(jtblMajorSpecialization.SpecializationTypeID)=2) AND
((tblMajorRequirement.CatalogYear)=[Forms]![frmProgramChange]!
[CatalogYear]) AND ((tblMajorRequirement.Program)=[Forms]!
[frmProgramChange]![PrimaryMajor]));"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

Now I have tried creating a select query based off of my SQL
statement, and the query runs just fine. However, even if I try to use
my query as the recordset (see below), I still get the same error
message.

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("qselMajorSpecialization", dbOpenDynaset)

If someone can please explain what is happening here, I would very
much appreciate it. Thanks.

 

[DoveArrow]

Why?  Well, it is important to remember that Access is not a single program,
but has separate pieces.   There is Access the Graphical User Interface
(GUI) that allows you to create tables, queries, form, reports and such.
Then there is the Jet database engine, which is the SQL interpreter.  And
lastly, there is Visual Basic for Applications (VBA) which can include a
variety of components, DAO being one of them.

The Access GUI and SQL work pretty well together.  That's why the you can
open it directly in Access.

However, VBA (and particularly DAO) needs some additional information in
order to read the text boxes on the form.  You could do this
programmatically by reading the Parameter Collection, but a simpler method
is to simply supply the VALUE of the textbox, rather than a reference to it.
Something like this:

strSQL = "SELECT jtblMajorSpecialization.SpecializationCode,
...
 ((tblMajorRequirement.CatalogYear)='" & [Forms]![frmProgramChange]!
 [CatalogYear] & "') AND ((tblMajorRequirement.Program)= '" & [Forms]!
 [frmProgramChange]![PrimaryMajor] & "'));"

The above assumes that CatalogYear and Primary Major are text fields.

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "TooManyParameters.mdb" which explains the problem in more
detail.

--
--Roger Carlson
  MS Access MVP
  Access Database Samples:www.rogersaccesslibrary.com
  Want answers to your Access questions in your Email?
  Free subscription:
 http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

When trying to run the following code, I get the error message "Too
few parameters. Expected 2."

Dim db As Database (I've also tried Dim db As DAO.Database, just in
case)
Dim rs As Recordset (I've also tried Dim rs As DAO.Recordset, just in
case)
Dim strSQL As String

strSQL = "SELECT jtblMajorSpecialization.SpecializationCode,
jtblMajorSpecialization.SpecializationRequirement,
jtblMajorSpecialization.[Specialization Description],
jtblMajorSpecialization.SpecializationTypeID FROM tblMajorRequirement
RIGHT JOIN jtblMajorSpecialization ON
tblMajorRequirement.MajorRequirement =
jtblMajorSpecialization.MajorRequirement WHERE
(((jtblMajorSpecialization.SpecializationTypeID)=1 Or
(jtblMajorSpecialization.SpecializationTypeID)=2) AND
((tblMajorRequirement.CatalogYear)=[Forms]![frmProgramChange]!
[CatalogYear]) AND ((tblMajorRequirement.Program)=[Forms]!
[frmProgramChange]![PrimaryMajor]));"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

Now I have tried creating a select query based off of my SQL
statement, and the query runs just fine. However, even if I try to use
my query as the recordset (see below), I still get the same error
message.

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("qselMajorSpecialization", dbOpenDynaset)

If someone can please explain what is happening here, I would very
much appreciate it. Thanks.- Hide quoted text -

- Show quoted text -

Well, your suggestion here didn't work. However, I checked out your
website, and after taking a look at your TooFewParameters.mdb
database, I decided to go with the following code.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As QueryDef
Dim prm As Parameter
Dim Respons As Integer
Dim strSQL As String

On Error GoTo Err_OK_Click

Set db = CurrentDb

Set qdf = db.QueryDefs("qfltMajorSpecialization")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rs = qdf.OpenRecordset()

Now, it works beautifully.

By the way, I really like your website. Too often, websites like this
use a sentence structure with which I'm not familiar, and I spend just
as much time trying to disassemble their comments as I do trying to
implement their suggestions. Your website is very approachable and
easy to understand. Thank you.

 

[Roger Carlson]

Good. I'm glad it worked for you. And thanks for the nice comments about
my site.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Why? Well, it is important to remember that Access is not a single
program,
but has separate pieces. There is Access the Graphical User Interface
(GUI) that allows you to create tables, queries, form, reports and such.
Then there is the Jet database engine, which is the SQL interpreter. And
lastly, there is Visual Basic for Applications (VBA) which can include a
variety of components, DAO being one of them.

The Access GUI and SQL work pretty well together. That's why the you can
open it directly in Access.

However, VBA (and particularly DAO) needs some additional information in
order to read the text boxes on the form. You could do this
programmatically by reading the Parameter Collection, but a simpler method
is to simply supply the VALUE of the textbox, rather than a reference to
it.
Something like this:

strSQL = "SELECT jtblMajorSpecialization.SpecializationCode,
...
((tblMajorRequirement.CatalogYear)='" & [Forms]![frmProgramChange]!
[CatalogYear] & "') AND ((tblMajorRequirement.Program)= '" & [Forms]!
[frmProgramChange]![PrimaryMajor] & "'));"

The above assumes that CatalogYear and Primary Major are text fields.

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "TooManyParameters.mdb" which explains the problem in more
detail.

--
--Roger Carlson
MS Access MVP
Access Database Samples:www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

When trying to run the following code, I get the error message "Too
few parameters. Expected 2."

Dim db As Database (I've also tried Dim db As DAO.Database, just in
case)
Dim rs As Recordset (I've also tried Dim rs As DAO.Recordset, just in
case)
Dim strSQL As String

strSQL = "SELECT jtblMajorSpecialization.SpecializationCode,
jtblMajorSpecialization.SpecializationRequirement,
jtblMajorSpecialization.[Specialization Description],
jtblMajorSpecialization.SpecializationTypeID FROM tblMajorRequirement
RIGHT JOIN jtblMajorSpecialization ON
tblMajorRequirement.MajorRequirement =
jtblMajorSpecialization.MajorRequirement WHERE
(((jtblMajorSpecialization.SpecializationTypeID)=1 Or
(jtblMajorSpecialization.SpecializationTypeID)=2) AND
((tblMajorRequirement.CatalogYear)=[Forms]![frmProgramChange]!
[CatalogYear]) AND ((tblMajorRequirement.Program)=[Forms]!
[frmProgramChange]![PrimaryMajor]));"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

Now I have tried creating a select query based off of my SQL
statement, and the query runs just fine. However, even if I try to use
my query as the recordset (see below), I still get the same error
message.

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("qselMajorSpecialization", dbOpenDynaset)

If someone can please explain what is happening here, I would very
much appreciate it. Thanks.- Hide quoted text -

- Show quoted text -

Well, your suggestion here didn't work. However, I checked out your
website, and after taking a look at your TooFewParameters.mdb
database, I decided to go with the following code.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As QueryDef
Dim prm As Parameter
Dim Respons As Integer
Dim strSQL As String

On Error GoTo Err_OK_Click

Set db = CurrentDb

Set qdf = db.QueryDefs("qfltMajorSpecialization")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rs = qdf.OpenRecordset()

Now, it works beautifully.

By the way, I really like your website. Too often, websites like this
use a sentence structure with which I'm not familiar, and I spend just
as much time trying to disassemble their comments as I do trying to
implement their suggestions. Your website is very approachable and
easy to understand. Thank you.