Setting field properties via code

[John]

Hi

How can I set the following properties of all Boolean (Yes/No) fields of a
table via code;

Default = 0
Required = Yes (I am assuming this amounts to Allow Nulls = False)

Thanks

Regards

 

[Dirk Goldgar]

Hi

How can I set the following properties of all Boolean (Yes/No) fields of a
table via code;

Default = 0
Required = Yes (I am assuming this amounts to Allow Nulls = False)

If your database is Jet/ADE (the default Access database type), this is
unnecessary, because in that format Boolean fields cannot be Null and always
default to False (0).

Do you still want to do it?

 

[John]

Yes please, as I am upsizing back end to sql server.

Thanks

Regards

 

[Dirk Goldgar]

Yes please, as I am upsizing back end to sql server.

Here's a procedure that would be passed the name of the table and apply your
requested properties to every boolean field:

'----- start of code -----
Sub SetBooleanProperties(TableName As String)

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb
Set tdf = db.TableDefs(TableName)

For Each fld In tdf.Fields
If fld.Type = dbBoolean Then
fld.Required = True
fld.DefaultValue = 0
End If
Next fld

Set fld = Nothing
Set tdf = Nothing
Set db = Nothing

End Sub
'----- end of code -----

Error-handling is left up to you.

As written, this code would need to be run in the back-end. However, if you
want to run it in the front-end, it would be easy enough to open the db
object variable on the back-end database:

Set db = DBEngine.OpenDatabase("C:\Your\Path\To\YourBackEnd.mdb")

' ... rest of code ...

Set fld = Nothing
Set tdf = Nothing
db.Close
Set db = Nothing

 

[Paul Shapiro]

A small addition- I think the DefaultValue property is a string, so the
setting would be:
fld.DefaultValue = "0"

And that might be one of the properties that doesn't exist until you set a
value in the design-mode UI. If you get an error that it doesn't exist, you
first create the property, set it's value and append it to the field:
fld.Properties.Append fld.CreateProperty("DefaultValue, dbText, "0")

 

[Dirk Goldgar]

A small addition- I think the DefaultValue property is a string, so the
setting would be:
fld.DefaultValue = "0"

You're right, but assigning 0 to a string property results in a conversion
to "0". However, it was just laziness that I didn't type the string literal
instead of the numeric one.

And that might be one of the properties that doesn't exist until you set a
value in the design-mode UI.

It isn't. I did test the code to make sure.