DAO.recordset update fail

[Guest]

Hello,

I am trying to update text fields in a DAO recordset.
Microsoft returns the error
"Run-time error 3197
The Microsoft Jet database engine stopped the process because you and
another user are attempting to change the same data at the same time."

The recordset contains order header records and order line records (one
header record to many line records).
The line record fields update perfectly. The header records
are causing the error.

The Code:
Dim newdate As String
Dim rs1 As DAO.Recordset
With Me.FrmDetail.Form
Set rs1 = Me.FrmDetail.Form.RecordsetClone
End With
With rs1
Do While Not rs1.EOF
.Edit
![PROMISE_DATE] = newdate
![REQUEST_DATE] = newdate
![REQ_SHIP_DATE] = newdate
![SHIPPING_DATE] = newdate
.Update
.MoveNext
Loop

End With

rs1.Close
Set rs1 = Nothing


Thanks for your help!

 

[Dirk Goldgar]

Hello,

I am trying to update text fields in a DAO recordset.
Microsoft returns the error
"Run-time error 3197
The Microsoft Jet database engine stopped the process because you and
another user are attempting to change the same data at the same time."

The recordset contains order header records and order line records
(one header record to many line records).
The line record fields update perfectly. The header records
are causing the error.

The Code:
Dim newdate As String
Dim rs1 As DAO.Recordset
With Me.FrmDetail.Form
Set rs1 = Me.FrmDetail.Form.RecordsetClone
End With
With rs1
Do While Not rs1.EOF
.Edit
![PROMISE_DATE] = newdate
![REQUEST_DATE] = newdate
![REQ_SHIP_DATE] = newdate
![SHIPPING_DATE] = newdate
.Update
.MoveNext
Loop

End With

rs1.Close
Set rs1 = Nothing


Thanks for your help!

From the looks of it, the recordset being updated is the recordsetclone
of a subform. Is that correct? I note that this cannot be all the
code, because you never set a value for newdate.

Usually, in these cases, the "other user" reported by the error message
is you -- either by way of code or by the user interface, you have
edited and not yet saved one of the records this code is trying to
update. Could that be the case here?

Minor notes:

With Me.FrmDetail.Form
Set rs1 = Me.FrmDetail.Form.RecordsetClone
End With

What's the point of the "With" block if you aren't going to use the
object reference it defines? The With and End With lines above could be
deleted, and probably should be.

rs1.Close

Don't close the recordsetclone. You didn't open it. IIRC, your attempt
to close it is going to be ignored anyway.

 

[Guest]

I did not include my code for the newdate variable.
It is just a string and it's not the issue.
The form has a text box for the newdate input.
There is a sub form displaying the results of a query in a grid.
and I am trying to update the
recordsetclone.
I am the only user in the database, so yes I am locking myself up.

Should I be refreshing the sub form after each update?

Hello,

I am trying to update text fields in a DAO recordset.
Microsoft returns the error
"Run-time error 3197
The Microsoft Jet database engine stopped the process because you and
another user are attempting to change the same data at the same time."

The recordset contains order header records and order line records
(one header record to many line records).
The line record fields update perfectly. The header records
are causing the error.

The Code:
Dim newdate As String
Dim rs1 As DAO.Recordset
With Me.FrmDetail.Form
Set rs1 = Me.FrmDetail.Form.RecordsetClone
End With
With rs1
Do While Not rs1.EOF
.Edit
![PROMISE_DATE] = newdate
![REQUEST_DATE] = newdate
![REQ_SHIP_DATE] = newdate
![SHIPPING_DATE] = newdate
.Update
.MoveNext
Loop

End With

rs1.Close
Set rs1 = Nothing


Thanks for your help!

From the looks of it, the recordset being updated is the recordsetclone
of a subform. Is that correct? I note that this cannot be all the
code, because you never set a value for newdate.

Usually, in these cases, the "other user" reported by the error message
is you -- either by way of code or by the user interface, you have
edited and not yet saved one of the records this code is trying to
update. Could that be the case here?

Minor notes:

With Me.FrmDetail.Form
Set rs1 = Me.FrmDetail.Form.RecordsetClone
End With

What's the point of the "With" block if you aren't going to use the
object reference it defines? The With and End With lines above could be
deleted, and probably should be.

rs1.Close

Don't close the recordsetclone. You didn't open it. IIRC, your attempt
to close it is going to be ignored anyway.

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

(please reply to the newsgroup)

 

[Dirk Goldgar]

I did not include my code for the newdate variable.
It is just a string and it's not the issue.
The form has a text box for the newdate input.
There is a sub form displaying the results of a query in a grid.
and I am trying to update the
recordsetclone.
I am the only user in the database, so yes I am locking myself up.

Should I be refreshing the sub form after each update?

Not refreshing, but you need to make sure the subform isn't "dirty" at
the time you update it. You can do that by changing this code:

With Me.FrmDetail.Form
Set rs1 = Me.FrmDetail.Form.RecordsetClone
End With

to this:

With Me.FrmDetail.Form
If .Dirty Then .Dirty = False
Set rs1 = Me.FrmDetail.Form.RecordsetClone
End With

That ought to ensure that any dirty record on the subform is saved
before you loop through the recordset to update it.

I can't quite figure out why this would be necessary, though, because
this code of yours is executing on the main form. If it was initiated
by some user action on the main form, so that the main form was active,
the subform couldn't be dirty. I guess you could be calling it from the
subform, and that would explain it; otherwise I'm at a loss.