Update two FIELD names in one table with TEXT from another table

[ERMQ]

Hello, I have basic Access and VBA knowledge.

I need to update two FIELD names in one table with TEXT from another table.
For example:

The field name [Class Rate Table]![PrevYr Class Weighted Rate Change] needs to be updated with the text present in
[00_T_Update Years]![Map Field Name] where [00_T_Update Years]![Year Renamed] = "PrevYr1"

Example accdb attached. TIA!

http://files.engineering.com/getfil...053-4451-830a-d9313b269261&file=Example.accdb

 

[MajP]

You can change a column name in DAO pretty easily

Public Sub ChangeFieldName(tblName As String, oldFieldName As String, newName As String)
  Dim tdf As DAO.TableDef
  Dim db As DAO.Database
  Set db = CurrentDb
  Set tdf = db.TableDefs(tblName)
  Dim fld As DAO.Field
  Set fld = tdf.Fields(oldFieldName)
  fld.Name = newName
End Sub

But, this sounds like a bad idea. My guess your data is not normalized. Why would you ever need to change a column name based on data? Also why would you need to do this in code? How often you expect to do this?

 

[SkipVought]

But, this sounds like a bad idea.

Ditto, DITTO!!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[ERMQ]

But, this sounds like a bad idea. My guess your data is not normalized. Why would you ever need to change a column name based on data? Also why would you need to do this in code? How often you expect to do this?

I should have mentioned that the example was a segment of the queries. I'm trying to automate this report as much as possible for the end user. The database has a number of queries, and table [00_T_Update Years] will be updated prior to running them. Currently, it only requires that the user updates the PEFFYR field, and then run the macro that houses the rest of the queries producing the [Class Rate Table]

There is a subsequent UPDATE query in there, that updates the [00_T_Update Years]![Map Field Name] with the year entered in the PEFFYR field.

I think the code you've written has me entering in the new field name, but I actually need it to pull from a field name from another table? [highlight #FCAF3E]newName[/highlight] would be [00_T_Update Years]![Map Field Name] WHERE [00_T_Update Years]![Year Renamed] = "PrevYr1" --Is that possible?

Public Sub ChangeFieldName("Class Weighted Rate Change by Year Table" As String, "PrevYr Class Weighted Rate Change" As String, [highlight #FCAF3E]newName[/highlight] As String)
  Dim tdf As DAO.TableDef
  Dim db As DAO.Database
  Set db = CurrentDb
  Set tdf = db.TableDefs("Class Weighted Rate Change by Year Table")
  Dim fld As DAO.Field
  Set fld = tdf.Fields("PrevYr Class Weighted Rate Change")
  fld.Name = [highlight #FCAF3E]newName[/highlight] 
End Sub

 

[MajP]

Public Sub ChangeFieldName(tblName As String, oldFieldName As String, newName As String)
  Dim tdf As DAO.TableDef
  Dim db As DAO.Database
  Set db = CurrentDb
  Set tdf = db.TableDefs(tblName)
  Dim fld As DAO.Field
  Set fld = tdf.Fields(oldFieldName)
  fld.Name = newName
End Sub

Public Sub TestChange()
  Dim newName As String
  Dim strWhere As String
  strWhere = "[Year Renamed] = 'PrevYr1'"
  newName = DLookup("[Map Field Name]", "00_T_Update Years", strWhere)
  ChangeFieldName "tblOne", "ClassCode", newName
End Sub

You would not want to hardwire a procedure like that, and you did it wrong anyways. You want to make the procedure reuseable and pass in the values. The original code could then be used on any table, any field, and any names and could be called multiple times for different fields. You can get a value from a table with a dlookup. Even if this is a temp table it still does not make sense, or at least not the simplest solution. Why change the field name of a table? Either alias the field caption in a query or change the caption in the report. In the reports on open event you could change the caption using the dlookup to find what you want to change the caption/label.
So the below code edits two labels on my report based on names in a table

Private Sub Report_Load()
  Dim newName As String
  Dim strWhere As String
  strWhere = "[Year Renamed] = 'PrevYr1'"
  newName = DLookup("[Map Field Name]", "00_T_Update Years", strWhere)
  Me.LblPrevYr.Caption = newName
  strWhere = "[Year Renamed] = 'CurrYr'"
  newName = DLookup("[Map Field Name]", "00_T_Update Years", strWhere)
  Me.lblCurYr.Caption = newName
 End Sub