Appending a "Caption" Property to Fields in a recordset

[David G.]

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.

 

[Jeff Boyce]

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.

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 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.

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 Goldgar]

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)


in message
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.

 

[Jeff Boyce]

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.

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.

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.

 

[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?

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.

 

[Dirk Goldgar]

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)



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?

 

[David G.]

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

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:


... 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.

 

[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

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.