I want to assign English/Readable captions to all fields in any table.
If I manually add a caption to a field, I can edit the property later
in VBA. If the caption has never been set, I can't seem to be able to
add a Caption property to any field.

--code snippet
dim rs as DAO.Recordset

Set rs = CurrentDb.OpenRecordset("Select * FROM tblMyTable")
For i = 0 To rs.Fields.Count - 1
rs.Fields(i).Properties.Append & _
rs.Fields(i).CreateProperty("Caption", & _
dbText, fBuildCaption(rs.Fields(i).Name))
next i
--end code


I get "Invalid Operation. 3219" error message.
THANKS!
David G.

David

Which version of Access?

When I'm working in table definitions, I have a Caption property available
for each field, no "appending" needed.

NOTE: if you are attempting to make an Access table more "readable", stop
now!

Access tables store data, Access forms (and reports) display data. An
Access table may look like a spreadsheet, but it isn't one.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"David G." <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
I want to assign English/Readable captions to all fields in any table.
If I manually add a caption to a field, I can edit the property later
in VBA. If the caption has never been set, I can't seem to be able to
add a Caption property to any field.

--code snippet
dim rs as DAO.Recordset

Set rs = CurrentDb.OpenRecordset("Select * FROM tblMyTable")
For i = 0 To rs.Fields.Count - 1
rs.Fields(i).Properties.Append & _
rs.Fields(i).CreateProperty("Caption", & _
dbText, fBuildCaption(rs.Fields(i).Name))
next i
--end code


I get "Invalid Operation. 3219" error message.
THANKS!
David G.

Access 2007
The table is created by a CREATE TABLE query. It pulls fields from
several tables. Unfortunately, the caption properties of the
originating fields aren't included in the final table.

I also want to allow for changes in which fields get included, so I
delete the table and recreate it whenever I need to work with the
data.

On Wed, 23 Dec 2009 11:39:51 -0800, "Jeff Boyce"
<(E-Mail Removed)> wrote:

>David
>
>Which version of Access?
>
>When I'm working in table definitions, I have a Caption property available
>for each field, no "appending" needed.
>
>NOTE: if you are attempting to make an Access table more "readable", stop
>now!
>
>Access tables store data, Access forms (and reports) display data. An
>Access table may look like a spreadsheet, but it isn't one.
>
>Good luck!
>
>Regards
>
>Jeff Boyce
>Microsoft Access MVP
THANKS!
David G.

I would use a TableDef object for this, rather than a Recordset. I didn't
even know you could modify an existing Caption property using a recordset,
but it still makes mnore sense to do it with a TableDef.

Further, there's a note in the help topic for the CreateProperty method:
"You can create a user-defined Property object only in the Properties
collection of an object that is *persistent*." The word "persistent" is a
in the help text links to this definition: "An object stored in the
database; for example, a database table or QueryDef object. Dynaset-type or
snapshot-type Recordset objects are not considered persistent objects
because they are created in memory as needed."

Now, although it refers to dynaset and snapshot-type recordsets, I've tried
it using a table-type recordset ('Set rs =
CurrentDb.OpenRecordset("tblMyTable", dbOpenTable)'), and it still wouldn't
let me append the property.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)


"David G." wrote in message
news:(E-Mail Removed)...
I want to assign English/Readable captions to all fields in any table.
If I manually add a caption to a field, I can edit the property later
in VBA. If the caption has never been set, I can't seem to be able to
add a Caption property to any field.

--code snippet
dim rs as DAO.Recordset

Set rs = CurrentDb.OpenRecordset("Select * FROM tblMyTable")
For i = 0 To rs.Fields.Count - 1
rs.Fields(i).Properties.Append & _
rs.Fields(i).CreateProperty("Caption", & _
dbText, fBuildCaption(rs.Fields(i).Name))
next i
--end code


I get "Invalid Operation. 3219" error message.
THANKS!
David G.

Sounds like Dirk (else-thread) has an approach for you.

I'm curious, though, what having a new table defined as a copy of fields
from other tables would allow you to do...?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"David G." <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
Access 2007
The table is created by a CREATE TABLE query. It pulls fields from
several tables. Unfortunately, the caption properties of the
originating fields aren't included in the final table.

I also want to allow for changes in which fields get included, so I
delete the table and recreate it whenever I need to work with the
data.

On Wed, 23 Dec 2009 11:39:51 -0800, "Jeff Boyce"
<(E-Mail Removed)> wrote:

>David
>
>Which version of Access?
>
>When I'm working in table definitions, I have a Caption property available
>for each field, no "appending" needed.
>
>NOTE: if you are attempting to make an Access table more "readable", stop
>now!
>
>Access tables store data, Access forms (and reports) display data. An
>Access table may look like a spreadsheet, but it isn't one.
>
>Good luck!
>
>Regards
>
>Jeff Boyce
>Microsoft Access MVP
THANKS!
David G.

Dirk:
Moved code for creating Caption property. I use the following to
modify the properties of a table definition:

dim tdf as tabledef
Set tdf = db.TableDefs("tblDataAnalysis")
For i = 0 To tdf.Fields.Count - 1
tdf.Fields(i).Properties.Append
tdf.Fields(i).CreateProperty("Caption", dbText,
fBuildCaption(rs.Fields(i).Name))
Next i
.....

I get Object required error #424. Can you point out why?

On Wed, 23 Dec 2009 15:50:56 -0500, "Dirk Goldgar"
<(E-Mail Removed)> wrote:

>I would use a TableDef object for this, rather than a Recordset. I didn't
>even know you could modify an existing Caption property using a recordset,
>but it still makes mnore sense to do it with a TableDef.
>
>Further, there's a note in the help topic for the CreateProperty method:
>"You can create a user-defined Property object only in the Properties
>collection of an object that is *persistent*." The word "persistent" isa
>in the help text links to this definition: "An object stored in the
>database; for example, a database table or QueryDef object. Dynaset-typeor
>snapshot-type Recordset objects are not considered persistent objects
>because they are created in memory as needed."
>
>Now, although it refers to dynaset and snapshot-type recordsets, I've tried
>it using a table-type recordset ('Set rs =
>CurrentDb.OpenRecordset("tblMyTable", dbOpenTable)'), and it still wouldn't
>let me append the property.
THANKS!
David G.

The capitalization suggests that's not a copy/paste of your code, so I can't
be sure what is really wrong; however, you certainly have an error here:

> fBuildCaption(rs.Fields(i).Name))

.... since "rs" is not being used here. Try it as:

fBuildCaption(tdf.Fields(i).Name))

I would probably write it slightly differently, to avoid unnecessary
repeated indexing into the Fields collection:

Dim tdf As DAO.TableDef

Set tdf = db.TableDefs("tblDataAnalysis")

For i = 0 To tdf.Fields.Count - 1
With tdf.Fields(i)
.Properties.Append .CreateProperty( _
"Caption", _
dbText, _
fBuildCaption(.Name))
End With
Next i


--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)



"David G." <(E-Mail Removed)> wrote in message
news(E-Mail Removed)...
Dirk:
Moved code for creating Caption property. I use the following to
modify the properties of a table definition:

dim tdf as tabledef
Set tdf = db.TableDefs("tblDataAnalysis")
For i = 0 To tdf.Fields.Count - 1
tdf.Fields(i).Properties.Append
tdf.Fields(i).CreateProperty("Caption", dbText,
fBuildCaption(rs.Fields(i).Name))
Next i
.....

I get Object required error #424. Can you point out why?

Thanks so much! Couldn't see the forest for the trees!

On Wed, 23 Dec 2009 16:58:16 -0500, "Dirk Goldgar"
<(E-Mail Removed)> wrote:

>The capitalization suggests that's not a copy/paste of your code, so I can't
>be sure what is really wrong; however, you certainly have an error here:
>
>> fBuildCaption(rs.Fields(i).Name))
>
>... since "rs" is not being used here. Try it as:
>
> fBuildCaption(tdf.Fields(i).Name))
>
>I would probably write it slightly differently, to avoid unnecessary
>repeated indexing into the Fields collection:
>
> Dim tdf As DAO.TableDef
>
> Set tdf = db.TableDefs("tblDataAnalysis")
>
> For i = 0 To tdf.Fields.Count - 1
> With tdf.Fields(i)
> .Properties.Append .CreateProperty( _
> "Caption", _
> dbText, _
> fBuildCaption(.Name))
> End With
> Next i
THANKS!
David G.

On Wed, 23 Dec 2009 13:13:45 -0800, "Jeff Boyce"
<(E-Mail Removed)> wrote:

>Sounds like Dirk (else-thread) has an approach for you.
>
>I'm curious, though, what having a new table defined as a copy of fields
>from other tables would allow you to do...?
>
>Regards
>
>Jeff Boyce
>Microsoft Access MVP


I needed some means of allowing users to build their own queries while
still protecting the data. I built a form that acts like a query
building wizard. I couldn't figure out how to deal with the join
complexities, so I created a make table query that builds a "flat
table". The user can select fields, criteria, sort, and grouping (for
reports) on the form, then see the qualifying data. The form takes the
users input and creates a SELECT SQL statement against the flat table.
The SQL statement is also saved for future reuse.

I am open to any comments, thoughts or suggestions.



THANKS!
David G.