Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2015
    Posts
    15
    Provided Answers: 1

    Unanswered: Invalid Use of Null requires Nz Function?

    Hi, I’m hoping someone can help me with an ongoing problem.
    I am currently running a form that I’ve asked to compare 5 values, (P,A,E,R or F_Resultant) and generate the highest as a statement. Whenever I open the db or go to a new record I get an “Invalid Use of Null”. I’ve been advised to use the Nz function however I am unfamiliar with where it should be inserted in the code… especially in an If Then Else statement. Originally this was written as a macro and now converted to VBA. The debug points to the ‘If (CInt(Forms!FRAP!R_Risk) < CInt(Forms!FRAP!A_Resultant)) Then’ line. Any help in resolving this would be most appreciated as I have been struggling with this for months now.

    Private Sub Form_Current()
    DoCmd.SetProperty "R_Risk", , CInt(Forms!FRAP!P_Resultant)
    If (CInt(Forms!FRAP!R_Risk) < CInt(Forms!FRAP!A_Resultant)) Then
    DoCmd.SetProperty "R_Risk", , CInt(Forms!FRAP!A_Resultant)
    End If
    If (CInt(Forms!FRAP!R_Risk) < CInt(Forms!FRAP!E_Resultant)) Then
    DoCmd.SetProperty "R_Risk", , CInt(Forms!FRAP!E_Resultant)
    End If
    If (CInt(Forms!FRAP!R_Risk) < CInt(Forms!FRAP!R_Resultant)) Then
    DoCmd.SetProperty "R_Risk", , CInt(Forms!FRAP!R_Resultant)
    End If
    If (CInt(Forms!FRAP!R_Risk) < CInt(Forms!FRAP!F_Resultant)) Then
    DoCmd.SetProperty "R_Risk", , CInt(Forms!FRAP!F_Resultant)
    End If
    If (CInt(R_Risk) <= 4) Then
    DoCmd.SetProperty "R_Statement", , "A Low Level of Risk is still evident in one or more sectors."
    End If
    If (CInt(R_Risk) > 4) Then
    DoCmd.SetProperty "R_Statement", , "A Moderate Level of Risk is still evident in one or more sectors. Manage actions or operations through identified controls such as defined procedures and work instructions. An additional ""Specific Action Plan"" may be incorporated if this event is unexpec"
    End If
    If (CInt(R_Risk) > 6) Then
    DoCmd.SetProperty "R_Statement", , "A High level of Risk is still evident in one or more sectors. Manage actions or operations through identified controls such as required training and/or experience as well as defined procedures, checklists and/or permits. Ensure a ""Specific Action Plan"
    End If
    If (CInt(R_Risk) > 8) Then
    DoCmd.SetProperty "R_Statement", , "An Extreme level of Risk is still evident in one or more sectors. Immediate preventative action is required to reduce this risk to an acceptable level. Senior Management to be advised of potential risk."
    End If

    End Sub

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if there is a risk that one or more of the forms controls contains a NULL value then use NZ to supply a replacement value

    Code:
    dim R_Risk as integer
    intR_Risk = 0
    if (NZ(P_Resultant,0) > intR_Risk) then intR_Risk = P_Resultant
    if (NZ(A_Resultant,0) > intR_Risk) then intR_Risk = A_Resultant
    if (NZ(E_Resultant,0) > intR_Risk) then intR_Risk = E_Resultant
    if (NZ(R_Resultant,0) > intR_Risk) then intR_Risk = R_Resultant
    if (NZ(F_Resultant,0) > intR_Risk) then intR_Risk = F_Resultant
    R_Risk.value = intR_Risk 'assign the value of our working variable to the forms control
    
    dim strMessage as string
    select case intR_Risk
      CASE > 8
        strMessage = "An Extreme level of Risk is still evident in one or more sectors. Immediate preventative action is required to reduce this risk to an acceptable level. Senior Management to be advised of potential risk."
      CASE > 6
        strMessage = "A High level of Risk is still evident in one or more sectors. Manage actions or operations through identified controls such as required training and/or experience as well as defined procedures, checklists and/or permits. Ensure a 'Specific Action Plan'"
      CASE > 4
        strMessage = "A Moderate Level of Risk is still evident in one or more sectors. Manage actions or operations through identified controls such as defined procedures and work instructions. An additional 'Specific Action Plan' may be incorporated if this event is unexpected"
      case >0
        strMessage = "A Low Level of Risk is still evident in one or more sectors."
      case else
        strMessage = "congratulations, you have achieved the impossible, done a risk assessment and found zero risk"
    end select
    R_Statement = strMessage
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2015
    Posts
    15
    Provided Answers: 1

    I Think I'm Getting Closer

    Quote Originally Posted by healdem View Post
    if there is a risk that one or more of the forms controls contains a NULL value then use NZ to supply a replacement value

    Code:
    dim R_Risk as integer
    intR_Risk = 0
    if (NZ(P_Resultant,0) > intR_Risk) then intR_Risk = P_Resultant
    if (NZ(A_Resultant,0) > intR_Risk) then intR_Risk = A_Resultant
    if (NZ(E_Resultant,0) > intR_Risk) then intR_Risk = E_Resultant
    if (NZ(R_Resultant,0) > intR_Risk) then intR_Risk = R_Resultant
    if (NZ(F_Resultant,0) > intR_Risk) then intR_Risk = F_Resultant
    R_Risk.value = intR_Risk 'assign the value of our working variable to the forms control
    
    dim strMessage as string
    select case intR_Risk
      CASE > 8
        strMessage = "An Extreme level of Risk is still evident in one or more sectors. Immediate preventative action is required to reduce this risk to an acceptable level. Senior Management to be advised of potential risk."
      CASE > 6
        strMessage = "A High level of Risk is still evident in one or more sectors. Manage actions or operations through identified controls such as required training and/or experience as well as defined procedures, checklists and/or permits. Ensure a 'Specific Action Plan'"
      CASE > 4
        strMessage = "A Moderate Level of Risk is still evident in one or more sectors. Manage actions or operations through identified controls such as defined procedures and work instructions. An additional 'Specific Action Plan' may be incorporated if this event is unexpected"
      case >0
        strMessage = "A Low Level of Risk is still evident in one or more sectors."
      case else
        strMessage = "congratulations, you have achieved the impossible, done a risk assessment and found zero risk"
    end select
    R_Statement = strMessage
    Good day healdem,
    First off, let me say thank you for your quick response. Everyone here has been helpful in assisting me with this project. A little background… I’ve been asked to develop a database solution involving Risk Assessment and although my background in risk is solid I have very little knowledge of vba except what I’ve been picking up on this project. By the way, your last comment on addressing a 0 risk outcome is hilarious and we will keep it. Cheers.

    Now to business. Here's how I attached it to an "On Current" event although I'm not sure if it should be an "Afer Update" or "Before Update":

    ------------------------
    Private Sub Form_Current()
    Dim R_Risk As Integer
    intR_Risk = 0
    If (Nz(P_Resultant, 0) > intR_Risk) Then intR_Risk = P_Resultant
    If (Nz(A_Resultant, 0) > intR_Risk) Then intR_Risk = A_Resultant
    If (Nz(E_Resultant, 0) > intR_Risk) Then intR_Risk = E_Resultant
    If (Nz(R_Resultant, 0) > intR_Risk) Then intR_Risk = R_Resultant
    If (Nz(F_Resultant, 0) > intR_Risk) Then intR_Risk = F_Resultant
    R_Risk.Value = intR_Risk 'assign the value of our working variable to the forms control

    Dim strMessage As String
    Select Case intR_Risk
    Case Is > 8
    strMessage = "An Extreme level of Risk is still evident in one or more sectors. Immediate preventative action is required to reduce this risk to an acceptable level. Senior Management to be advised of potential risk."
    Case Is > 6
    strMessage = "A High level of Risk is still evident in one or more sectors. Manage actions or operations through identified controls such as required training and/or experience as well as defined procedures, checklists and/or permits. Ensure a 'Specific Action Plan'"
    Case Is > 4
    strMessage = "A Moderate Level of Risk is still evident in one or more sectors. Manage actions or operations through identified controls such as defined procedures and work instructions. An additional 'Specific Action Plan' may be incorporated if this event is unexpected"
    Case Is > 0
    strMessage = "A Low Level of Risk is still evident in one or more sectors."
    Case Else
    strMessage = "congratulations, you have achieved the impossible, done a risk assessment and found zero risk"
    End Select
    R_Statement = strMessage
    End Sub

    I inserted your code as “On Current” event but no I’m getting an “invalid qualifier” error on the R_Risk.Value = intR_Risk 'assign the value of our working variable to the forms control line. Any clues?? At least I'm beginning to understand the issue and I'm finding it easier to read the code. Much appreciated.

    Regards and thank you again.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    there's separate issues here
    one is running code to generate the message
    the other is selecting the correct event(s) to work out what that message should be

    first off I'd suggest you push the message creation into a function that returns the message. if the message has to appear in multiple consumers (forsm and or reports then a redesign may be needed)

    next work out when you would generate the message, and therfore waht event9s) the code shoudl run in.
    on current event is fired when the current row changes (either moved the internal pointer to show another row or start to add a new row). its probably when adding that you are hitting the original null error

    candidate events are:-
    on current
    on change
    before update
    as to which one(s) are required I dunno, thats down to your requirements, when you see this needed. bear in mind on change, before update can be triggered at form and individual control levels

    making code easier to read is a large part of the battle of trying to keep ongoing maintenance costs of software down / mangeable. comment where needed, where relevant. use comments to explain your thinking if its not immediately obviosu what you are up to. Id argue that in most situations well thought out variable / control names go a long way of self documenting, reducing the need for comments. but always comment waht you are trying to achieve. it shoudl almnost be like a book

    as to why you get invalid qualifier” error I dunno

    Im assuming that R_Risk is a control, if its not then drop the .value

    if R_Risk is neither a control or variable in your form/db then thats the most likely source of the error
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

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