Default properties for new tables

[Soundsmith]

I have a set of standard criteria I'd like to set for new tables as they are being created (Access 2000):

Text fields-AllowZeroLength True
Numbers - Fixed, 0 decimals
Currency - Format field set to Fixed, decimals=2
Dates - Short Date, mask set as 99/99/0000;0;_ (Short Date)

I don't see any way within access itself, so I tried to create a VBA routine, first to display property names, the to set values

Public Function setProps(tblStr As String) As Boolean
' set default properties for theTbl as follows:
'    str-allowZeroLen-true, num-long, curr-fixed,2, date=short
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim Fld As Field
Dim i As Long

Set db = CurrentDb
Set tbl = db.TableDefs(tblStr)

For Each Fld In tbl.Fields
    Debug.Print Fld.name, Fld.Type 'this gave me the Case numbers
    Select Case Fld.Type 
    Case 10 'text
        Fld.AllowZeroLength = True  ' THIS WORKS
        
    Case 5   ' number type, I want the property names and values
        Debug.Print Fld.name
' this was run once to create list of property names
        For i = 0 To Fld.Properties.count - 1
            Debug.Print , Fld.Properties(i).name, Fld.Properties(i).Type
        Next i
' The above gave me "Format" as property name
' but when I try to set it:
        Fld.Properties("Format") = 10 
' I get error 3270, Property not found
    Case 8   ' Date-time field
        Debug.Print Fld.name
' as above, this was run once
        For i = 0 To Fld.Properties.count - 1
            Debug.Print , Fld.Properties(i).name, Fld.Properties(i).Type
        Next i
        Fld.Properties("Format") = 10 ' Also Property not found
        Fld.Properties("InputMask") = 10 ' Also Property not found
    Case Else
    End Select
Next Fld
Set tbl = Nothing
Set db = Nothing

End Function

Although the for next routines displayed property names, VBA does not recognise them when requested to alter.

How can I do this without having to manually set the properties every time? fld.AllowZeroLength works, the others do not.
David 'Dasher' Kempton
The Soundsmith

http://www.thesoundsmith.com

 

[jebry]

Hi!

I don't know if it will give you everything you want but, while in the database window, go to Tools - Options and click on the Tables/Queries tab. You will find some default values there.

hth Jeff Bridgham
bridgham@purdue.edu