Determining a value

[Gasman]

I did look at all the code.
However I am only looking for the logic in this code, or missing logic.

I myself have never ever declared a variable as a field.
Are you saying that if you do that and then set it from a recordset field, that somehow the two are interlinked and changing one changes the other?

The link I posted shows clearly how to update a recordset field. That method I have used myself many times.
If your other code works, then I would only suggest comparing one against the other, as something is clearly different. :(

However from your code, I myself cannot see how on earth rst!Profit gets updated unless it is by that virtual link? You can learn somthing new everyday though?

Might be best to wait for Hans, as I am just a dabbler in Access (or was) and I tend to use it the way I have found googling or asking for help. That way has worked for me.

[bknight]

I'm not sure what you mean by declaring a variable as a field. I'm setting Fld12(this may be your variable, not sure) to a field in the record set. Then I go through some math to arrive at an answer and set that answer as the value of the field. If you are asking if this can be done, the answer is most assuredly yes. I have sever Db that are updated daily using this method to "input" calculated values into fields. They all work save the one I'm discussing. That is the quandary they all work except one. Why I ask because the other all work. In fact in the Db I'm discussing there is another profit function that works very well. It is similar in structure as the one that works, different math steps to arrive at a result. I can set up a watch on Fld12 and it will show a value has been calculated and placed in Fld12, but in a dirty state the dirty state(and this is my way of describing, graduates of the courses may use slightly different words to describe an entry) and that dirty state must be cleared before the entry is accepted. I believe I have posted both functions in this thread.
Hans probably won't comment on the question, because in this environment he would need to look at the Db with the accompanying code sets.

[Gasman]

Well I went off and had a test of setting a field variable/object to a recordset field and then updating that fater a .Edit and before a .Update and it worked.
So I have learnt something new today, so thank you for that, at least.

If you do not get to the bottom of it, you could always fall back to what I would call the standard method as described in that link?

[SpeakEasy]

No one has an answer as to why a Rs.Edit followed by some calculation and then a Rs.Update fails to create a value in the profit field? I altered the code slightly to encompass more possible scenarios

Code: Select all

Function CalcTUProfit(Profit)
'This Function Will Calculate The Profit for TU Only
Dim db As DAO.Database
Dim Rs As DAO.Recordset
Dim Fld1 As Field, Fld2 As Field, Fld3 As Field
Dim Fld4 As Field, Fld5 As Field, Fld6 As Field
Dim I As Long, J As Long, CountTicks As Long, intCurQty As Integer, intnextQty As Integer, intbackRec As Integer
Dim intCurWhle As Integer, intPrevWhle As Integer, intWhleMult As Integer
Dim dblPrevAmt As Double, dblCurAmt As Double, dblPrevFrc As Double, dblCurFrc As Double, dblBuyP As Double, dblSellP As Double
Dim dblCurCom As Double, dblCurFee As Double, dblPrevCom As Double, dblPrevFee As Double
Dim strCurSymbol As String, strCurCon As String, strCurRawP As String, strPrevRawP As String
Dim varStart As Variant, varEnd As Variant
Dim p As Double, p1 As Double
Set db = CurrentDb
Set Rs = db.OpenRecordset("Select * From Trades Order By ID")
Set Fld1 = Rs!ID
Set Fld2 = Rs!Tradedate
Set Fld3 = Rs!Symbol
Set Fld4 = Rs!ContractMonth
Set Fld5 = Rs!Quantity
Set Fld6 = Rs!ActionID
Set Fld7 = Rs!RawP
Set Fld11 = Rs!Amount
Set Fld12 = Rs!Profit
Rs.MoveFirst
Rs.MoveLast
    strCurSymbol = Fld3
    strCurCon = Fld4
    intCurQty = Fld5
    strCurRawP = Fld7 'Closing Price of TU
    p = InStr(strCurRawP, "'")
    intCurWhle = Left(strCurRawP, p - 1)
    dblCurFrc = Mid(strCurRawP, p + 1)
    If Fld6 = 46 Or Fld6 = 47 Then
        dblBuyP = intCurWhle + dblCurFrc / 32
    Else
        dblSellP = intCurWhle + dblCurFrc / 32
    End If
    dblCurCom = -Abs(Fld5 * 1.5)
    dblCurFee = -Abs(Fld5 * 0.67)
    varEnd = dblCurFrc
 For I = Rs.RecordCount To 2 Step -1
    If I < Rs.RecordCount Then
        If Fld6 = 46 Or Fld6 = 48 Then
        'Find prev opening trade
            If Fld3 = strCurSymbol And Fld4 = strCurCon And IsNull(Fld12) Then 'If multiple openings, then place a zero in profit of closed opening trade
            'Found prev opening symbol and contract
            strPrevRawP = Fld7
            p = InStr(strPrevRawP, "'")
            intPrevWhle = Left(strPrevRawP, p - 1)
            dblPrevFrc = Mid(strPrevRawP, p + 1)
            dblPrevCom = -Abs(1.5 * Fld5)
            dblPrevFee = -Abs(0.67 * Fld5)
            If Fld6 = 46 Or Fld6 = 47 Then
                dblBuyP = intPrevWhle + dblPrevFrc / 32
            Else
                dblSellP = intPrevWhle + dblPrevFrc / 32
            End If
            varStart = dblPrevFrc 'Example 102+02.1/32
            'Count the number of ticks between Start and End
            'For each whole number there are 256 ticks
            varEnd = Abs(intPrevWhle - intCurWhle) * 256 + dblCurFrc
                If varStart <= varEnd Then
                    For J = 10 * varStart + 1 To 10 * varEnd
                        If J Mod 10 <> 4 And J Mod 10 <> 9 Then
                            CountTicks = CountTicks + 1
                        End If
                    Next J
                Else
                    For J = 10 * varStart - 1 To 10 * varEnd Step -1
                        If J Mod 10 <> 4 And J Mod 10 <> 9 Then
                            CountTicks = CountTicks + 1
                        End If
                    Next J
                End If
    'End If
                Rs.MoveLast ' move to the closing trade
                Rs.Edit
                If dblSellP > dblBuyP Then
                    Fld12 = Abs(Fld5) * CountTicks * 7.8125 - Abs(Fld5 * 1.5) - Abs(Fld5 * 0.67) - Abs(dblPrevCom) - Abs(dblPrevFee)
                Else
                    Fld12 = -Abs(Fld5) * CountTicks * 7.8125 - Abs(Fld5) * 1.5 - Abs(Fld5 * 0.67) - Abs(dblPrevCom) - Abs(dblPrevFee)
                End If
                Rs.Update
                Exit For
            End If
        End If
    End If
Rs.MovePrevious
Next I
Set Rs = Nothing
Set db = Nothing
End Function

When stepping through the code, a watchpoint on Fld12(Profit) indicates a value is present subsequent to the last If statement and following the Rs.Update, but that value is not present in the Profit field after the code finishes.

In THIS version of your code FLd12 is NOT declared. Thus it defaults to a Variant (since you don't have Option Explicit set, which would warn you about things like this). Since it is a variant it stores the contents of Rs!Profit when you assign it (Fld12=Rs!Profit) rather than pointing to the same underlying object. Which means that subsequently assigning a value to Fld12 does NOT assign a value to Rs!Profit - and thus you see the behaviour you describe. It is expected.

[bknight]

Set Fld12 = Rs!Profit is not a declaration?

[bknight]

Thank you for the catch on my error Fld112 was NOT dimensioned, it is now and the code works.

[SpeakEasy]

>Set Fld12 = Rs!Profit is not a declaration?

No, that is an assignment

>ld112 was NOT dimensioned, it is now and the code works

Hurrah.

And, as I think you have presumably discovered, 'dimensioning' is declaring (Dim was originally used to set dimensions for arrays, but later got hijacked to refer to the definition/declaration of all variables, not just arrays - e.g from VBA documentation "Dim is used to define any variable")

[Gasman]

In THIS version of your code FLd12 is NOT declared. Thus it defaults to a Variant (since you don't have Option Explicit set, which would warn you about things like this). Since it is a variant it stores the contents of Rs!Profit when you assign it (Fld12=Rs!Profit) rather than pointing to the same underlying object. Which means that subsequently assigning a value to Fld12 does NOT assign a value to Rs!Profit - and thus you see the behaviour you describe. It is expected.

Good spot @SpeakEasy

[SpeakEasy]

Gasman, you sent me a PM on this subject, but I cannot directly reply to you because you have PMs disabled. I'll reply here instead

"Yes, I explained it relatively poorly, but I only had a few moments to spare and didn't really have time to explain default properties, object references, or VBA's penchant for implicit type conversion, all of which pay a part here.

Default properties seemed a good idea once upon a time, but can lead to sometimes inexplicable (or at least hard to track down) bugs. Microsoft changed their minds about them, and eliminated them in VB.NET"