Open Recordset too few parameters expected 1 error 3601

[Peter]

when I click button in a form this event fires which attempts to query the db.

code is below.

strSQL = "SELECT SumOfAmount,Budget from dbo_qryJobExpenseTotalJDIDBudget
where jobdetailsID = " & List0.Column(4) & " and expensecodeid = " &
List0.Column(5)

Set rs = CurrentDb.OpenRecordset(strSQL)

There is no problem with the code, I have copied the strSQL sql statement
and run it successfully in a query.

I have searched the web and tried using querydefs etc as many answers to
other peoples questions seem to be this. But it has not worked for me.

I suspect that the problem is that I am doing a query Q1 on a query Q2, Q2
has a parameter off a form. I suspect that this is the problem. But not
sure how to fix it.

Any help would be greatly appreciated.

 

[Allen Browne]

To find out what's wrong, add this line between your two:
Debug.Print strSql

When it fails, open the Immediate Window (Ctrl+G), and copy the SQL string.
Create a new query, switch to SQL View, and paste. Try to run the query, and
Access will help you pin down what's wrong.

Examples of what it could be:

a) The SQL statement is invalid, e.g. the column contained no text, so the
SQL is incomplete:
where jobdetailsID = AND ...

b) Delimiters are needed (if the fields are Text type.)

c) The field name is not recognised (so Access asks for a parameter.)

 

[Peter]

Allen thanks for the reply.

Here is the result of the CTRL-G:
SELECT SumOfAmount,Budget from dbo_qryJobExpenseTotalJDIDBudget where
jobdetailsID = 2703 and expensecodeid = 14

when I run this when I am in debug, ie stepping through my button event vba
code, I get no data. But when I run it after the form has finished it works
perfectly. Does that make any sense?? How can this happen. you can see
that I am querying a query. In the query one of my where clauses is to use a
main form field value [Forms]![frm_MntServiceNumber]![ServiceNumber]. Its
almost like when in debug mode this value is null so all the queries stop
working.

in fact doing [Forms]![frm_MntServiceNumber]![ServiceNumber] gives me
nothing too. But when I am not in debug it works perfectly.

 

[Peter]

Allen,

Well Ithought it might be because in a sub query I have a parameter ... but
I have hard coded that and it doesn't make a difference. When the vba code
breaks due to the too few parameter error, if I copy the sql code and run in
a query window I get nothing. But after the debug has completed I can run
this code and it works .. here is the full code.

Private Sub cmdAllocate_Click()

'Before Allocate check to see if what has been selected will force this item
to be overallocated:
'select Sumofamount from dbo_qryJobExpenseTotalJDID where jobdetailsID =
???????

'SELECT dbo_qryJobExpenseTotalJDIDBudget.*,
dbo_qryJobExpenseTotalJDIDBudget.jobdetailsID,
dbo_qryJobExpenseTotalJDIDBudget.expensecodeid
'FROM dbo_qryJobExpenseTotalJDIDBudget
'WHERE (((dbo_qryJobExpenseTotalJDIDBudget.jobdetailsID)=3276) AND
((dbo_qryJobExpenseTotalJDIDBudget.expensecodeid)=4));

Dim ctlSourceJobExp As Control
Dim total As Double

total = 0

Set ctlSourceJobExp = List6

For intCurrentRow = 0 To ctlSourceJobExp.ListCount - 1
If ctlSourceJobExp.Selected(intCurrentRow) Then
'MsgBox ctlSourceJobExp.Column(0, intCurrentRow)
'MsgBox ctlSourceJobExp.Column(1, intCurrentRow)
total = total + ctlSourceJobExp.Column(5, intCurrentRow)

End If
Next intCurrentRow

MsgBox total
MsgBox [Forms]![frm_MntServiceNumber]![ServiceNumber]

strSQL = "SELECT SumOfAmount,Budget from
dbo_qryJobExpenseTotalJDIDBudget1 where jobdetailsID = " & List0.Column(4) &
" and expensecodeid = " & List0.Column(5)

Debug.Print strSQL
Debug.Print [Forms]![frm_MntServiceNumber]![ServiceNumber]

Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
Allocated = rs.Fields("SumofAmount")
budget = rs.Fields("Budget")

If (total + Allocated) > budget Then
MsgBox "You cannot enter more time than is allocated. Please
talk to your Project Manager."
End If
End If

End Sub

 

[Jeanette Cunningham]

Peter,
while Allen is off-line this week, here are my thoughts.
The sql needs
& "
after List0.Column(5)

I have split it up over several lines, so as to miss the line wrap in the
newsreader.

strSQL = "SELECT SumOfAmount,Budget " _
& "from dbo_qryJobExpenseTotalJDIDBudget1 " _
& "where jobdetailsID = " & List0.Column(4) & " " _
& "and expensecodeid = " & List0.Column(5) & "


Jeanette Cunningham

 

[Peter]

Jeanette,

thanks for the reply.

my query when the variables are resolved looks like this:
SELECT SumOfAmount,Budget from dbo_qryJobExpenseTotalJDIDBudget where
jobdetailsID = 2703 and expensecodeid = 14

I am not sure that putting a " at the end of it will actually work, It will
throw a syntax error.

Peter,
while Allen is off-line this week, here are my thoughts.
The sql needs
& "
after List0.Column(5)

I have split it up over several lines, so as to miss the line wrap in the
newsreader.

strSQL = "SELECT SumOfAmount,Budget " _
& "from dbo_qryJobExpenseTotalJDIDBudget1 " _
& "where jobdetailsID = " & List0.Column(4) & " " _
& "and expensecodeid = " & List0.Column(5) & "


Jeanette Cunningham

Allen,

Well Ithought it might be because in a sub query I have a parameter ...
but
I have hard coded that and it doesn't make a difference. When the vba
code
breaks due to the too few parameter error, if I copy the sql code and run
in
a query window I get nothing. But after the debug has completed I can run
this code and it works .. here is the full code.

Private Sub cmdAllocate_Click()

'Before Allocate check to see if what has been selected will force this
item
to be overallocated:
'select Sumofamount from dbo_qryJobExpenseTotalJDID where jobdetailsID =
???????

'SELECT dbo_qryJobExpenseTotalJDIDBudget.*,
dbo_qryJobExpenseTotalJDIDBudget.jobdetailsID,
dbo_qryJobExpenseTotalJDIDBudget.expensecodeid
'FROM dbo_qryJobExpenseTotalJDIDBudget
'WHERE (((dbo_qryJobExpenseTotalJDIDBudget.jobdetailsID)=3276) AND
((dbo_qryJobExpenseTotalJDIDBudget.expensecodeid)=4));

Dim ctlSourceJobExp As Control
Dim total As Double

total = 0

Set ctlSourceJobExp = List6

For intCurrentRow = 0 To ctlSourceJobExp.ListCount - 1
If ctlSourceJobExp.Selected(intCurrentRow) Then
'MsgBox ctlSourceJobExp.Column(0, intCurrentRow)
'MsgBox ctlSourceJobExp.Column(1, intCurrentRow)
total = total + ctlSourceJobExp.Column(5, intCurrentRow)

End If
Next intCurrentRow

MsgBox total
MsgBox [Forms]![frm_MntServiceNumber]![ServiceNumber]

strSQL = "SELECT SumOfAmount,Budget from
dbo_qryJobExpenseTotalJDIDBudget1 where jobdetailsID = " & List0.Column(4)
&
" and expensecodeid = " & List0.Column(5)

Debug.Print strSQL
Debug.Print [Forms]![frm_MntServiceNumber]![ServiceNumber]

Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
Allocated = rs.Fields("SumofAmount")
budget = rs.Fields("Budget")

If (total + Allocated) > budget Then
MsgBox "You cannot enter more time than is allocated. Please
talk to your Project Manager."
End If
End If

End Sub

 

[Jeanette Cunningham]

Peter,
access gives the message about too few parameters when it can't find one or
several fields in the query.
This can be because there is an incorrect spelling of a field or control
name in the query or the form.
It could be because access is confused over finding the field if you have a
field and its control with the same name.
It could be because the form with the control is not open at the time it is
needed.

What happens on this line
Debug.Print [Forms]![frm_MntServiceNumber]![ServiceNumber] ?

Is the form called frm_MntServiceNumber open at the time the code runs?


Jeanette Cunningham

Jeanette,

thanks for the reply.

my query when the variables are resolved looks like this:
SELECT SumOfAmount,Budget from dbo_qryJobExpenseTotalJDIDBudget where
jobdetailsID = 2703 and expensecodeid = 14

I am not sure that putting a " at the end of it will actually work, It
will
throw a syntax error.

Peter,
while Allen is off-line this week, here are my thoughts.
The sql needs
& "
after List0.Column(5)

I have split it up over several lines, so as to miss the line wrap in the
newsreader.

strSQL = "SELECT SumOfAmount,Budget " _
& "from dbo_qryJobExpenseTotalJDIDBudget1 " _
& "where jobdetailsID = " & List0.Column(4) & " " _
& "and expensecodeid = " & List0.Column(5) & "


Jeanette Cunningham

Allen,

Well Ithought it might be because in a sub query I have a parameter ...
but
I have hard coded that and it doesn't make a difference. When the vba
code
breaks due to the too few parameter error, if I copy the sql code and
run
in
a query window I get nothing. But after the debug has completed I can
run
this code and it works .. here is the full code.

Private Sub cmdAllocate_Click()

'Before Allocate check to see if what has been selected will force this
item
to be overallocated:
'select Sumofamount from dbo_qryJobExpenseTotalJDID where jobdetailsID
=
???????

'SELECT dbo_qryJobExpenseTotalJDIDBudget.*,
dbo_qryJobExpenseTotalJDIDBudget.jobdetailsID,
dbo_qryJobExpenseTotalJDIDBudget.expensecodeid
'FROM dbo_qryJobExpenseTotalJDIDBudget
'WHERE (((dbo_qryJobExpenseTotalJDIDBudget.jobdetailsID)=3276) AND
((dbo_qryJobExpenseTotalJDIDBudget.expensecodeid)=4));

Dim ctlSourceJobExp As Control
Dim total As Double

total = 0

Set ctlSourceJobExp = List6

For intCurrentRow = 0 To ctlSourceJobExp.ListCount - 1
If ctlSourceJobExp.Selected(intCurrentRow) Then
'MsgBox ctlSourceJobExp.Column(0, intCurrentRow)
'MsgBox ctlSourceJobExp.Column(1, intCurrentRow)
total = total + ctlSourceJobExp.Column(5, intCurrentRow)

End If
Next intCurrentRow

MsgBox total
MsgBox [Forms]![frm_MntServiceNumber]![ServiceNumber]

strSQL = "SELECT SumOfAmount,Budget from
dbo_qryJobExpenseTotalJDIDBudget1 where jobdetailsID = " &
List0.Column(4)
&
" and expensecodeid = " & List0.Column(5)

Debug.Print strSQL
Debug.Print [Forms]![frm_MntServiceNumber]![ServiceNumber]

Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
Allocated = rs.Fields("SumofAmount")
budget = rs.Fields("Budget")

If (total + Allocated) > budget Then
MsgBox "You cannot enter more time than is allocated.
Please
talk to your Project Manager."
End If
End If

End Sub

 

[Peter]

Jeanette,

Thanks again. I thought the same thing ... that's why I completely
hardcoded bits of this. Including this bit that you asked about. When I run
this form the form below is always open.

I thought there was a problem with this so I hard coded that part of the
query 'dbo_qryJobExpenseTotalJDIDBudget' which is used here. This query is
actually a sum query on another query dbo_qryJobExpenseTotalJDID. I wonder
if it is because I have a query within a query within a query and access
can't cope going this many levels deep.

 

[Jeanette Cunningham]

Peter,
where exactly in the code does it error out with that message?

Jeanette Cunningham

Jeanette,

Thanks again. I thought the same thing ... that's why I completely
hardcoded bits of this. Including this bit that you asked about. When I
run
this form the form below is always open.

I thought there was a problem with this so I hard coded that part of the
query 'dbo_qryJobExpenseTotalJDIDBudget' which is used here. This query
is
actually a sum query on another query dbo_qryJobExpenseTotalJDID. I
wonder
if it is because I have a query within a query within a query and access
can't cope going this many levels deep.

Peter,
access gives the message about too few parameters when it can't find one
or
several fields in the query.
This can be because there is an incorrect spelling of a field or control
name in the query or the form.
It could be because access is confused over finding the field if you have
a
field and its control with the same name.
It could be because the form with the control is not open at the time it
is
needed.

What happens on this line
Debug.Print [Forms]![frm_MntServiceNumber]![ServiceNumber] ?

Is the form called frm_MntServiceNumber open at the time the code runs?


Jeanette Cunningham

 

[Jeanette Cunningham]

Peter,
this line
strSQL = "SELECT SumOfAmount,Budget from
dbo_qryJobExpenseTotalJDIDBudget1 where jobdetailsID = " & List0.Column(4) &
" and expensecodeid = " & List0.Column(5)

has a comma instead of a period between SumOfAmount and Budget.
If this is a direct copy and paste of your code, it could be the problem.


Jeanette Cunningham

Peter,
where exactly in the code does it error out with that message?

Jeanette Cunningham

Jeanette,

Thanks again. I thought the same thing ... that's why I completely
hardcoded bits of this. Including this bit that you asked about. When I
run
this form the form below is always open.

I thought there was a problem with this so I hard coded that part of the
query 'dbo_qryJobExpenseTotalJDIDBudget' which is used here. This query
is
actually a sum query on another query dbo_qryJobExpenseTotalJDID. I
wonder
if it is because I have a query within a query within a query and access
can't cope going this many levels deep.

Peter,
access gives the message about too few parameters when it can't find one
or
several fields in the query.
This can be because there is an incorrect spelling of a field or control
name in the query or the form.
It could be because access is confused over finding the field if you
have a
field and its control with the same name.
It could be because the form with the control is not open at the time it
is
needed.

What happens on this line
Debug.Print [Forms]![frm_MntServiceNumber]![ServiceNumber] ?

Is the form called frm_MntServiceNumber open at the time the code runs?


Jeanette Cunningham

 

[Jeanette Cunningham]

The punctuation looks incorrect.
I was assuming that Budget was a field of SumOfAmount, but when I look
closer both are fields.
There needs to be a space between SumOfAmount and Budget.
It may help if you write the query like this, explicitly telling acces where
to look for SumOfAmount and Budget.

strSQL = "SELECT dbo_qryJobExpenseTotalJDIDBudget1.SumOfAmount,
dbo_qryJobExpenseTotalJDIDBudget1.Budget from
dbo_qryJobExpenseTotalJDIDBudget1 where yadda, yadda ...

I can't see the start of the sub, where you set up the variables.
Did you qualify the variable for the rs as a DAO recordset? assuming you
have set a reference to DAO?


Jeanette Cunningham

Peter,
this line
strSQL = "SELECT SumOfAmount,Budget from
dbo_qryJobExpenseTotalJDIDBudget1 where jobdetailsID = " & List0.Column(4)
&
" and expensecodeid = " & List0.Column(5)

has a comma instead of a period between SumOfAmount and Budget.
If this is a direct copy and paste of your code, it could be the problem.


Jeanette Cunningham

Peter,
where exactly in the code does it error out with that message?

Jeanette Cunningham

Jeanette,

Thanks again. I thought the same thing ... that's why I completely
hardcoded bits of this. Including this bit that you asked about. When
I run
this form the form below is always open.

I thought there was a problem with this so I hard coded that part of the
query 'dbo_qryJobExpenseTotalJDIDBudget' which is used here. This query
is
actually a sum query on another query dbo_qryJobExpenseTotalJDID. I
wonder
if it is because I have a query within a query within a query and access
can't cope going this many levels deep.


:

Peter,
access gives the message about too few parameters when it can't find
one or
several fields in the query.
This can be because there is an incorrect spelling of a field or
control
name in the query or the form.
It could be because access is confused over finding the field if you
have a
field and its control with the same name.
It could be because the form with the control is not open at the time
it is
needed.

What happens on this line
Debug.Print [Forms]![frm_MntServiceNumber]![ServiceNumber] ?

Is the form called frm_MntServiceNumber open at the time the code runs?


Jeanette Cunningham