adding a second percentage into a vba access data base

Question

hi all,

new to VBA and im having trouble and im hopeful someone can assist me resolving it? 

I have 27 suppliers in a access database and each one pays a commission of 6.5% from their total invoice.

Recently one supplier negotiated a 6.0% commission payment.

The vba which does this for is for all suppliers, see below,

_______________________________________________________________________________________________________

Private Sub Ctl281_Inv_No__AfterUpdate()

'Declare Commission Percentage as 6.5%

Const commPercent As Double = 0.065

Dim qtyShipped As Integer, unitPrice As Single, commVal As Variant

    commVal = Me.[281 Inv Val].Value

    If IsNull(Me.[Quantity Shipped].Value) Then

        MsgBox "The Merchandiser has not updated the shipped quantity." & vbNewLine & _

            "Please contact the Merchandiser.": Exit Sub

    Else: qtyShipped = Me.[Quantity Shipped].Value

    End If

    unitPrice = Me.[Unit Price].Value

'MsgBox "Shipped: " & qtyShipped & ": Price : " & unitPrice & ": Amount: " & unitPrice * qtyShipped

    If IsNull(commVal) Then

        commVal = Round(qtyShipped * unitPrice * commPercent, 2)

        Me.[281 Inv Val].Value = commVal

        Me.Refresh

    Else

        If MsgBox("Do you want to update the commission value", vbYesNo) = vbYes Then

            commVal = Round(qtyShipped * unitPrice * commPercent, 2)

            Me.[281 Inv Val].Value = commVal

            Me.Refresh

        End If

    End If

End Sub

_____________________________________________________________________________________________________________________

This VBA is found in the "Form_subfrmInvoicing"

Any sugguest how to get one supplier from 27 to calulate at 6% and the rest at 6.5%?

Supplier name is " Fox Toys Ltd and their suplpier code is CCFT15" if that helps

many thanks community

Gary 

<The thread has been moved to the correct category by forum moderator>

Answer 1

Hard coding data in code is bad relational database design.  A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as values at column positions in rows in tables, and in no other way.

The current commission rate is an attribute of Suppliers, so should be a column in the Suppliers table.  The value should be stored as a fractional number, e.g. 0.065 for 6.5%.

The commission rate applied to an invoice is an attribute of Invoices so should be a column in the Invoices table.  This does not introduce any redundancy as the rate might change over time, but that applied to each invoice should remain static as the rate current at the time the invoice was raised.  The event procedure should therefore reference the value in the column in Suppliers for the supplier in question, and assign the rate to the column in Invoices.  The commission for the invoice can then be computed by multiplying the commission rate by the gross invoice amount.

Answer 2

Data belongs in your tables, not in your code. You should be storing the commission percent in a table of suppliers. Any other solution will cause issues when values change. 

How you use this depends on your application. If it's in the table of suppliers it should be available in all queries that need suppliers.