How to check if a property is invalid?

[Stormin]

I have created a Ribbon button to switch the "MaintainConnection" property of the (first) pivot table's OBDC on the active worksheet. This throws an error when the pivot table's source is local, I assume because the property doesn't exist.

Currently I have the code set to exit when any error occurs, but I would like to check whether the "MaintainConnection" property exists (i.e. the pivot tables datasource is an OBDC) and then exit if it does not exist, plus a msgbox.

Does anyone know how I check can check if the property exists before changing it?

Sub ODBC_PT_StayConnected()

Dim ws As Worksheet
Dim pt As PivotTable
Dim pc As PivotCache

On Error GoTo Exit_Sub

Set ws = ActiveSheet
Set pt = ws.PivotTables(1)
Set pc = pt.PivotCache

'If function here to check if property MaintainConnection exists, or pt has external datasource (OBDC)

If MsgBox(pt & "'s ODBC MaintainConnection is currently " & pc.MaintainConnection & "." & vbNewLine & _
          "Switch to " & (Not pc.MaintainConnection) & "?", vbYesNo) _
    = vbYes Then
        pc.MaintainConnection = Not pc.MaintainConnection
        MsgBox pt & "'s ODBC MaintainConnection has been set to " & pc.MaintainConnection
End If

Exit_Sub:
End Sub

 

[gemma-the-husky]

add an error check to it.

on error goto fail
do it
exit sub

fail:
    msgbox "report error"

 

[Stormin]

That's what's in the current code above, which works, however I am trying to code by the ethos "the best way to handle errors is to avoid them" i.e. I know exactly where my error will be, so why trigger the error handler for ANY error if I can catch the specific error myself before it causes a code break? Hmm...

P.S. Should have mentioned in the original post that I have tried

If pc.MaintainConnection Is Nothing Then GoTo Err_NoOBDC

but that throws a type mismatch in that line.

 

[gemma-the-husky]

OK. I see

This may be technically a slight mis-explanation, but if you try to read something to do with an object collection, and it fails, then DAO throws an error. You can't avoid it.

eg. Is there a field called "RecordID"

fieldexists = currentdb.tabledefs("sometable").fields("RecordID").name = "RecordID"

if it exists, you get fieldexists = true
if it doesn't exist you get a trappable run time error. ie you do not get a result of False. So you have to detect a false by error trapping.

Same thing when seeing if a table exists, or in your case, a property.

 

[The_Doc_Man]

Here is the way to do this for anything that has a property. I used this for a control, but you can adapt it to the object over which you are concerned.

Public Function HasProp(ctlX As Access.Control, stPName As String) as Boolean

Dim pProp As Property
Dim bHasIt As Boolean

bHasIt = False

For Each pProp In ctlX.Properties
    If pProp.Name = stPName Then
        bHasIt = True
        Exit For
    End If
Next pProp

HasProp = bHasIt

End Function

This scans the list of properties, which is usually fairly short, and stops if it finds the named property, returning TRUE, or if it reaches the end of the list, returns FALSE. From there, an IF lets you do what you need.

 

[Stormin]

Ah, I've just realised I've made an embarrassing mistake. This is all in Excel, I thought I was in the Excel forums! Sorry!

I do not mind if this thread is closed/deleted as it's in completely the wrong category. Oops.

 

[gemma-the-husky]

I expect it's the same for excel, though.

 

[Stormin]

I believe you are right Dave,

if it doesn't exist you get a trappable run time error. ie you do not get a result of False. So you have to detect a false by error trapping.

Same thing when seeing if a table exists, or in your case, a property.

I think I will go with trapping the error number with the error handler and do it that way.