Update a table's caption | Newbie | | Join Date: Jun 2009
Posts: 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?
|  | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,214
| | | re: Update a table's caption
Quote:
Originally Posted by 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? - I created a Generic Function that will change a Field Caption in a given Table to another Caption, simply pass the Function 3 Arguments:
- The Table Name (strTableName)
- The Original Field Caption (strOldCaption)
- 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
-
- To change the Caption of [Field1] in Table1 to NEW CAPTION:
- Call fChangeFieldName("Table1","Field1","NEW CAPTION")
- 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.
|  | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,722
| | | re: Update a table's caption
If you have a field object, then from there the reference would be :
NB. This will fail if that field has no Caption property.
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,722
| | | re: Update a table's caption
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")
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,214
| | | re: Update a table's caption
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)
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,722
| | | re: Update a table's caption
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).
| | Newbie | | Join Date: Jun 2009
Posts: 5
| | | re: Update a table's caption
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
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,722
| | | re: Update a table's caption
Quote:
Originally Posted by Davbib 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). Probably won't matter in your scenario, but I included code in my earlier post that would read that for you just in case. Quote:
Originally Posted by Davbib Thanks for the response. I can't seem to get this to work. 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: 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.
| | Newbie | | Join Date: Jun 2009
Posts: 5
| | | re: Update a table's caption
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.
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,214
| | | re: Update a table's caption
Quote:
Originally Posted by 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. fChangeFieldName() must be Declared as a 'Public' Function in a 'Standard Code Module'.
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,722
| | | re: Update a table's caption
Quote:
Originally Posted by 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. 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 (fChangeField Name <> fChangeField Caption)?
| | Newbie | | Join Date: Jun 2009
Posts: 5
| | | re: Update a table's caption
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.
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,214
| | | re: Update a table's caption
Quote:
Originally Posted by 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. 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!
| | Newbie | | Join Date: Jun 2009
Posts: 5
| | | re: Update a table's caption
Quote:
Originally Posted by ADezii The Error was on my part, not yours. In Post #2 the actual Function Definition in Item #1 does not agree with the Calling Procedure that I had given you in Item #2. 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! 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 :)
|  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,419 network members.
|
