Modifying the current record in code

[Guest]

I am trying to modify values of the currently selected record of a form in VB
Code. The following gets called when the check box is clicked on a treeview
on the form:

rs.Edit
If (complete) Then
rs("LastStatus") = rs("Status")
rs("Status") = CStatus
Else
rs("Status") = rs("LastStatus")
rs("LastStatus") = CStatus
End If
rs.Update

I get the error message, "Run-time error '31'97': The Microsoft Jet database
engine stopped the provess because you and another user are attempting to
change the same data at the same time." on the rs.Update.

I thought that rs.Update dbUpdateCurrentRecord might solve the problem, but
I get an "Invalid Argument" error on that call.

I have tried setting rs = Me.recordset and Me.recordsetclone; neither seem
to work. I have also tried calling me.refresh first and setting me.dirty =
false all to no avail.

I am assuming that since the mouse_click event code sets the current record
based on which node was clicked in the treeview, that access is using
something akin to "Me.recordset.edit" as soon as it changes to the current
record. This "locks" the current record and I am not allowed to concurrently
edit the data.

Unfortunately, I cannot put code in the "current" event procedure to
disallow edits since I have no way of knowing if the node has been "checked"
or just simply clicked.

I realize that I can access the fields of the recordset directly in VB (i.e.
"LastStatus = Status"). This would be fine in most cases, but there are
others in which I am forced to iterate over the records in the recordset and
this may include the "current record" that is open on the form. Perhaps I
could just check for the condition that the current record in the recordset
is the same as the one in the form, but I would really like to avoid this. I
have a hard time believing and can't force access to temporarily relinquish
its lock on the current record.

So, my question is, how can I programmtically edit the values of fields in
the current record of the form using a recordset?

 

[Graham Mandeno]

Hi Robbie

You don't need to open another recordset - in fact that is what is causing
the problem. Refer to the fields directly via the fom's own recordset:

If (complete) Then
Me!LastStatus = Me!Status
Me!Status = CStatus
Else
Me!Status = Me!LastStatus
Me!LastStatus = CStatus
End If

You should remove the Edit and Update also.

 

[Guest]

Thank you for the help. As noted at the end of my post, I have successfully
used code such as "LastStatus = Status" (I usually leave off the "me"s). It
is my fault for not being more clear on the problem I was asking since you
did answer the question I asked even though I already had the answer.

While the method of directly assigning the variables works for the current
record, I continue to get the same error message while iterating through a
recordset.

However, I believe I finally found the problem. I think that iterating
through the recordset would hit the current record and cause the problem
(though I believe other records still threw the error; I still haven't
figured the exact cause).

Because rs.FindFirst doesn't appear to support subqueries (i.e. (Field IN
(SELECT ...))) I think the best thing to do is run an update query and the
requery the form.

Hi Robbie

You don't need to open another recordset - in fact that is what is causing
the problem. Refer to the fields directly via the fom's own recordset:

If (complete) Then
Me!LastStatus = Me!Status
Me!Status = CStatus
Else
Me!Status = Me!LastStatus
Me!LastStatus = CStatus
End If

You should remove the Edit and Update also.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am trying to modify values of the currently selected record of a form in
VB
Code. The following gets called when the check box is clicked on a
treeview
on the form:

rs.Edit
If (complete) Then
rs("LastStatus") = rs("Status")
rs("Status") = CStatus
Else
rs("Status") = rs("LastStatus")
rs("LastStatus") = CStatus
End If
rs.Update

I get the error message, "Run-time error '31'97': The Microsoft Jet
database
engine stopped the provess because you and another user are attempting to
change the same data at the same time." on the rs.Update.

I thought that rs.Update dbUpdateCurrentRecord might solve the problem,
but
I get an "Invalid Argument" error on that call.

I have tried setting rs = Me.recordset and Me.recordsetclone; neither seem
to work. I have also tried calling me.refresh first and setting me.dirty
=
false all to no avail.

I am assuming that since the mouse_click event code sets the current
record
based on which node was clicked in the treeview, that access is using
something akin to "Me.recordset.edit" as soon as it changes to the current
record. This "locks" the current record and I am not allowed to
concurrently
edit the data.

Unfortunately, I cannot put code in the "current" event procedure to
disallow edits since I have no way of knowing if the node has been
"checked"
or just simply clicked.

I realize that I can access the fields of the recordset directly in VB
(i.e.
"LastStatus = Status"). This would be fine in most cases, but there are
others in which I am forced to iterate over the records in the recordset
and
this may include the "current record" that is open on the form. Perhaps I
could just check for the condition that the current record in the
recordset
is the same as the one in the form, but I would really like to avoid this.
I
have a hard time believing and can't force access to temporarily
relinquish
its lock on the current record.

So, my question is, how can I programmtically edit the values of fields in
the current record of the form using a recordset?

 

[Graham Mandeno]

Hi Robbie

You should be able to modify the current record, either using an update
query or using a DAO .Edit/.Update, provided the current record is not
dirty.

I suggest you force a save record before you run your update code:

If Me.Dirty Then RunCommand acCmdSaveRecord
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thank you for the help. As noted at the end of my post, I have
successfully
used code such as "LastStatus = Status" (I usually leave off the "me"s).
It
is my fault for not being more clear on the problem I was asking since you
did answer the question I asked even though I already had the answer.

While the method of directly assigning the variables works for the current
record, I continue to get the same error message while iterating through a
recordset.

However, I believe I finally found the problem. I think that iterating
through the recordset would hit the current record and cause the problem
(though I believe other records still threw the error; I still haven't
figured the exact cause).

Because rs.FindFirst doesn't appear to support subqueries (i.e. (Field IN
(SELECT ...))) I think the best thing to do is run an update query and the
requery the form.

Hi Robbie

You don't need to open another recordset - in fact that is what is
causing
the problem. Refer to the fields directly via the fom's own recordset:

If (complete) Then
Me!LastStatus = Me!Status
Me!Status = CStatus
Else
Me!Status = Me!LastStatus
Me!LastStatus = CStatus
End If

You should remove the Edit and Update also.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am trying to modify values of the currently selected record of a form
in
VB
Code. The following gets called when the check box is clicked on a
treeview
on the form:

rs.Edit
If (complete) Then
rs("LastStatus") = rs("Status")
rs("Status") = CStatus
Else
rs("Status") = rs("LastStatus")
rs("LastStatus") = CStatus
End If
rs.Update

I get the error message, "Run-time error '31'97': The Microsoft Jet
database
engine stopped the provess because you and another user are attempting
to
change the same data at the same time." on the rs.Update.

I thought that rs.Update dbUpdateCurrentRecord might solve the problem,
but
I get an "Invalid Argument" error on that call.

I have tried setting rs = Me.recordset and Me.recordsetclone; neither
seem
to work. I have also tried calling me.refresh first and setting
me.dirty
=
false all to no avail.

I am assuming that since the mouse_click event code sets the current
record
based on which node was clicked in the treeview, that access is using
something akin to "Me.recordset.edit" as soon as it changes to the
current
record. This "locks" the current record and I am not allowed to
concurrently
edit the data.

Unfortunately, I cannot put code in the "current" event procedure to
disallow edits since I have no way of knowing if the node has been
"checked"
or just simply clicked.

I realize that I can access the fields of the recordset directly in VB
(i.e.
"LastStatus = Status"). This would be fine in most cases, but there
are
others in which I am forced to iterate over the records in the
recordset
and
this may include the "current record" that is open on the form.
Perhaps I
could just check for the condition that the current record in the
recordset
is the same as the one in the form, but I would really like to avoid
this.
I
have a hard time believing and can't force access to temporarily
relinquish
its lock on the current record.

So, my question is, how can I programmtically edit the values of fields
in
the current record of the form using a recordset?