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

    Unanswered: Invalid Use of Null Using Checkbox

    Good day everyone, I have been researching this issue and am having no luck and its driving me nuts. I have developed a form and am using a check box to enable greyed out text boxes. I keep getting an "Invalid Use of Null" error once I open up the form or get a new record. Any help would be greatly appreciated. Below is a copy of the script:

    Private Sub P_Check_AfterUpdate()
    If Me.P_Check.Value = -1 Then
    People_Cons.Enabled = True
    Else
    People_Cons.Enabled = False
    End If
    End Sub
    Private Sub Form_Current()
    If Me.P_Check.Value = -1 Then
    People_Cons.Enabled = True
    Else
    People_Cons.Enabled = False
    End If
    End Sub

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    There are times, such as in a New Record, when a Checkbox is Null, rather than being equal to 0/-1...so try using the reverse logic, instead, i.e.

    Code:
    If Me.P_Check.Value <> -1 Then
      People_Cons.Enabled = False
    Else
      People_Cons.Enabled = True
    End If

    This covers the Checkbox being either Null or 0, i.e. anything except -1.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Aug 2015
    Posts
    15
    Provided Answers: 1
    Good day Linq,
    Thanks for the quick reply. I tried the script as below and no joy... error keeps coming up.
    should there be anything before or after the <>? I liked the logic of reversing the context but something is still wonky.

    Private Sub P_Check_AfterUpdate()
    If Me.P_Check.Value <> -1 Then
    People_Cons.Enabled = False
    Else
    People_Cons.Enabled = True
    End If
    End Sub
    Private Sub Form_Current()
    If Me.P_Check.Value <> -1 Then
    People_Cons.Enabled = False
    Else
    People_Cons.Enabled = True
    End If
    End Sub

  4. #4
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    I would use:
    Code:
    Private Sub P_Check_AfterUpdate()
    
        People_Cons.Enabled = Nz(Me.P_Check.Value, 0)
    
    End Sub
    
    Private Sub Form_Current()
    
        People_Cons.Enabled = Nz(Me.P_Check.Value, 0)
    
    End Sub
    Have a nice day!

  5. #5
    Join Date
    Aug 2015
    Posts
    15
    Provided Answers: 1
    First let me say thank you to everyone for helping me/us on this problem. Needless to say I'm a little embarrassed now as it seems the problem is NOT check-box related. After pulling up a "clean" db it turns out the Invalid Use of Null is most likely attributed to a macro that we needed to use. The form needs to compare 5 resultant scores to find the highest (see macro screen shot below). I'm guessing now that when we go to or refresh a new form it searches the 5 resultant scores
    P_Resultant
    A_Resultant
    E_Resultant
    R_Resultant
    F_Resultant
    and of course finds 0. I'm assuming this is the problem and I'm not sure how to resolve this. I tried adding 1 as the default value but that just stops the macro. Anyone have any ideas as to what I can put in the macro that eliminates the the Invalid Use of Null? any help would be greatly appreciated.

    Click image for larger version. 

Name:	Macro.jpg 
Views:	6 
Size:	116.3 KB 
ID:	16534

    Click image for larger version. 

Name:	Macro Invalid v3.jpg 
Views:	3 
Size:	24.1 KB 
ID:	16535

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by Kentski View Post

    I'm guessing now that when we go to or refresh a new form it searches the 5 resultant scores...and of course finds 0.
    It's not finding 0...it's finding Null, hence the error! I can't get your attachment large enough to read, and like most of us, here, seldom, if ever, use Embedded Macros, but the standard way of dealing with Nulls, when they present problems, is by wrapping the individual Value or expression in the Nz() Function and assigning another Value, such as Zero. So have a look at Nz() in Access Help.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

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

    Red face Invalid Use of Null

    Again, thank you Linq,
    I will try and re-write the script as code instead of as a macro, unfortunately this is a collaborative effort and my partner in crime is now at sea (this is part of a Risk Assessment Project) and I'm more of the concept guy while he is the coding guy (I guess)

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

    Invalid Use of Null

    Good day everyone and again thanks for all the support and help. I have been struggling to solve an "Invalid Use of Null" error on a form that my team designed. On the form we are trying to get a resultant score after comparing 5 fields. The script was written as a macro (unfortunately) and after some advice it was recommended to use the Nz function. I have tried to write the Nz function into the original macro as I'm not comfortable or familiar enough to re-write the whole thing in VBL. I have posted the newest script below but am still encountering an "Invalid Use of Null" error. I'm not sure if its because of the syntax or I'm writing it in wrong. Can someone please review the script and let me know if I messed up.

    SetProperty
    Control Name R_Risk
    Property Value
    Value =Nz(CInt([Forms]![FRAP]![P_Resultant]))

    If Nz(CInt([Forms]![FRAP]![R_Risk])<CInt([Forms]![FRAP]![A_Resultant])) Then
    SetProperty
    Control Name R_Risk
    Property Value
    Value =CInt([Forms]![FRAP]![A_Resultant])

    End If

    If Nz(CInt([Forms]![FRAP]![R_Risk])<CInt([Forms]![FRAP]![E_Resultant])) Then
    SetProperty
    Control Name R_Risk
    Property Value
    Value =CInt([Forms]![FRAP]![E_Resultant])

    End If

    If Nz(CInt([Forms]![FRAP]![R_Risk])<CInt([Forms]![FRAP]![R_Resultant])) Then
    SetProperty
    Control Name R_Risk
    Property Value
    Value =CInt([Forms]![FRAP]![R_Resultant])

    End If

    If Nz(CInt([Forms]![FRAP]![R_Risk])<CInt([Forms]![FRAP]![F_Resultant])) Then
    SetProperty
    Control Name R_Risk
    Property Value
    Value =CInt([Forms]![FRAP]![F_Resultant])

    End If

    Again, sorry that its in a macro. This is my first attempt at a Nz function. If someone could let me know if this looks right or at least let me know how it should look in VBL it would be greatly appreciated. Either way the help would be nice.

    Regards and thank you
    Kentski

  9. #9
    Join Date
    Aug 2015
    Posts
    15
    Provided Answers: 1
    ot sure if this matters but the macro is query based.

  10. #10
    Join Date
    Aug 2015
    Posts
    15
    Provided Answers: 1
    Not sure if it matters but the macro is based on a query.

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    OK so there are 2, possibly,3 states your combo box could have
    ...no value (NULL) because no value has been selected as yet (coudl be a new record, could be the user just hasn't selected a value and you haven't provided a default as part of the design
    ...a value has been selected and therefore you need to do whatever work is required
    ...or (possibly) an incorrect value has been supplied and your code to do wahtever work must be capable of recovering from that error

    going back to your original code
    Code:
    Private Sub P_Check_AfterUpdate()
      If Me.P_Check.Value = -1 Then
        People_Cons.Enabled = True
      Else
        People_Cons.Enabled = False
      End If
    End Sub
    
    Private Sub Form_Current()
      If Me.P_Check.Value = -1 Then
        People_Cons.Enabled = True
      Else
        People_Cons.Enabled = False
      End If
    End Sub
    ..refactor this so that instead of having the same code in more than one place, push it into a single function and call that function as required

    Code:
    private Sub P_Check_AfterUpdate()
      SetPeopleCons(P_Check.Value)
    end sub
    
    Private Sub Form_Current()
      SetPeopleCons(P_Check.Value)
    end sub
    
    private function SetPeopleCons(Value as boolean)
     if isNull(Value) then
       'what to do? 'decide what should happen in this event
      else
       If Me.P_Check.Value = -1 Then
          People_Cons.Enabled = True
        Else
          People_Cons.Enabled = False
        End if
      End If
    end function
    this fucntion can in truen be shortcircuited to:-

    Code:
    private function SetPeopleCons(Value as boolean)
     if isNull(Value) then
       'what to do? 'decide what should happen in this event
      else
        People_Cons.Enabled = Me.P_Check.Value
      End If
    end function
    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
  •