Function error (run time 91) seems to bypass handler? (Access 2007)

[AOB]

Hi there,

I have a public function which connects to a defined Outlook mailbox folder and returns the number of e-mails within that folder (or, a null string, if for whatever reason it can't connect to the folder)

It's been running fine for weeks now but just today it's started causing me problems (the function is called as part of a FormLoad event, to populate a textbox)

Below is a shortened version of the code :

Option Compare Database
Option Explicit
 
' Outlook objects
 
Private appOutlook As Object                ' Outlook Application
Private objNameSpace As Object              ' Outlook NameSpace
Private objMailbox As Object                ' Outlook MAPIFolder
Private objInbox As Object                  ' Outlook MAPIFolder
 
Public Function CheckInbox(blnRetainObjects As Boolean) As Variant
 
[INDENT]Dim frm As Form
Dim i As Long
 
[COLOR=seagreen]Set frm = Forms("frmNavigation")[/COLOR]
 
' Connect to MS Outlook
 
Set appOutlook = CreateObject("Outlook.Application")
Set objNameSpace = appOutlook.GetNamespace("MAPI")
 
' Connect to mailbox
 
Set objMailbox = ConnectToFolder(objNameSpace, GetMailbox)
 
If objMailbox Is Nothing Then
[INDENT]CheckInbox = ""
GoTo ExitSub
[/INDENT]End If
 
' Connect to inbox
 
Set objInbox = ConnectToFolder(objMailbox, GetInbox)
 
If objInbox Is Nothing Then
[INDENT]CheckInbox = ""
GoTo ExitSub
[/INDENT]End If
 
' Return number of outstanding e-mails to be processed
 
[COLOR=red]CheckInbox = objInbox.Items.Count[/COLOR]
 
[/INDENT]ExitSub:
[INDENT]frm.txtEMailsOutstanding.Value = CheckInbox
 
If Not blnRetainObjects Then
[INDENT]Set appOutlook = Nothing
Set objNameSpace = Nothing
Set objMailbox = Nothing
Set objInbox = Nothing
Set frm = Nothing
[/INDENT]End If
 
 
[/INDENT]End Function

The weird thing is, whenever I try to load the form, the VBA errors on the line in red :

Run-time error '91':
Object variable or With block variable not set

But if I set the next statement to the line in green (i.e. right-click and 'Set Next Statement...'), and continue from there, it runs fine. It's as if the code is skipping down to the line in red and has to be manually manipulated back to the start to pick up the object variables.

I'm stumped - especially as this worked up until this afternoon without any issues - I've obviously inadvertently changed something in the background but I haven't touched this function (or in fact this module whatsoever) so I have no idea where to even start?

Any suggestions?

Thanks

AOB

 

[Rx_]

Our place is converting to Outlook in a few weeks (hooray!)
Can't test this for you now.
Just for grins, can you go to the module Tools References
See if the outlook references are broken.
They might be checked, but show an error.

To that effect, there is a set of reference values for IsBroken
that can be checked by code.
What could cause such a thing? Perhaps a patch upgrade to the Com object.
One time I had an IT type change my entire webserver IP address six evenings over a two week period with out notifying anyone. Of course I was the bad person for mentioning it since he was up for the IT Employee of the year award. LOL
My Zen philosophy is "no why questions"

Since it was working, this the first thing to check. Let us know what you find, please.
It will be interesting to know.

 

[AOB]

Hi Rx,

I'm using late binding so that specific Outlook references aren't required - notice that everything is declared as a plain object and then set arbitrarily at run-time rather than declared upfront as an Outlook.MAPIFolder / Outlook.NameSpace etc.

Makes for much easier deployment...

Problem seems to be driven by a separate load event specific to a page in a multi-tab control. Think I've trapped it but it's spat out a few other problems for me. I love this stuff...

 

[pr2-eugin]

I know this might be a silly question, but what is ConnectToFolder?

 

[AOB]

Hi pr2-eugin,

There are no silly questions in here I've found!

It's just a separate function to locate the appropriate folder within the parent

Public Function ConnectToFolder(objParent As Object, strFolder As String) As Object
[INDENT]Dim i As Long
 
For i = 1 To objParent.Folders.Count
[INDENT]If objParent.Folders(i).Name Like "*" & strFolder & "*" Then
[INDENT]Set ConnectToFolder = objParent.Folders(i)
Exit For
[/INDENT]End If
[/INDENT]Next i
 
[/INDENT]End Function

 

[pr2-eugin]

Might be something wrong with me.. But what are GetMailBox and GetInbox? Why are they passed as String?

 

[AOB]

Apologies Paul, that' smy bad - again, these are plain functions to get the specified mailbox and inbox names from a table of static - GetLocation is the same...

Public Function GetMailbox() As String
[INDENT]GetMailbox = Nz(DLookup("[MailboxName]", "[tblStatic]", _
"[Location]='" & GetLocation() & "'"), "Unregistered")
 
[/INDENT]End Function
 
Public Function GetInbox() As String
[INDENT]GetInbox = Nz(DLookup("[InboxName]", "[tblStatic]", _
"[Location]='" & GetLocation() & "'"), "Unregistered")
 
[/INDENT]End Function

 

[The_Doc_Man]

I would suggest that you put a trap-catcher in that code. The message (to me) clearly says that objInBox is unbound (because if it were properly bound, .Items.Count would always work even if the count were zero). But I recall some issues about Outlook not being happy if you create an Outlook Object when one was already open on the 'puter already. I had to use something similar to this... (OLApp is an object, if you had to ask.)

    Set OLApp = Nothing                 'start with simplest assumption
    
    On Error Resume Next                'block traps while we poke around
    Set OLApp = GetObject(, "Outlook.Application") 'find existing instance of Outlook
    On Error GoTo 0                     'remove the trap block
    
    On Error GoTo SAM_Trapped           'ok, we are good now, restore intended trap.
    
    TrapPoint = "Create New Outlook Application"
    lRetSts = 0                         'assume we are going to work
    
'   at this point, either we got an application link or we did not
    
    OLNew = False                       'assume we found one
    If OLApp Is Nothing Then            'did we find an existing Outlook we could use?
        OLNew = True                    'no, have to create one, so ...
        Set OLApp = CreateObject("Outlook.Application")    'create a private apps object
    End If

I'm not going to say this is your problem, but if there is a chance that in prior tests Outlook wasn't open and now suddenly it WAS open, this might be part of your problem.

 

[AOB]

Thanks Doc_Man

I agree, and that was one of the first things I considered, however Outlook has always been open whenever I've run this (before now and today) and it's never been a problem.

The bit that really puzzles me (troubles me?...) is that when the debugger halted at the line in red, the objInbox object was empty (i.e. = Nothing)

But the handler above it is supposed to exit the sub if the code fails to apply an appropriate folder to that object. The code appears to be skipping past all of that and then dying at that line.

Then (with the code still paused), if I tell it to start again from the top, it picks up the object absolutely fine and runs without issue.

As I mentioned, the problem seemed to stem from a requery on the tab that holds the textbox that displays the result of the function, and by tweaking that the problem seems to have gone away.

What bothers me is that I can't figure out how the compiler made its way halfway down the code of the function instead of getting stopped by the handlers above. That freaks me out a little bit!

But no longer urgent as, like I said, I've managed to work around the problem...

(And appreciate all the suggestions so far, thanks guys!)

AOB