Format, Input Mask, Caption

[Tom Ellison]

Dear Friends:

Format, Input Mask, and Caption are three things seen in the lower part of
the Design View for Tables.

They are not reflected, as far as I can see, in the Properties of the Field
object. Are these accessible to be read or set in code, and if so, how.

Thanks.

Tom Ellison

 

[Duane Hookom]

Some properties are not created until you have set them either manually or
through code. You can search Help on "CreateProperty" and find some sample
code.

 

[John Nurick]

Hi Tom,

One of the properties of the Field object is the Properties Collection.
If a "property" such as Format, InputMask, ValidationRule has been set
in the UI you can access it with syntax like

CurrentDB.TableDefs("X").Fields("Y").Properties("Format").Value

But the "properties" don't exist by default. If you set a previously
unset property in the UI, the relevant item is added to the field's
Properties collection and is then available to VBA. Otherwise, you have
to write code to explicitly create a new Property object and and append
it to the Field's Properties collection.

 

[Tom Ellison]

Dear John and Duane,

Thanks for the information. So, I believe I should reference these
properties using On Error Resume Next. Make sense?

Some experimentation will be necessary to confirm the names of these
properties. Next, I'll need to determine that they can be created and set.

This seems like an unnecessarily clumsy arrangement, given that the
properties are displayed in the Table Design View dialog. I would have
expected such properties to be permanent. Am I missing a point here? Would
there be a disadvantage to having them permanent, like the other properties
of a field?

Tom Ellison

 

[Duane Hookom]

I never use any of these properties in table design. I doubt my preferences
had anything to do the MS's decision to exclude them from their standard
properties.
--
Duane Hookom
MS Access MVP

Dear John and Duane,

Thanks for the information. So, I believe I should reference these
properties using On Error Resume Next. Make sense?

Some experimentation will be necessary to confirm the names of these
properties. Next, I'll need to determine that they can be created and
set.

This seems like an unnecessarily clumsy arrangement, given that the
properties are displayed in the Table Design View dialog. I would have
expected such properties to be permanent. Am I missing a point here?
Would there be a disadvantage to having them permanent, like the other
properties of a field?

Tom Ellison

 

[Tim Ferguson]

Format, Input Mask, and Caption are three things seen in the lower
part of the Design View for Tables.

They are not reflected, as far as I can see, in the Properties of the
Field object. Are these accessible to be read or set in code, and if
so, how.

As you have read below, they are all in the Properties collection.

One thing, though: these things only have any meaning in the context of
the Access GUI. They are read when you create a form or report and base a
control on the field. That is all. Nothing else. If you are not creating
Access objects, then they are simply no use.

I have never understood why anyone would want programmatic access to
these properties. If you are building forms, then you can use the UI to
change them. If you haven't got access to the UI, then you can't build
forms either.

<retire mode="puzzled" />

Tim F

 

[Tom Ellison]

Dear Tim:

Here's the reason I want access to these.

I'm building software that can display and recreate what is in a table, any
table, and any property. It is the foundation for a "Data Dictionary" of
any application. It is, in some ways, a replacement for the Access GUI in
these respects. So, naturally, I need to be able to read and write these
properties.

Tom Ellison

 

[John Nurick]

Hi Tom,

Thanks for the information. So, I believe I should reference these
properties using On Error Resume Next. Make sense?

Yes, or if that hurts you could use a loop (air code alert):

Dim TheField As DAO.Field
Dim P As DAO.Property, j As Long

With TheField
For j = 0 to .Properties.Count - 1
If .Properties(j).Name = "Format" Then
Set P = .Properties(j)
Exit For
End If
Next j
If P Is Nothing Then
Set P = New DAO.Property
P.Name = "Format"
.Properties.Append P
End If
End With

Some experimentation will be necessary to confirm the names of these
properties. Next, I'll need to determine that they can be created and set.

This seems like an unnecessarily clumsy arrangement, given that the
properties are displayed in the Table Design View dialog. I would have
expected such properties to be permanent. Am I missing a point here? Would
there be a disadvantage to having them permanent, like the other properties
of a field?

Tom Ellison

 

[Tim Ferguson]

So, naturally, I need to be able to read and write these
properties.

No you don't. They are not part of the data model (which is why they aren't
visible via bread-and-butter DAO or ADO) and are only relevant within the
Access GUI. As soon as you start saying "I am the developer, and the user
will only come come to the Data via me", then they become immediately
redundant. Perhaps a useful shortcut to you as the developer, but invisible
and unknowable to the user and to the dbengine.

All the best


Tim F

 

[Tom Ellison]

Dear Tim:

But the utility I'm creating is FOR developers, not for users. Certainly
this is a valid reason.

Tom Ellison