Update or CancelUpdate without AddNew or Edit

[jender624]

OK, this error has been SUCH a beat down.

I'm using Access 2000, and manipulating the contents of an ADO recordset on an unbound form.

Sometimes when I get to this line:
rsCurr.Update

I receive the error in the title of this post. The few solutions I've seen don't apply to my situation; I've seen one solution to add rsCurr.Edit before my update statement, but this is only for DAO recordsets. I've also seen another solution to check the status of the Me.Dirty property of the form, but this doesn't apply because I'm not using a bound form.

PLEASE, does anybody have any input as to the resolution of this error under the conditions I've described?

Thanks,

jender624

 

[kjv1611]

If you are creating a new record, you need this:

rsCurr.AddNew

Before the code adding the new record.. If editing an existing record, then you need

rsCurr.Edit

instead..


Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[jender624]

Thanks for the reply!

As I stated in my original post, the .Edit method appears to be available with DAO recordsets, not ADO recordsets which is what I'm using.

The .AddNew appears to be available with ADO recordsets, but this error occurs when I'm editing a record, not adding one.

Any other ideas?

jender624

 

[RoyVidar]

Havent had the error, but since the .Edit method, as you state, only exists in DAO, my impression is it suggests you're somewhere working with a DAO recordset. But how would we know without seeing the code (we don't know the coditions by your description)? So, I think to be of assistance, we would need to see the code...

Roy-Vidar

 

[jender624]

Here's the code I'm using for the ADO recordset. Basically, the code cycles through all the controls on the form, and since the names of the controls are the same as the corresponding fields in the table, an update can be made to the recordset based on a reference to the control name. After all the updates are complete, the recordset updates with the .Update method.

'-- Open the ADO connection
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
sConnect = sConnect & rsDBPath!be_db_location & ";"
conBE.Open sConnect
rsCurr.Open TableName, conBE, adOpenKeyset, adLockOptimistic, adCmdTable

For Each ctlCurrent In Me.Controls

DoEvents

gvDummy = rsCurr(ctlCurrent.Name).Name

If Err = 0 Then

bCheckField = True

'-- If the current control is a keyfield and a counter, don't mess with it.
If ctlCurrent.Name = sKeyName Then
If rsCurr.Fields(ctlCurrent.Name).Properties("AutoIncrement") = True Then
bCheckField = False
End If
End If

If bCheckField Then
'-- If data has changed, then update it.
If IsNull(rsCurr(ctlCurrent.Name).Value) And Not IsNull(ctlCurrent.Value) Then
rsCurr(ctlCurrent.Name).Value = ctlCurrent.Value
ElseIf Not IsNull(rsCurr(ctlCurrent.Name).Value) And IsNull(ctlCurrent.Value) Then
rsCurr(ctlCurrent.Name).Value = ctlCurrent.Value
ElseIf rsCurr(ctlCurrent.Name).Value <> ctlCurrent.Value Then
rsCurr(ctlCurrent.Name).Value = ctlCurrent.Value
End If
End If

End If

Err = 0

Next

DoEvents
rsCurr.Update
rsCurr.Close



jender624

 

[jender624]

Oops, sorry....disregard the previous post; I forgot to include the code that finds the current record. Here you go.


'-- Open the ADO connection
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
sConnect = sConnect & rsDBPath!be_db_location & ";"
conBE.Open sConnect
rsCurr.Open TableName, conBE, adOpenKeyset, adLockOptimistic, adCmdTable

sCriteria = "[KeyName] = " & Me.Controls("KeyName")
rsCurr.Find sCriteria

For Each ctlCurrent In Me.Controls

DoEvents

gvDummy = rsCurr(ctlCurrent.Name).Name

If Err = 0 Then

bCheckField = True

'-- If the current control is a keyfield and a counter, don't mess with it.
If ctlCurrent.Name = sKeyName Then
If rsCurr.Fields(ctlCurrent.Name).Properties("AutoIncrement") = True Then
bCheckField = False
End If
End If

If bCheckField Then
'-- If data has changed, then update it.
If IsNull(rsCurr(ctlCurrent.Name).Value) And Not IsNull(ctlCurrent.Value) Then
rsCurr(ctlCurrent.Name).Value = ctlCurrent.Value
ElseIf Not IsNull(rsCurr(ctlCurrent.Name).Value) And IsNull(ctlCurrent.Value) Then
rsCurr(ctlCurrent.Name).Value = ctlCurrent.Value
ElseIf rsCurr(ctlCurrent.Name).Value <> ctlCurrent.Value Then
rsCurr(ctlCurrent.Name).Value = ctlCurrent.Value
End If
End If

End If

Err = 0

Next

DoEvents
rsCurr.Update
rsCurr.Close



jender624

 

[RoyVidar]

I can reproduce the error with a dao recordset, but not with ADO (just a short test). Is it declared and instantiated as a ADODB.recordset?

Ok, perhaps just test the state of the recordset prior to (trying) to issue the update:

[tt]if (rscurr.editmode<>adetidnone) then
rscurr.update
end if[/tt]

- be aware that TableName might be a reserwed word (at least if you're doing some Word automation.

Roy-Vidar

 

[jender624]

I declare the recordset with the following line:

Dim rsCurr As New ADODB.Recordset

I did put in your suggestion of testing the editmode of the recordset. Hopefully this helps (heck, it can't hurt). Like I said, this is a sporadic error, and therefore is difficult to test. For now I'll see if this change makes a difference over the next week or so.

Thanks for all your suggestions!

jender624