VBA not recogzniing field type in column ACCESS 2010

I am trying to get rid of a lot of combobox lookup fields in tables created by six other developers on this database.  thelookup fields are on the forms so they ar enot needed in the tables as well.  Below is the code I am trying to create to loop through and if the datatype is Text for the column, then check the displaycontrol and if it is set to combobox, change it to text.  But when it this the .fields(I).type = "Text" errors out with a type mismatch error.

Public Sub ChangeDisplayControls()
On Error GoTo ErrorHandler
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim strTableName As String
    Dim rst As DAO.Recordset
    Dim i As Integer
   
Set dbs = CurrentDb

For Each tdf In dbs.TableDefs
    If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*") Then
        strTableName = tdf.Name & 1
        Set rst = dbs.OpenRecordset(strTableName)
        With rst
        For i = 1 To .Fields.Count
            If .Fields(i).Type = "Text" Then  'Errors out here
                If .Fields(i).Properties("DisplayControl") = "ComboBox" Then
                    .Fields(i).Properties("DisplayControl") = "Text"
                End If
            End If
        Next i
        End With
    End If
Next tdf
Set tdf = Nothing
Set dbs = Nothing
   
Exit_Errorhandler:
    Exit Sub
ErrorHandler:
    MsgBox "Error Number: " & Err.Number & " Description: " & Err.Description
    Resume Exit_Errorhandler
End Sub
Sandra SmithRetiredAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
try

     If .Fields(i).Type = dbText  Then  'Errors out here

or

     If .Fields(i).Type = 10 Then  'Errors out here
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sandra SmithRetiredAuthor Commented:
Found it was actually returning the data itself in the column, not looping through the columns.  I have posted another question to address this first, than perhaps when that is solved, will try your suggestion.
0
Sandra SmithRetiredAuthor Commented:
ray, using your suggestion about printing out, discovered datatype is string.  So now, the code does get to the displaycontrol area, but will not determine what the DisplayControl type is.  

Public Sub ChangeDisplayControls()
On Error GoTo ErrorHandler
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim strTableName As String
    Dim rst As DAO.Recordset
    Dim i As Integer
   
Set dbs = CurrentDb

For Each tdf In dbs.TableDefs
    If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*") Then
        strTableName = tdf.Name & 1
        Set rst = dbs.OpenRecordset(strTableName)
        With rst
        For i = 1 To .Fields.Count
            Debug.Print "Field Name: " & .Fields(i).Name
            Debug.Print "TypeName: " & TypeName(.Fields(i).Name)
            If TypeName(.Fields(i).Name) = "String" Then

                If .Fields(i).Properties("DisplayControl") = "ComboBox" Then
                    .Fields(i).Properties("DisplayControl") = "Text"
                End If
            End If
        Next i
        End With
    End If
Next tdf
Set tdf = Nothing
Set dbs = Nothing
   
Exit_Errorhandler:
    Exit Sub
ErrorHandler:
    MsgBox "Error Number: " & Err.Number & " Description: " & Err.Description
    Resume Exit_Errorhandler
End Sub
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PatHartmanCommented:
The process isn't as straightforward as this.  Although I laud your effort to stamp out all table level lookups, you are going to have to modify forms/reports/queries otherwise, those might end up showing the ID field rather than the lookup text field.
0
Rey Obrero (Capricorn1)Commented:
try

                If .Fields(i).Properties("DisplayControl") = acComboBox Then
                    .Fields(i).Properties("DisplayControl") = acTextBox
                End If
0
Sandra SmithRetiredAuthor Commented:
Did a rewrite with you suggestion and this works.  As for the forms, since the lookup are there, there is no problem.

Public Sub ChangeDisplayControls()
On Error GoTo ErrorHandler
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim strTableName As String
    Dim rst As DAO.Recordset
    Dim i As Integer
   
Set dbs = CurrentDb

For Each tdf In dbs.TableDefs
    If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*") Then
        strTableName = tdf.Name & 1
        Set rst = dbs.OpenRecordset(strTableName)
        With rst
            For i = 1 To .Fields.Count
                Debug.Print "Field Name: " & .Fields(i).Name
                Debug.Print "TypeName: " & TypeName(.Fields(i).Name)
                   If .Fields(i).Type = dbText Then    'Only modify Text fields
                        .Fields(i).Properties("DisplayControl") = acTextBox
                   End If
             Next i
        End With
     End If
Next tdf

Set tdf = Nothing
Set dbs = Nothing
   
Exit_Errorhandler:
    Exit Sub
ErrorHandler:
    MsgBox "Error Number: " & Err.Number & " Description: " & Err.Description
    Resume Exit_Errorhandler
End Sub
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.