Change the Caption of a field in a tble

[MarionD]

Hi there,

I am trying to change the caption of a field in a table per VBA.

Set td = db.TableDefs("tbl_Dress")
With td
Set fld = td.Fields("DPurchasePrice")
fld.????? = "Einkaufspreis"
td.Fields.Refresh

Cn anyone tell me how to reference this property please? Sorry, I am using a German version.

 

[plog]

Changing is different than adding.

Dim TABLE_NAME As String
Dim FIELD_NAME As String
Dim DESCRIPTION As String


TABLE_NAME = "Put table name here"
FIELD_NAME = "Put field name here"
DESCRIPTION = "Put the description here"


--Adding Description below
Set tmp_property = CurrentDb.TableDefs(TABLE_NAME).Fields(FIELD_NAME).CreateProperty("Description", 10, DESCRIPTION)
CurrentDb.TableDefs(TABLE_NAME).Fields(FIELD_NAME).Properties.Append tmp_property

--Changing Description below
CurrentDb.TableDefs(TABLE_NAME).Fields(FIELD_NAME).Properties("description").Value = DESCRIPTION

 

[MarionD]

The bigger question is why are you doing this? Hopefully, you aren't allowing the user to make this change.

Also, changes to tables MUST be made in the BE database. You cannot make changes to linked tables this way. You would need to define a link to the BE db rather than the CurrentDB.

Hi Pat,
No not the user - I am trying to include this in an automatic Version Update - of course on the back end Tables. Just a once of converting the back end from Version 1.09 to 2.00
Thanks for the input

 

[MarionD]

Changing is different than adding.

Dim TABLE_NAME As String
Dim FIELD_NAME As String
Dim DESCRIPTION As String


TABLE_NAME = "Put table name here"
FIELD_NAME = "Put field name here"
DESCRIPTION = "Put the description here"


--Adding Description below
Set tmp_property = CurrentDb.TableDefs(TABLE_NAME).Fields(FIELD_NAME).CreateProperty("Description", 10, DESCRIPTION)
CurrentDb.TableDefs(TABLE_NAME).Fields(FIELD_NAME).Properties.Append tmp_property

--Changing Description below
CurrentDb.TableDefs(TABLE_NAME).Fields(FIELD_NAME).Properties("description").Value = DESCRIPTION

Hi Plog,
Thanks so much for the answer.
I get an error message on the set tmp_property..... as what must I dim tmp_property?
I have tried dim tmp_property as property and as accessobject property but somehow it's not working for me.
Thanks again

 

[plog]

What was the error? Did you set the variables correctly? Can you post your entire code?

 

[MarionD]

Did you create a second db object to use to reference the tables in the BE?

Set BE_db = DBEngine.Workspaces(0).OpenDatabase(strDatabase)

strDatabase = the full path to the BE database.

Yes thanks I did do that....

 

[MarionD]

What was the error? Did you set the variables correctly? Can you post your entire code?

Dim ws As DAO.Workspace
Dim prog_db As DAO.Database 'verweist auf die currentdb der REBECCA.mdb
Dim rs As DAO.Recordset
Dim db As DAO.Database 'verweist auf die db der Back end daten.mdb
Dim td As DAO.TableDef
Dim fld As DAO.Field
Dim idx As DAO.Index
Dim tmp_property As Property
'*********
On Error GoTo fehler
Set ws = DAO.CreateWorkspace("Konv", "Admin", "")
Set db = ws.OpenDatabase(NetzFile, False) *netzfile returns the backend db
Set prog_db = CurrentDb()

DoCmd.OpenForm "frm_Meldung"

'***********************************add/delete /change fields in tables in BE DB

Forms("frm_Meldung").Controls("Meldung").Caption = "Tabellen werden bearbeitet...." '& td.Name
Forms("frm_Meldung").Repaint

Set td = db.TableDefs("tbl_Bride")
With td
.Fields.Append .CreateField("Brautbild", dbAttachment)
.Fields.Refresh
End With

Forms("frm_Meldung").Controls("Meldung").Caption = "Tabellen werden bearbeitet...." '& td.Name
Forms("frm_Meldung").Repaint
Set td = db.TableDefs("tbl_Dress")

With td

.Fields.Append .CreateField("Bildkleid", dbAttachment)
.Fields.Refresh

Set tmp_property = td.Fields("DPurchasePrice").CreateProperty("description", 20, "Einkaufspreis")
.Field("DPurchasePrice").Properties.Append tmp_property
.Fields("DPurchasePrice").Properties("description").Value = "Einkaufspreis"
.Fields.Refresh
'****************************this is my problem part. I don't want to change the field name - only the description
End With

'**Next table
Forms("frm_Meldung").Controls("Meldung").Caption = "Tabellen werden bearbeitet...." '& td.Name
Forms("frm_Meldung").Repaint
Set td = db.TableDefs("tbl_Bride_Dress")
With td
.Fields.Append .CreateField("DPreisohneMwSt", dbCurrency)
.Fields.Refresh
End With

Set rs = db.OpenRecordset("tbl_Eigenschaften")
rs.edit
rs!Version = "1.02"
rs.Update
rs.Close
Set rs = Nothing

thanks so much for your time!

 

[ebs17]

Off Topic:
It would drive me crazy if the title was something other than the real name of the field. You can't program like that if you can't trust what's written there.
Hopefully no other programmer than you will have to maintain this application.

 

[plog]

Again: What was the error?

I see a missing 'Field' instead of 'Fields' on the line after you set tmp_property. But without that error message I'm taking random guesses.

 

[MarionD]

Again: What was the error?

I see a missing 'Field' instead of 'Fields' on the line after you set tmp_property. But without that error message I'm taking random guesses.

 

[Uncle Gizmo]

Conversion of the message in the Pop-Up error message box to English is:- data type conversion error.

In fact Reading the German message in the message box and comparing it to the translation, I can hear it in my head, it's the same!

The heading of the message box contains:- Problem converting to 1.02

 

[plog]

Sounds like it was able to change the description. The issue is with other portions of the code. Specifically:

rs!Version = "1.02"

 

[MarionD]

Sounds like it was able to change the description. The issue is with other portions of the code. Specifically:

rs!Version = "1.02"

Set tmp_property = td.Fields("DPurchasePrice").CreateProperty("description", 20, "Einkaufspreis")

the error occurs on this line - I think the proble is with set tmp_property- thats why I asked as what should I dim tmp_property?

it doesn't get as far as changing the description.... thanks anyway

 

[MarionD]

100% in agreement. One of my earliest jobs was to fix a problem with an application that used Captions and this was back in the days when if you had Captions, that is what you saw in code. You didn't see the actual column names. That made it quite clear to me that no programmer would ever willingly define a caption.

I am also in agreement - this is more trying to adapt to make it understandable for German user.

 

[ebs17]

In a proper application, a user does not see table names, but the headings that are shown to him in the form or report (assignment control/label to table field).
Only the developer deals with table names, and he shouldn't be afraid of English, since VBA and SQL are also in English.

 

[MarionD]

The Description property does not exist by default. You will have to define it if it does not already exist.

OK thanks.... I dont know how to do that. I thought that it would be quite simple in VBA as it's so easy change in the table design manually!
Thanks for the input everybody - I think I will abandon the idea.

In a proper application, a user does not see table names, but the headings that are shown to him in the form or report.
Only the developer deals with table names, and he shouldn't be afraid of English, since VBA and SQL are also in English.

Yes - it's these headings that I'm trying to change. Not the table names, or the field names ---only what shows on user level- I can change the captions in a form or report, I just thought that one could change it in the table first, then it displays that name as caption when creating a form.
In German it's called Beschriftung...(Caption)

 

[ebs17]

when creating a form

Thinking is good, thinking fully is better. In a normal bound form, the caption of the label attached to the control is displayed. The field name in the table doesn't matter at all.

For cases where you have to work with field names from tables on the surface, you will use a column alias in queries.

SELECT FieldNameA AS NeuerName [, FieldList]
FROM TableX

 

[MarionD]

Thinking is good, thinking fully is better. In a normal bound form, the caption of the label attached to the control is displayed. The field name in the table doesn't matter at all.

For cases where you have to work with field names from tables on the surface, you will use a column alias in queries.

SELECT FieldNameA AS NeuerName [, FieldList]
FROM TableX

Thank you.

 

[isladogs]

Setting a field caption can occasionally be useful for readability e.g. to display a space or use full words in a field name
e.g. field ErrNo with caption Error Number ; YrGrp => Year Group etc

Contrary to Pat's comments in post #3., you CAN change field captions for linked BE tables in the FE.
This works because changing captions is NOT changing the structure of the linked table itself.
The captions used will persist after the database is closed including after compacting the FE.

Doing this is equivalent to using an alias on a field name in a query

 

[MarionD]

Setting a field caption can occasionally be useful for readability e.g. to display a space or use full words in a field name
e.g. field ErrNo with caption Error Number ; YrGrp => Year Group etc

Contrary to Pat's comments in post #3., you CAN change field captions for linked BE tables in the FE.
This works because changing captions is NOT changing the structure of the linked table itself.
The captions used will persist after the database is closed including after compacting the FE.

Doing this is equivalent to using an alias on a field name in a query

Thank you so much for your friendly and helpful comment! I am just off to Switzerland for a weekend in the mountains... will get straight back to work after that.