Avatar of JOKL
JOKL
 asked on

Workaround Could not update; currently locked by another session on this machine

I have built  A Share Registration Application and in the Transaction Subform you select the share certificates to cancel and then resisue them to someone else.

When you select a share cert to cancel the application must find the record in the transaction detail table and change its inissue flag and recored the current transactionid in the cancelationID field for audit trail   - so you can track which transcation cancelled the shaares when veiewing the original record and to ensure that a cert when cancelled does not presentitself again on the Combo box which offers the user the share certs owned by the shareholder and available for Cancellation.

So when the user selects a cert in the Combo box the before update code kicks in to effect the above.

The 1st step is to see if he is changing his mind on which cert he wants to cancel - you have to undo the previous cancellation and cancel this one
 if it is new cancellation you just go ahead and cancel the cert issued previously and record the information on the currect record

My problem is that I get the error above when i try and edit the old record on the table my code is as follows: Do I need to clone the recordset?

Private Sub CertNoIssued_BeforeUpdate(Cancel As Integer)
Dim test As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("sharetransactiondetails", dbOpenDynaset)
Dim Msg As String
Dim NewID As String
Set db = CurrentDb
Dim SearchID As Variant
test = Me!TransactionDescription2
Select Case test
        Case "Sell", "Cancel", "Redemption"

        'Are you Editing an Existing Cert # or Entering a new One ???
        If Me.CertNoIssued.OldValue <> Me.CertNoIssued And Me.CertNoIssued.OldValue > 0 Then
            ' You are editing a sharecert therefore I must undo the cancellation ID and Reset the Inissue Flag
       
            SearchCertID = Me.CertNoIssued.OldValue
            SearchCompanyId = Forms!Sharetransactions.CompanyId
            SearchShareholderID = Me.ShareholderID
            SearchNoOfShares = Me.NoOfShares * -1
            SearchCancelID = Forms!Sharetransactions.TransactionID
           
            rst.FindFirst "CertNoIssued =" & SearchCertID & "AND NoOfShares =" & SearchNoOfShares & "And CancelationID = " & SearchCancelID
            rst.Edit
            rst!InIssue = True
            rst!CancelationID = 0
            rst!ShareLocation = 1
            rst.Update
            SearchID = Me.CertNoIssued.Column(2)
            rst.FindFirst "TransactionDetailID =" & SearchID
            rst.Edit
            rst!InIssue = False
            rst!CancelationID = Forms!Sharetransactions!TransactionID
            rst.Update
            rst.Close
           
            Me.NoOfShares = -Me.CertNoIssued.Column(1)
            Me.InIssue = False
            Me.ShareLocation = 3
        Else
        ' you are selecting a cert for the 1st time
        'search for the transaction on the original cert and update the cancellation ID and Reset the Inissue Flag
         'Set rst = CurrentDb.OpenRecordset("sharetransactiondetails", dbOpenDynaset)
                SearchID = Me.CertNoIssued.Column(2)
                rst.FindFirst "TransactionDetailID =" & SearchID
                rst.Edit
                rst!InIssue = False
                rst!CancelationID = Forms!Sharetransactions!TransactionID
                rst.Update
                rst.Close
                Me.NoOfShares = -Me.CertNoIssued.Column(1)
                Me.InIssue = False
                Me.ShareLocation = 3
        End If

End Select


Microsoft Access

Avatar of undefined
Last Comment
JOKL

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
puppydogbuddy

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
JOKL

ASKER
Sorry I have been away:

ok let me try that this week and I will get back to you
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck