Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Utah, USA
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Event Procedure Code (Access 2000)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Event Procedure Code (Access 2000)

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

    Do you mean the product?

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Utah, USA
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Event Procedure Code (Access 2000)

    Hans,

    Yes!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Event Procedure Code (Access 2000)

    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)

  5. #5
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    vancouver, BC, Br. Columbia
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Event Procedure Code (Access 2000)

    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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Event Procedure Code (Access 2000)

    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...

  7. #7
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Utah, USA
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Event Procedure Code (Access 2000)

    Thank you Gwenda

  8. #8
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Utah, USA
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Event Procedure Code (Access 2000)

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

  9. #9
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Utah, USA
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Event Procedure Code (Access 2000)

    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

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Event Procedure Code (Access 2000)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •