Object Invalid or No Longer Set Error (2 Viewers)

[Deleted member 73419]

Hi,

When I run this code, I get a 'Object Invalid or No Longer Set' error

Function FieldExists(strTableName As String, strFieldName As String) As Boolean

    Dim fld As Field

    For Each fld In CurrentDb.TableDefs(strTableName).Fields
'        If (StrComp(strFieldName, fld.Name, vbTextCompare) = 0) Then
'            FieldExists = True
'            Exit Function
'        End If
    Next
    
'    FieldExists = False

End Function

Sub test_FieldExists()

    Debug.Print FieldExists("Bands", "cat")
    
End Sub

I have heard it's something to do with using CurrentDB but I'm using it in other functions within the module and it's fine.

I can also see that the table 'Bands' also exists.

Anyone got any ideas?

Thanks

 

[cheekybuddha]

Try it like this to hold the single CurrentDb reference:

Function FieldExists(strTableName As String, strFieldName As String) As Boolean

  Dim fld As Field

  With CurrentDb
    For Each fld In .TableDefs(strTableName).Fields
      If (StrComp(strFieldName, fld.Name, vbTextCompare) = 0) Then
          FieldExists = True
          Exit Function
      End If
    Next
  End With
  FieldExists = False      ' <-- Actually unnecessary, since a boolean defaults to False anyway

End Function

CurrentDb is a function that returns a new instance of the current database on each invocation

 

[Deleted member 73419]

Try it like this to hold the single CurrentDb reference:

Function FieldExists(strTableName As String, strFieldName As String) As Boolean

  Dim fld As Field

  With CurrentDb
    For Each fld In .TableDefs(strTableName).Fields
      If (StrComp(strFieldName, fld.Name, vbTextCompare) = 0) Then
          FieldExists = True
          Exit Function
      End If
    Next
  End With
  FieldExists = False      ' <-- Actually unnecessary, since a boolean defaults to False anyway

End Function

CurrentDb is a function that returns a new instance of the current database on each invocation

Hi,

Thanks for this.

All appears to work now.

I wasn't aware that it created a new instance on each invocation. With that in mind, I think I'll need to modify some of my other code now

 

[The_Doc_Man]

The "With" construct holds it to a single invocation, or the other approach is similar to this:

DIM dbCur AS DAO.Database
...
SET dbCUR = CurrentDB
...
FOR EACH fld IN dbCUR.TableDefs(... etc

 

[cheekybuddha]

All appears to work now.

Glad it's working for you now!

It might even be marginally more efficient to use the With ... End With to grab the TableDef directly since you are iterating the .Fields collection, rather than re-grab the tableDef on each iteration of the loop:

Function FieldExists(strTableName As String, strFieldName As String) As Boolean

  Dim fld As Field

  With CurrentDb.TableDefs(strTableName)
    For Each fld In .Fields
      If (StrComp(strFieldName, fld.Name, vbTextCompare) = 0) Then
        FieldExists = True
        Exit Function
      End If
    Next
  End With

End Function

 

[MarkK]

You can also do this...

Function FieldExists(table As String, field As String) As Boolean
On Error Resume Next
    FieldExists = CurrentDb.TableDefs(table).Fields(field).Name = field
End Function

I timed them both on a seven column table, looking for the last column in the table, and they are equally fast.

 

[MajP]

None of those explanations explain why, and to tell you the truth I have never fully understood why. But to be safe whenever working with a tabledef always set a variable reference if using current db.

dim db as dao.dataabase
set db = currentdb
db.tabledefs

SYMPTOMS
When you refer to properties and methods belonging to objects created with the CurrentDb function, you may receive the following error message: Object invalid or no longer set.

CAUSE
When you set an object variable, such as a TableDef object, which requires a reference to a database object, your code refers directly to the CurrentDb function instead of referring to a database object variable that you set with the CurrentDb function.

RESOLUTION
Create a database object variable in your code that refers to the CurrentDb function, rather than using the CurrentDb function directly in Set statements to create other objects.

CurrentDB and the "Object invalid or no longer set" error

Misuse of the CurrentDb function is a common cause of the "Object invalid or no longer set" error. Are you using it safely?

nolongerset.com

To understand this you need to under that currentdb is a function and returns a pointer

CurrentDB() vs. dbEngine.Workspaces(0).Databases(0) and an alternative | Experts Exchange

Learn more about CurrentDB() vs. dbEngine.Workspaces(0).Databases(0) and an alternative from the expert community at Experts Exchange

www.experts-exchange.com