Function returning TableDef

[LadyMarmalade]

Hi guys,
I have a function which is intended to return a TableDef

Function GetTable(ByVal TableCode As String) As TableDef
Dim myTable             As DAO.TableDef
Dim myDB                As DAO.Database

Set myDB = CurrentDb

For Each myTable In myDB.TableDefs
    If myTable.Name Like TableCode & "*" Then
        Set GetTable = myTable
        Set myDB = Nothing
        Exit Function
    End If
Next myTable

NoneFound:
MsgBox ("No table with code " & TableCode & " found.")
Set myDB = Nothing
End Function

I have two issues:

1. Run-time error '3420'
   It gets as far as finding the table before returning "Object invalid or no longer set"
   I have tried replacing Set GetTable = myTable to Set GetTable = myDB.TableDefs(myTable.Name) - no luck.
2. Returning an error if unfound
   Is there a better way to do this? If I later do something like GetTable("C5").Name I expect it'd not be happy with GetTable not being defined.

Thank you!
Lady Marmalade

 

[gemma-the-husky]

not sure but
a) maybe you do not need the "set"
b) if dao is significant, then maybe you need dao.tabledef in the function return type
c) maybe the "like" is the problem

try instead of "like"

if left(tdf.name,len(tablecode))=tablecode then
etc


it certainly IS NOT the alternative solution you tried, as that will return a string, not a table def

 

[gemma-the-husky]

I just couldn't get it to work.

I think it's that gettable returns a tabledef.
However, currentdb.tabledefs ("sometable") fails in certain circumstance because of the currentdb object, and not the table def object.

The best I could do was return a string from the function, and use that - see below

Option Compare Database
Option Explicit
 
Dim thetable As TableDef
Dim thedb As Database
 
Function GetTable(ByVal TableCode As String) As String
Dim myTable             As TableDef
Dim myDB                As Database

 Set myDB = CurrentDb
Set thetable = Nothing

 On Error GoTo fail
For Each myTable In myDB.TableDefs
    If Left(myTable.Name, Len(TableCode)) = TableCode Then
        MsgBox "assigning " & myTable.Name
        GetTable = myTable.Name
        Set myDB = Nothing
        Exit Function
    End If
Next myTable

 NoneFound:
MsgBox ("No table with code " & TableCode & " found.")
Set myDB = Nothing
GetTable = ""  
Exit Function

 fail:
    MsgBox "Err " & Err & "   Desc: " & Err.Description
End Function
 
Sub test()
Dim result As String
     result = GetTable("XXX")
    If result > "" Then
        Set thedb = CurrentDb
        Set thetable = thedb.TableDefs(result)
        MsgBox thetable.Name
    End If
 
End Sub

 

[LadyMarmalade]

not sure but
a) maybe you do not need the "set"
b) if dao is significant, then maybe you need dao.tabledef in the function return type
c) maybe the "like" is the problem

try instead of "like"

if left(tdf.name,len(tablecode))=tablecode then
etc


it certainly IS NOT the alternative solution you tried, as that will return a string, not a table def

Thank you for the reply! It was none of those - something to do with relating databases to tables. I found the solution (sort of) here.