Prev: Some help if your subreports don't show up in 2007
Next: Setfocus going to wrong control
From: Parag on 29 Mar 2010 22:37
I'm trying to write a macro to update column headers in a table - any
ideas on how I can do this?

Basically, for a given table I'd like to update column headers e.g. A,
B, C with the values e.g. "Apple", "Boy" and "Cat".

This is related to an earlier problem I listed here** but with a
relaxed approach.

**http://bit.ly/bDFPCQ



From: Salad on 30 Mar 2010 12:15
Parag wrote:
> I'm trying to write a macro to update column headers in a table - any
> ideas on how I can do this?
>
> Basically, for a given table I'd like to update column headers e.g. A,
> B, C with the values e.g. "Apple", "Boy" and "Cat".
>
> This is related to an earlier problem I listed here** but with a
> relaxed approach.
>
> **http://bit.ly/bDFPCQ
>

I don't know why you want to change the field names of a table. That
could really mess up your database.

I created a table called Table1 with 3 text fields; A, B, and C. I then
ran this code. It sets the caption for each field to the value I want.
The end result is A has a caption of "Apple", B is now "Boy", and "C"
is "Cat". Do the same to test it out.

I don't know how you'd do this with a macro unless you did a RunCode in
the macro and ran a similar sub like below.

Public Sub SetTableCaption()
On Error Resume Next

Dim dbs As Database
Dim tdf As TableDef
Dim fld As Field
Dim prp As Property
Dim strCaption As String

Set dbs = CurrentDb
Set tdf = dbs.TableDefs("Table1")

With tdf
'read each field of the table
For Each fld In tdf.Fields
'determine the caption
Select Case Left(fld.Name, 1)
Case "A"
strCaption = "Apple"
Case "B"
strCaption = "Boy"
Case "C"
strCaption = "Cat"
End Select

tdf.Properties("Caption") = strCaption

' If the property didn't exist, there will have been an
' error and the property needs to be added.
If Err.Number = 3270 Then
Err.Clear

Set prp = fld.CreateProperty("Caption", dbText, strCaption)
fld.Properties.Append prp
End If
Next
End With
Set tdf = Nothing
msgbox "Done"
End Sub
From: Parag on 31 Mar 2010 17:34
On Mar 31, 5:15 am, Salad <sa...(a)oilandvinegar.com> wrote:
> Paragwrote:
> > I'm trying to write a macro to update column headers in a table - any
> > ideas on how I can do this?
>
> > Basically, for a given table I'd like to update column headers e.g. A,
> > B, C with the values e.g. "Apple", "Boy" and "Cat".
>
> > This is related to an earlier problem I listed here** but with a
> > relaxed approach.
>
> > **http://bit.ly/bDFPCQ
>
> I don't know why you want to change the field names of a table.  That
> could really mess up your database.
>
> I created a table called Table1 with 3 text fields; A, B, and C.  I then
> ran this code.  It sets the caption for each field to the value I want.
>   The end result is A has a caption of "Apple", B is now "Boy", and "C"
> is "Cat".  Do the same to test it out.
>
> I don't know how you'd do this with a macro unless you did a RunCode in
> the macro and ran a similar sub like below.
>
> Public Sub SetTableCaption()
>      On Error Resume Next
>
>      Dim dbs As Database
>      Dim tdf As TableDef
>      Dim fld As Field
>      Dim prp As Property
>      Dim strCaption As String
>
>      Set dbs = CurrentDb
>      Set tdf = dbs.TableDefs("Table1")
>
>      With tdf
>          'read each field of the table
>          For Each fld In tdf.Fields
>              'determine the caption
>              Select Case Left(fld.Name, 1)
>              Case "A"
>                  strCaption = "Apple"
>              Case "B"
>                  strCaption = "Boy"
>              Case "C"
>                  strCaption = "Cat"
>              End Select
>
>              tdf.Properties("Caption") = strCaption
>
>              ' If the property didn't exist, there will have been an
>              ' error and the property needs to be added.
>              If Err.Number = 3270 Then
>                  Err.Clear
>
>              Set prp = fld.CreateProperty("Caption", dbText, strCaption)
>                  fld.Properties.Append prp
>              End If
>          Next
>      End With
>      Set tdf = Nothing
>      msgbox "Done"
> End Sub

Thanks Salad, I'll generate the code for the 4000+ fields. The purpose
of this macro is to clean up the large column headers we're getting
from a clinical data extract. We currently get VISIT/FORM/QUESTION and
need to transform the field to Question (Visit) so it's viewable when
running a query.
Speaking of queries, I've posted another question re querying column
headers here. http://bit.ly/cLydXy
 | 
Pages: 1
Prev: Some help if your subreports don't show up in 2007
Next: Setfocus going to wrong control