Problem with ADO Code

  • Thread starter Thread starter NNlogistics
  • Start date Start date
N

NNlogistics

I am trying to update a query with pricing after a level of 100sq ft is
reaced on all products, I need to go back and reset pricing. So I thought I
would try to loop through the query and make changes. I am getting an error

Run-time error(2147217900) Invalid SQL statement expected
delete,insert,procedure,select,update ???
I am getting it at
rst.Open "qryTotalSquareFeet", Conn, adOpenDynamic, adLockOptimistic
although I know by now that it may not be the statement actually causing
problem.

Coding looks like
'Check if the Total sq ft of Products starting with 3 or 4 is over 100'
If Me.txtTotalSquareFeet >= 100 Then
Dim Conn As ADODB.Connection
Dim rst As ADODB.Recordset

Set Conn = New ADODB.Connection
Set rst = New ADODB.Recordset

Conn.Open CurrentProject.Connection
rst.Open "qryTotalSquareFeet", Conn, adOpenDynamic, adLockOptimistic
'qryTotalSquareFeet is looking for "Order Number" that is being
referenced on frmOrder

rst.MoveFirst
Do While Not rst.EOF
rst.edit
'Make Selling Price per unit 1.92
rst!fldProductSellingPrice = rst!fldUnitsperPerProduct * 1.92
rst.Update
'Change Extended Price
rst!fldExtendedSellingPrice = rst!fldProductSellingPrice * rst!fldQuantity
rst.Update




rst.MoveNext
Loop


rst.Close
Set rst = Nothing
Set Conn = Nothing


End If
 
There are a number of problems with your code:
There may also be problems inside your query.
Also your code is a very inefficient way of doing this - it would be much
better to do it in a query rather than via a recordset.
-Dorian
 
NNlogistics said:
I am trying to update a query with pricing after a level of 100sq ft is
reaced on all products, I need to go back and reset pricing. So I thought
I
would try to loop through the query and make changes. I am getting an
error

Run-time error(2147217900) Invalid SQL statement expected
delete,insert,procedure,select,update ???
I am getting it at
rst.Open "qryTotalSquareFeet", Conn, adOpenDynamic, adLockOptimistic
although I know by now that it may not be the statement actually causing
problem.

Coding looks like
'Check if the Total sq ft of Products starting with 3 or 4 is over 100'
If Me.txtTotalSquareFeet >= 100 Then
Dim Conn As ADODB.Connection
Dim rst As ADODB.Recordset

Set Conn = New ADODB.Connection
Set rst = New ADODB.Recordset

Conn.Open CurrentProject.Connection
rst.Open "qryTotalSquareFeet", Conn, adOpenDynamic, adLockOptimistic
'qryTotalSquareFeet is looking for "Order Number" that is being
referenced on frmOrder

rst.MoveFirst
Do While Not rst.EOF
rst.edit
'Make Selling Price per unit 1.92
rst!fldProductSellingPrice = rst!fldUnitsperPerProduct * 1.92
rst.Update
'Change Extended Price
rst!fldExtendedSellingPrice = rst!fldProductSellingPrice *
rst!fldQuantity
rst.Update




rst.MoveNext
Loop


rst.Close
Set rst = Nothing
Set Conn = Nothing


End If
Click to expand...


As mscertified pointed out, you have several minor problems with your code.
However, the reason for the specific error you're getting is that your query
is a parameter query -- it references a control on a form -- and so you need
to use a Command object and fill in the value of the parameter. I gather
that the query is looking for [Forms]![frmOrder]![Order Number]. I don't
know if that is a number field or a text field. If it's a number field, try
this:

'----- start of suggested code -----
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset

Set cmd = New ADODB.Connection

With cmd
Set .ActiveConnection = CurrentProject.Connection
.CommandText = "qryTotalSquareFeet"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter( _
.Parameters.Append .CreateParameter( _
"[Forms]![frmOrder]![Order Number]", _
adInteger, _
adParamInput, _
, _
Forms!frmOrder![Order Number])
Set rst = .Execute
End With

With rst
Do While Not .EOF
'Make Selling Price per unit 1.92
!fldProductSellingPrice = rst!fldUnitsperPerProduct * 1.92
'Change Extended Price
!fldExtendedSellingPrice = rst!fldProductSellingPrice *
rst!fldQuantity
.Update
.MoveNext
Loop
.Close
End With

Set rst = Nothing
Set cmd = Nothing

'----- end of suggested code -----


If [Order Number] is a text field, I believe you have to provide the size of
the parameter when creating it, so you might change that statement to:

.Parameters.Append .CreateParameter( _
"[Forms]![frmOrder]![Order Number]", _
adVarChar, _
adParamInput, _
Len(Forms!frmOrder![Order Number]), _
Forms!frmOrder![Order Number])

If you are using a Jet database, this is rather easier to do using the DAO
QueryDef object.
 
mscertified/Dirk

Thankyou very much for the response. I have plenty of ideas and options to
check out. I'll get back and let you know how I made out.
--
Thanks for any assistance


Dirk Goldgar said:
NNlogistics said:
I am trying to update a query with pricing after a level of 100sq ft is
reaced on all products, I need to go back and reset pricing. So I thought
I
would try to loop through the query and make changes. I am getting an
error

Run-time error(2147217900) Invalid SQL statement expected
delete,insert,procedure,select,update ???
I am getting it at
rst.Open "qryTotalSquareFeet", Conn, adOpenDynamic, adLockOptimistic
although I know by now that it may not be the statement actually causing
problem.

Coding looks like
'Check if the Total sq ft of Products starting with 3 or 4 is over 100'
If Me.txtTotalSquareFeet >= 100 Then
Dim Conn As ADODB.Connection
Dim rst As ADODB.Recordset

Set Conn = New ADODB.Connection
Set rst = New ADODB.Recordset

Conn.Open CurrentProject.Connection
rst.Open "qryTotalSquareFeet", Conn, adOpenDynamic, adLockOptimistic
'qryTotalSquareFeet is looking for "Order Number" that is being
referenced on frmOrder

rst.MoveFirst
Do While Not rst.EOF
rst.edit
'Make Selling Price per unit 1.92
rst!fldProductSellingPrice = rst!fldUnitsperPerProduct * 1.92
rst.Update
'Change Extended Price
rst!fldExtendedSellingPrice = rst!fldProductSellingPrice *
rst!fldQuantity
rst.Update




rst.MoveNext
Loop


rst.Close
Set rst = Nothing
Set Conn = Nothing


End If
Click to expand...


As mscertified pointed out, you have several minor problems with your code.
However, the reason for the specific error you're getting is that your query
is a parameter query -- it references a control on a form -- and so you need
to use a Command object and fill in the value of the parameter. I gather
that the query is looking for [Forms]![frmOrder]![Order Number]. I don't
know if that is a number field or a text field. If it's a number field, try
this:

'----- start of suggested code -----
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset

Set cmd = New ADODB.Connection

With cmd
Set .ActiveConnection = CurrentProject.Connection
.CommandText = "qryTotalSquareFeet"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter( _
.Parameters.Append .CreateParameter( _
"[Forms]![frmOrder]![Order Number]", _
adInteger, _
adParamInput, _
, _
Forms!frmOrder![Order Number])
Set rst = .Execute
End With

With rst
Do While Not .EOF
'Make Selling Price per unit 1.92
!fldProductSellingPrice = rst!fldUnitsperPerProduct * 1.92
'Change Extended Price
!fldExtendedSellingPrice = rst!fldProductSellingPrice *
rst!fldQuantity
.Update
.MoveNext
Loop
.Close
End With

Set rst = Nothing
Set cmd = Nothing

'----- end of suggested code -----


If [Order Number] is a text field, I believe you have to provide the size of
the parameter when creating it, so you might change that statement to:

.Parameters.Append .CreateParameter( _
"[Forms]![frmOrder]![Order Number]", _
adVarChar, _
adParamInput, _
Len(Forms!frmOrder![Order Number]), _
Forms!frmOrder![Order Number])

If you are using a Jet database, this is rather easier to do using the DAO
QueryDef object.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top