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.
Determining a value
-
- 2StarLounger
- Posts: 120
- Joined: 22 Feb 2022, 09:04
Re: Determining a value
Using Access 2007/2019.
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
Please, please use code tags when posting code snippets, click the </>icon.
Debug.Print is your lifesaver.
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
Please, please use code tags when posting code snippets, click the </>icon.
Debug.Print is your lifesaver.
-
- BronzeLounger
- Posts: 1429
- Joined: 08 Jul 2016, 18:53
Re: Determining a value
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.
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.
-
- 2StarLounger
- Posts: 120
- Joined: 22 Feb 2022, 09:04
Re: Determining a value
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?
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?
Using Access 2007/2019.
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
Please, please use code tags when posting code snippets, click the </>icon.
Debug.Print is your lifesaver.
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
Please, please use code tags when posting code snippets, click the </>icon.
Debug.Print is your lifesaver.
-
- 4StarLounger
- Posts: 579
- Joined: 27 Jun 2021, 10:46
Re: Determining a value
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 wrote: ↑20 Jun 2023, 16:38No 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 scenariosWhen 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.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
-
- BronzeLounger
- Posts: 1429
- Joined: 08 Jul 2016, 18:53
Re: Determining a value
Set Fld12 = Rs!Profit is not a declaration?
-
- BronzeLounger
- Posts: 1429
- Joined: 08 Jul 2016, 18:53
Re: Determining a value
Thank you for the catch on my error Fld112 was NOT dimensioned, it is now and the code works.
-
- 4StarLounger
- Posts: 579
- Joined: 27 Jun 2021, 10:46
Re: Determining a value
>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")
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")
-
- 2StarLounger
- Posts: 120
- Joined: 22 Feb 2022, 09:04
Re: Determining a value
Good spot @SpeakEasySpeakEasy wrote: ↑22 Jun 2023, 13:11
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.
Using Access 2007/2019.
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
Please, please use code tags when posting code snippets, click the </>icon.
Debug.Print is your lifesaver.
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
Please, please use code tags when posting code snippets, click the </>icon.
Debug.Print is your lifesaver.
-
- 4StarLounger
- Posts: 579
- Joined: 27 Jun 2021, 10:46
Re: Determining a value
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"
"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"