making recordset allow edits

T

Ted

Good morning all, I was wondering if someone could give me a quick tip. How
do i make a recordset allow edits? Its telling me "Update or CancelUpdate
without AddNew or Edit." I know its got to be something simple that i'm
missing...rst.edit?? Any help would be very much appreciated.

Dim strCancReason As String
Dim i As Integer
Dim n As Long
Dim strSQL As String
Dim rst As Recordset
Dim dbs As Database

Set dbs = CurrentDb
strSQL = "SELECT ALLCOMPANIESDATA.* FROM ALLCOMPANIESDATA WHERE
(((ALLCOMPANIESDATA.TRANSCD)='04'))"

Set rst = dbs.OpenRecordset(strSQL)

rst.MoveLast
rst.MoveFirst

For n = 1 To rst.RecordCount
i = 0
Do Until strCancReason Like "(*)"
strCancReason = Right(rst![NAME], i + 1)
i = i + 1
Loop

rst![CANCREASON] = Mid(strCancReason, 2, Len(strCancReason) - 2)

strCancReason = ""

DoCmd.GoToRecord , , acNext

Next n

DoCmd.SetWarnings True

TIA
Ted
 
T

Ted

OK I added rst.edit to my code and it seemed to be working but it only works
when updating the first record
then I get "Update or CancelUpdate without AddNew or Edit." and whats really
confusing is when i go to look
back at the table none of the records are updated in the table(when it looks
like the first record got updated).
this is my first time updating records this way am i missing something?

Dim strCancReason As String
Dim i As Integer
Dim n As Long
Dim strLengthOfName As String
Dim intRecordCount As Integer
Dim strSQL As String
Dim rst As Recordset
Dim dbs As Database

Set dbs = CurrentDb
strSQL = "SELECT ALLCOMPANIESDATA.* FROM ALLCOMPANIESDATA WHERE
(((ALLCOMPANIESDATA.TRANSCD)='04'))"

Set rst = dbs.OpenRecordset(strSQL)

rst.MoveLast
rst.MoveFirst
rst.Edit

For n = 1 To (rst.RecordCount)
i = 0
Do Until strCancReason Like "(*)"
strCancReason = Right(rst![Name], i + 1)
i = i + 1
Loop

rst![CANCREASON] = Mid(strCancReason, 2, Len(strCancReason) - 2)

strCancReason = ""

rst.MoveNext

Next n

DoCmd.SetWarnings True
 
T

Ted

Thank You Daniel...worked like a charm!

Daniel said:
You are missing some basic coding in your loop.

You need to tell access that you are editing before doing so and then
finish
by telling it to update the record.

rst.edit
rst![CANCREASON] = Mid(strCancReason, 2, Len(strCancReason) - 2)
rst.update

you might want to take a look at

http://www.functionx.com/vbaccess/howto/editrecord.htm
--
Hope this helps,

Daniel P


Ted said:
Good morning all, I was wondering if someone could give me a quick tip.
How
do i make a recordset allow edits? Its telling me "Update or CancelUpdate
without AddNew or Edit." I know its got to be something simple that i'm
missing...rst.edit?? Any help would be very much appreciated.

Dim strCancReason As String
Dim i As Integer
Dim n As Long
Dim strSQL As String
Dim rst As Recordset
Dim dbs As Database

Set dbs = CurrentDb
strSQL = "SELECT ALLCOMPANIESDATA.* FROM ALLCOMPANIESDATA WHERE
(((ALLCOMPANIESDATA.TRANSCD)='04'))"

Set rst = dbs.OpenRecordset(strSQL)

rst.MoveLast
rst.MoveFirst

For n = 1 To rst.RecordCount
i = 0
Do Until strCancReason Like "(*)"
strCancReason = Right(rst![NAME], i + 1)
i = i + 1
Loop

rst![CANCREASON] = Mid(strCancReason, 2, Len(strCancReason) - 2)

strCancReason = ""

DoCmd.GoToRecord , , acNext

Next n

DoCmd.SetWarnings True

TIA
Ted
Click to expand...
Click to expand...
 
G

Guest

You need to start indenting your code to make it easier to follow. Here is
what I put together rapidly

Dim strCancReason As String
Dim i As Integer
Dim n As Long
Dim strLengthOfName As String
Dim intRecordCount As Integer
Dim strSQL As String
Dim rst As DAO.Recordset
Dim db As DAO.Database

Set db = CurrentDb()
strSQL = "SELECT ALLCOMPANIESDATA.* FROM ALLCOMPANIESDATA WHERE
(((ALLCOMPANIESDATA.TRANSCD)='04'))"

Set rst = db.OpenRecordset(strSQL)
DoCmd.SetWarnings False

With rst
.MoveLast

For n = 1 To .RecordCount
i = 0
Do Until strCancReason Like "(*)"
strCancReason = Right(![Name], i + 1)
i = i + 1
Loop

.edit
![CANCREASON] = Mid(strCancReason, 2, Len(strCancReason) - 2)
.update

strCancReason = ""

.MoveNext
Next n

End With

DoCmd.SetWarnings True
--
Hope this helps,

Daniel P


Ted said:
OK I added rst.edit to my code and it seemed to be working but it only works
when updating the first record
then I get "Update or CancelUpdate without AddNew or Edit." and whats really
confusing is when i go to look
back at the table none of the records are updated in the table(when it looks
like the first record got updated).
this is my first time updating records this way am i missing something?

Dim strCancReason As String
Dim i As Integer
Dim n As Long
Dim strLengthOfName As String
Dim intRecordCount As Integer
Dim strSQL As String
Dim rst As Recordset
Dim dbs As Database

Set dbs = CurrentDb
strSQL = "SELECT ALLCOMPANIESDATA.* FROM ALLCOMPANIESDATA WHERE
(((ALLCOMPANIESDATA.TRANSCD)='04'))"

Set rst = dbs.OpenRecordset(strSQL)

rst.MoveLast
rst.MoveFirst
rst.Edit

For n = 1 To (rst.RecordCount)
i = 0
Do Until strCancReason Like "(*)"
strCancReason = Right(rst![Name], i + 1)
i = i + 1
Loop

rst![CANCREASON] = Mid(strCancReason, 2, Len(strCancReason) - 2)

strCancReason = ""

rst.MoveNext

Next n

DoCmd.SetWarnings True

Ted said:
Good morning all, I was wondering if someone could give me a quick tip.
How do i make a recordset allow edits? Its telling me "Update or
CancelUpdate without AddNew or Edit." I know its got to be something
simple that i'm missing...rst.edit?? Any help would be very much
appreciated.

Dim strCancReason As String
Dim i As Integer
Dim n As Long
Dim strSQL As String
Dim rst As Recordset
Dim dbs As Database

Set dbs = CurrentDb
strSQL = "SELECT ALLCOMPANIESDATA.* FROM ALLCOMPANIESDATA WHERE
(((ALLCOMPANIESDATA.TRANSCD)='04'))"

Set rst = dbs.OpenRecordset(strSQL)

rst.MoveLast
rst.MoveFirst

For n = 1 To rst.RecordCount
i = 0
Do Until strCancReason Like "(*)"
strCancReason = Right(rst![NAME], i + 1)
i = i + 1
Loop

rst![CANCREASON] = Mid(strCancReason, 2, Len(strCancReason) - 2)

strCancReason = ""

DoCmd.GoToRecord , , acNext

Next n

DoCmd.SetWarnings True

TIA
Ted
Click to expand...
Click to expand...
 
T

Ted

Thank you for your time Daniel. That helped a lot!

Daniel said:
You need to start indenting your code to make it easier to follow. Here
is
what I put together rapidly

Dim strCancReason As String
Dim i As Integer
Dim n As Long
Dim strLengthOfName As String
Dim intRecordCount As Integer
Dim strSQL As String
Dim rst As DAO.Recordset
Dim db As DAO.Database

Set db = CurrentDb()
strSQL = "SELECT ALLCOMPANIESDATA.* FROM ALLCOMPANIESDATA WHERE
(((ALLCOMPANIESDATA.TRANSCD)='04'))"

Set rst = db.OpenRecordset(strSQL)
DoCmd.SetWarnings False

With rst
.MoveLast

For n = 1 To .RecordCount
i = 0
Do Until strCancReason Like "(*)"
strCancReason = Right(![Name], i + 1)
i = i + 1
Loop

.edit
![CANCREASON] = Mid(strCancReason, 2, Len(strCancReason) - 2)
.update

strCancReason = ""

.MoveNext
Next n

End With

DoCmd.SetWarnings True
--
Hope this helps,

Daniel P


Ted said:
OK I added rst.edit to my code and it seemed to be working but it only
works
when updating the first record
then I get "Update or CancelUpdate without AddNew or Edit." and whats
really
confusing is when i go to look
back at the table none of the records are updated in the table(when it
looks
like the first record got updated).
this is my first time updating records this way am i missing something?

Dim strCancReason As String
Dim i As Integer
Dim n As Long
Dim strLengthOfName As String
Dim intRecordCount As Integer
Dim strSQL As String
Dim rst As Recordset
Dim dbs As Database

Set dbs = CurrentDb
strSQL = "SELECT ALLCOMPANIESDATA.* FROM ALLCOMPANIESDATA WHERE
(((ALLCOMPANIESDATA.TRANSCD)='04'))"

Set rst = dbs.OpenRecordset(strSQL)

rst.MoveLast
rst.MoveFirst
rst.Edit

For n = 1 To (rst.RecordCount)
i = 0
Do Until strCancReason Like "(*)"
strCancReason = Right(rst![Name], i + 1)
i = i + 1
Loop

rst![CANCREASON] = Mid(strCancReason, 2, Len(strCancReason) - 2)

strCancReason = ""

rst.MoveNext

Next n

DoCmd.SetWarnings True

Ted said:
Good morning all, I was wondering if someone could give me a quick tip.
How do i make a recordset allow edits? Its telling me "Update or
CancelUpdate without AddNew or Edit." I know its got to be something
simple that i'm missing...rst.edit?? Any help would be very much
appreciated.

Dim strCancReason As String
Dim i As Integer
Dim n As Long
Dim strSQL As String
Dim rst As Recordset
Dim dbs As Database

Set dbs = CurrentDb
strSQL = "SELECT ALLCOMPANIESDATA.* FROM ALLCOMPANIESDATA WHERE
(((ALLCOMPANIESDATA.TRANSCD)='04'))"

Set rst = dbs.OpenRecordset(strSQL)

rst.MoveLast
rst.MoveFirst

For n = 1 To rst.RecordCount
i = 0
Do Until strCancReason Like "(*)"
strCancReason = Right(rst![NAME], i + 1)
i = i + 1
Loop

rst![CANCREASON] = Mid(strCancReason, 2, Len(strCancReason) - 2)

strCancReason = ""

DoCmd.GoToRecord , , acNext

Next n

DoCmd.SetWarnings True

TIA
Ted
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Save selected items NOT selected in an Extended List Box based onother condition 6
Type mismatch error 2
Too few parameters 13
RecordSet Clone 7
Canceling an OnClick Event 3
Type Mismatch in Recordset sql 1
Catching the Null Value 2
Error 4

Top