Too few parameters

  • Thread starter Thread starter Max
  • Start date Start date
M

Max

I am experimenting with recordsets. I have cut the recordcount example that
is in help and pasted it into the on click event of my form. I had to change
the query name in the strsql. I am getting an error
"Too few parameters. expected 4". Below is my code.

Private Sub Command0_Click()
Dim dbs As Database, rst As Recordset
Dim strSQL As String

' Return reference to current database.
Set dbs = CurrentDb
strSQL = "SELECT qry_Orders* FROM qry_Orders"
Set rst = dbs.OpenRecordset(strSQL)
rst.MoveLast
Debug.Print rst.RecordCount
rst.Close
Set dbs = Nothing
End Sub
 
Specify the type of recordset, i.e.:
rst As DAO.Recordset

Include the dot:
strSQL = "SELECT qry_Orders.* FROM qry_Orders;"
 
Thanks Allen,
I Am still getting the same result. If the query was a union query would
that make a difference?
 
Post the SQL of the query ... it appears that the query has parameters in
it.
 
PARAMETERS [txb_MonthstartDate] DateTime, [txb_MonthEndDate] DateTime;
SELECT Meetings.MeetingID, Meetings.ClientID, Meetings.EmployeeID,
Meetings.StartRecurRange, Meetings.EndRecurRange,
nz([Meetings].[endrecurrange],#12/31/9999#) AS ActualEndRecurRange
FROM Meetings
WHERE (((Meetings.ClientID)=[cbo_ClientName]) AND
((Meetings.StartRecurRange)<=[txb_MonthEndDate]) AND
((nz([Meetings].[endrecurrange],#12/31/9999#))>=[txb_MonthStartDate])) OR
(((Meetings.EmployeeID)=[cbo_EmployeeName]));
 
There are 4 criteria in Qry_Orders. The OpenRecordset method requires that
you explicity define those parameters. Open Qry_Orders in design view, right
click in the query window and open the Parameters dialog box. Enter the four
parameters there exactly as they appear in the criteria, ie:
Parameter Data Type
Forms!MyForm!MyField Text
<For all four>
 
Marie, Thanks for the help
My criteria in the query is:
PARAMETERS [txb_MonthstartDate] DateTime, [txb_MonthEndDate] DateTime,
[cbo_ClientName] IEEEDouble, [cbo_EmployeeName] IEEEDouble;

Do I declare these in my commmand button code
Dim txb_MonthStartDate as Date etc...
or is there another way to set these parameters
Im Confused....please explain
 
Your query has four parameters. In order to have your code open this query,
you must tell it what the values of those parameters are. This is done
fairly easily by changing your code to this:


Private Sub Command0_Click()
Dim dbs As DAO.Database, rst As DAO.Recordset
Dim qdf As DAO.QueryDef, prm As DAO.Parameter
' Return reference to current database.
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qry_Orders")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset()
rst.MoveLast
Debug.Print rst.RecordCount
rst.Close
Set dbs = Nothing
End Sub

--

Ken Snell
<MS ACCESS MVP>



Max said:
PARAMETERS [txb_MonthstartDate] DateTime, [txb_MonthEndDate] DateTime;
SELECT Meetings.MeetingID, Meetings.ClientID, Meetings.EmployeeID,
Meetings.StartRecurRange, Meetings.EndRecurRange,
nz([Meetings].[endrecurrange],#12/31/9999#) AS ActualEndRecurRange
FROM Meetings
WHERE (((Meetings.ClientID)=[cbo_ClientName]) AND
((Meetings.StartRecurRange)<=[txb_MonthEndDate]) AND
((nz([Meetings].[endrecurrange],#12/31/9999#))>=[txb_MonthStartDate])) OR
(((Meetings.EmployeeID)=[cbo_EmployeeName]));




Ken Snell said:
Post the SQL of the query ... it appears that the query has parameters in
it.
Click to expand...
Click to expand...
 
Ken, Error
MS Access cant find the name txb_MonthStartDate you entered in the
expression.
This is beyond me...but I am paying attention!


Ken Snell said:
Your query has four parameters. In order to have your code open this query,
you must tell it what the values of those parameters are. This is done
fairly easily by changing your code to this:


Private Sub Command0_Click()
Dim dbs As DAO.Database, rst As DAO.Recordset
Dim qdf As DAO.QueryDef, prm As DAO.Parameter
' Return reference to current database.
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qry_Orders")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset()
rst.MoveLast
Debug.Print rst.RecordCount
rst.Close
Set dbs = Nothing
End Sub

--

Ken Snell
<MS ACCESS MVP>



Max said:
PARAMETERS [txb_MonthstartDate] DateTime, [txb_MonthEndDate] DateTime;
SELECT Meetings.MeetingID, Meetings.ClientID, Meetings.EmployeeID,
Meetings.StartRecurRange, Meetings.EndRecurRange,
nz([Meetings].[endrecurrange],#12/31/9999#) AS ActualEndRecurRange
FROM Meetings
WHERE (((Meetings.ClientID)=[cbo_ClientName]) AND
((Meetings.StartRecurRange)<=[txb_MonthEndDate]) AND
((nz([Meetings].[endrecurrange],#12/31/9999#))>=[txb_MonthStartDate])) OR
(((Meetings.EmployeeID)=[cbo_EmployeeName]));




Ken Snell said:
Post the SQL of the query ... it appears that the query has parameters in
it.

--

Ken Snell
<MS ACCESS MVP>

Thanks Allen,
I Am still getting the same result. If the query was a union query would
that make a difference?

Specify the type of recordset, i.e.:
rst As DAO.Recordset

Include the dot:
strSQL = "SELECT qry_Orders.* FROM qry_Orders;"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am experimenting with recordsets. I have cut the recordcount example
that
is in help and pasted it into the on click event of my form. I
Click to expand...
had
to
change
the query name in the strsql. I am getting an error
"Too few parameters. expected 4". Below is my code.

Private Sub Command0_Click()
Dim dbs As Database, rst As Recordset
Dim strSQL As String

' Return reference to current database.
Set dbs = CurrentDb
strSQL = "SELECT qry_Orders* FROM qry_Orders"
Set rst = dbs.OpenRecordset(strSQL)
rst.MoveLast
Debug.Print rst.RecordCount
rst.Close
Set dbs = Nothing
End Sub
Click to expand...
Click to expand...
Click to expand...
 
Max,

Follow my instructions in my previous post ---

Open Qry_Orders in design view, right click in the query window and open the
Parameters dialog box. Enter the
four parameters there exactly as they appear in the criteria, ie:
Parameter Data Type
[cbo_ClientName] Text
[txb_MonthEndDate] Date/Time
[txb_MonthStartDate] Date/Time
[cbo_EmployeeName] Text

Marie

Max said:
Marie, Thanks for the help
My criteria in the query is:
PARAMETERS [txb_MonthstartDate] DateTime, [txb_MonthEndDate] DateTime,
[cbo_ClientName] IEEEDouble, [cbo_EmployeeName] IEEEDouble;

Do I declare these in my commmand button code
Dim txb_MonthStartDate as Date etc...
or is there another way to set these parameters
Im Confused....please explain


Marie said:
There are 4 criteria in Qry_Orders. The OpenRecordset method requires that
you explicity define those parameters. Open Qry_Orders in design view, right
click in the query window and open the Parameters dialog box. Enter the four
parameters there exactly as they appear in the criteria, ie:
Parameter Data Type
Forms!MyForm!MyField Text
<For all four>


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com



example
that
Click to expand...
Click to expand...
 
That means that ACCESS cannot find a field or a control on your current form
(the form that is running this code) named txb_MonthStartDate. Check for
typos or changes to field/control names.

--

Ken Snell
<MS ACCESS MVP>

Max said:
Ken, Error
MS Access cant find the name txb_MonthStartDate you entered in the
expression.
This is beyond me...but I am paying attention!


Ken Snell said:
Your query has four parameters. In order to have your code open this query,
you must tell it what the values of those parameters are. This is done
fairly easily by changing your code to this:


Private Sub Command0_Click()
Dim dbs As DAO.Database, rst As DAO.Recordset
Dim qdf As DAO.QueryDef, prm As DAO.Parameter
' Return reference to current database.
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qry_Orders")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset()
rst.MoveLast
Debug.Print rst.RecordCount
rst.Close
Set dbs = Nothing
End Sub

--

Ken Snell
<MS ACCESS MVP>



Max said:
PARAMETERS [txb_MonthstartDate] DateTime, [txb_MonthEndDate] DateTime;
SELECT Meetings.MeetingID, Meetings.ClientID, Meetings.EmployeeID,
Meetings.StartRecurRange, Meetings.EndRecurRange,
nz([Meetings].[endrecurrange],#12/31/9999#) AS ActualEndRecurRange
FROM Meetings
WHERE (((Meetings.ClientID)=[cbo_ClientName]) AND
((Meetings.StartRecurRange)<=[txb_MonthEndDate]) AND
((nz([Meetings].[endrecurrange],#12/31/9999#))>=[txb_MonthStartDate])) OR
(((Meetings.EmployeeID)=[cbo_EmployeeName]));




Post the SQL of the query ... it appears that the query has
Click to expand...
parameters
in
it.

--

Ken Snell
<MS ACCESS MVP>

Thanks Allen,
I Am still getting the same result. If the query was a union query
would
that make a difference?

Specify the type of recordset, i.e.:
rst As DAO.Recordset

Include the dot:
strSQL = "SELECT qry_Orders.* FROM qry_Orders;"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am experimenting with recordsets. I have cut the recordcount
example
that
is in help and pasted it into the on click event of my form. I had
to
change
the query name in the strsql. I am getting an error
"Too few parameters. expected 4". Below is my code.

Private Sub Command0_Click()
Dim dbs As Database, rst As Recordset
Dim strSQL As String

' Return reference to current database.
Set dbs = CurrentDb
strSQL = "SELECT qry_Orders* FROM qry_Orders"
Set rst = dbs.OpenRecordset(strSQL)
rst.MoveLast
Debug.Print rst.RecordCount
rst.Close
Set dbs = Nothing
End Sub
Click to expand...
Click to expand...
Click to expand...
 
Ken,
Thanks for the help on this problem. Everything is working like I need
except one minor problem. When the recordset is empty (no records) I am
getting a runtime error 3021 "no current record". I have tried to place the
code DoCmd.SetWarnings False at the beginning of the code and returning it
to true at the end of your code. Still errors. I must be setting these
warnings incorrectly. Help??

Ken Snell said:
That means that ACCESS cannot find a field or a control on your current form
(the form that is running this code) named txb_MonthStartDate. Check for
typos or changes to field/control names.

--

Ken Snell
<MS ACCESS MVP>

Max said:
Ken, Error
MS Access cant find the name txb_MonthStartDate you entered in the
expression.
This is beyond me...but I am paying attention!


Ken Snell said:
Your query has four parameters. In order to have your code open this query,
you must tell it what the values of those parameters are. This is done
fairly easily by changing your code to this:


Private Sub Command0_Click()
Dim dbs As DAO.Database, rst As DAO.Recordset
Dim qdf As DAO.QueryDef, prm As DAO.Parameter
' Return reference to current database.
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qry_Orders")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset()
rst.MoveLast
Debug.Print rst.RecordCount
rst.Close
Set dbs = Nothing
End Sub

--

Ken Snell
<MS ACCESS MVP>



PARAMETERS [txb_MonthstartDate] DateTime, [txb_MonthEndDate] DateTime;
SELECT Meetings.MeetingID, Meetings.ClientID, Meetings.EmployeeID,
Meetings.StartRecurRange, Meetings.EndRecurRange,
nz([Meetings].[endrecurrange],#12/31/9999#) AS ActualEndRecurRange
FROM Meetings
WHERE (((Meetings.ClientID)=[cbo_ClientName]) AND
((Meetings.StartRecurRange)<=[txb_MonthEndDate]) AND
Click to expand...
((nz([Meetings].[endrecurrange],#12/31/9999#))>=[txb_MonthStartDate]))
OR
(((Meetings.EmployeeID)=[cbo_EmployeeName]));




Post the SQL of the query ... it appears that the query has parameters
in
it.

--

Ken Snell
<MS ACCESS MVP>

Thanks Allen,
I Am still getting the same result. If the query was a union query
would
that make a difference?

Specify the type of recordset, i.e.:
rst As DAO.Recordset

Include the dot:
strSQL = "SELECT qry_Orders.* FROM qry_Orders;"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am experimenting with recordsets. I have cut the recordcount
example
that
is in help and pasted it into the on click event of my form.
Click to expand...
I
had
to
change
the query name in the strsql. I am getting an error
"Too few parameters. expected 4". Below is my code.

Private Sub Command0_Click()
Dim dbs As Database, rst As Recordset
Dim strSQL As String

' Return reference to current database.
Set dbs = CurrentDb
strSQL = "SELECT qry_Orders* FROM qry_Orders"
Set rst = dbs.OpenRecordset(strSQL)
rst.MoveLast
Debug.Print rst.RecordCount
rst.Close
Set dbs = Nothing
End Sub
Click to expand...
Click to expand...
Click to expand...
 
You can test for this when you first open the recordset:

Private Sub Command0_Click()
Dim dbs As DAO.Database, rst As DAO.Recordset
Dim qdf As DAO.QueryDef, prm As DAO.Parameter
' Return reference to current database.
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qry_Orders")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset()
' If both the end of file (EOF) and begin of file (BOF)
' are False, there is at least one record in the recordset.
If rst.EOF = False And rst.BOF = False Then
rst.MoveLast
Debug.Print rst.RecordCount
End If
rst.Close
Set dbs = Nothing
End Sub


--

Ken Snell
<MS ACCESS MVP>

Max said:
Ken,
Thanks for the help on this problem. Everything is working like I need
except one minor problem. When the recordset is empty (no records) I am
getting a runtime error 3021 "no current record". I have tried to place the
code DoCmd.SetWarnings False at the beginning of the code and returning it
to true at the end of your code. Still errors. I must be setting these
warnings incorrectly. Help??

Ken Snell said:
That means that ACCESS cannot find a field or a control on your current form
(the form that is running this code) named txb_MonthStartDate. Check for
typos or changes to field/control names.

--

Ken Snell
<MS ACCESS MVP>

Max said:
Ken, Error
MS Access cant find the name txb_MonthStartDate you entered in the
expression.
This is beyond me...but I am paying attention!


Your query has four parameters. In order to have your code open this
query,
you must tell it what the values of those parameters are. This is done
fairly easily by changing your code to this:


Private Sub Command0_Click()
Dim dbs As DAO.Database, rst As DAO.Recordset
Dim qdf As DAO.QueryDef, prm As DAO.Parameter
' Return reference to current database.
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qry_Orders")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset()
rst.MoveLast
Debug.Print rst.RecordCount
rst.Close
Set dbs = Nothing
End Sub

--

Ken Snell
<MS ACCESS MVP>



PARAMETERS [txb_MonthstartDate] DateTime, [txb_MonthEndDate] DateTime;
SELECT Meetings.MeetingID, Meetings.ClientID, Meetings.EmployeeID,
Meetings.StartRecurRange, Meetings.EndRecurRange,
nz([Meetings].[endrecurrange],#12/31/9999#) AS ActualEndRecurRange
FROM Meetings
WHERE (((Meetings.ClientID)=[cbo_ClientName]) AND
((Meetings.StartRecurRange)<=[txb_MonthEndDate]) AND
((nz([Meetings].[endrecurrange],#12/31/9999#))>=[txb_MonthStartDate]))
OR
(((Meetings.EmployeeID)=[cbo_EmployeeName]));




Post the SQL of the query ... it appears that the query has parameters
in
it.

--

Ken Snell
<MS ACCESS MVP>

Thanks Allen,
I Am still getting the same result. If the query was a union query
would
that make a difference?

Specify the type of recordset, i.e.:
rst As DAO.Recordset

Include the dot:
strSQL = "SELECT qry_Orders.* FROM qry_Orders;"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am experimenting with recordsets. I have cut the recordcount
example
that
is in help and pasted it into the on click event of my
Click to expand...
Click to expand...
form.
I
Click to expand...
Click to expand...
 
works great.....Simple....thanks

Ken Snell said:
You can test for this when you first open the recordset:

Private Sub Command0_Click()
Dim dbs As DAO.Database, rst As DAO.Recordset
Dim qdf As DAO.QueryDef, prm As DAO.Parameter
' Return reference to current database.
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qry_Orders")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset()
' If both the end of file (EOF) and begin of file (BOF)
' are False, there is at least one record in the recordset.
If rst.EOF = False And rst.BOF = False Then
rst.MoveLast
Debug.Print rst.RecordCount
End If
rst.Close
Set dbs = Nothing
End Sub


--

Ken Snell
<MS ACCESS MVP>

Max said:
Ken,
Thanks for the help on this problem. Everything is working like I need
except one minor problem. When the recordset is empty (no records) I am
getting a runtime error 3021 "no current record". I have tried to place the
code DoCmd.SetWarnings False at the beginning of the code and
Click to expand...
returning
it
to true at the end of your code. Still errors. I must be setting these
warnings incorrectly. Help??

Ken Snell said:
That means that ACCESS cannot find a field or a control on your
Click to expand...
current
form
(the form that is running this code) named txb_MonthStartDate. Check for
typos or changes to field/control names.

--

Ken Snell
<MS ACCESS MVP>

Ken, Error
MS Access cant find the name txb_MonthStartDate you entered in the
expression.
This is beyond me...but I am paying attention!


Your query has four parameters. In order to have your code open this
query,
you must tell it what the values of those parameters are. This is done
fairly easily by changing your code to this:


Private Sub Command0_Click()
Dim dbs As DAO.Database, rst As DAO.Recordset
Dim qdf As DAO.QueryDef, prm As DAO.Parameter
' Return reference to current database.
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qry_Orders")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset()
rst.MoveLast
Debug.Print rst.RecordCount
rst.Close
Set dbs = Nothing
End Sub

--

Ken Snell
<MS ACCESS MVP>



PARAMETERS [txb_MonthstartDate] DateTime, [txb_MonthEndDate] DateTime;
SELECT Meetings.MeetingID, Meetings.ClientID, Meetings.EmployeeID,
Meetings.StartRecurRange, Meetings.EndRecurRange,
nz([Meetings].[endrecurrange],#12/31/9999#) AS ActualEndRecurRange
FROM Meetings
WHERE (((Meetings.ClientID)=[cbo_ClientName]) AND
((Meetings.StartRecurRange)<=[txb_MonthEndDate]) AND
((nz([Meetings].[endrecurrange],#12/31/9999#))>=[txb_MonthStartDate]))
OR
(((Meetings.EmployeeID)=[cbo_EmployeeName]));




Post the SQL of the query ... it appears that the query has
parameters
in
it.

--

Ken Snell
<MS ACCESS MVP>

Thanks Allen,
I Am still getting the same result. If the query was a union
query
would
that make a difference?

Specify the type of recordset, i.e.:
rst As DAO.Recordset

Include the dot:
strSQL = "SELECT qry_Orders.* FROM qry_Orders;"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am experimenting with recordsets. I have cut the recordcount
example
that
is in help and pasted it into the on click event of my
Click to expand...
form.
I
had
to
change
the query name in the strsql. I am getting an error
"Too few parameters. expected 4". Below is my code.

Private Sub Command0_Click()
Dim dbs As Database, rst As Recordset
Dim strSQL As String

' Return reference to current database.
Set dbs = CurrentDb
strSQL = "SELECT qry_Orders* FROM qry_Orders"
Set rst = dbs.OpenRecordset(strSQL)
rst.MoveLast
Debug.Print rst.RecordCount
rst.Close
Set dbs = Nothing
End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top