Format - Fixed property (1 Viewer)

aziz rasul · Apr 28, 2008

I have the following code: -

Code:

CurrentDb.Execute "ALTER TABLE tblTemp ALTER COLUMN MedianValue Double;"

How do I add to this so that the Format property is set to 'Fixed'?

DJkarl · Apr 28, 2008

I don't think you can set the format property of a table in Access via SQL. If you are already running VBA code you should be able to set the properties using a tabledef object.

aziz rasul · Apr 28, 2008

OK I'll look into that.

aziz rasul · Apr 28, 2008

I've written the following code to resolve the issue, but I get error 3420 i.e. 'Object invalid or no longer set' on the line indicated.

Code:

Public Sub ChangeTableFieldFormat(strTable As String, strFieldName As String)

    Dim tdf As DAO.TableDef
    Dim fld As Field
    Dim prp As Property
    
    Set tdf = CurrentDb.TableDefs(strTable)
    Set fld = tdf.Fields(strFieldName) 'ERRORS HERE
    Set prp = fld.CreateProperty("Format", dbText, "Fixed")
    
    fld.Properties.Append prp
    
    Set tdf = Nothing
    Set fld = Nothing
    Set prp = Nothing
    
End Sub

aziz rasul · Apr 28, 2008

It's OK folks, I got the solution from another source. The code should have been

Code:

Public Sub ChangeTableFieldFormat(strTable As String, strFieldName As String)

    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim prp As DAO.Property
    
    With CurrentDb
        Set tdf = .TableDefs(strTable)
        Set fld = tdf.Fields(strFieldName)
        Set prp = fld.CreateProperty("Format", dbText, "Fixed")
        
        fld.Properties.Append prp
        
        Set tdf = Nothing
        Set fld = Nothing
        Set prp = Nothing
    End With
    
End Sub

Format - Fixed property (1 Viewer)

aziz rasul

Active member

DJkarl

Registered User.

aziz rasul

Active member

aziz rasul

Active member

aziz rasul

Active member

Similar threads

Users who are viewing this thread