Update a table's caption

[Davbib]

Hi- my first post here. Kind of a newbie. I want to update a fields caption programmatically. SQL or VB is fine and the table will always have an existing caption to overwrite.

Can u help me out?

[ADezii]

@Davbib

1. I created a Generic Function that will change a Field Caption in a given Table to another Caption, simply pass the Function 3 Arguments:
   1. The Table Name (strTableName)
   2. The Original Field Caption (strOldCaption)
   3. The New Caption for the Field (strNewCaption)

   Public Function fChangeFieldCaption(strTableName As String, strOldCaption As String, strNewCaption As String)
   On Error GoTo Err_fChangeFieldCaption
   Dim MyDB As DAO.Database
   Dim fld As DAO.Field
    
   If Len(strTableName) = 0 Or Len(strOldCaption) = 0 Or Len(strNewCaption) = 0 Then
     MsgBox "The Field Caption change cannot be completed", vbExclamation
     Exit Function
   End If
    
   Set MyDB = CurrentDb
   Set fld = MyDB.TableDefs(strTableName).Fields(strOldCaption)
    
   fld.Properties("Caption") = strNewCaption
    
   Set MyDB = Nothing
   Set fld = Nothing
    
   Exit_fChangeFieldCaption:
     Exit Function
    
   Err_fChangeFieldCaption:
     MsgBox Err.Description, vbExclamation, "Error in fChangeFieldCaption()"
     Resume Exit_fChangeFieldCaption
   End Function
    

2. To change the Caption of [Field1] in Table1 to NEW CAPTION:
   

   Call fChangeFieldName("Table1","Field1","NEW CAPTION")

3. To remove the Caption from [Field1] in Table1:
   

   Call fChangeFieldName("Table1","Field1"," ")

P.S. - This Code will only work if a Caption for the Field previously existed, which you have indicated will always be True. Should this not be the case, then the Caption Property would have to be dynamically created, since Caption is a User Defined Property.

[NeoPa]

If you have a field object, then from there the reference would be :

.Properties("Caption")

NB. This will fail if that field has no Caption property.

[NeoPa]

With the same field, it is also possible to add or remove the optional Caption property. Changes made this way are persistent and effect the design of your table until changed.

To add a Caption property to a field you need first to create the property object, before then appending this new object to the Properties collection. Assuming you have code where the Field object has been the subject of a With statement, a single line to add your property might be (avoiding variables) :

Call .Properties.Append(.CreateProperty("Caption", dbText, "Caption Value"))

To remove this property simply use :

Call .Properties.Delete("Caption")

[ADezii]

Hello NePa, just some useless information. The usual approach when dealing with User Defined Properties is to specifically Trap Error #3270, as indicated in the SHIFT ByPass code below. The OP stated that there will always be a Caption Property, so I did not incorporate this into the code in order to try to avoid confusion since he/she is a Newbie:

Private Function ChangeProperty(strPropertyName As String, varPropertyType As Variant, varPropertyValue As Variant) As Integer
On Error GoTo Err_ChangeProperty
Dim MyDB As DAO.Database
Dim MyProperty As DAO.Property
 
Set MyDB = CurrentDb()
 
'Property exists, so set its Value
MyDB.Properties(strPropertyName) = varPropertyValue
ChangeProperty = True
 
Exit_ChangeProperty:
  Exit Function
 
Err_ChangeProperty:
  If Err.Number = 3270 Then       'Property not found
    'Since the Property isn't found, create it!
    Set MyProperty = MyDB.CreateProperty(strPropertyName, varPropertyType, varPropertyValue)
    MyDB.Properties.Append MyProperty
      Resume Next
  Else
   'Unknown Error
   ChangeProperty = False
     Resume Exit_ChangeProperty
  End If
End Function

Dim intFunctionReturn As Integer
 
'Allow ShiftByPass
intFunctionReturn = ChangeProperty("AllowBypassKey", dbBoolean, True)

[NeoPa]

Indeed ADezii. I hadn't overlooked that (for a change). To be clear - I see no problem with your code.

I simply added some extra information so that some of the many searchers for similar info could find what they need also (Your info about trapping error #3270 is also valuable in that category).

[Davbib]

Thanks for the response. I can't seem to get this to work. I should also add that although I will always have an existing field caption, I won't know what that field caption is (doubt that matters). What I want to do is put the value captured in the first input box (q1) into a field's caption in a table.

Here is my original code for reference:

Sub Import()
 
On Error GoTo Err_Trap
 
DoCmd.SetWarnings "0"
 
Dim name, path1
 
path1 = "C:\documents and settings\"
name = Environ("username")
 
q1 = InputBox("Enter the file name of the first quarter to compare (FYyyQa.txt)", "Quarter 1")
 
q2 = InputBox("Enter the file name of the second quarter to compare (FYyyQb.txt)", "Quarter 2")
 
filestrQ1 = path1 & name & "\My Documents\" & q1
filestrQ2 = path1 & name & "\My Documents\" & q2
filestrSD = path1 & name & "\My Documents\Sourcing Document.xls"
filestrPricing = path1 & name & "\My Documents\Pricing.xls"
 
ck = 0
 
If Dir(filestrQ1) = "" Then ck = ck + 1
If Dir(filestrQ2) = "" Then ck = ck + 1
If Dir(filestrPricing) = "" Then ck = ck + 1
If Dir(filestrSD) = "" Then ck = ck + 1
 
If ck <> 0 Then
 
MsgBox "Sorry, " & ck & " file(s) are either missing or named improperly.  Please check your 'My Documents' folder."
 
Else
 
DoCmd.Hourglass True
 
Clear_Tables
 
DoCmd.TransferText acImportDelim, "COST_1 Spec", "COST_1", q1, yes
DoCmd.TransferText acImportDelim, "COST_2 Spec", "COST_2", q2, yes
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "SD", filestrSD, 1
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "PRICING", filestrPricing, 1
 
DoCmd.OpenQuery "Cost_Savings_Forecast_1", acViewNormal ' for 291
DoCmd.OpenQuery "Cost_Savings_Forecast_2", acViewNormal ' for non-291
 
DoCmd.Hourglass False
 
Beep
MsgBox "Raw data successully imported"
 
End If
 
Err_Trap_Exit:
    Exit Sub
 
Err_Trap:
    MsgBox Err.Description
    Resume Err_Trap_Exit
 
End Sub

[NeoPa]

@Davbib
Probably won't matter in your scenario, but I included code in my earlier post that would read that for you just in case.
@Davbib
Can you clarify if this is the code you produced after reading the replies or the original as stated. Clearly there's very little point in your posting the original code.

[Davbib]

Sorry. Thought that maybe I was making it harder than it needed to be. What I did was copy the code provided and then tested it this way.

Sub test()
 
Call fChangeFieldName("Cost_Savings_Forecast_Table", "FC Qty", "Test Caption Change")
 
End Sub

when I run this, I get "Compile Error- Sub or Function not defined.

[ADezii]

@Davbib
fChangeFieldName() must be Declared as a 'Public' Function in a 'Standard Code Module'.

[NeoPa]

@Davbib
Where did you put the pasted copy of fChangeFieldCaption()?

As ADezii says it must be available either in a standard code module or in the same module as the test() subroutine is in.

PS. Is there any reason why you are calling the function with a different name (fChangeFieldName <> fChangeFieldCaption)?

[Davbib]

Ah. found it. I was calling fchangefieldname, not fchangefieldcaption! It works now!

Thanks so much for the help. I've put over 20 hours into this little problem.

[ADezii]

@Davbib
The Error was on my part, not yours. In Post #2 the actual Function Definition in Item #1 does not agree with the Calling Procedures that I had given you in Items #2 and #3. Sorry! I was experimenting with different Options in order to try to find a workable solution to your problem, but unfortunately, did not sync them up!

[Davbib]

@ADezii
No problem at all. I appreciate the help and since I did manage to find it on my own, I look at it as you giving me a learning experience on debugging code. So, with your help I learned two things today :)