By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,946 Members | 712 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,946 IT Pros & Developers. It's quick & easy.

Error accessing fields in a tabledef

P: 69
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo_Box_1_Change()
  2. Dim FLD As String
  3. Dim INDEXtdef As DAO.TableDef
  4. Dim VarItm As Variant
  5.  
  6.     'Initiate the FLD string variable
  7.     FLD = ""
  8.  
  9.     Set INDEXtdef = CurrentDb.TableDefs(Combo_Box_1.Value)
  10.     For Each VarItm In INDEXtdef.Fields
  11.  
  12.         'Build the string variable
  13.         FLD = FLD & VarItm.Name & "; "
  14.  
  15.     Next
  16.  
  17.     'Trim off the extra semi-colon and space
  18.     FLD = Left(FLD, Len(FLD) - 2)
  19.  
  20.     Combo_Box_2.RowSource = FLD
  21.  
  22. End Sub
Problem is I get an run-time error 3420 ("Object invalid or no longer set"). When I Debug, the offending line is:

Expand|Select|Wrap|Line Numbers
  1. 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
Sep 17 '08 #1
Share this Question
Share on Google+
5 Replies


FishVal
Expert 2.5K+
P: 2,653
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
Sep 17 '08 #2

ADezii
Expert 5K+
P: 8,619
Just a slight change in syntax will do the trick:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo_Box_1_AfterUpdate()
  2. Dim MyDB As DAO.Database
  3. Dim INDEXtdef As DAO.TableDef
  4. Dim fld As DAO.Field
  5. Dim strFldName As String
  6.  
  7. 'Must contain a Table Name
  8. If IsNull(Me![Combo_Box_1]) Then Exit Sub
  9.  
  10. Set MyDB = CurrentDb()
  11.  
  12. 'Initiate the strFldName String Variable
  13. strFldName = ""
  14.  
  15. Set INDEXtdef = MyDB.TableDefs(Me![Combo_Box_1])
  16.  
  17. For Each fld In INDEXtdef.Fields
  18.   'Build the string variable
  19.   strFldName = strFldName & fld.Name & ";"
  20. Next
  21.  
  22. 'Trim off the extra semi-colon
  23. strFldName = Left(strFldName, Len(strFldName) - 1)
  24.  
  25. 'Define certain characteristics of the 2nd Combo Box
  26. With Me![Combo_Box_2]
  27.   .ColumnCount = 1
  28.   .RowSourceType = "Value List"
  29.   .RowSource = strFieldName
  30. End With
  31.  
  32. Combo_Box_2.RowSource = strFldName
  33. End Sub
Sep 18 '08 #3

P: 69
Just a slight change in syntax will do the trick:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo_Box_1_AfterUpdate()
  2. Dim MyDB As DAO.Database
  3. Dim INDEXtdef As DAO.TableDef
  4. Dim fld As DAO.Field
  5. Dim strFldName As String
  6.  
  7. 'Must contain a Table Name
  8. If IsNull(Me![Combo_Box_1]) Then Exit Sub
  9.  
  10. Set MyDB = CurrentDb()
  11.  
  12. 'Initiate the strFldName String Variable
  13. strFldName = ""
  14.  
  15. Set INDEXtdef = MyDB.TableDefs(Me![Combo_Box_1])
  16.  
  17. For Each fld In INDEXtdef.Fields
  18.   'Build the string variable
  19.   strFldName = strFldName & fld.Name & ";"
  20. Next
  21.  
  22. 'Trim off the extra semi-colon
  23. strFldName = Left(strFldName, Len(strFldName) - 1)
  24.  
  25. 'Define certain characteristics of the 2nd Combo Box
  26. With Me![Combo_Box_2]
  27.   .ColumnCount = 1
  28.   .RowSourceType = "Value List"
  29.   .RowSource = strFieldName
  30. End With
  31.  
  32. Combo_Box_2.RowSource = strFldName
  33. 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
Sep 18 '08 #4

FishVal
Expert 2.5K+
P: 2,653
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
Sep 18 '08 #5

ADezii
Expert 5K+
P: 8,619
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(...)
Sep 18 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.