Entering data in a text box.

[bknight]

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.

[bknight]

Click was a better choice.

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

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

[HansV]

To test whether the record as a whole has been modified, use Me.Dirty

I have no idea whether that will help you.

[bknight]

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

[bknight]

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.

[bknight]

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?

[HansV]

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.

[bknight]

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.