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?
13 10099 @Davbib - 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.
NeoPa 32,556
Expert Mod 16PB
If you have a field object, then from there the reference would be :
NB. This will fail if that field has no Caption property.
NeoPa 32,556
Expert Mod 16PB
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")
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 32,556
Expert Mod 16PB
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).
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 32,556
Expert Mod 16PB @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.
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.
@Davbib fChangeFieldName() must be Declared as a 'Public' Function in a 'Standard Code Module'.
NeoPa 32,556
Expert Mod 16PB @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 (fChangeField Name <> fChangeField 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.
@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!
@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 :)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Sims |
last post by:
Hi,
This will validate, (http://validator.w3.org/)
<form method="POST" action="" name='xForm' style='background:inherit;'>
<table style='background:inherit; width:100%;'>
<tr width=80%>
<td>...
|
by: lionel |
last post by:
Hello yall,
I can't find a way to center a <fo:table> element.
I have tried many attributes
(display-align,start-indent,padding-start,...).
I have tried to encapslulate it in a <fo:block>...
|
by: FDYocum |
last post by:
I am having problems with a Web site that I've designed and I am
grinding my teeth in frustration.
The pages are built around a table with four cells. The first cell is
spanned two rows and is...
|
by: Wang, Jay |
last post by:
I try to group several rows in a table into a div and show/hide them by
click on a button somewhere with a javascript link. When clicked, the link
will toggle the style of the div section's style...
|
by: altacct |
last post by:
I've got a multi-page subform with a tab control on one of the pages.
The code that moves you from page three to page four, which is where
the tab is, changes the tab captions according to some...
|
by: D. Shane Fowlkes |
last post by:
I have a Datagrid which in theory, should allow you to edit and update the
records. I've stripped my test page down so that it's only attempting to
update one field - "description". Yet when I...
|
by: Chifo |
last post by:
hello.
i have a problem with a populate html table with data from table here
it's the problem
two querys retrieving data from table, one of querys show me a
colletion of data from 6:00 am to...
|
by: Slavan |
last post by:
I have an update statement that I'm executing against Oracle database
from my C# code and it won't work.
UPDATE MenuCaptions SET Caption = N@Caption WHERE MenuId = @MenuId AND
CultureId =...
|
by: webbi |
last post by:
I have a text box called txtAttirubte1 and an update button in FormA. I want to update the label caption for all label name Attribute1_Lable in FormB and FormC with the WORD I type in the FormA text...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |