0

I am creating a database to track KPIs with a front-end for specific user to enter values for those KPIs.

I created my tables and attributes as well as forms that enables users to access their own KPIs and enter their values.

Each user selects a KPI based on a selected process, and to each KPI is either 1 or 2 variables and a formula to calculate the KPI, the user enters integers for one or both variable (combo box shows the user if there are two variables or only one)

Currently, I am looking for a way to calculate the KPI Value so that I can have the user read it and validate the Variable integers entered within that same session.

I considered Functions and VBA, but I am new to VBA and would need guidance. I am not sure how to start but what I need to do is identify the KPI to get the formula, get the relevant variables and identify which is V1 and which is V2(Attribute Var_No in the variable table identifies if the variable entered is V1 or V2), so I can execute the formulas which are limited. My formulas:

1- (V1/V2)*100

2- 1-(V1/V2)*100

3- V1

4- V1/V2.

So, trying the second method I have this code:

Private Sub Command47_Click()

Dim V1 As Integer

Dim V2 As Integer

If Me.Combo44.Value = (V1 / V2) * 100 Then

Private Sub KPI1()

Dim KPI As Integer

KPI = KPI1()

End If

If Me.Combo44 = "1-(V1/V2)*100" Then

Private Sub KPI2()

Dim KPI As Integer

KPI = KPI2()

End If

If Me.Combo44 = "V1/V2" Then

Private Sub KPI3()

Dim KPI As Integer

KPI = KPI3()

End If

If Me.Combo44 = "V1" Then

Private Sub KPI4()

Dim KPI As Integer

KPI = KPI4()

End If

End Sub

And Modules as:

Public Function KPI1(V1 As Integer, V2 As Integer)

Dim KPI As Integer

KPI = (V1 / V2) * 100

End Function

Public Function KPI2(V1 As Integer, V2 As Integer)

Dim KPI As Integer

KPI = 1 - (V1 / V2) * 100

End Function

Public Function KPI3(V1 As Integer, V2 As Integer)

Dim KPI As Integer

KPI = V1 / V2

End Function

Public Function KPI4(V1 As Integer)

Dim KPI As Integer

KPI = V1

End Function

However, an Error shows when I try to compile the first part of the code.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
AlanoudK
  • 1
  • 1
  • I'm guessing I could have the calculation done in a query once the new data is entered and then retrieve it and show it in a msg box. But then again, what sort of criteria and expression would I need to use to do the calculation? – AlanoudK Feb 26 '18 at 01:28
  • Another option would be to create a module for each function with the two parameters (V1,V2), and create an If statement with VBA to decide which function to execute. – AlanoudK Feb 26 '18 at 01:52

1 Answers1

0

I worked it out: Model Class:

{Option Compare Database

Option Explicit

Public V1 As Double
Public V2 As Double
Public DataType As Variant


Public Function GetTotal() As Long
    GetTotal = (V1 / V2) * 100
End Function

Public Function GetTotal1() As Long
    GetTotal1 = 1 - (V1 / V2) * 100
End Function

Public Function GetTotal2() As Long
    GetTotal2 = V1 / V2
End Function
Public Function GetTotal3() As Long
    GetTotal3 = V1
End Function
}

On Click 

{Private Sub btnCalculate_Click()
If Me.KPIF.Value = "(V1/V2)*100" Then

Dim calcData As clsCalcData
    Dim total As Long

    Set calcData = New clsCalcData
    calcData.V1 = Me.Variable1.Value
    calcData.V2 = Me.Variable2.Value

    total = calcData.GetTotal()
    MsgBox (total)
    End If
    If Me.KPIF.Value = "1-(V1/V2)*100" Then
    Dim calcData1 As clsCalcData
    Dim total1 As Long

    Set calcData1 = New clsCalcData
    calcData1.V1 = Me.Variable1.Value
    calcData1.V2 = Me.Variable2.Value

    total1 = calcData1.GetTotal1()
    MsgBox (total1)
    End If
    If Me.KPIF.Value = "V1/V2" Then
    Dim calcData2 As clsCalcData
    Dim total2 As Long

    Set calcData2 = New clsCalcData
    calcData2.V1 = Me.Variable1.Value
    calcData2.V2 = Me.Variable2.Value

    total2 = calcData2.GetTotal2()
    MsgBox (total2)
    End If
        If Me.KPIF.Value = "V1" Then
    Dim calcData3 As clsCalcData
    Dim total3 As Long

    Set calcData3 = New clsCalcData
    calcData3.V1 = Me.Variable1.Value

    total3 = calcData3.GetTotal3()
    MsgBox (total3)
    End If
End Sub}
AlanoudK
  • 1
  • 1