Event Procedure Code (Access 2000)

Topic

New Reply

I’m not an Access programmer but I need to create a database.
In this database there are 3 fields (Likelihood, Consequence, and Total Risk Exposure). I’m supposed to multiply the values of Likelihood and Consequence and place the sum in Total Risk Exposure. Depending on the sum I’m also supposed to change the color of Total Risk Exposure to either Green, Yellow, or Red. I’ve been told I need to do this as an After Update Event on both Likelihood and Consequence. Can anyone help me?
Thanks in advance.

Reply | Quote

Replies

> … multiply the values of Likelihood and Consequence and place the sum …

Do you mean the product?

Reply | Quote

Hans,

Yes!

Reply | Quote

The “standard” approach is not to store a calculated field in a table, since it is derived information. Instead, create a query based on the table, and add a calculated column. The column will be up-to-date each time the query is opened, and the query can be used as record source for forms and reports.
To create such a column, enter the following in the first blank column in the query grid:

Total Risk Exposure: [Likelihood]*[Consequence]

If you really need to store the calculated field in the table, you must create some code in the form based on the table that is used to enter/edit records.
Open the form in design view.
Select the text box bound to the Likelihood field. I’ll assume it is named Likelihood.
Activate the Event tab of the Properties window.
Click in the After Update event.
Select [Event Procedure] from the dropdown list.
Click the builder button … to the right of the dropdown arrow.
Complete the code so that it looks like this:

Private Sub Likelihood_AfterUpdate()
Is IsNull(Me.Likelihood) Or IsNull(Me.Consequence) Then
Me.[Total Risk Exposure] = Null
Else
Me.[Total Risk Exposure] = [Likelihood] * [Consequence]
End If
End Sub

Switch back to Access.
Repeat the above for the text box bound to the Consequence field. (The procedure will be named Consequence_AfterUpdate, of course)

Reply | Quote

Gwenda gave you code to change the color. You can also use Conditional Formatting for this: select the Total Risk Exposure text box and select Format | Conditional Formatting…

Reply | Quote

Hans, thank you. You’re an excellent programmer.

Reply | Quote

You’ll also need to create a procedure to change colours for the text of Total Exposure. You’ll need to do this everytime you change either Likelihood or Consequence PLUS everytime you move from one record to another. Since this code will have to go into 3 places, it’s better to create a separate sub routine or function and then call it from the Form’s Current event (which occurs when you move from one record to another), and the AfterUpdate events of Likelihood and Consequence.

You need to co into the Code behind the form and put this:

Private Sub Form_Current()
Call ChangeExposureColor
End Sub

Private Sub Likelihood_AfterUpdate()
Call ChangeExposureColor
End Sub

Private Sub Consequence_AfterUpdate()
Call ChangeExposureColor
End Sub

Sub ChangeExposureColor()
Select Case Me.Exposure
Case 1 To 5
Me.Exposure.ForeColor = 8453888 ‘green
Case 6 To 10
Me.Exposure.ForeColor = 65535 ‘yellow
Case Is > 10
Me.Exposure.ForeColor = 255 ‘red
End Select
End Sub

Reply | Quote

Thank you Gwenda

Reply | Quote

Gwenda,
Your code is great, thanks.
Is there a way to be more specific about the backcolor change. When I start a new record the color from the previous record is still there. I assumed it would reset to white till new numbers were entered in Likelihood and COnsequence.
Also, Is there a way to be more specific about the cases? 1*5 =5 is yellow but 5*1=5 is green. I’m dealing with a 5X5 Risk Matrix so it makes a difference what order you do the math. It needs to always be likelihood * consequence.
5 GYRRR
4 GYYRR
3 GYYYR
2 GGGYY
1 GGGGY
1 2 3 4 5

Reply | Quote

Try this:

Sub ChangeExposureColor()
If IsNull(Me.Likelihood) Or IsNull(Me.Consequence) Then
Me.Exposure.ForeColor = vbBlack
Exit Sub
End If
Select Case 10 * Me.Likelihood + Me.Consequence
Case 11 To 14, 21 To 23, 31, 41, 51
Me.Exposure.ForeColor = vbGreen
Case 15, 24 To 25, 32 To 34, 42 to 43, 52
Me.Exposure.ForeColor = vbYellow
Case Else
Me.Exposure.ForeColor = vbRed
End Select
End Sub

Reply | Quote