Coding using multiple tables

[bknight]

In my latest project and with a lot of help, I have coded steps used to calculate/enter data into fields. By using if statements to determine specific values. This started of with two ifs but anytime a different symbol is used the if statements must grow to "add" that symbol. Once the if statements are expanded, no need to enter additional code lines. I was wondering whether two extra tables (tblCommission and tblFees) could be employed to take the place of the if statements? Here are the specific codes that I refer. Disregarding the concept of not entering calculated values into fields, what is the coding syntax using data between tables? The two tables structure would be Symbol:SymbolCommission, and Symbol:SymbolFees. The calculated values would be entered into the main table as they are entered currently.

Code: Select all

Private Sub Commission_AfterUpdate()
    If Me.[Symbol] = "MES" Or Me.Symbol = "M2K" Or Me.Symbol = "MYM" Or Me.Symbol = "MNQ" Then
        If Me.[ActionID] = 46 Or Me.[ActionID] = 47 Then
            Me.[Commission] = -Me.[Quantity] * 0.5 'buy
        Else
            Me.[Commission] = Me.[Quantity] * 0.5 'sell
        End If
    ElseIf Me.[Symbol] = "FV" Then
        If Me.[ActionID] = 46 Or Me.[ActionID] = 47 Then
            Me.[Commission] = -Me.Quantity * 1.5 'buy
        Else
            Me.[Commission] = Me.[Quantity] * 1.5 'sell
        End If
    ElseIf Me.[Symbol] = "TY" Then
        If Me.[ActionID] = 46 Or Me.[ActionID] = 47 Then
            Me.[Commission] = -Me.Quantity * 1.5 'buy
        Else
            Me.[Commission] = Me.[Quantity] * 1.5 'sell
        End If
    ElseIf Me.[Symbol] = "US" Then
        If Me.[ActionID] = 46 Or Me.[ActionID] = 47 Then
            Me.[Commission] = -Me.[Quantity] * 1.5 'buy
        Else
            Me.[Commission] = Me.[Quantity] * 1.5 'sell
        End If
    End If
End Sub
Private Sub Fees_AfterUpdate()
    If Me.[Symbol] = "MES" Or Me.Symbol = "M2K" Or Me.Symbol = "MYM" Or Me.Symbol = "MNQ" Then
        If Me.[ActionID] = 46 Or Me.[ActionID] = 47 Then
            Me.[Fees] = -Me.[Quantity] * 0.37 'buy
        Else
            Me.[Fees] = Me.[Quantity] * 0.37 'sell
        End If
    ElseIf Me.[Symbol] = "FV" Then
        If Me.[ActionID] = 46 Or Me.ActionID = 47 Then
            Me.[Fees] = -Me.[Quantity] * 0.67 'buy
        Else
            Me.[Fees] = Me.[Quantity] * 0.67 'sell
        End If
    ElseIf Me.[Symbol] = "TY" Then
        If Me.[ActionID] = 46 Or Me.ActionID = 47 Then
            Me.[Fees] = -Me.[Quantity] * 0.82 'buy
        Else
            Me.[Fees] = Me.[Quantity] * 0.82 'sell
        End If
    ElseIf Me.Symbol = "US" Then
        If Me.ActionID = 46 Or Me.ActionID = 47 Then
            Me.Fees = -Me.Quantity * 0.89 'buy
        Else
            Me.Fees = Me.Quantity * 0.89 'sell
        End If
    End If
    If Me.Symbol = "MES" Or Me.Symbol = "M2K" Then
            Me.Amount = -Me.Quantity * Me.Price * 5 + Me.Commission + Me.Fees 'buy
    ElseIf Me.Symbol = "TY" Or Me.Symbol = "US" Or Me.Symbol = "FV" Then
            Me.Amount = -Me.Quantity * Me.Price * 1000 + Me.Commission + Me.Fees
    ElseIf Me.Symbol = "MYM" Then
            Me.Amount = -Me.Quantity * Me.Price * 0.5 + Me.Commission + Me.Fees
    ElseIf Me.Symbol = "MNQ" Then
            Me.Amount = -Me.Quantity * Me.Price * 2 + Me.Commission + Me.Fees
    End If
    Me.OrderNum.SetFocus
End Sub
Private Sub Amount_AfterUpdate()
    If Me.Symbol = "MES" Or Me.Symbol = "M2K" Then
            Me.Amount = -Me.Quantity * Me.Price * 5 + Me.Commission + Me.Fees 'buy
    ElseIf Me.Symbol = "TY" Or Me.Symbol = "US" Or Me.Symbol = "FV" Then
            Me.Amount = -Me.Quantity * Me.Price * 1000 + Me.Commission + Me.Fees
    ElseIf Me.Symbol = "MYM" Then
            Me.Amount = -Me.Quantity * Me.Price * 0.5 + Me.Commission + Me.Fees
    ElseIf Me.Symbol = "MNQ" Then
            Me.Amount = -Me.Quantity * Me.Price * 2 + Me.Commission + Me.Fees
    End If
End Sub

[xps35]

You could create an extra table to store the commission and fee percentages. The table should be linked to the symbol and action tables.
The factors used in the last sub depend only on the symbol and can be stored in the symbol table.
Calculations can be made in a query linking all relevant tables.

[bknight]

You might do that but the question how to code it. The if statements work well but need to be updated in all the code sections. I think using a table, that has to be updated also, solves all the if statements.

[xps35]

Coding is not a good idea I think. You have to adjust code if something changes.
If you use data only, there is no need to adjust.
You are thinking about using a table. That is exactly what I do. No coding needed in that case.

[bknight]

Currently the code works for all entries except Profit. For the most part done manually but is calculated with a one to one trade.