By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,080 Members | 1,276 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,080 IT Pros & Developers. It's quick & easy.

Update a table's caption

P: 5
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?
Jun 13 '09 #1
Share this Question
Share on Google+
13 Replies


ADezii
Expert 5K+
P: 8,679
@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)
    Expand|Select|Wrap|Line Numbers
    1. Public Function fChangeFieldCaption(strTableName As String, strOldCaption As String, strNewCaption As String)
    2. On Error GoTo Err_fChangeFieldCaption
    3. Dim MyDB As DAO.Database
    4. Dim fld As DAO.Field
    5.  
    6. If Len(strTableName) = 0 Or Len(strOldCaption) = 0 Or Len(strNewCaption) = 0 Then
    7.   MsgBox "The Field Caption change cannot be completed", vbExclamation
    8.   Exit Function
    9. End If
    10.  
    11. Set MyDB = CurrentDb
    12. Set fld = MyDB.TableDefs(strTableName).Fields(strOldCaption)
    13.  
    14. fld.Properties("Caption") = strNewCaption
    15.  
    16. Set MyDB = Nothing
    17. Set fld = Nothing
    18.  
    19. Exit_fChangeFieldCaption:
    20.   Exit Function
    21.  
    22. Err_fChangeFieldCaption:
    23.   MsgBox Err.Description, vbExclamation, "Error in fChangeFieldCaption()"
    24.   Resume Exit_fChangeFieldCaption
    25. End Function
    26.  
  2. To change the Caption of [Field1] in Table1 to NEW CAPTION:
    Expand|Select|Wrap|Line Numbers
    1. Call fChangeFieldName("Table1","Field1","NEW CAPTION")
  3. To remove the Caption from [Field1] in Table1:
    Expand|Select|Wrap|Line Numbers
    1. 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.
Jun 13 '09 #2

NeoPa
Expert Mod 15k+
P: 31,709
If you have a field object, then from there the reference would be :
Expand|Select|Wrap|Line Numbers
  1. .Properties("Caption")
NB. This will fail if that field has no Caption property.
Jun 15 '09 #3

NeoPa
Expert Mod 15k+
P: 31,709
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) :
Expand|Select|Wrap|Line Numbers
  1. Call .Properties.Append(.CreateProperty("Caption", dbText, "Caption Value"))
To remove this property simply use :
Expand|Select|Wrap|Line Numbers
  1. Call .Properties.Delete("Caption")
Jun 15 '09 #4

ADezii
Expert 5K+
P: 8,679
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:
Expand|Select|Wrap|Line Numbers
  1. Private Function ChangeProperty(strPropertyName As String, varPropertyType As Variant, varPropertyValue As Variant) As Integer
  2. On Error GoTo Err_ChangeProperty
  3. Dim MyDB As DAO.Database
  4. Dim MyProperty As DAO.Property
  5.  
  6. Set MyDB = CurrentDb()
  7.  
  8. 'Property exists, so set its Value
  9. MyDB.Properties(strPropertyName) = varPropertyValue
  10. ChangeProperty = True
  11.  
  12. Exit_ChangeProperty:
  13.   Exit Function
  14.  
  15. Err_ChangeProperty:
  16.   If Err.Number = 3270 Then       'Property not found
  17.     'Since the Property isn't found, create it!
  18.     Set MyProperty = MyDB.CreateProperty(strPropertyName, varPropertyType, varPropertyValue)
  19.     MyDB.Properties.Append MyProperty
  20.       Resume Next
  21.   Else
  22.    'Unknown Error
  23.    ChangeProperty = False
  24.      Resume Exit_ChangeProperty
  25.   End If
  26. End Function
Expand|Select|Wrap|Line Numbers
  1. Dim intFunctionReturn As Integer
  2.  
  3. 'Allow ShiftByPass
  4. intFunctionReturn = ChangeProperty("AllowBypassKey", dbBoolean, True)
Jun 15 '09 #5

NeoPa
Expert Mod 15k+
P: 31,709
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).
Jun 15 '09 #6

P: 5
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:
Expand|Select|Wrap|Line Numbers
  1. Sub Import()
  2.  
  3. On Error GoTo Err_Trap
  4.  
  5. DoCmd.SetWarnings "0"
  6.  
  7. Dim name, path1
  8.  
  9. path1 = "C:\documents and settings\"
  10. name = Environ("username")
  11.  
  12. q1 = InputBox("Enter the file name of the first quarter to compare (FYyyQa.txt)", "Quarter 1")
  13.  
  14. q2 = InputBox("Enter the file name of the second quarter to compare (FYyyQb.txt)", "Quarter 2")
  15.  
  16. filestrQ1 = path1 & name & "\My Documents\" & q1
  17. filestrQ2 = path1 & name & "\My Documents\" & q2
  18. filestrSD = path1 & name & "\My Documents\Sourcing Document.xls"
  19. filestrPricing = path1 & name & "\My Documents\Pricing.xls"
  20.  
  21. ck = 0
  22.  
  23. If Dir(filestrQ1) = "" Then ck = ck + 1
  24. If Dir(filestrQ2) = "" Then ck = ck + 1
  25. If Dir(filestrPricing) = "" Then ck = ck + 1
  26. If Dir(filestrSD) = "" Then ck = ck + 1
  27.  
  28. If ck <> 0 Then
  29.  
  30. MsgBox "Sorry, " & ck & " file(s) are either missing or named improperly.  Please check your 'My Documents' folder."
  31.  
  32. Else
  33.  
  34. DoCmd.Hourglass True
  35.  
  36. Clear_Tables
  37.  
  38. DoCmd.TransferText acImportDelim, "COST_1 Spec", "COST_1", q1, yes
  39. DoCmd.TransferText acImportDelim, "COST_2 Spec", "COST_2", q2, yes
  40. DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "SD", filestrSD, 1
  41. DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "PRICING", filestrPricing, 1
  42.  
  43. DoCmd.OpenQuery "Cost_Savings_Forecast_1", acViewNormal ' for 291
  44. DoCmd.OpenQuery "Cost_Savings_Forecast_2", acViewNormal ' for non-291
  45.  
  46. DoCmd.Hourglass False
  47.  
  48. Beep
  49. MsgBox "Raw data successully imported"
  50.  
  51. End If
  52.  
  53. Err_Trap_Exit:
  54.     Exit Sub
  55.  
  56. Err_Trap:
  57.     MsgBox Err.Description
  58.     Resume Err_Trap_Exit
  59.  
  60. End Sub
Jun 15 '09 #7

NeoPa
Expert Mod 15k+
P: 31,709
@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.
Jun 15 '09 #8

P: 5
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.
Expand|Select|Wrap|Line Numbers
  1. Sub test()
  2.  
  3. Call fChangeFieldName("Cost_Savings_Forecast_Table", "FC Qty", "Test Caption Change")
  4.  
  5. End Sub
when I run this, I get "Compile Error- Sub or Function not defined.
Jun 15 '09 #9

ADezii
Expert 5K+
P: 8,679
@Davbib
fChangeFieldName() must be Declared as a 'Public' Function in a 'Standard Code Module'.
Jun 15 '09 #10

NeoPa
Expert Mod 15k+
P: 31,709
@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)?
Jun 15 '09 #11

P: 5
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.
Jun 15 '09 #12

ADezii
Expert 5K+
P: 8,679
@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!
Jun 15 '09 #13

P: 5
@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 :)
Jun 15 '09 #14

Post your reply

Sign in to post your reply or Sign up for a free account.