Too few parameters

[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

 

[Allen Browne]

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

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

 

[Max]

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

 

[Ken Snell [MVP]]

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

 

[Max]

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]));

 

[Marie]

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>

 

[Max]

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

 

[Ken Snell [MVP]]

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.

 

[Max]

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

 

[Marie]

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

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

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

 

[Ken Snell [MVP]]

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>

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

 

[Max]

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

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>

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

 

[Ken Snell [MVP]]

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>

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

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>

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

form.

I

 

[Max]

works great.....Simple....thanks

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>

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

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>

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

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