none
Failure of me! in Access VBA code RRS feed

  • Question

  • This one is a real head-scratcher for me.

    I have a fairly simple Access database that reads the filenames of a directory of image files and saves them to a table so that the can be displayed using a search form. The database parses various search fields from information encoded in the filenames. That's all working well.

    Trouble is, the group I prepared this for uses network storage, and when they scan the network location, it seems to break Access. Subsequent to that, VBA code that had previously worked to refer to form controls stops working. The scan itself runs like it should (it throws no errors and the data ends up correctly inserted into the appropriate table), but afterwards, VBA code in the on_load event of a form no longer functions. Specifically:

    Private Sub Form_Load()
    Dim pathStr As String
    Dim SQLStr As String
    Dim ctl As Control

    'set the DB object
    Set DB = New DBConn 'custom class, not relevant to this problem

    Call setPath

    For Each ctl In Me.Controls
        Debug.Print "name = " & ctl.Name
    Next ctl

    Set ctl = Me!ImageList 'this doesn't work once this problem is encountered-prior to it, this assignment works fine
    Set PicList = New SelectionList
    SQLStr = "SELECT TPX.pic_ID, TPX.FilePath FROM Tbl_Pictures AS TPX"

    Call PicList.CreateList(DB, ctl, SQLStr) 'PicList is a custom class

    This code runs up to the PicList.CreateList command, which fails because ctl is null. Prior to scanning the network location (the scan is simple, it basically just uses dir recursively, and it works great both locally and on network drives wherever I try it), this code ran fine.

    Also, the for-next loop I added as debugging to iterate through the controls collection shows everything on the form as it should be.

    If I hover over Me!Imagelist in the "Set ctl =" line, it reads as null.

    I've tried switching out Me! for forms("imageform"); no luck there. Recompiling also doesn't help. Once it's broken, it stays broken.

    Anybody have any ideas about what's going on here? Thanks in advance for any thoughts.

    Monday, September 28, 2015 7:38 PM

Answers

  • Found it! There was a sneaky unhandled error I had previously missed. Thanks for the thoughts.

    • Marked as answer by Gellis72 Tuesday, September 29, 2015 12:53 AM
    Tuesday, September 29, 2015 12:53 AM

All replies

  • Anybody have any ideas about what's going on here? Thanks in advance for any thoughts.

    Hi Gellis72,

    When you encounter not intercepted error, you are asked to continue (which is hardly possible because of the error), or to reset all (global) variables to a default value, loosing the meaningful data.

    Try to catch the error with an appropriate error handling. Or use some recovery mechanism to restore all important globals after such an non-intercepted error.

    Imb.


    • Edited by Imb-hb Monday, September 28, 2015 8:28 PM
    • Proposed as answer by Gary Voth Tuesday, September 29, 2015 5:24 AM
    Monday, September 28, 2015 8:28 PM
  • If you can see a value for ctl, regardless of null or otherwise, there isn't a problem with Me!.  Your variable has been set and that is the value of the control it references.

    What type of control is Me!ImageList?  Is it a list box?  If so, it's going to return null until something in the list is selected.

    Can you show us the code for PicList.CreateList()?

    Where is PicList declared and instantiated?



    • Edited by RunningManHD Monday, September 28, 2015 10:52 PM
    Monday, September 28, 2015 10:47 PM
  • Hi. I could be reading your code wrong, but I couldn't understand how it could have worked before. Since ctl was declared as an object (control), and you may be assigning a "value" to it instead. When you said that the code doesn't function, were you getting any error messages? For example, did you get a Type Mismatch error? Just curious... I might be missing something obvious...
    Tuesday, September 29, 2015 12:24 AM
  • Found it! There was a sneaky unhandled error I had previously missed. Thanks for the thoughts.

    • Marked as answer by Gellis72 Tuesday, September 29, 2015 12:53 AM
    Tuesday, September 29, 2015 12:53 AM
  • Hi. Congratulations on figuring it out. Good luck with your project.
    Tuesday, September 29, 2015 1:29 AM