AllowZeroLengthString Help

[hamrthroer]

Seriously I know there has to be a simple line of code for this. I want to set the AllowZeroLenthString property for one field on one table to true just long enough to run an update query to clear out the fields for preperation of new assigned numbers.

I don't want to permenently set it to true. could someone please provide an example?

tblStaff.Random is the feild.

All I can find is bloated code examples that set it to true for all tables, etc.

Thanks!

 

[vbaInet]

Can the field not be set manually? If done through code I don't know the implications if a user was accessing that table or even the field for updating/inserting.

Dim db As DAO.Database

Set db = CurrentDb
db.TableDefs("Table_Name").Fields("Field_Name").AllowZeroLength = True

..... Perform Operation

db.TableDefs("Table_Name").Fields("Field_Name").AllowZeroLength = False
set db = nothing

 

[hamrthroer]

Thanks for the reply. I can change it manually. The table is shared between two massive DBs and I was concerned about the effects changing it would have in the other applications. Would that be problematic?

 

[DCrake]

If you are programatically changing the property from the db the affected db will not adopt this rule until it is next opened.

 

[DCrake]

Here is some code to change it in VBA

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]

of course the other way is to add a new field to the table

populate that to suit

then rename the fields, so the new field, gets the old fields name.

==========
and note that you could leave the field as allow zls = true, and just make sure that the programme forces you to have a value.

 

[hamrthroer]

Thanks for all your help! I changed the property in the table and have recieved no errors so far.