How do I change the Allow Zero Length Property

[aziz rasul]

The following link

http://allenbrowne.com/bug-09.html

let's me change the Allow Zero Length property to No. However I can only get to it work if the table is empty. How do I perform the same task with a populated table?

 

[DCrake]

Here is a function that you can run to reset the zero length property

Function SetAllowZeroLength ()
    Dim I As Integer, J As Integer
    Dim db As DAO.Database, td As TableDef, fld As Field

    Set db = CurrentDB()
    'The following line prevents the code from stopping if you do not
    'have permissions to modify particular tables, such as system
    'tables.
    On Error Resume Next
    For I = 0 To db.TableDefs.Count - 1
       Set td = db(I)
       For J = 0 To td.Fields.Count - 1
          Set fld = td(J)
          If (fld.Type = DB_TEXT Or fld.Type = DB_MEMO) And Not _
            fld.AllowZeroLength Then
             fld.AllowZeroLength = True
          End If
       Next J
    Next I
    db.Close
End Function

 

[gemma-the-husky]

its not that you can only do it, if the table is empty

its that you can only do it, if every item IN the table is NOT zero length (as otherwise your constraint fails)

open the table, sort A-Z on the field you are interested in, and set the blank items to something suitable. THEN you WILL be able to change the field setting.

 

[aziz rasul]

The table initially comes as a result of a make-table query. All the text fields have their AllowZeroLength properties set to Yes. I then add a couple of fields to the table using ADD COLUMN i.e.

CurrentDb.Execute "ALTER TABLE " & strTableName & " ADD COLUMN POD TEXT(10);"

I have populated the column with an update query to populate it as you suggested Dave. When I try:-

Public Sub ChangeSpecificTableFieldPropertyAllowZeroLength(strTableName As String, strFieldname As String)

    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim prp As DAO.Property
    Const conPropName = "AllowZeroLength"
    Const conPropValue = False
    
    For Each tdf In CurrentDb.TableDefs
        If (tdf.Attributes And dbSystemObject) = 0 Then
            If tdf.Name = strTableName Then
                For Each fld In tdf.Fields
                    If fld.Properties(conPropName) Then
                        If fld.Name = strFieldname Then
                            fld.Properties(conPropName) = conPropValue
                        End If
                    End If
                Next
            End If
        End If
    Next
        
    Set prp = Nothing
    Set fld = Nothing
    Set tdf = Nothing

End Sub

it still doesn't work?

 

[gemma-the-husky]

try changing the field manually in the table definition -

if there are any records in the table with null or zero length, then you will not be able to change the constraint. otherwise you will

therefore if you can do it manually, but the code doesnt work, then there is something wrong with the code surely.

 

[aziz rasul]

As you said it must be the code as I can change it manually. It does come up with the integrity rules blah blah. That may be what's causing the problem with the code?