Create Table statement for fields with validation

[Sha S]

Hello,

I have this statement:

Set dbs = CurrentDb

Set tbl = dbs.CreateTableDef("usrtmptbl_linkedobjs")

With tbl
.Fields.Append .CreateField("uID", dbLong)
.Fields("uID").Attributes = dbAutoIncrField
.Fields.Append .CreateField("build_date", dbDate)
.Fields("build_date").DefaultValue = "Now()"
.Fields.Append .CreateField("uNAME", dbText)
.Fields.Append .CreateField("Local_Flag", dbInteger)
End With

dbs.TableDefs.Append tbl


In the above table, for field "Local_Flag", the values must be one of (0, 1
or Null ).

I would appreciate if someone could suggest how to include that in the above
code.

Thank You,

sHa.

 

[JimBurke via AccessMonster.com]

Can this be a true/false value, or is there a reason you need the 0 and 1? If
it's just a true/false value then make it a boolean field and you're all set.
I'm guessing there's some reason you want the integer values. I'm assuming
you have a data entry form somewhere where the values for this table are
entered. Personally, I wouldn't create any rules in the table definition. On
the data entry form I would make the field a combo box rather than a text
field, set the RowSource Type to 'Value list', set the Row Source to "0;1"
and set Limit To List to True. This would allow the field to remain null or
would allow the user to select a 0 or a 1 and nothing else. Hope this helps.
I always set my restrictions for field values on my forms rather than in the
table defs - don't know if this is 'accepted practice' or not.

 

[Marshall Barton]

Hello,

I have this statement:

Set dbs = CurrentDb

Set tbl = dbs.CreateTableDef("usrtmptbl_linkedobjs")

With tbl
.Fields.Append .CreateField("uID", dbLong)
.Fields("uID").Attributes = dbAutoIncrField
.Fields.Append .CreateField("build_date", dbDate)
.Fields("build_date").DefaultValue = "Now()"
.Fields.Append .CreateField("uNAME", dbText)
.Fields.Append .CreateField("Local_Flag", dbInteger)
End With

dbs.TableDefs.Append tbl


In the above table, for field "Local_Flag", the values must be one of (0, 1
or Null ).

I think all you need is to replace the line:
.Fields.Append .CreateField("Local_Flag", dbInteger)
with something like:
Set fld = .CreateField("Local_Flag", dbInteger)
fld.ValidationRule = "Is Null Or =0 Or =1"
.Fields.Append fld

 

[Sha S]

Thanks for the reply, JimBurke.

I was thinking of some sort of a validation rule, and how to code it into
the create table routine. Since it is a temp table, figured no harm in
doing it on the table vs on the form. If there is a way to add a validation
rule through the code, any help is appreciated.

Thanks,

Sha.