Need help debugging listing fields.

[Guest]

All,

I am trying to list the fields in a table as the RowSource for a combo box.
Have referred to both VB Help and to Andi Mayer's post on 1/10/05, but I keep
getting an Error 3420, "Object invalid or no longer set." at the beginning of
the "For Each..." loop. Could use some help figuring out why. Here is the
code:

Private Function VerifyInput() as Boolean
Dim GoodTbl As Boolean
Dim GoodFld As Boolean
Dim tbl As DAO.TableDef ;Have tried both of these
Dim fld As DAO.Field ;with and without the "DAO."

GoodTbl = False
GoodFld = False

If Me![ReIdxTbl] > "" And _
Me![ReIdxTbl] <> "Choose table..." Then
GoodTbl = True
Me.ReIdxFld.RowSource = ""
Set tbl = CurrentDb.TableDefs(Me![ReIdxTbl])

;Text Box [ReIdxTbl] contains a valid table name.

For Each fld In tbl.Fields
;Error is reported after trying to process the line above.

If fld.Type <> dbMemo And _
fld.Type <> dbLongBinary Then
Me.ReIdxFld.RowSource = Me.ReIdxFld.RowSource & ";" & fld.Name
End If
Next fld
Set tbl = Nothing
Set fld = Nothing
Me.ReIdxFld.RowSource = Mid(Me.ReIdxFld.RowSource, 2)
....

Running Access 2002. The database's References include "Microsoft DAO 3.6
Object Library".

Any ideas?

Thanks,
BruceS

 

[Jeff Conrad]

in message:

I am trying to list the fields in a table as the RowSource for a combo box.
Have referred to both VB Help and to Andi Mayer's post on 1/10/05, but I keep
getting an Error 3420, "Object invalid or no longer set." at the beginning of
the "For Each..." loop. Could use some help figuring out why. Here is the
code:

Hi Bruce,

Well I'm not 100% clear on everything you are trying to do with this code since
there is obviously more to this code than was posted (an ending If for example).
However, I have been able to piece together a working puzzle without seeing
the box cover to go by. I believe the problem is your object was going out of
scope. Try this revised code:

'*******Start of Revised Code************
Private Function VerifyInput() As Boolean
On Error GoTo ErrorPoint

Dim blnGoodTbl As Boolean
Dim blnGoodFld As Boolean
Dim strTable As String
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

' Set reference to Current Database
Set dbs = CurrentDb()

' Set boolean values to false.
' Note: not really needed unless you are changing them elsewhere
blnGoodTbl = False
blnGoodFld = False

If Me![ReIdxTbl] > "" And Me![ReIdxTbl] <> "Choose table..." Then
blnGoodTbl = True
' Clear the field row source
Me.ReIdxFld.RowSource = ""
' Remember name of the chosen table
strTable = Me.ReIdxTbl
' Set a reference to that table
Set tdf = dbs.TableDefs(strTable)

' Loop through the fields collection for that table
For Each fld In tdf.Fields
If fld.Type <> dbMemo And _
fld.Type <> dbLongBinary Then
' Skip Memo and Long Binary data type fields
' Build up a rowsource for the combo box
Me.ReIdxFld.RowSource = Me.ReIdxFld.RowSource & ";" & fld.Name
End If
Next fld

' Set RowSourceType to Value List if not already
Me.ReIdxFld.RowSourceType = "Value List"
' Strip out preceding ;
Me.ReIdxFld.RowSource = Mid(Me.ReIdxFld.RowSource, 2)

End If

ExitPoint:
' Cleanup code
On Error Resume Next
Set tdf = Nothing
Set fld = Nothing
Set dbs = Nothing
Exit Function

ErrorPoint:
MsgBox "The following error has occurred:" _
& vbNewLine & "Error Number: " & Err.Number _
& vbNewLine & "Error Description: " & Err.Description _
, vbExclamation, "Unexpected Error"
Resume ExitPoint

End Function
'*******End of revised code************

Hope that gets you going,

 

[Guest]

Thanks, Jeff!

The problem was in my use of the "CurrentDb" constant. For some reason,
using "CurrentDb.TableDefs" instead of using "Set dbs = CurrentDb()" and
"dbs.TableDefs" caused the error. That one change made it work.

Bruce

PS: Next time, I'll try to be a little more clear on what I am trying to
accomplish. The Access app I'm updating has a bunch of very wide, very long
tables that really need about 30 different indexes for speed purposes. Due
to limitations of Access, and the resulting file size when a lot of indexes
are present, I have to add and remove indexes needed by the various queries
before running them. This code was part of a form I'm using in the
development process to easily change the indexes manually for speed testing.
Sure beats having to open the tables in design view and do it that way! BS

in message:

I am trying to list the fields in a table as the RowSource for a combo box.
Have referred to both VB Help and to Andi Mayer's post on 1/10/05, but I keep
getting an Error 3420, "Object invalid or no longer set." at the beginning of
the "For Each..." loop. Could use some help figuring out why. Here is the
code:

Hi Bruce,

Well I'm not 100% clear on everything you are trying to do with this code since
there is obviously more to this code than was posted (an ending If for example).
However, I have been able to piece together a working puzzle without seeing
the box cover to go by. I believe the problem is your object was going out of
scope. Try this revised code:

'*******Start of Revised Code************
Private Function VerifyInput() As Boolean
On Error GoTo ErrorPoint

Dim blnGoodTbl As Boolean
Dim blnGoodFld As Boolean
Dim strTable As String
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

' Set reference to Current Database
Set dbs = CurrentDb()

' Set boolean values to false.
' Note: not really needed unless you are changing them elsewhere
blnGoodTbl = False
blnGoodFld = False

If Me![ReIdxTbl] > "" And Me![ReIdxTbl] <> "Choose table..." Then
blnGoodTbl = True
' Clear the field row source
Me.ReIdxFld.RowSource = ""
' Remember name of the chosen table
strTable = Me.ReIdxTbl
' Set a reference to that table
Set tdf = dbs.TableDefs(strTable)

' Loop through the fields collection for that table
For Each fld In tdf.Fields
If fld.Type <> dbMemo And _
fld.Type <> dbLongBinary Then
' Skip Memo and Long Binary data type fields
' Build up a rowsource for the combo box
Me.ReIdxFld.RowSource = Me.ReIdxFld.RowSource & ";" & fld.Name
End If
Next fld

' Set RowSourceType to Value List if not already
Me.ReIdxFld.RowSourceType = "Value List"
' Strip out preceding ;
Me.ReIdxFld.RowSource = Mid(Me.ReIdxFld.RowSource, 2)

End If

ExitPoint:
' Cleanup code
On Error Resume Next
Set tdf = Nothing
Set fld = Nothing
Set dbs = Nothing
Exit Function

ErrorPoint:
MsgBox "The following error has occurred:" _
& vbNewLine & "Error Number: " & Err.Number _
& vbNewLine & "Error Description: " & Err.Description _
, vbExclamation, "Unexpected Error"
Resume ExitPoint

End Function
'*******End of revised code************

Hope that gets you going,

 

[Jeff Conrad]

in message:

Thanks, Jeff!

The problem was in my use of the "CurrentDb" constant. For some reason,
using "CurrentDb.TableDefs" instead of using "Set dbs = CurrentDb()" and
"dbs.TableDefs" caused the error. That one change made it work.

Yep, without setting dbs = CurrentDb() it immediately falls out of scope
in the code that you had.

You're welcome for the help.
Good luck with your project.

 

[Tim Ferguson]

The Access app I'm updating has a bunch of very wide, very long
tables that really need about 30 different indexes for speed purposes.
Due to limitations of Access,

With respect, I don't think it's the limitations of Access that you are
hitting here so much as the limitations of the original design.

Thirty fields is reasonably uncommon in a well designed table (although, I
grant you, certainly not impossible): there is just no way that one table
could sensibly need thirty indexes.

B Wishes


Tim F