Setting TableDef properties 2

[Shake412]

Can anyone tell me how I programmatically set the the 'Format' and 'Decimal Places' properties when trying to create the TableDef object.

I can creat the object then set the other properties using the fields collect, e.g. tdf.Fields(0).DefaultValue, but cannot see any option for setting the format or decimal places.

Using Access 97

Thanks for any assistance

 

[BillPower]

I think the following should help. Why these topics 1-4 are not readily available in Help, I just don't know. Anyway this shows you how to:

1. Create an AutoNumber Field
2. Create a Hyperlink Field
3. Add a Primary Key and Index
4. Format a Field

Dim dbs As Database
Dim strTableName a String
Dim fld As Field, fld1 As Field, fld2 As Field, fld3 As Field
Dim prop1 As Property, prop2 As Property, prop3 As Property
Dim idx As Index, idxFld As Field
Dim tdf As TableDef, tdf2 As TableDef
On Error GoTo ErrCreateTable

strTableName = "MyFormattedTable"
Set dbs = CurrentDb()

'********************Create the Table and Add Fields****************************

Set tdf = dbs.CreateTableDef(strTableName) 'Create a New Table
Set fld = tdf.CreateField("AnAutoNumberField", dbLong) 'Create AutoNumber Field
fld.Attributes = dbAutoIncrField 'then Set it's Attributes to AutoNumber
tdf.Fields.Append fld

With tdf
.Fields.Append .CreateField("AStringField", dbText, 75)
.Fields.Append .CreateField("ANumberField", dbDouble)
.Fields.Append .CreateField("ADateTimeField", dbDate)
End With

Set fld = tdf.CreateField("AHyperlinkField", dbMemo) 'Create Hyperlink Field
fld.Attributes = dbHyperlinkField 'then Set it's Attributes to Hyperlink
tdf.Fields.Append fld
dbs.TableDefs.Append tdf

'********************Set Primary Key and Indexes****************************

Set tdf2 = dbs.TableDefs(strTableName)
Set idx = tdf2.CreateIndex("PrimaryKey1&quot 'Index ANumberField, Primary Yes, No Duplicates
With idx
.Required = True
.Primary = True
.Unique = True
End With
Set idxFld = idx.CreateField("ANumberField&quot
idx.Fields.Append idxFld
tdf2.Indexes.Append idx

Set idx = tdf2.CreateIndex("PrimaryKey2&quot 'Index AStringField, Duplicates OK
Set idxFld = idx.CreateField("AStringField&quot
idx.Fields.Append idxFld
tdf2.Indexes.Append idx

'*******************************Format Fields*******************************

Set fld1 = tdf2.Fields("ANumberField&quot 'Do some Formatting on ANumberField
Set prop1 = fld1.CreateProperty("Format", dbText, "Fixed&quot
fld1.Properties.Append prop1
Set prop1 = fld1.CreateProperty("DecimalPlaces", dbByte, 2)
With fld1
.Properties.Append prop1
.DefaultValue = 0
.ValidationRule = "Is Not Null"
.ValidationText = "You must enter a Value"
.Required = True
End With

Set fld2 = tdf2.Fields("ADateTimeField&quot 'Do some Formatting on ADateTimeField
Set prop2 = fld2.CreateProperty("Format", dbText, "Medium Time&quot
fld2.Properties.Append prop2

Set fld3 = tdf2.Fields("AStringField&quot 'Do some Formatting on AStringField
Set prop3 = fld3.CreateProperty("InputMask", dbText, "(###) ###-####&quot
fld3.Properties.Append prop3
Set prop3 = fld3.CreateProperty("Caption", dbText, "MyTelephoneNo&quot
fld3.Properties.Append prop3
fld3.AllowZeroLength = True
dbs.Close

ErrCreateTableExit:
Exit Sub
ErrCreateTable:
If Err <> 91 Then
MsgBox Err.Description & &quot; ; Error No: &quot; & Err.Number
Resume ErrCreateTableExit
End If

Paste the above into the On Click event of a button on a Form to test it.

 

[Shake412]

Thanks for the response. I've inserted you code, but I keep getting a run time error 91 (Object variable of With block variable not set) on the first line of the following code.

Set prp = fld.CreateProperty(&quot;Format&quot;, dbText, &quot;Fixed&quot
fld.Properties.Append prp
Set prp = fld.CreateProperty(&quot;Format&quot;, dbByte, 2)
fld.Properties.Append prp

Do you not get this error? Any ideas why I do?

Thanks again.

 

[Shake412]

Don't worry I've spotted my error and your code works fine now. Thanks again for your help.

 

[Shake412]

Sorry I lied. When trying to append the property to the field object on line...

fld.Properties.Append prp

I get a run time error 3219 (Invalid Operation)

 

[BillPower]

If you've copied the above exactly as written there should be no problems, other than &quot;as&quot; came out as &quot;a&quot; in one of the declarations. You shouldn't be getting Error 91 because I trapped for that. I think Error 3219 happens when you specify a Format or Property that a Field doesn't support. Try starting afresh and turn that a into an as. Let me know how you get on.

Bill

 

[Shake412]

I've got it working now. The problem was that I was using only one tabledef object.

Thank you once again for you help.

 

[bitech]

Hello:

When I tried the code, I received a Type mismatch error. Does anyone know how to avoid that. The error comes on the

Set prop3 = fld3.CreateProperty(&quot;InputMask&quot;, dbText, &quot;(###) ###-####&quot