asked on

How do I programmatically change an existing Text Field's size?

I get an invalid operation in the line "fld.Size = bytFieldSize".
How can I change the code below to work?

Private Function ChangeTextFieldSize(strTableName As String, strFieldName As String, bytFieldSize As Byte)
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field    
    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs(strTableName)
    Set fld = tdf.Fields(strFieldName)
    With tdf
        fld.Size = bytFieldSize
    End With
    Set tdf = Nothing
    Set fld = Nothing
    Set dbs = Nothing
End Function

Open in new window

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rey Obrero (Capricorn1)

just take note that if the field contains data bigger than a byte can hold, you will lose some of the data.

Robert Batchelor

ASKER

But if a text field can only be a maximum of 255 characters and if a byte stores numbers from 0 to 255, can I still lose data?

Rey Obrero (Capricorn1)

255 characters >> is the number of characters *not* the value

a byte field can hold only a *maximum* value of 255

Rey Obrero (Capricorn1)

example > 256 is three characters, but a byte field will reject 256 as an entry to the field because it exceeds the max value of 255

Robert Batchelor

ASKER

Thanks, that worked. I used:
CurrentDb.Execute "ALTER TABLE " & strTableName & " ALTER COLUMN " & strFieldName & " TEXT (" & bytFieldSize & ")" The entire code is in the snippet below.

Private Sub cmdChangeTextFieldSize_Click()
    z = ChangeTextFieldSize("tblInvoice", "strAddressFormat", 25)
End Sub
 
Private Function ChangeTextFieldSize(strTableName As String, strFieldName As String, bytFieldSize As Byte)
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    
    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs(strTableName)
    Set fld = tdf.Fields(strFieldName)
 
    CurrentDb.Execute "ALTER TABLE " & strTableName & " ALTER COLUMN " & strFieldName & " TEXT (" & bytFieldSize & ")"
    
    Set tdf = Nothing
    Set fld = Nothing
    Set dbs = Nothing
End Function

Open in new window