Solved

How to remove all "captions" from all field properties from all tables in database

Posted on 2016-07-17
11
45 Views
Last Modified: 2016-07-18
How can I use VBA to remove all captions from all field properties from all tables in my database.
I use Access 2010.
0
Comment
Question by:Fritz Paul
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 39

Expert Comment

by:als315
ID: 41715045
Try this code:
Public Sub remove_captions()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Set db = CurrentDb
On Error Resume Next
For Each tdf In db.TableDefs
    ' ignore system and temporary tables
    If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*") Then
        For Each fld In tdf.Fields
            If fld.Properties("Caption") <> "" Then
                Debug.Print tdf.Name, fld.Name, fld.Properties("Caption")
                fld.Properties.Delete "Caption"
            End If
        Next
    End If
Next
End Sub

Open in new window

0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 41715736
Paul,

Can I ask why?  Using the Caption property of the field allows you to give the field a meaningful name which shows up in queries and datasheets without actually changing the name of the field.  It is frequently more useful to display those captions than the actual name of your fields

Dale
0
 

Author Comment

by:Fritz Paul
ID: 41715877
@als315
Thanks that looks super. However when I run the code I get an error. Can you see the problem?
Error message.
0
 
LVL 39

Expert Comment

by:als315
ID: 41715883
Was my code modified? There are no FIndRecord or FindNext in my code.
Open debugger and try to find line with error
0
 

Author Comment

by:Fritz Paul
ID: 41715885
@Dale Fye
Thanks for your interest.
I have often wondered about this issue. Adding these captions cause so much confusion when you try to edit forms and queries.
I paste a simple example below.
To make the connection between the data on a report or form and the table is so much more difficult. And whenever you want to change the caption in a datasheet to a new one or to another language, then you really get entangled, because you are just ignored if you add a caption in the query if there is a caption in the table.
So I just wonder why don't you give the field a descriptive name in the first place?
I try to do that and then I add captions if required in the queries.
In this case I work with a legacy database with many tables and fields. It was well written, but unfortunately the programmer used a lot of captions of which some are the same as the field names and some differ. I have wasted so much time editing this database that I now decided to clean the whole thing.
Do you think I am missing something here?
Illustration of confusion.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 39

Accepted Solution

by:
als315 earned 500 total points
ID: 41715890
Try this sample. Line with deleting is commented. Run code and check errors
Database15.accdb
0
 

Author Closing Comment

by:Fritz Paul
ID: 41715919
Thanks for that sample.
I missed a reference in my database.
It works perfectly now.
Thanks
0
 
LVL 39

Expert Comment

by:als315
ID: 41715949
You are welcome
0
 
LVL 26

Expert Comment

by:Nick67
ID: 41717517
Do you think I am missing something here?
No, if it is a mess, it is a mess.
So I just wonder why don't you give the field a descriptive name in the first place?
I would hate "The ID for the Plant" as a caption, too.
Captions can be nice, in that when you create a new bound control, the label for the control picks up the caption name -- so for PlantID, a caption like "Plant ID" can be nice.
(spaces in field names are abominations!  But nice in labels)

I don't permit the user to see queries, or datasheets, and in design view, a bound control has the field name in the control, so I don't think I would find captions confusing.

And whenever you want to change the caption in a datasheet to a new one or to another language, then you really get entangled, because you are just ignored if you add a caption in the query if there is a caption in the table.
That isn't phrased well.
I hadn't realized that field aliases would be ignored -- because I generally don't use captions -- but you are right that an attempt to alias a column that has a caption fails.
If I put MyNewName:[SomeField] in a query, I DEFINITELY expect to see the alias MyNewName and not the caption of SomeField.  That would be an absolute pain in the rear.

It turns out you can override the caption inherited from the table by right-clicking the field and choosing Properties|Caption, but I could see why you may not want that.

There is also
fld.Properties("Caption").Inherited
It may be too late now, but you could perhaps have kept the captions intact, but prevented them from propagating  to labels and datasheets in whole or in part.
1
 

Author Comment

by:Fritz Paul
ID: 41717611
Nick67 thanks for your detailed comments.
Regards.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 41717635
It was an interesting question.
When I built my tables, I didn't put captions to very many fields (as of now I have 1595 fields in 163 tables) and had always thought about a UI to do so -- but since most of the work that captioning could have saved me is done, I never have.

But aliasing failures would be dreadfully annoying -- but I guess if you knew about what a caption does to aliasing, then the few times you wanted a  custom column caption may not have outweighed the thousands of labels that had to be reworded.

So, thank you for an interesting question
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

943 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now