Change the Caption of a field in a tble (1 Viewer)

[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

 

[Pat Hartman]

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.

 

[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

 

[Pat Hartman]

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.

 

[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

 

[Pat Hartman]

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.

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.

 

[Pat Hartman]

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?

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

 

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