transaction with two recordsets

tonyhatwork · Nov 18, 2007

Hi,

I have two recordsets (one record in each ) that are being updated with recordset.update.
I want to be able to rollback if either of these records is locked.
Does begintrans only permit rollback for a single recordset ?

I have tried a couple of methods, one being :

on error goto errorhandle

workspace.begintrans

workspace.begintrans
rs1.update
workspace.committrans

workspace.begintrans
rs2.update
workspace.comittrans

workspace.committrans

errorhandle:

workspace.rollback

end

end sub

I keep on getting err 3034 - you tried to commit or rollback a transaction without first beginning a transaction

Should I re-write my sql to retrieve both records joined together ?

The database is microsoft jet in a multi-user environment.

Thanks in advance of your assistance.

Tony

Ed2020 · Nov 19, 2007

You don't need so many BeginTrans/CommitTrans blocks. The following will do:

Code:

on error goto errorhandle

workspace.begintrans

rs1.update

rs2.update

workspace.committrans

errorhandle:
workspace.rollback

end sub

Ed Metcalfe.

Please do not feed the trolls.....

tonyhatwork · Nov 19, 2007

Thanks Ed.

Unfortunately I had already tried that. So the problem is deeper than that.

The recordset updates are done with class methods.

So :
ws.begintrans
class1.update
class2.update
ws.committrans

errorhandle:
ws.rollback

Any reason reason why this should not work ?
I have made sure that the public variable ws does not get set within the two classes in case that would be a problem.

Both updates work correctly on their own.

Any suggestions ?

Tony

Ed2020 · Nov 19, 2007

Tony,

I'm not sure without seeing the code from your two classes and also the code you are calling it from.

Do you have BeginTrans/Commit blocks in your class code as well as the calling code? Are all the workspace references pointing to the same workspace?

Ed Metcalfe.

Please do not feed the trolls.....

tonyhatwork · Nov 19, 2007

Ed, That would be a lot of code to send to you.

There is only the one database and I have not created any additional workspaces in code.
There are no begintrans/commit blocks in the classes.
The only place that the workspace variable gets set is in the calling code.

Between the begintrans and the updates there are a couple of recordsets that are created that are are eventually set the recordset property of the class.

I can send the code through as this is the only thing preventing me from completing the project but it may require the data in addition.

Pls excuse the novice programmer as there is a fair amount of duplication of code.

Should I paste all the code on to this site ?

Thanks

Tony

Ed2020 · Nov 19, 2007

Tony,

Yeah go for it. Post it here.

Ed Metcalfe.

Please do not feed the trolls.....

tonyhatwork · Nov 19, 2007

Main form code - Form_FixingForm

Private Sub complete_Click()

On Error GoTo errorhandle:

Dim db As Database
Set db = Application.CurrentDb
Set ws = DBEngine.Workspaces(0)

'validation function
If complValidate = True Then

fixing.setProptoForm

If fixing.CouponPayDate Mod 7 = 1 Or fixing.CouponPayDate Mod 7 = 0 Then
MsgBox ("The coupon payment date is a weekend")
Exit Sub
End If

If Me.lastFix.Value = fale Then
If fixing.NextFixDate Mod 7 = 1 Or fixing.NextFixDate Mod 7 = 0 Then
MsgBox ("The next fixing date is a weekend")
Exit Sub
End
End If
End If

If Me.murexcheck = False Then
fixing.Waiting = True
Else: fixing.Waiting = False
End If

'ws.BeginTrans

If Form_FixingForm.FixingNumber = "" Then
Stop
'have yet to go this path of the if statement

Set fixing = New fixing
fixing.setProptoForm
Stop
fixing.AddNew
Stop
fixing.Update
'fixing.add waiting murex flag property ?
'
Else:
fixing.Update

'
'
'
End If

Set security = New security

OpenFolder.Visible = False
koCallRedeem.Visible = False
koCallRedeemLabel.Visible = False
Form_FixingForm.AddCB.Enabled = False
Form_FixingForm.AddIsinTB = Null
Form_FixingForm.AddIsinTB.Enabled = True
Form_FixingForm.Folder.Visible = False
Form_FixingForm.AddMurexTB.Value = ""
Form_FixingForm.AddIssuerCbo.Value = Null
Form_FixingForm.AddSeriesTB.Value = Null
Form_FixingForm.AddNextFixDate.Value = Null
Form_FixingForm.AmendSecurity.Enabled = False

Me.GetCalendar.SetFocus
Me.calcpay.Enabled = False
Me.CancelFixing.Enabled = False
Me.complete.Enabled = False
Me.regenNotice.Enabled = False
Me.template.Enabled = False
Me.amend.Enabled = False

security.ISIN = fixing.ISIN

Me.AddIsinTB = security.ISIN

security.LoadTextBoxes

security.NextFixDate = fixing.NextDay & "/" & fixing.NextMonth & "/" & fixing.NextYear

security.Update

'ws.CommitTrans (1)

If murexcheck Then

fixing.notification
End If

End If

Exit Sub

errorhandle:
'if appropriate error number then ws.rollback
MsgBox (Err.Number & " " & Err.Description)
Stop

End Sub

'below sets the fixing recordset

Private Sub WaitingCalendarList_DblClick(Cancel As Integer)
Form_FixingForm.PreviousList.RowSourceType = "list"
Form_FixingForm.PreviousList.RowSource = ""

Set fixing = New fixing

fixing.getSelectedItem

'Form_FixingForm.Folder.Visible = False
regenNotice.Enabled = False
ByDenom.Enabled = True
ByDenom.Value = 0
amend.Enabled = False
template.Enabled = False
'FixingNumber.Enabled = False
'VersionNumber.Enabled = False
'ISIN.Enabled = False
'Issuer.Enabled = False
'SeriesNum.Enabled = False
Notional.Enabled = True
IssueCcy.Enabled = True
PayCcy.Enabled = True
Maturity_Date.Enabled = True
OptRate1.Enabled = True
OptAmt1.Enabled = True
StartDay.Enabled = True
StartMonth.Enabled = True
Start_Year.Enabled = True
EndDay.Enabled = True
EndMonth.Enabled = True
EndYear.Enabled = True
CoupPayDay.Enabled = True
CoupPayMonth.Enabled = True
CoupPayYear.Enabled = True
NextDay.Enabled = True
NextMonth.Enabled = True
NextYear.Enabled = True
DayBasis.Enabled = True
PayAmt.Enabled = True
Multi.Enabled = True
Days1Top.Enabled = True
Day1Bottom.Enabled = True
OptRate2.Enabled = True
OptAmt2.Enabled = True
Notes.Enabled = True

Days2Top.Enabled = True
Days2Bottom.Enabled = True
OptRate3.Enabled = True
OptAmt3.Enabled = True
'CancelFixing.Enabled = False

calcpay.Enabled = True

complete.Enabled = True
CouponRate.Enabled = True
OptNarrative.Enabled = True

lastFix.Enabled = True
murexcheck.Enabled = True

If Form_FixingForm.ListTitle.Caption = "Calendar" Then
Days2Top.Visible = True
Days2Bottom.Visible = True
Label123.Visible = True
Label134.Visible = True

Notional = Null
IssueCcy = Null
PayCcy = Null
Maturity_Date = Null
OptRate1 = Null
OptAmt1 = Null
StartDay = Null
StartMonth = Null
Start_Year = Null
EndDay = Null
EndMonth = Null
EndYear = Null
CoupPayDay = Null
CoupPayMonth = Null
CoupPayYear = Null
NextDay = Null
NextMonth = Null
NextYear = Null
DayBasis = Null
Me.IssueCcy = Null

PayAmt = Null

Days1Top = Null
Day1Bottom = Null
OptRate2 = Null
OptAmt2 = Null
Notes = Null

Days2Top = Null
Days2Bottom = Null
OptRate3 = Null
OptAmt3 = Null
CouponRate = Null
OptNarrative = Null
kocall = Null

lastFix = 0
murexcheck = 0

VersionNumber.Value = 1
CancelFixing.Enabled = False

'adds any previous fixings in that security to listbox
fixing.PopulateList ("ISIN" & Form_FixingForm.WaitingCalendarList.Column(0, fixing.selectedItem))

'add calendar entries to fixingform part
Form_FixingForm.ISIN = Form_FixingForm.WaitingCalendarList.Column(0, fixing.selectedItem)
Form_FixingForm.Issuer = Form_FixingForm.WaitingCalendarList.Column(4, fixing.selectedItem)
Form_FixingForm.SeriesNum = Form_FixingForm.WaitingCalendarList.Column(2, fixing.selectedItem)
Form_FixingForm.FixingNumber = ""
Me.Notional.SetFocus
Form_FixingForm.AddIsinTB.Value = Form_FixingForm.ISIN
Else:
CancelFixing.Enabled = True
Me.murexcheck.SetFocus
Me.lastFix = 0

fixing.getRecordset (Form_FixingForm.WaitingCalendarList.Column(0, Form_FixingForm.WaitingCalendarList.ItemsSelected(0)))

fixing.Load
fixing.Waiting = True

Form_FixingForm.AddIsinTB.Value = fixing.ISIN
If fixing.Multiperiod = True Then
Days2Top.Visible = True
Days2Bottom.Visible = True
Label123.Visible = True
Label134.Visible = True
Else
Days2Top.Visible = False
Days2Bottom.Visible = False
Label123.Visible = False
Label134.Visible = False
End If
'should be no requirement to populate from previous for waiting
'fixing.PopulateList ("ISIN" & Form_FixingForm.WaitingCalendarList.Column(3, fixing.selectedItem))
End If

Set security = New security
security.LoadTextBoxes

Select Case Form_FixingForm.ListTitle.Caption

Dim col As Integer

Case "Calendar"
col = 0
Case "Waiting"
col = 4
End Select

security.getFolderPath (Form_FixingForm.WaitingCalendarList.Column(col, fixing.selectedItem))

If security.FolderPath <> "" Then

security.OpenFolder

Else: MsgBox ("If you wish to add the folder pls do so by searching the ISIN at the top of page?")

End If
Form_FixingForm.SetFocus

End Sub

fixingclass

Function setProptoForm()
' Procedure: setProptoForm
' DateTime: 14/11/2007 15:05:36
' Author: Tony Hallett
' Description: sets the fixing properties to the data in the form
'--
'problem with isnull as properties not set as variant
On Error Resume Next

'mlngPrimarykey = .Fields("Primary key").Value

Me.ISIN = Form_FixingForm.ISIN

Me.Issuer = Form_FixingForm.Issuer

Me.PaymentAmount = Form_FixingForm.PayAmt

Me.Notes = Form_FixingForm.Notes

Me.Notional = Form_FixingForm.Notional

Me.IssueCurrency = Form_FixingForm.IssueCcy

Me.SeriesNo = Form_FixingForm.SeriesNum

Me.MaturityDate = Form_FixingForm.Maturity_Date

Me.StartDay = Form_FixingForm.StartDay

Me.StartMonth = Form_FixingForm.StartMonth

Me.startyear = Form_FixingForm.Start_Year

Me.EndDay = Form_FixingForm.EndDay

Me.EndMonth = Form_FixingForm.EndMonth

Me.EndYear = Form_FixingForm.EndYear

Me.DayBasis = Form_FixingForm.DayBasis

Me.Narrative1 = Form_FixingForm.OptRate1

Me.Narr1Amt = Form_FixingForm.OptAmt1

Me.Narrative2 = Form_FixingForm.OptRate2

Me.Narr2Amt = Form_FixingForm.OptAmt2

Me.CouponRate = Form_FixingForm.CouponRate

Me.NarrativeDays = Form_FixingForm.OptNarrative

Me.Narrative3 = Form_FixingForm.OptRate3

Me.Narr3Amt = Form_FixingForm.OptAmt3

Me.DaysTop = Form_FixingForm.Days1Top

Me.DaysBottom = Form_FixingForm.Day1Bottom

Me.Days2Top = Form_FixingForm.Days2Top

Me.Days2Bottom = Form_FixingForm.Days2Bottom

Me.PayDay = Form_FixingForm.CoupPayDay

Me.PayMonth = Form_FixingForm.CoupPayMonth

Me.PayYear = Form_FixingForm.CoupPayYear

Me.PayCcy = Form_FixingForm.PayCcy

Me.NextDay = Form_FixingForm.NextDay

Me.NextMonth = Form_FixingForm.NextMonth

Me.NextYear = Form_FixingForm.NextYear

Me.Multiperiod = Form_FixingForm.Multi

Me.ByDenom = Form_FixingForm.ByDenom

Me.FixingDate = Date

Me.CouponPayDate = Me.PayDay & "/" & Me.PayMonth & "/" & Me.PayYear

Me.NextFixDate = Me.NextDay & "/" & Me.NextMonth & "/" & Me.NextYear

Me.version = Form_FixingForm.VersionNumber

Me.FixingNumber = Form_FixingForm.FixingNumber

Me.FixedBy = CurrentUser

End Function

Public Sub AddNew()
mbooLoaded = False
End Sub

Public Sub Update()

With Recordset

If mbooLoaded = True Then
.Edit
Else
.AddNew
End If

.Fields("ISIN").Value = Me.ISIN

.Fields("Issuer").Value = Me.Issuer

.Fields("Payment Amount").Value = Me.PaymentAmount

.Fields("Notes").Value = Me.Notes

.Fields("Notional").Value = Me.Notional

.Fields("Issue Currency").Value = Me.IssueCurrency

.Fields("Series No").Value = Me.SeriesNo

.Fields("Maturity Date").Value = Me.MaturityDate

.Fields("StartDay").Value = Me.StartDay

.Fields("StartMonth").Value = Me.StartMonth

.Fields("StartYear").Value = Me.startyear

.Fields("EndDay").Value = Me.EndDay

.Fields("EndMonth").Value = Me.EndMonth

.Fields("EndYear").Value = Me.EndYear

.Fields("Day Basis").Value = Me.DayBasis

.Fields("Narrative 1").Value = Me.Narrative1

.Fields("Narr1 Amt").Value = Me.Narr1Amt

.Fields("Narrative 2").Value = Me.Narrative2

.Fields("Narr2 Amt").Value = Me.Narr2Amt

.Fields("Coupon Rate").Value = Me.CouponRate

.Fields("Narrative Days").Value = Me.NarrativeDays

.Fields("Narrative 3").Value = Me.Narrative3

.Fields("Narr3 Amt").Value = Me.Narr3Amt

.Fields("Days Top").Value = Me.DaysTop

.Fields("Days Bottom").Value = Me.DaysBottom

.Fields("Days2 Top").Value = Me.Days2Top

.Fields("Days2 Bottom").Value = Me.Days2Bottom

.Fields("PayDay").Value = Me.PayDay

.Fields("PayMonth").Value = Me.PayMonth

.Fields("PayYear").Value = Me.PayYear

.Fields("Pay CCY").Value = Me.PayCcy

.Fields("NextDay").Value = Me.NextDay

.Fields("NextMonth").Value = Me.NextMonth

.Fields("NextYear").Value = Me.NextYear

.Fields("Multiperiod").Value = Me.Multiperiod

.Fields("By Denom").Value = Me.ByDenom

'preferable to get from the network drive
.Fields("Fixing Date").Value = Date

.Fields("Coupon Pay Date").Value = Me.CouponPayDate

.Fields("Waiting").Value = Me.Waiting

.Fields("Fixed By").Value = Application.CurrentUser

.Fields("Cancelled").Value = Me.Cancelled

.Fields("nextfixdate").Value = Me.NextDay & "/" & Me.NextMonth & "/" & Me.NextYear

.Update
End With
mbooLoaded = True
End Sub

Public Sub getSelectedItem()
' Procedure: getSelectedItem
' DateTime: 10/11/2007 14:37:22
' Author: Tony Hallett
' Description: gets the selected item from the waiting/calendar list
'--
Dim selitm As Variant
On Error GoTo errorhandle
selitm = Form_FixingForm.WaitingCalendarList.ItemsSelected(0)
Me.selectedItem = selitm
Exit Sub

errorhandle:
MsgBox ("Please select an item in the list")

End

End Sub

Public Function getRecordset(num As Integer)
' Procedure: getRecordset
' DateTime: 11/11/2007 23:00:24
' Author: Tony Hallett
' Description: sets the recordset property from the primary key
'--

On Error GoTo errorhandle
Dim newquerydef As QueryDef
Dim strSql As String
strSql = "SELECT * FROM AllRetailFixings2 " & _
"WHERE [Primary key] =" & num
Set newquerydef = Application.CurrentDb.CreateQueryDef("", strSql)
Set Recordset = newquerydef.OpenRecordset

'''''''''''''''''''''''

Exit Function

errorhandle:

End Function

Public Sub Load()
On Error Resume Next
With Recordset
mlngPrimarykey = .Fields("Primary key").Value

Me.ISIN = .Fields("ISIN").Value
Form_FixingForm.ISIN = .Fields("ISIN").Value

Me.Issuer = .Fields("Issuer").Value
Form_FixingForm.Issuer = .Fields("Issuer").Value

'Me.Murexreference = .Fields("Murex reference").Value
'Form_FixingForm.Murex = .Fields("Murex reference").Value

Me.PaymentAmount = .Fields("Payment Amount").Value
Form_FixingForm.PayAmt = .Fields("Payment Amount").Value

Me.Notes = .Fields("Notes").Value
Form_FixingForm.Notes = .Fields("Notes").Value

Me.Notional = .Fields("Notional").Value
Form_FixingForm.Notional = .Fields("Notional").Value

Me.IssueCurrency = .Fields("Issue Currency").Value
Form_FixingForm.IssueCcy = .Fields("Issue Currency").Value

Me.SeriesNo = .Fields("Series No").Value
Form_FixingForm.SeriesNum = .Fields("Series No").Value

Me.MaturityDate = .Fields("Maturity Date").Value
Form_FixingForm.Maturity_Date = .Fields("Maturity Date").Value

Me.StartDay = .Fields("StartDay").Value
Form_FixingForm.StartDay = .Fields("StartDay").Value

Me.StartMonth = .Fields("StartMonth").Value
Form_FixingForm.StartMonth = .Fields("StartMonth").Value

Me.startyear = .Fields("StartYear").Value
Form_FixingForm.Start_Year = .Fields("StartYear").Value

Me.EndDay = .Fields("EndDay").Value
Form_FixingForm.EndDay = .Fields("EndDay").Value

Me.EndMonth = .Fields("EndMonth").Value
Form_FixingForm.EndMonth = .Fields("EndMonth").Value

Me.EndYear = .Fields("EndYear").Value
Form_FixingForm.EndYear = .Fields("EndYear").Value

Me.DayBasis = .Fields("Day Basis").Value
Form_FixingForm.DayBasis = .Fields("Day Basis").Value
''''''
Me.Narrative1 = .Fields("Narrative 1").Value
Form_FixingForm.OptRate1 = .Fields("Narrative 1").Value

Me.Narr1Amt = .Fields("Narr1 Amt").Value
Form_FixingForm.OptAmt1 = .Fields("Narr1 Amt").Value

Me.Narrative2 = .Fields("Narrative 2").Value
Form_FixingForm.OptRate2 = .Fields("Narrative 2").Value

Me.Narr2Amt = .Fields("Narr2 Amt").Value
Form_FixingForm.OptAmt2 = .Fields("Narr2 Amt").Value

Me.CouponRate = .Fields("Coupon Rate").Value
Form_FixingForm.CouponRate = .Fields("Coupon Rate").Value

Me.NarrativeDays = .Fields("Narrative Days").Value
Form_FixingForm.OptNarrative = .Fields("Narrative Days").Value

Me.Narrative3 = .Fields("Narrative 3").Value
Form_FixingForm.OptRate3 = .Fields("Narrative 3").Value

Me.Narr3Amt = .Fields("Narr3 Amt").Value
Form_FixingForm.OptAmt3 = .Fields("Narr3 Amt").Value

Me.DaysTop = .Fields("Days Top").Value
Form_FixingForm.Days1Top = .Fields("Days Top").Value

Me.DaysBottom = .Fields("Days Bottom").Value
Form_FixingForm.Day1Bottom = .Fields("Days Bottom").Value

Me.Days2Top = .Fields("Days2 Top").Value
Form_FixingForm.Days2Top = .Fields("Days2 Top").Value

Me.Days2Bottom = .Fields("Days2 Bottom").Value
Form_FixingForm.Days2Bottom = .Fields("Days2 Bottom").Value

Me.PayDay = .Fields("PayDay").Value
Form_FixingForm.CoupPayDay = .Fields("PayDay").Value

Me.PayMonth = .Fields("PayMonth").Value
Form_FixingForm.CoupPayMonth = .Fields("PayMonth").Value

Me.PayYear = .Fields("PayYear").Value
Form_FixingForm.CoupPayYear = .Fields("PayYear").Value

Me.PayCcy = .Fields("Pay CCY").Value
Form_FixingForm.PayCcy.Value = .Fields("Pay CCY").Value

Me.NextDay = .Fields("NextDay").Value
Form_FixingForm.NextDay = .Fields("NextDay").Value

Me.NextMonth = .Fields("NextMonth").Value
Form_FixingForm.NextMonth = .Fields("NextMonth").Value

Me.NextYear = .Fields("NextYear").Value
Form_FixingForm.NextYear = .Fields("NextYear").Value

Me.Multiperiod = .Fields("Multiperiod").Value
Form_FixingForm.Multi = .Fields("Multiperiod").Value

Me.ByDenom = .Fields("By Denom").Value
Form_FixingForm.ByDenom = .Fields("By Denom").Value

Me.FixedBy = .Fields("Fixed By").Value
Form_FixingForm.FixedBy = .Fields("Fixed By").Value

Me.FixingDate = .Fields("Fixing Date").Value
'''''

Me.CouponPayDate = .Fields("Coupon Pay Date").Value
'''''

Me.Waiting = .Fields("Waiting").Value
Form_FixingForm.murexcheck = Not Me.Waiting

'''''

'''''
Me.Cancelled = .Fields("Cancelled").Value

Me.NextFixDate = .Fields("NextFixDate").Value

Me.version = .Fields("Version").Value
Form_FixingForm.VersionNumber = .Fields("Version").Value

If IsNull(.Fields("FixingNumber").Value) Then
Me.FixingNumber = .Fields("Primary key")
Form_FixingForm.FixingNumber = .Fields("Primary key")
Else: Me.FixingNumber = .Fields("FixingNumber").Value
Form_FixingForm.FixingNumber = .Fields("FixingNumber").Value
End If

End With
mbooLoaded = True
End Sub

security class

Public Sub LoadTextBoxes()
'add button enabled only if record found on an isin search

Dim isisin As Boolean

Dim rs As DAO.Recordset

If Not (IsNull(Form_FixingForm.AddIsinTB.Value) And IsNull(Form_FixingForm.AddSeriesTB.Value)) Then

If IsNull(Form_FixingForm.AddIsinTB.Value) Or Form_FixingForm.AddIsinTB.Value = "" Then
'searchforseries
isisin = False

Set rs = Me.getAllRecords("Series" & Form_FixingForm.AddSeriesTB.Value)

rs.FindFirst "[SeriesNumber] = '" & Form_FixingForm.AddSeriesTB.Value & "'"

Else
'search for isin
isisin = True

If Len(Form_FixingForm.AddIsinTB) = 12 And Not Form_FixingForm.AddIsinTB Like "* *" Then
Set rs = Me.getAllRecords("ISIN" & Form_FixingForm.AddIsinTB.Value)
rs.FindFirst "[ISIN]= '" & Form_FixingForm.AddIsinTB.Value & "'"
End If
End If

On Error GoTo errorhandle

If rs.NoMatch = False Then

With rs
Form_FixingForm.AddIssuerCbo.SetFocus

Form_FixingForm.OpenFolder.Visible = True

mbooLoaded = True

Form_FixingForm.AddIsinTB = .Fields("ISIN").Value
Me.ISIN = .Fields("ISIN").Value

Form_FixingForm.AddSeriesTB = .Fields("seriesNumber").Value
Me.SeriesNumber = .Fields("seriesNumber").Value

Form_FixingForm.AddMurexTB = .Fields("Murex reference").Value
Me.Murexreference = .Fields("Murex reference").Value

Me.FolderPath = .Fields("FolderPath").Value

Form_FixingForm.AddIssuerCbo = .Fields("Issuer").Value
Me.Issuer = .Fields("Issuer").Value

'Me.Waiting = .Fields("Waiting").Value

Form_FixingForm.AddNextFixDate = .Fields("Next Fix Date").Value
Me.NextFixDate = .Fields("Next Fix Date").Value

Form_FixingForm.koCallRedeem.Value = Not (.Fields("More fixings").Value)
Me.Morefixings = .Fields("More fixings").Value

'Me.Waitinguser = .Fields("Waiting user").Value
Me.NextFixDateSet = .Fields("Next Fix Date Set").Value
Me.Lockedby = .Fields("Locked by").Value
Me.fixedsincejuly = .Fields("fixedsincejuly").Value

Form_FixingForm.koCallRedeemLabel.Visible = True
Form_FixingForm.koCallRedeem.Visible = True
Form_FixingForm.AmendSecurity.Enabled = True
Form_FixingForm.AddIsinTB.Enabled = False
Form_FixingForm.Folder.Visible = True
Form_FixingForm.OpenFolder.Visible = True

'Me.oldSeries = Form_FixingForm.AddSeriesTB.Value

Set Me.Recordset = rs

End With

Else:

Set Me.Recordset = Me.getAllRecords("none")
Form_FixingForm.AddIsinTB.SetFocus

If isisin = True Then
MsgBox ("The issue is not in the database")
Form_FixingForm.AddIssuerCbo.SetFocus
Form_FixingForm.AddCB.Enabled = True
Form_FixingForm.Folder.Visible = True
Form_FixingForm.AddIsinTB.Enabled = False
Else:
MsgBox ("The series number is not in the database")
End If

End If

End If

Exit Sub

errorhandle:

'is null property
Resume Next

End Sub

Public Function getAllRecords(rstr As String) As DAO.Recordset
' Procedure: getRecord
' DateTime: 04/11/2007 23:25:53
' Author: Tony Hallett
' Description: returns the recordset for a particular ISIN

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qrydef As QueryDef

Dim strSql As String

'Set ws = DBEngine.Workspaces(0)
'Set db = ws.Databases(0)
Set db = Application.CurrentDb

If rstr Like "ISIN*" Then

rstr = Mid(rstr, 5)

strSql = "SELECT * FROM TableLookUp WHERE ISIN='" & rstr & "'"
ElseIf rstr Like "SERIES*" Then
rstr = Mid(rstr, 7)

strSql = "SELECT * FROM TableLookUp WHERE [seriesNumber]='" & rstr & "'"
Else:

strSql = "SELECT * FROM TableLookUp"

End If
Set qrydef = db.CreateQueryDef("", strSql)
Set rs = qrydef.OpenRecordset()

Set getAllRecords = rs

End Function

Public Sub Update()
On Error GoTo errorhandle

With Recordset
If mbooLoaded = True Then
.Edit
Else
.AddNew
End If

.Fields("ISIN").Value = Me.ISIN

.Fields("seriesNumber").Value = Me.SeriesNumber
.Fields("Murex reference").Value = Me.Murexreference
.Fields("FolderPath").Value = Me.FolderPath
.Fields("Issuer").Value = Me.Issuer
'.Fields("Waiting").Value = Me.Waiting

.Fields("Next Fix Date").Value = Me.NextFixDate
.Fields("Locked by").Value = Me.Lockedby
.Fields("fixedsincejuly").Value = Me.fixedsincejuly
.Fields("More fixings").Value = Me.Morefixings
'.Fields("Waiting user").Value = Me.Waitinguser
.Fields("Next Fix Date Set").Value = Me.NextFixDateSet

.Update
End With
mbooLoaded = True

Exit Sub
errorhandle:
If Err.Number = "3022" Then

MsgBox ("The series number already exists in the table, pls amend this record first.")
End
Else: MsgBox (Err.Number & " " & Err.Description)
Stop
End If

End Sub

Ok! It is messy and I have a lot to learn. I think I have attached all code that is relevant to the update conditons and the creation of the recordset. I haven't attached the get/let/set properties of the classes.
Hopefully it will stick out like a sore thumb.

Thanks Ed even if you decide it's a lost cause.

Tony

Ed2020 · Nov 19, 2007

Tony,

Not a lost cause but it may take me a little while to work through it. It's late here now so I'll check it out tomorrow morning.

Ed Metcalfe.

Please do not feed the trolls.....

Ed2020 · Nov 20, 2007

Tony,

I haven't quite worked out what the problem is. As far as I can see providing your database and recordset objects are all opening in the same workspace object then it should work. To test the theory I created the following example, which works fine for me:

Code:

'Beginning of basMain
Public Sub Test()
    Dim EdsDept As New cStaff
    Dim EdsLocation As New cLocation
    Dim ws As DAO.Workspace
    
    Set ws = DBEngine.Workspaces(0)
    
    With ws
        .BeginTrans
            EdsDept.strName = "Ed"
            EdsDept.strDepartment = "Application Support"
            EdsDept.SaveMe
            
            EdsLocation.strName = "Ed"
            EdsLocation.strLocation = "1st floor north"
            EdsLocation.SaveMe
        .CommitTrans
    End With
    
End Sub
'End of basMain

Code:

'Beginning of cLocation class code
Public strName As String
Public strLocation As String

Public Sub SaveMe()
    Dim ThisDB As DAO.Database
    Dim rstLocations As DAO.Recordset
    
    Set ThisDB = CurrentDb()
    Set rstLocations = ThisDB.OpenRecordset("tblLocations", dbOpenDynaset)
    
    With rstLocations
        .AddNew
            !Name = strName
            !Location = strLocation
        .Update
    End With
End Sub
'End of cLocation class code

Code:

'Beginning of cStaff class code
Public strName As String
Public strDepartment As String

Public Sub SaveMe()
    Dim ThisDB As DAO.Database
    Dim rstStaff As DAO.Recordset
    
    Set ThisDB = CurrentDb()
    Set rstStaff = ThisDB.OpenRecordset("tblStaff", dbOpenDynaset)
    
    With rstStaff
        .AddNew
            !Name = strName
            !Department = strDepartment
        .Update
    End With
End Sub
'End of cStaff class code

I'm struggling a bit with not being able to actually run your code... :-(

Ed Metcalfe.

Please do not feed the trolls.....

tonyhatwork · Nov 20, 2007

Hi Ed,

I have reversed the order of the form code and it is no longer giving the error message :

Private Sub complete_Click()

On Error GoTo errorhandle:

Dim db As Database
Set db = Application.CurrentDb
Set ws = DBEngine.Workspaces(0)

'validation function
If complValidate = True Then

fixing.setProptoForm

If fixing.CouponPayDate Mod 7 = 1 Or fixing.CouponPayDate Mod 7 = 0 Then
MsgBox ("The coupon payment date is a weekend")
Exit Sub
End If

If Me.lastFix.Value = fale Then
If fixing.NextFixDate Mod 7 = 1 Or fixing.NextFixDate Mod 7 = 0 Then
MsgBox ("The next fixing date is a weekend")
Exit Sub
End
End If
End If

If Me.murexcheck = False Then
fixing.Waiting = True
Else: fixing.Waiting = False
End If

Stop

OpenFolder.Visible = False
koCallRedeem.Visible = False
koCallRedeemLabel.Visible = False
Form_FixingForm.AddCB.Enabled = False
Form_FixingForm.AddIsinTB = Null
Form_FixingForm.AddIsinTB.Enabled = True
Form_FixingForm.Folder.Visible = False
Form_FixingForm.AddMurexTB.Value = ""
Form_FixingForm.AddIssuerCbo.Value = Null
Form_FixingForm.AddSeriesTB.Value = Null
Form_FixingForm.AddNextFixDate.Value = Null
Form_FixingForm.AmendSecurity.Enabled = False

Me.GetCalendar.SetFocus
Me.calcpay.Enabled = False
Me.CancelFixing.Enabled = False
Me.complete.Enabled = False
Me.regenNotice.Enabled = False
Me.template.Enabled = False
Me.amend.Enabled = False

Set security = New security
security.ISIN = fixing.ISIN

Me.AddIsinTB = security.ISIN

security.LoadTextBoxes

security.NextFixDate = fixing.NextDay & "/" & fixing.NextMonth & "/" & fixing.NextYear

ws.BeginTrans
Stop
security.Update

Stop
If Form_FixingForm.FixingNumber = "" Then
Stop
'have yet to go this path of the if statement

Set fixing = New fixing
fixing.setProptoForm
Stop
fixing.AddNew
Stop
fixing.Update
'fixing.add waiting murex flag property ?
'
Else:
Stop
fixing.Update

'
'
'
End If

ws.CommitTrans (1)

If murexcheck Then

fixing.notification
End If

End If

Exit Sub

errorhandle:
'if appropriate error number then ws.rollback
MsgBox (Err.Number & " " & Err.Description)
Stop

End Sub

The main difference is that the retrieving of the recordset for security class is not being done mid transaction with this order. Perhaps that is the problem ?

Tony

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

transaction with two recordsets

tonyhatwork

Programmer

Ed2020

Programmer

tonyhatwork

Programmer

Ed2020

Programmer

tonyhatwork

Programmer

Ed2020

Programmer

tonyhatwork

Programmer

Ed2020

Programmer

Ed2020

Programmer

tonyhatwork

Programmer

Similar threads

Part and Inventory Search

Sponsor