I'm trying to create a field

  • Thread starter Thread starter Fia
  • Start date Start date
F

Fia

Hi
I'am trying to create a field from a field I already have in a table. The
field I have is called Date and has the type dbDate and when you look at the
field in designview it has the property Format put to "short date".
But when I'm using the code below and looks at the new field called Date1 in
designview it doesn't have anything put in the property Format. I hope the
property is called the same in the english version. I'm using Access 97 in
Swedish.

Dim tblDef As DAO.TableDef
Dim DB As DAO.Database
Dim prp As DAO.Property
Dim i As Integer
Dim f, f1 As Field
Set DB = OpenDatabase(CurrentDb.Name)
Set tblDef = DB.TableDefs("Table")
set f=tbldef.Fields("Date")
with f
Set f1 = tblDef.CreateField("Date1", f.Type, f.Size)
f1.Attributes = f.Attributes
i = 0
For Each prp In f.Properties
On Error Resume Next
If prp.Name <> "Name" And prp.Name <> "Type" And
prp.Name <> "Attributes" And prp.Name <> "Size" Then
f1.Properties(i) = prp.Value
End If
On Error GoTo 0
i = i + 1
Next
tblDef.Fields.Append f1
end with

Thank's

Fia
 
Hi Fia, there are several properties like this such as InputMask that arn't
really properties. They don't describe the field, instead they allow the
user to default a value for all the forms / reports that use the field.

So, basically you won't find it as a property.
 
Fia said:
Hi
I'am trying to create a field from a field I already have in a table.
The field I have is called Date and has the type dbDate and when you
look at the field in designview it has the property Format put to
"short date".
But when I'm using the code below and looks at the new field called
Date1 in designview it doesn't have anything put in the property
Format. I hope the property is called the same in the english
version. I'm using Access 97 in Swedish.

Dim tblDef As DAO.TableDef
Dim DB As DAO.Database
Dim prp As DAO.Property
Dim i As Integer
Dim f, f1 As Field
Set DB = OpenDatabase(CurrentDb.Name)
Set tblDef = DB.TableDefs("Table")
set f=tbldef.Fields("Date")
with f
Set f1 = tblDef.CreateField("Date1", f.Type, f.Size)
f1.Attributes = f.Attributes
i = 0
For Each prp In f.Properties
On Error Resume Next
If prp.Name <> "Name" And prp.Name <>
"Type" And prp.Name <> "Attributes" And prp.Name <> "Size" Then
f1.Properties(i) = prp.Value
End If
On Error GoTo 0
i = i + 1
Next
tblDef.Fields.Append f1
end with

Thank's

Fia
Click to expand...

Fields in Access tables have two kinds of properties -- "Jet"
properties, which are inherent in the DAO field object, and "extended"
or "Access" properties, which must be explictly created. Access creates
them when you are in table design view and fill in something on the
property sheet for one of these properties, such as "Format" or "Input
Mask", but they don't exist until they are created.

If you want to copy a field and all its properties, you'll have to
create those properties yourself and add them to the copied field's
Properties collection. The code might look like this:

Dim DB As DAO.Database
Dim tblDef As DAO.TableDef
Dim f As DAO.Field
Dim f1 As DAO.Field
Dim prp As DAO.Property
Dim prp1 As DAO.Property

Set DB = CurrentDb
Set tblDef = DB.TableDefs("tblTest")
Set f = tblDef.Fields("TestField")

With f
Set f1 = tblDef.CreateField("TestFieldCopy", f.Type, f.Size)
f1.Attributes = f.Attributes
tblDef.Fields.Append f1

On Error Resume Next ' use inline error-handling

For Each prp In f.Properties
Select Case prp.Name
Case "Name", "Type", "Attributes", "Size", "Value", _
"CollatingOrder", "DataUpdatable", _
"SourceField", "SourceTable"
' ignore these, since we can't set them.
Case "OrdinalPosition"
' set this only if we care where the field goes.
Case Else
Err.Clear
f1.Properties(prp.Name) = prp.Value
If Err.Number <> 0 Then
Set prp1 = f1.CreateProperty( _
prp.Name, prp.Type, prp.Value)
f1.Properties.Append prp1
Set prp1 = Nothing
End If
End Select
Next

On Error GoTo 0 ' or your error-handler

End With

Note that standard error-handling is still deisabled in the above code,
because there are some field properties that you can't set for fields in
TableDefs. Also note that the Index property of a field, as shown in
the field's property sheet, doesn't really exist -- it's an abstraction
of the TableDef's Indexes collection.
 
Back
Top