Current Recrdset does not support updating | Familiar Sight | | Join Date: Nov 2006
Posts: 134
| | |
I got this Run-time error 3251
Current Recrdset does not support updating. This may be limation of the provider, or of the selected locktype.
Dim red1 As New ADODB.Recordset
Dim strmess As String
Set cnp = CurrentProject.Connection
strmess = "SELECT * from tblinvoicelines WHERE invoice=" & newInvo
Set red1 = cnp.Execute(strmess)
If Not red1.EOF Then
red1.MoveFirst
Do While Not red1.EOF
If red1!Invoice = newInvo Then
red1.Fields("amount") = Round(red1!Qty_Order * red1!Sell_price, 2)
red1.Update
End If
red1.MoveNext
Loop
End If
How can I resolve this, why MS Access does allow this simple updating ?
|  | Expert | | Join Date: Jun 2006 Location: Seremban, Malaysia
Posts: 1,630
| | | re: Current Recrdset does not support updating
Quote:
Originally Posted by jamesnkk I got this Run-time error 3251
Current Recrdset does not support updating. This may be limation of the provider, or of the selected locktype.
Dim red1 As New ADODB.Recordset
Dim strmess As String
Set cnp = CurrentProject.Connection
strmess = "SELECT * from tblinvoicelines WHERE invoice=" & newInvo
Set red1 = cnp.Execute(strmess)
If Not red1.EOF Then
red1.MoveFirst
Do While Not red1.EOF
If red1!Invoice = newInvo Then
red1.Fields("amount") = Round(red1!Qty_Order * red1!Sell_price, 2)
red1.Update
End If
red1.MoveNext
Loop
End If
How can I resolve this, why MS Access does allow this simple updating ? Hi there,
Kindly refer to below modified code segment, hope it helps. Good luck & Take care. -
strmess = ""
-
strmess = "SELECT * from tblinvoicelines WHERE invoice=" & newInvo
-
red1.Open strmess, CurrentProject.Connection, _
-
adOpenKeyset, adLockOptimistic
-
|  | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,216
| | | re: Current Recrdset does not support updating
Quote:
Originally Posted by jamesnkk I got this Run-time error 3251
Current Recrdset does not support updating. This may be limation of the provider, or of the selected locktype.
Dim red1 As New ADODB.Recordset
Dim strmess As String
Set cnp = CurrentProject.Connection
strmess = "SELECT * from tblinvoicelines WHERE invoice=" & newInvo
Set red1 = cnp.Execute(strmess)
If Not red1.EOF Then
red1.MoveFirst
Do While Not red1.EOF
If red1!Invoice = newInvo Then
red1.Fields("amount") = Round(red1!Qty_Order * red1!Sell_price, 2)
red1.Update
End If
red1.MoveNext
Loop
End If
How can I resolve this, why MS Access does allow this simple updating ? If I am not mistaken, and unless otherwise specified, isn't the Default ADO
Recordset Read Only..
| | Expert | | Join Date: Jun 2006 Location: Seremban, Malaysia
Posts: 1,630
| | | re: Current Recrdset does not support updating
Quote:
Originally Posted by ADezii If I am not mistaken, and unless otherwise specified, isn't the Default ADO
Recordset Read Only.. Hi there,
Yes, the default locktype is set to adLockReadOnly. This property is set to read/write on a closed recordset and read-only on an open recordset. Take care.
| | Familiar Sight | | Join Date: Nov 2006
Posts: 134
| | | re: Current Recrdset does not support updating
Quote:
Originally Posted by sashi Hi there,
Kindly refer to below modified code segment, hope it helps. Good luck & Take care.
-
strmess = ""
-
strmess = "SELECT * from tblinvoicelines WHERE invoice=" & newInvo
-
red1.Open strmess, CurrentProject.Connection, _
-
adOpenKeyset, adLockOptimistic
-
I copy your statement, I got an error message - Run time error - 3705
Operation is not allowed when the object is open.
| | Lives Here | | Join Date: Oct 2006
Posts: 1,626
| | | re: Current Recrdset does not support updating
Quote:
Originally Posted by jamesnkk I copy your statement, I got an error message - Run time error - 3705
Operation is not allowed when the object is open. that is because you have already opened the recordset earlier in your code.
insert this just before the code that you posted above:
red1.Close
| | Lives Here | | Join Date: Oct 2006
Posts: 1,626
| | | re: Current Recrdset does not support updating
BTW it is much better to explicitly initialize your recordset like this: - Dim red1 As ADODB.Recordset
-
Set red1 = New ADODB.Recordset
If you do it this way: - Dim red1 As New ADODB.Recordset
each time you use red1 it will be checked to see if has been initialized which is inefficient
| | Familiar Sight | | Join Date: Nov 2006
Posts: 134
| | | re: Current Recrdset does not support updating
Quote:
Originally Posted by willakawill BTW it is much better to explicitly initialize your recordset like this:
- Dim red1 As ADODB.Recordset
-
Set red1 = New ADODB.Recordset
If you do it this way: - Dim red1 As New ADODB.Recordset
each time you use red1 it will be checked to see if has been initialized which is inefficient Wow !, Thank so much for your coding, I got it work now !, and Thank so Much for the explanation. Also Thank Sashi, Thank God, I came to the right forum.
| | Expert | | Join Date: Jun 2006 Location: Seremban, Malaysia
Posts: 1,630
| | | re: Current Recrdset does not support updating
Quote:
Originally Posted by jamesnkk Wow !, Thank so much for your coding, I got it work now !, and Thank so Much for the explanation. Also Thank Sashi, Thank God, I came to the right forum. Hi there,
Good luck & Take care.
|  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,449 network members.
|
