Error accessing fields in a tabledef

[sphinney]

Before I state my problem I want to say thanks to anyone who reads this post. I appreciate your time.

I have a simple form in an Access 2007 database. The form contains two combo boxes. Combo box 1 contains the names of all the base and linked tables in the database.

When the user selects a table name in combo box 1, I want combo box 2 show the list of fields for that table.

The code I have to populate combo box 2 with field names looks like this:

Private Sub Combo_Box_1_Change()
Dim FLD As String
Dim INDEXtdef As DAO.TableDef
Dim VarItm As Variant
 
    'Initiate the FLD string variable
    FLD = ""
 
    Set INDEXtdef = CurrentDb.TableDefs(Combo_Box_1.Value)
    For Each VarItm In INDEXtdef.Fields
 
        'Build the string variable
        FLD = FLD & VarItm.Name & "; "
 
    Next
 
    'Trim off the extra semi-colon and space
    FLD = Left(FLD, Len(FLD) - 2)
 
    Combo_Box_2.RowSource = FLD
 
End Sub

Problem is I get an run-time error 3420 ("Object invalid or no longer set"). When I Debug, the offending line is:

For Each VarItm In INDEXtdef.Fields

Can anyone point out what I'm doing wrong? I've looked at the code for too long and I can't see what's wrong with it.

Thanks,
sphinney

[FishVal]

Hello, sphiney.

You've already asked a similar question.
Me answer is still the same. From some reasons Access doesn't allow to hold a reference to DAO.Tabledef, DAO.Field etc objects unless they has been just created and not yet added to a correspondent collection.

You should call Tabledefs property each time you want to call nested properties/methods.

Kind regards,
Fish

[ADezii]

Just a slight change in syntax will do the trick:

Private Sub Combo_Box_1_AfterUpdate()
Dim MyDB As DAO.Database
Dim INDEXtdef As DAO.TableDef
Dim fld As DAO.Field
Dim strFldName As String
 
'Must contain a Table Name
If IsNull(Me![Combo_Box_1]) Then Exit Sub
 
Set MyDB = CurrentDb()
 
'Initiate the strFldName String Variable
strFldName = ""
 
Set INDEXtdef = MyDB.TableDefs(Me![Combo_Box_1])
 
For Each fld In INDEXtdef.Fields
  'Build the string variable
  strFldName = strFldName & fld.Name & ";"
Next
 
'Trim off the extra semi-colon
strFldName = Left(strFldName, Len(strFldName) - 1)
 
'Define certain characteristics of the 2nd Combo Box
With Me![Combo_Box_2]
  .ColumnCount = 1
  .RowSourceType = "Value List"
  .RowSource = strFieldName
End With
 
Combo_Box_2.RowSource = strFldName
End Sub

[sphinney]

Just a slight change in syntax will do the trick:

Private Sub Combo_Box_1_AfterUpdate()
Dim MyDB As DAO.Database
Dim INDEXtdef As DAO.TableDef
Dim fld As DAO.Field
Dim strFldName As String
 
'Must contain a Table Name
If IsNull(Me![Combo_Box_1]) Then Exit Sub
 
Set MyDB = CurrentDb()
 
'Initiate the strFldName String Variable
strFldName = ""
 
Set INDEXtdef = MyDB.TableDefs(Me![Combo_Box_1])
 
For Each fld In INDEXtdef.Fields
  'Build the string variable
  strFldName = strFldName & fld.Name & ";"
Next
 
'Trim off the extra semi-colon
strFldName = Left(strFldName, Len(strFldName) - 1)
 
'Define certain characteristics of the 2nd Combo Box
With Me![Combo_Box_2]
  .ColumnCount = 1
  .RowSourceType = "Value List"
  .RowSource = strFieldName
End With
 
Combo_Box_2.RowSource = strFldName
End Sub

ADezii - That was it! Apparently it must have been setting a reference to the CurrentDb that did the trick. Thanks!

FishVal - My appologies. I didn't even remember posting a similar question in the past. I will definitely be more thorough in the future in searching previous posts to this forum. Thanks for taking the time to read my post and responding.

Sincerely,
sphinney

[FishVal]

Oops.

ADezii, do you have any thoughts about that behavior of Application.CurrentDb method?

BTW,
DBEngine.Workspaces(0).Databases(0).TableDefs(...)
or omitting defaults
DBEngine(0)(0).Tabledefs(...)

gives stable reference to Tabledef object too.

Regards,
Fish

[ADezii]

Oops.

ADezii, do you have any thoughts about that behavior of Application.CurrentDb method?

BTW,
DBEngine.Workspaces(0).Databases(0).TableDefs(...)
or omitting defaults
DBEngine(0)(0).Tabledefs(...)

gives stable reference to Tabledef object too.

Regards,
Fish

ADezii, do you have any thoughts about that behavior of Application.CurrentDb method?

Hello FishVal, actually I've been aware of this peculiar behavior for some time and have compensated for it on many occasions. If you have a minute, read the following Thread, specifically Posts 5 thru 7, in which this same problem was discussed.
http://bytes.com/forum/thread834052-MyDB.html

P.S. - Nice Tip on DBEngine(0)(0).Tabledefs(...)