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.

vbaformsfunctionaccesskpi answered 4 months ago AlanoudK #1

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}
```