Results 1 to 7 of 7
  1. #1
    dannyok90 is offline Novice
    Windows 10 Office 365
    Join Date
    Nov 2021
    Posts
    2

    Invalid Use of Null (probably a simple fix)

    Hi all



    can anybody explain to me why I'm getting and invalid use of null with this code:

    Code:
    Private Sub Lst_TopNavigation_DblClick(Cancel As Integer)
    
        On Error GoTo errHandler
        Dim frm$
    
    
        With Me
            frm$ = .Lst_TopNavigation.Column(2)
            Select Case .Lst_TopNavigation
                Case 3
                    DoCmd.OpenForm frm$, acFormDS
                Case Else
                    DoCmd.OpenForm frm$, acNormal
            End Select
        End With
    
    
    procDone:
        
        Exit Sub
    
    
    errHandler:
    
    
        MsgBox Err.Description, vbCritical, "Error Opening Screen"
        Resume procDone
    
    
    End Sub
    your help is warrmmmly received!!

    Thanks,
    Dan

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    2,028
    Something is null when it should not be.
    Walk through the code with F8
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?search_query=debug+access+vba



  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    10,625
    Agreed. A little simpler if you just pass the name (at least I presume that's what is in Column(2) )?
    Code:
    Dim strName As String
    strName = Me.Lst_TopNavigation.Column(2)
    
    If Me.Lst_TopNavigation=3 Then
      DoCmd.OpenForm, strName,acFormDS
    Else
      DoCmd.OpenForm, strName,acNormal
    End If
    Not that this will help where either the bound column or column 2 has no value.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    dannyok90 is offline Novice
    Windows 10 Office 365
    Join Date
    Nov 2021
    Posts
    2
    That's exactly what's in n column2 mate. I'm think I'm kind of in between beginner and intermediate with VBA, atleast I understand the logic based on excel functions and certainly with Access. I haven't used the case function before, my understanding is that its less complicated than if and else statements. I'm not so sure haha. thanks, ill give this a go tomorrow.

    ps. thanks welshgasman.. I have stepped through it but because of my understand (or lack there of haha) I'm struggling to identify!!

    cheers,
    Dan

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    2,028
    Well columns start at 0, so is that the 3rd column being referenced?
    Set a break point on the With Me line, and then use the cursor to hover over each variable control.
    Nb when stopped on a line, it has NOT been executed.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?search_query=debug+access+vba



  6. #6
    ssanfu is online now Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,603
    Just an FYI,
    In IBM BASIC (from the days of the XT, 286 , 386 and 486 processor Computers), the following is how you defined variable types in code:

    These are the Variable types that could be declared in IBM BASIC, GW BASIC, Tiny BASIC, TRS-80 BASIC, etc....
    Character Character Name Type of Variable
    @ (at sign) Currency
    & (ampersand) Long Integer variable
    $ (dollar sign) String variable
    % (percent sign) Integer variable
    ! (exclamation point) Single-precision variable
    # (pound sign) Double-precision variable

    Although they are still backward compatible in VBA, you are really limiting what variable types you can define.
    It would be better for you if you started using modern syntax (as Micron demonstrated)
    Code:
    Dim FormName As String


    And Welcome to the forum!
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    10,625
    Hint - when you step through the code and a line is yellow highlighted ( e.g. frm$ = .Lst_TopNavigation.Column(2) ), that line as not been executed so you can only check properties or values that have been set/assigned/calculated before that line. So you cannot check what frm$ is, but you can check what Me.Lst_TopNavigation.Column(2) is because that is a direct reference to a control on your form. You do these checks by either mousing over the variable in the code while stepping through or in the immediate window, you can ask the question (including question mark):

    ?frm$
    and hit Enter key. Again, that question can only be asked after the line was executed.

    As for Select vs IF block, Case is often better than several IF tests but IMO
    - for sure not better when you are only making 1 test as in this case
    - If Case can be Null, you cannot test for Null and would have to convert it to a value that won't be used by any other Case

    I'd use case where there are more than 2 or 3 tests or if the action to be taken is the same for ranges
    Case 1 to 5
    ..do this

    or multiple values
    Case 1,3,5
    ..do that

    or other slightly complex tests. Cannot do those in an IF block.

    EDIT - I gotta start shortening my responses! Too much happening while I'm writing my essays.
    Oh, and for the perfectionists, not 100% true:
    So you cannot check what frm$ is
    You can check but in the case of a string, it will show "".
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 03-09-2021, 08:44 AM
  2. Replies: 4
    Last Post: 04-09-2019, 02:05 PM
  3. Invalid use of Null
    By hellfire45 in forum Access
    Replies: 12
    Last Post: 04-16-2015, 11:28 AM
  4. Replies: 3
    Last Post: 11-13-2013, 08:56 AM
  5. Invalid use of Null
    By Wayne311 in forum Programming
    Replies: 4
    Last Post: 01-27-2011, 05:10 PM

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
  •  
Other Forums: Microsoft Office Forums