Too few Parameter. Expected 1

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to get records from query and populate them to a table. I just used
that code below but in the 6th line "Set rs2 =
db.OpenRecordset("CoursesQuery2")" program gives that error. Can somebody
explain how can I fix this problem?

Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Set db = CurrentDb
Set rs1 = db.OpenRecordset("ChangeMajorTemp")
Set rs2 = db.OpenRecordset("CoursesQuery2")
For Each Item In rs2
rs1.MoveLast
rs1.AddNew
rs1!ChangeInMajor = rs2!CourseClassifications
rs1.Update
Next
 
Your query has a parameter in it. You need to set the value of the parameter
to open it as a recordset.

Set qdfActual = dbs.QueryDefs("qselProjectChartsActual")
qdfActual.Parameters(0) = rstmaster![Mactivity]
Set rstActual = qdfActual.OpenRecordset(dbOpenSnapshot, dbReadOnly)

Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim qdf2 as QueryDef

Set db = CurrentDb
Set rs1 = db.OpenRecordset("ChangeMajorTemp")
Set qdf2 = db.QueryDefs("CoursesQuery2")
qdf2.Parameters(0) = 'Whatever your filter value is
Set rst2 = qdf2.OpenRecordset
For Each Item In rs2
rs1.MoveLast <---This Line is unnecessary and only slows down the app
rs1.AddNew
rs1!ChangeInMajor = rs2!CourseClassifications
rs1.Update
Next
 
Thank you for your solution. If you dont mind I have one more question. I
have a form and subform which is not related each other. My sub form has a
two field and one is combobox (look up). I also have a combobox in the main
form and when I change it I want to change the content of my subcombobox's
content. I tried requery method but it didnt work..Can you pleeeeeaseeee help
me..

--
Thanks


Klatuu said:
Your query has a parameter in it. You need to set the value of the parameter
to open it as a recordset.

Set qdfActual = dbs.QueryDefs("qselProjectChartsActual")
qdfActual.Parameters(0) = rstmaster![Mactivity]
Set rstActual = qdfActual.OpenRecordset(dbOpenSnapshot, dbReadOnly)

Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim qdf2 as QueryDef

Set db = CurrentDb
Set rs1 = db.OpenRecordset("ChangeMajorTemp")
Set qdf2 = db.QueryDefs("CoursesQuery2")
qdf2.Parameters(0) = 'Whatever your filter value is
Set rst2 = qdf2.OpenRecordset
For Each Item In rs2
rs1.MoveLast <---This Line is unnecessary and only slows down the app
rs1.AddNew
rs1!ChangeInMajor = rs2!CourseClassifications
rs1.Update
Next



Appache said:
I want to get records from query and populate them to a table. I just used
that code below but in the 6th line "Set rs2 =
db.OpenRecordset("CoursesQuery2")" program gives that error. Can somebody
explain how can I fix this problem?

Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Set db = CurrentDb
Set rs1 = db.OpenRecordset("ChangeMajorTemp")
Set rs2 = db.OpenRecordset("CoursesQuery2")
For Each Item In rs2
rs1.MoveLast
rs1.AddNew
rs1!ChangeInMajor = rs2!CourseClassifications
rs1.Update
Next
Click to expand...
Click to expand...
 
line "Set rs2 =
db.OpenRecordset("CoursesQuery2")" program gives that error.
Click to expand...

What is the text of the querydef SQL? There are two likely errors:

1 A misspelled field name -- if Access cannot identify a column name, it
assumes it is a parameter that has not been supplied. The query should
fail when run from the UI.

2 A reference to a UI object, such as forms!MyForm!MyControl, a common
way of allowing a user to provide data to a parameterised query. When the
query is opened in the UI, the expression evaluator can find the
appropriate object and pass in the data. When the query is passed
straight through to the dbengine, however, (e.g. by using DAO) the
expression evaluator is bypassed and the jet engine itself knows nothing
about Access objects. The answer for that is to provide the data
yourself:

set qdf = querydefs("CoursesQuery2")
qdf.Parameters("forms!MyForm!MyControl") = _
forms!MyForm!MyControl.Value
set rs = qdf.OpenRecordset(dbOpenSnapshot, dbForwardOnly)
' etc etc


Hope that helps


Tim F
 
If you mean you want to requery the subforms row source based on what is in
the form's combo then in the form's combo After Update
Me.MySubFormControlName.SubFormComboName.Requery
Note that MySubFormControlName is the name of the subform control on your
form, not the name of the subform itself.

If you mean you want to change what is displayed in the subform's combo to
be the same as what is selected in the form's combo:
Me.MySubFormControlName.SubFormComboName = Me.FormCombo


Appache said:
Thank you for your solution. If you dont mind I have one more question. I
have a form and subform which is not related each other. My sub form has a
two field and one is combobox (look up). I also have a combobox in the main
form and when I change it I want to change the content of my subcombobox's
content. I tried requery method but it didnt work..Can you pleeeeeaseeee help
me..

--
Thanks


Klatuu said:
Your query has a parameter in it. You need to set the value of the parameter
to open it as a recordset.

Set qdfActual = dbs.QueryDefs("qselProjectChartsActual")
qdfActual.Parameters(0) = rstmaster![Mactivity]
Set rstActual = qdfActual.OpenRecordset(dbOpenSnapshot, dbReadOnly)

Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim qdf2 as QueryDef

Set db = CurrentDb
Set rs1 = db.OpenRecordset("ChangeMajorTemp")
Set qdf2 = db.QueryDefs("CoursesQuery2")
qdf2.Parameters(0) = 'Whatever your filter value is
Set rst2 = qdf2.OpenRecordset
For Each Item In rs2
rs1.MoveLast <---This Line is unnecessary and only slows down the app
rs1.AddNew
rs1!ChangeInMajor = rs2!CourseClassifications
rs1.Update
Next



Appache said:
I want to get records from query and populate them to a table. I just used
that code below but in the 6th line "Set rs2 =
db.OpenRecordset("CoursesQuery2")" program gives that error. Can somebody
explain how can I fix this problem?

Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Set db = CurrentDb
Set rs1 = db.OpenRecordset("ChangeMajorTemp")
Set rs2 = db.OpenRecordset("CoursesQuery2")
For Each Item In rs2
rs1.MoveLast
rs1.AddNew
rs1!ChangeInMajor = rs2!CourseClassifications
rs1.Update
Next
Click to expand...
Click to expand...
Click to expand...
 
I want to requery the subforms row source based on what is in
the form's combo but I didnt understand the meaning of what you said
"Me.MySubFormControlName.SubFormComboName.Requery. Note that
MySubFormControlName is the name of the subform control on your
form, not the name of the subform itself" My subform's name is
ChangeMajorTempSub and the name of my subcombo is NewMajorCourses and my
Mainformcombobox is NewMajor. And what I want to do is
Me.ChangemajorTempSub.NewMajorCourses.Requery but after I write
Me.ChangeMajorTempSub and add . the menu shows properties and methods doesnt
show up. That is my problem :(:( please help..


--
Thanks


Klatuu said:
If you mean you want to requery the subforms row source based on what is in
the form's combo then in the form's combo After Update
Me.MySubFormControlName.SubFormComboName.Requery
Note that MySubFormControlName is the name of the subform control on your
form, not the name of the subform itself.

If you mean you want to change what is displayed in the subform's combo to
be the same as what is selected in the form's combo:
Me.MySubFormControlName.SubFormComboName = Me.FormCombo


Appache said:
Thank you for your solution. If you dont mind I have one more question. I
have a form and subform which is not related each other. My sub form has a
two field and one is combobox (look up). I also have a combobox in the main
form and when I change it I want to change the content of my subcombobox's
content. I tried requery method but it didnt work..Can you pleeeeeaseeee help
me..

--
Thanks


Klatuu said:
Your query has a parameter in it. You need to set the value of the parameter
to open it as a recordset.

Set qdfActual = dbs.QueryDefs("qselProjectChartsActual")
qdfActual.Parameters(0) = rstmaster![Mactivity]
Set rstActual = qdfActual.OpenRecordset(dbOpenSnapshot, dbReadOnly)

Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim qdf2 as QueryDef

Set db = CurrentDb
Set rs1 = db.OpenRecordset("ChangeMajorTemp")
Set qdf2 = db.QueryDefs("CoursesQuery2")
qdf2.Parameters(0) = 'Whatever your filter value is
Set rst2 = qdf2.OpenRecordset
For Each Item In rs2
rs1.MoveLast <---This Line is unnecessary and only slows down the app
rs1.AddNew
rs1!ChangeInMajor = rs2!CourseClassifications
rs1.Update
Next



:

I want to get records from query and populate them to a table. I just used
that code below but in the 6th line "Set rs2 =
db.OpenRecordset("CoursesQuery2")" program gives that error. Can somebody
explain how can I fix this problem?

Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Set db = CurrentDb
Set rs1 = db.OpenRecordset("ChangeMajorTemp")
Set rs2 = db.OpenRecordset("CoursesQuery2")
For Each Item In rs2
rs1.MoveLast
rs1.AddNew
rs1!ChangeInMajor = rs2!CourseClassifications
rs1.Update
Next
Click to expand...
Click to expand...
Click to expand...
 
Actually I dont have any sql string. I am not very familiar with writing
program so I created a query in Access and used a parameter
([Forms]![ChangeMajor]![NewMajor] It is the name of my MainFormCombobox) Do
you thing I should do it that with program code? if so how? can you send an
example of that?
 
I understand what you are trying to do. What I was trying to point out is
the propert syntax to address objects in a sub form from the mainform. When
you address the subform, you don't use the name of the subform. You use the
name of the subform control on your main form. They may or may not be the
same. For example, I have a form where the subform control is used for five
differnt sub forms. The user has five command buttons to select the subform
to show in the subform control. When they click a button, the code changes
the subform control's source object property, but regardless of which subform
is the source object, I still address the controls using the subform
control's name. So the syntax is

NameOfMainForm!NameOfSubFormControl!NameOfControl.Method

The intellisense that pops up the properties and methods may not bring them
up when you go to the subform control, because it doesn't know what objects
exist in the control.

Appache said:
I want to requery the subforms row source based on what is in
the form's combo but I didnt understand the meaning of what you said
"Me.MySubFormControlName.SubFormComboName.Requery. Note that
MySubFormControlName is the name of the subform control on your
form, not the name of the subform itself" My subform's name is
ChangeMajorTempSub and the name of my subcombo is NewMajorCourses and my
Mainformcombobox is NewMajor. And what I want to do is
Me.ChangemajorTempSub.NewMajorCourses.Requery but after I write
Me.ChangeMajorTempSub and add . the menu shows properties and methods doesnt
show up. That is my problem :(:( please help..


--
Thanks


Klatuu said:
If you mean you want to requery the subforms row source based on what is in
the form's combo then in the form's combo After Update
Me.MySubFormControlName.SubFormComboName.Requery
Note that MySubFormControlName is the name of the subform control on your
form, not the name of the subform itself.

If you mean you want to change what is displayed in the subform's combo to
be the same as what is selected in the form's combo:
Me.MySubFormControlName.SubFormComboName = Me.FormCombo


Appache said:
Thank you for your solution. If you dont mind I have one more question. I
have a form and subform which is not related each other. My sub form has a
two field and one is combobox (look up). I also have a combobox in the main
form and when I change it I want to change the content of my subcombobox's
content. I tried requery method but it didnt work..Can you pleeeeeaseeee help
me..

--
Thanks


:

Your query has a parameter in it. You need to set the value of the parameter
to open it as a recordset.

Set qdfActual = dbs.QueryDefs("qselProjectChartsActual")
qdfActual.Parameters(0) = rstmaster![Mactivity]
Set rstActual = qdfActual.OpenRecordset(dbOpenSnapshot, dbReadOnly)

Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim qdf2 as QueryDef

Set db = CurrentDb
Set rs1 = db.OpenRecordset("ChangeMajorTemp")
Set qdf2 = db.QueryDefs("CoursesQuery2")
qdf2.Parameters(0) = 'Whatever your filter value is
Set rst2 = qdf2.OpenRecordset
For Each Item In rs2
rs1.MoveLast <---This Line is unnecessary and only slows down the app
rs1.AddNew
rs1!ChangeInMajor = rs2!CourseClassifications
rs1.Update
Next



:

I want to get records from query and populate them to a table. I just used
that code below but in the 6th line "Set rs2 =
db.OpenRecordset("CoursesQuery2")" program gives that error. Can somebody
explain how can I fix this problem?

Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Set db = CurrentDb
Set rs1 = db.OpenRecordset("ChangeMajorTemp")
Set rs2 = db.OpenRecordset("CoursesQuery2")
For Each Item In rs2
rs1.MoveLast
rs1.AddNew
rs1!ChangeInMajor = rs2!CourseClassifications
rs1.Update
Next
Click to expand...
Click to expand...
Click to expand...
 
Actually I dont have any sql string. I am not very familiar with
writing program so I created a query
Click to expand...

A querydef in Access is just a wrapper round a plain old SQL command. In
the query design view, select View | SQL and you'll see it.
in Access and used a parameter
([Forms]![ChangeMajor]![NewMajor] It is the name of my
MainFormCombobox) Do you thing I should do it that with program code?
if so how? can you send an example of that?
Click to expand...

It's exactly what I suggested up-thread...

dim qdf as dao.querydef

set qdf = querydefs("CoursesQuery2")
qdf.Parameters("[Forms]![ChangeMajor]![NewMajor]") = _
[Forms]![ChangeMajor]![NewMajor].Value
set rs2 = qdf.OpenRecordset(dbOpenSnapshot, dbForwardOnly)
' etc etc

All the best

Tim F
 
Back
Top