Entering data in a text box.
-
- BronzeLounger
- Posts: 1416
- Joined: 08 Jul 2016, 18:53
Re: Entering data in a text box.
Referencing the containers worked to get the form to appear, but pressing the button has done nothing, let me look at the choices for the button.
-
- BronzeLounger
- Posts: 1416
- Joined: 08 Jul 2016, 18:53
Re: Entering data in a text box.
Click was a better choice.
Currently the data is being entered at record 247, but it is dirty still as the pointer is still in the record. When
Rs.MoveFirst
Rs.MoveLast
is executed the paired commands tell access how many records are in the recordset. In this case the calculation is 246 not the 47th being entered.
Question would a command of
if Me.(I don't know the proper command to encompass the whole record).dirty = true
then me.nnnn.dirty = false in the command set prior to calling the sub? The code will work when the Rs=247 records. work
Code: Select all
Function CalcProfit(Profit)
'This Function Will Calculate The Profit
Dim db As DAO.Database
Dim Rs As DAO.Recordset
Dim Fld2 As Field, Fld3 As Field
Dim Fld4 As Field, Fld6 As Field
Dim Fld11 As Field, Fld12 As Field
Dim I As Long, intCurQty As Integer, intnextQty As Integer, intbackRec As Integer, ContractsLeft As Integer
Dim dblNextAmt As Double, dblCurAmt As Double
Dim strCurSymbol As String, strNextSymbol As String, strCurCon As String, strNextCon As String
Set db = CurrentDb
Set Rs = db.OpenRecordset("Select * From Trades Order By Tradedate")
Set Fld2 = Rs!Tradedate
Set Fld3 = Rs!Symbol
Set Fld4 = Rs!ContractMonth
Set Fld5 = Rs!Quantity
Set Fld6 = Rs!ActionID
Set Fld11 = Rs!Amount
Set Fld12 = Rs!Profit
Rs.MoveFirst
Rs.MoveLast
strCurSymbol = Fld3
strCurCon = Fld4
intCurQty = Fld5
dblCurAmt = Fld11
For I = Rs.RecordCount To 2 Step -1
If I < Rs.RecordCount Then
If Fld6 = 46 Or Fld6 = 48 Then
'Find prev opening trades
If Fld3 = strCurSymbol And Fld4 = strCurCon Then
'Found prev opening symbol and contract
If Fld5 + intCurQty <> 0 Then
ContractsLeft = Fld5 + intCurQty
Select Case ContractsLeft
Case 1
Rs.Edit
Fld12 = Fld11 * Abs(intCurQty) / Abs(Fld5)
Rs.Update
Exit For
Case 2
Case 3
Case 4
Case 5
Case 6
Case 7
Case 8
Case 9
End Select
'closing quantity equals prev opening quan
dblNextAmt = Fld11
Rs.MoveLast ' move to the closing trade
Rs.Edit
Fld12 = dblNextAmt + dblCurAmt
Rs.Update
Exit For
Else
Rs.Edit
Fld12 = Fld11 * (Abs(Fld5) - Abs(intCurQty)) / Abs(Fld5) + Fld11
dblCurAmt = dblCurAmt + Fld11
intCurQty = intCurQty + Fld5
End If
End If
End If
End If
Rs.MovePrevious
Next I
Set Rs = Nothing
Set db = Nothing
End Function
Rs.MoveFirst
Rs.MoveLast
is executed the paired commands tell access how many records are in the recordset. In this case the calculation is 246 not the 47th being entered.
Question would a command of
if Me.(I don't know the proper command to encompass the whole record).dirty = true
then me.nnnn.dirty = false in the command set prior to calling the sub? The code will work when the Rs=247 records. work
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Entering data in a text box.
To test whether the record as a whole has been modified, use Me.Dirty
I have no idea whether that will help you.
I have no idea whether that will help you.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1416
- Joined: 08 Jul 2016, 18:53
Re: Entering data in a text box.
Yes when Me.dirty= false then the little pen/pencil at the left of the record disappears and the recordset is 247, worked well finally for this type execution, not all executions.
Thanks
Thanks
-
- BronzeLounger
- Posts: 1416
- Joined: 08 Jul 2016, 18:53
Re: Entering data in a text box.
After testing this morning, the Me.Dirty =False doesn't work with the code. If I manually move from the last record the code works. Even when the Me.Dirty =False was applied and the code recognized the correct number of records. After Rs.MoveLast was applied the values for the fields were of the next to the last record. I don't understand, but still get the code to work by manually moving.
-
- BronzeLounger
- Posts: 1416
- Joined: 08 Jul 2016, 18:53
Re: Entering data in a text box.
A curious event happens every morning, but only once per day. I inserted a watch point in the calculation of profit on a one to one trade sub to check for logical errors during the calculation. Those watch points have been cleared long ago, but the first calculation in a day always display the code displaying For I = Rs.RecordCount To 2 Step -1. I hit run and the code finishes. That display only shows for the first calculation of the day, the rest if any don't show the code. How curious, any idea why this occurs?
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Entering data in a text box.
Sometimes discarded settings lurk in the dark corners of the code.
Try the following:
Close the database.
Press Windows key+R to activate the Run dialog.
Type the following, then click OK or press Enter:
msaccess.exe "C:\MyDatabases\MyDatabase.accdb" /decompile
where C:\MyDatabases\MyDatabase.accdb is the full path and filename of your database.
Your database should open.
Activate the Visual Basic Editor.
Select Debug > Compile Database1
(The name of the database may vary).
Switch back to Access.
Click the Office button.
Select Manage > Compact and Repair Database.
Try the following:
Close the database.
Press Windows key+R to activate the Run dialog.
Type the following, then click OK or press Enter:
msaccess.exe "C:\MyDatabases\MyDatabase.accdb" /decompile
where C:\MyDatabases\MyDatabase.accdb is the full path and filename of your database.
Your database should open.
Activate the Visual Basic Editor.
Select Debug > Compile Database1
(The name of the database may vary).
Switch back to Access.
Click the Office button.
Select Manage > Compact and Repair Database.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1416
- Joined: 08 Jul 2016, 18:53
Re: Entering data in a text box.
Other than the path was typed incorrectly but got there, and Access options gives me this. I know there is a repair function, but I'm not there yet. Another tab/function?
ETA: Never Mind I had to shut down the Db and then repair, didn't understand all the steps.
ETA: Never Mind I had to shut down the Db and then repair, didn't understand all the steps.
You do not have the required permissions to view the files attached to this post.