Calcuations in a form using Event procedure

[colval]

In a form called FormSample we have embedded drop down list that when selected use various tables, for example one will use Asbestos Type, Conditions, Treatments etc…., when you select an option form these drop down lists the selection as a score against them.

The score is placed in a control on the form called SampleMaterialRisk which uses a Control source as Expression builder and in here is the expression SampleMaterialRisk
On all drop down list the After Update as Expression builder and in it is =CalcMaterialRisk() The Function code for this is

Private Function CalcMaterialRisk()

On Error GoTo Incomplete
Dim lists(6) As String
lists(0) = "ListAnalysis"
lists(1) = "ListAsbestosType"
lists(2) = "ListCondition"
lists(3) = "ListFriability"
lists(4) = "ListPosition"
lists(5) = "ListTreatment"
Dim rs(6) As Integer
rs(0) = ComboSampleAnalysisID.Value
rs(1) = ComboSampleAsbestosTypeID.Value
rs(2) = ComboSampleConditionID.Value
rs(3) = ComboSampleFriabilityID.Value
rs(4) = ComboSamplePositionID.Value
rs(5) = ComboSampleTreatmentID.Value
Dim risk As Byte
risk = 0
For n = 0 To 5
risk = risk + GetFactor(lists, rs)
Next n
SampleMaterialRisk = risk
TextMaterialRiskString = "(" & Mod_Custom.MaterialRiskString(risk) & ")"
Exit Function
Incomplete:
SampleMaterialRisk = Null
TextMaterialR

I have 2 questions
1.I don’t quite understand how it gets the total figure could someone explain
2.If we wanted it not to allow the calculation to never be greater than 12 , how can we stop it going over 12 using the code above (if that is possible)

Thanks

 

[Andrzejek]

For the start, you need to:
1. Provide the code for a function called [tt]GetFactor[/tt]
2. Specify what "it" is in this sentence: "how it gets the total figure"
3. "not to allow the calculation to never be greater than 12" calculation of WHAT?

Have fun.

---- Andy

 

[colval]

Hi

Public Function GetFactor(listname As String, ID As Integer) As Byte
If ID = 0 Then
GetFactor = 0
Exit Function
End If
Dim db As Database
Set db = CurrentDb
fctr = db.OpenRecordset("SELECT Factor FROM " & listname & " WHERE ID = " & ID, dbOpenSnapshot)
GetFactor = fctr(0)
db.Close
End Function


I dont understand what your asking in 2.

For 3 it calculates and then uses the below to say what factor it may be in a report, however when we enter the information in the Access form it should neve be allowed to calcual;te more that above 12, so if the material risks add up to 13, it should either give a warning message OR a message saying it will go over 12 and to adjust as necessary.


Public Function GetMaterialRisk(SampleID As Integer) As Byte
Dim lists(6) As String
lists(0) = "ListAnalysis"
lists(1) = "ListAsbestosType"
lists(2) = "ListCondition"
lists(3) = "ListFriability"
lists(4) = "ListPosition"
lists(5) = "ListTreatment"
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT SampleAnalysisID, SampleAsbestosTypeID, SampleConditionID, SampleFriabilityID, SamplePositionID, SampleTreatmentID FROM TableSamples WHERE SampleID=" & SampleID, dbOpenSnapshot)
Dim risk As Byte
risk = 0
For n = 0 To 5
risk = risk + GetFactor(lists, rs)
Next n
GetMaterialRisk = risk
End Function

Public Function GetPriorityRisk(SampleID As Integer) As Byte
Dim lists(10) As String
lists(0) = "ListLocation"
lists(1) = "ListAccessibility"
lists(2) = "ListExtent"
lists(3) = "ListNumOccupants"
lists(4) = "ListUseFreq"
lists(5) = "ListUseAvgTime"
lists(6) = "ListActivity"
lists(7) = "ListActivity"
lists(8) = "ListMaintenanceActivity"
lists(9) = "ListMaintenanceFreq"
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT SampleLocationID, SampleAccessibilityID, SampleExtentID, SampleNumOccupantsID, SampleUseFreqID, SampleUseAvgTimeID, SampleActivityMainID, SampleActivitySecondaryID, SampleMaintenanceActivityID, SampleMaintenanceFreqID FROM TableSamples WHERE SampleID=" & SampleID, dbOpenSnapshot)
Dim risk As Byte
risk = 0
For n = 0 To 5
risk = risk + GetFactor(lists, rs)
Next n
risk = -VBA.Int(-risk / 3)
For n = 6 To 9
risk = risk + GetFactor(lists, rs)
Next n
GetPriorityRisk = risk
End Function

Public Function MaterialRiskString(risk As Variant) As String
MaterialRiskString = ""
If IsNull(risk) Or risk = -1 Then Exit Function
If risk = 0 Then MaterialRiskString = "NADIS"
If risk >= 1 Then MaterialRiskString = "Very Low"
If risk >= 5 Then MaterialRiskString = "Low"
If risk >= 7 Then MaterialRiskString = "Medium"
If risk >= 10 Then MaterialRiskString = "High"
End Function


Public Function PriorityRiskString(risk As Variant) As String
PriorityRiskString = ""
If IsNull(risk) Or risk = -1 Then Exit Function
If risk = 0 Then PriorityRiskString = "NFA"
If risk >= 1 Then PriorityRiskString = "Very Low"
If risk >= 5 Then PriorityRiskString = "Low"
If risk >= 7 Then PriorityRiskString = "Medium"
If risk >= 10 Then PriorityRiskString = "High"
End Function