Cannot Open Recordset

[TESA0_4]

Hi,
I have a form open that is using tblRiskAssess as its Record Source. On the
form is a command button that allows a user to initiate an 'archiving
process' that is intended to change the status of another record on
tblRiskAssess to 'Archived'. The code that is intended to complete this
process is as follows:

Dim MyDb2 As DAO.Database, MyRec2 As DAO.Recordset
Set MyDb2 = CurrentDb
Set MyRec2 = MyDb2.OpenRecordset("select [RAStatus], " & _
"[ApproveUse], [ArchivedDte], [ArchivedBy] " & _
"from tblRiskAssess where [RAIDAuto] = " & ArchiveRAIDAuto)
While Not MyRec2.EOF
MyRec2.Edit
MyRec2!RAStatus = "ARCHIVED"
MyRec2!ArchivedDte = Date
MyRec2!ArchivedBy = CurrentUser()
MyRec2.Update
MyRec2.MoveNext
Wend

When the code runs I get error 3188 Cannot Open Recordset - Locked by
another session.

I assume the error message appears because I am trying to update
tblRiskAssess at the same time as the open form is displaying another record
from tblRiskAssess.

If my assumption about the cause of the error message is wrong please
advise. If my assumption is correct, what do I need to do to enable the other
record to be updated while the form is open?

Thanks in anticipation of any guidance.

 

[banem2]

Hi,
I have a form open that is using tblRiskAssess as its Record Source. On the
form is a command button that allows a user to initiate an 'archiving
process' that is intended to change the status of another record on
tblRiskAssess to 'Archived'. The code that is intended to complete this
process is as follows:

Dim MyDb2 As DAO.Database, MyRec2 As DAO.Recordset
Set MyDb2 = CurrentDb
Set MyRec2 = MyDb2.OpenRecordset("select [RAStatus], " & _
"[ApproveUse], [ArchivedDte], [ArchivedBy] " & _
"from tblRiskAssess where [RAIDAuto] = " & ArchiveRAIDAuto)
While Not MyRec2.EOF
MyRec2.Edit
MyRec2!RAStatus = "ARCHIVED"
MyRec2!ArchivedDte = Date
MyRec2!ArchivedBy = CurrentUser()
MyRec2.Update
MyRec2.MoveNext
Wend

When the code runs I get error 3188 Cannot Open Recordset - Locked by
another session.

I assume the error message appears because I am trying to update
tblRiskAssess at the same time as the open form is displaying another record
from tblRiskAssess.

If my assumption about the cause of the error message is wrong please
advise. If my assumption is correct, what do I need to do to enable the other
record to be updated while the form is open?

Thanks in anticipation of any guidance.

Hi,

Try with SQL (assuming that ArchiveRAIDAuto is a free entry field on
your form):

On Error GoTo ErrHandler
Dim strSQL As String
Me.Dirty = False
strSQL = "UPDATE tblRiskAssess " & _
"SET tblRiskAssess.RAStatus = 'ARCHIVED', " & _
"tblRiskAssess.ArchivedDte = Date(), " & _
"tblRiskAssess.ArchivedBy = CurrentUser() "& _
"WHERE tblRiskAssess.RAIDAuto = " & [forms]![frmName]!
[ArchiveRAIDAuto]
CurrentDb.Execute strSQL, dbFailOnError
Exit Sub
ErrHandler:
MsgBox Err.Description

Regards,
Branislav Mihaljev,
Microsoft Access MVP

 

[TESA0_4]

Thanks Branislav. I inserted your code (with a slight modifcation) and it did
not work. While scratching my head for ideas I happened to open tblRiskAssess
while the debugger was active, and noticed that the record I was trying to
update was locked. In fact the nine records prior to the record I had open in
my form were locked. I put a Me.Refresh in the code so that the user input to
the form was saved before the Archive code runs and that resolved my problem
by unlocking the record I was trying to update (it was one of the nine
adjacent records).

Thanks for your inspiration!! Much appreciated.

Hi,
I have a form open that is using tblRiskAssess as its Record Source. On the
form is a command button that allows a user to initiate an 'archiving
process' that is intended to change the status of another record on
tblRiskAssess to 'Archived'. The code that is intended to complete this
process is as follows:

Dim MyDb2 As DAO.Database, MyRec2 As DAO.Recordset
Set MyDb2 = CurrentDb
Set MyRec2 = MyDb2.OpenRecordset("select [RAStatus], " & _
"[ApproveUse], [ArchivedDte], [ArchivedBy] " & _
"from tblRiskAssess where [RAIDAuto] = " & ArchiveRAIDAuto)
While Not MyRec2.EOF
MyRec2.Edit
MyRec2!RAStatus = "ARCHIVED"
MyRec2!ArchivedDte = Date
MyRec2!ArchivedBy = CurrentUser()
MyRec2.Update
MyRec2.MoveNext
Wend

When the code runs I get error 3188 Cannot Open Recordset - Locked by
another session.

I assume the error message appears because I am trying to update
tblRiskAssess at the same time as the open form is displaying another record
from tblRiskAssess.

If my assumption about the cause of the error message is wrong please
advise. If my assumption is correct, what do I need to do to enable the other
record to be updated while the form is open?

Thanks in anticipation of any guidance.

Hi,

Try with SQL (assuming that ArchiveRAIDAuto is a free entry field on
your form):

On Error GoTo ErrHandler
Dim strSQL As String
Me.Dirty = False
strSQL = "UPDATE tblRiskAssess " & _
"SET tblRiskAssess.RAStatus = 'ARCHIVED', " & _
"tblRiskAssess.ArchivedDte = Date(), " & _
"tblRiskAssess.ArchivedBy = CurrentUser() "& _
"WHERE tblRiskAssess.RAIDAuto = " & [forms]![frmName]!
[ArchiveRAIDAuto]
CurrentDb.Execute strSQL, dbFailOnError
Exit Sub
ErrHandler:
MsgBox Err.Description

Regards,
Branislav Mihaljev,
Microsoft Access MVP