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("s haretransa ctiondetai ls", 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.Co mpanyId
SearchShareholderID = Me.ShareholderID
SearchNoOfShares = Me.NoOfShares * -1
SearchCancelID = Forms!Sharetransactions.Tr ansactionI D
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!Tr ansactionI D
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("s haretransa ctiondetai ls", dbOpenDynaset)
SearchID = Me.CertNoIssued.Column(2)
rst.FindFirst "TransactionDetailID =" & SearchID
rst.Edit
rst!InIssue = False
rst!CancelationID = Forms!Sharetransactions!Tr ansactionI D
rst.Update
rst.Close
Me.NoOfShares = -Me.CertNoIssued.Column(1)
Me.InIssue = False
Me.ShareLocation = 3
End If
End Select
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(
Dim test As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("s
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.Co
SearchShareholderID = Me.ShareholderID
SearchNoOfShares = Me.NoOfShares * -1
SearchCancelID = Forms!Sharetransactions.Tr
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!Tr
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("s
SearchID = Me.CertNoIssued.Column(2)
rst.FindFirst "TransactionDetailID =" & SearchID
rst.Edit
rst!InIssue = False
rst!CancelationID = Forms!Sharetransactions!Tr
rst.Update
rst.Close
Me.NoOfShares = -Me.CertNoIssued.Column(1)
Me.InIssue = False
Me.ShareLocation = 3
End If
End Select
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok let me try that this week and I will get back to you