Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Apr 2003
    Location
    Salem, Oregon, USA
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Field Caption Properties (2002/XP)

    Is there a way to update the caption property of all table fields using code/module? There are 15+ tables in my database and some captions are missing...just striving for consistency here and don't want to edit each table manually.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Field Caption Properties (2002/XP)

    What are you trying to accomplish? If you don't have a caption, the name of the field will be used. If is certainly possibly to set the caption property of a field using code, but it there aren't too many of them or if it isn't really necessary, then you're best best would be to do it by hand.
    Charlotte

  3. #3
    New Lounger
    Join Date
    Apr 2003
    Location
    Salem, Oregon, USA
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field Caption Properties (2002/XP)

    Probably more than necessary.

    In conforming some tables that cannot be normalized, I've managed to get the structures cleaned up so that all the tables from 1994 to 2003 are identical (where they can be). In the process, I neglected to add descriptions and comments for several fields in each yearly table. Rather than do the fixes by hand for 9 years of table definitions, I thought there had to be a way to update them via SQL or VBA.

    What started my thinking had nothing to do with captions and descriptions, believe it or not. Several of the tables had boolean fields, but some were yes/no while others were true/false. For consistency reasons, I was looking for a way to transform those field properties to yes/no throughout, and that's when I noticed the caption/description properties missing.

    The database belongs to a non-profit association, and it's in an evolutionary stage again going into the "electronic era," as they call it. Consistency in the table field formats will help us later on when it gets ported up to whatever they decide to port it to.

    After posting my message, I did find some threads in the lounge on the same subject and I'm exploring the options Bill (I believe it was) presented.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Field Caption Properties (2002/XP)

    <hr>Several of the tables had boolean fields, but some were yes/no while others were true/false.<hr>
    You totally lost me here. Boolean fields are *called* yes/no field, but they hold a boolean value. True/False, Yes/No and -1/0 are all the same thing with only a difference in display format, so I'm still not sure what you're doing, especially since captions and descriptions are two entirely different things.
    Charlotte

  5. #5
    New Lounger
    Join Date
    Apr 2003
    Location
    Salem, Oregon, USA
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field Caption Properties (2002/XP)

    <img src=/w3timages/censored.gif alt=censored border=0>
    Several fields are boolean yes/no with a format of yes/no; several are yes/no with a format of true/false. They all need to be boolean yes/no with a format property of yes/no.
    Several fields have no description property; they all need a consistent description property.
    Multiple tables are involved -- from 9 to 15.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Field Description Properties (2002/XP)

    The format property of the Yes/No field in the tables is pretty much irrelevant since it is a display format and your users should never see the tables. The display format is easily controlled in forms and reports by use of the display property of the control. I have to say, I still think it's a waste of time to try and do this through code. You can easily copy and paste the descriptions between tables faster than you could write code to search for fields without descriptions, prompt the user to enter a description, alter the table and then go on to the next table. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15> Some things just don't need to be done in code, and 9 to 15 tables is a small number of tables. If it were hundreds or even dozens of tables, I might suggest a product like SpeedFerret.
    Charlotte

  7. #7
    New Lounger
    Join Date
    Apr 2003
    Location
    Salem, Oregon, USA
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field Description Properties (2002/XP)

    They *always* see the tables. I'm the user at this point, one and only. But...eventually, others will be users who will see those tables, too. One never knows when that bus might come too fast around the corner and strike one dead, eh? This is called contingency planning for now, moving them forward into new designs later. If I start right, as Grand Ms. Helen told me once, I'll end right. This one didn't start right. Righting it is important to me right now. I realize this whole thing sounds silly, but it's a passionate thing for me right now...what can I say?

    Back to the original post, I said loosely:

    "In conforming some tables that cannot be normalized, I've managed to get the structures cleaned up so that all the tables from 1994 to 2003 are identical (where they can be). In the process, I neglected to add descriptions and comments for several fields in each yearly table..."

    In each of the yearly tables from 1994 to 2003, plus the template table (long story) and 3-4 "specialized" tables of the same format as the yearly tables, the missing captions, field formats, and descriptions _are identically missing_. So, if I know exactly which tables have the missing format or description or caption in the specific (identical) fields, THEN is there a way through code to fix that?

    I do use Rick's Find and Replace, but I don't believe it will work on the structure of a table. I could be wrong there, so if anyone knows Rick's software and how I can make part or all of these changes using Find and Replace, please pipe in. I don't have/know/use SpeedFerret...does it allow structural (e.g., property) changes to fields?

    <sigh> This gets more detailed (and perhaps less interesting to the other forum members) as we go along.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Field Description Properties (2002/XP)

    OK then, here you go. I still think Charlotte's objections are valid, but since you insist...

    The following code uses DAO to change table properties. You need a reference to the Microsoft DAO 3.6 Object Library in Tools/References... (in the Visual Basic Editor).

    Create a standard module and copy this code into it:

    <img src=/w3timages/blueline.gif width=33% height=2>

    ' This procedure sets the text property strPropertyName to strValue
    ' for the strFieldName property in ALL tables in the database.

    Sub SetFieldProperty( _
    strFieldName As String, _
    strPropertyName As String, _
    strValue As String)
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field

    On Error GoTo ErrHandler

    Set dbs = CurrentDb
    For Each tdf In dbs.TableDefs
    For Each fld In tdf.Fields
    If fld.Name = strFieldName Then
    SetProperty fld, strPropertyName, dbText, strValue
    End If
    Next fld
    Next tdf

    ExitHandler:
    Set fld = Nothing
    Set tdf = Nothing
    Set dbs = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

    ' Utility function to set (and if necessary create) a property

    Function SetProperty(obj As Object, strName As String, _
    intType As Integer, varSetting As Variant) As Boolean
    Const conPropertyNotFound As Integer = 3270

    On Error GoTo ErrHandler
    ' Try to set property.
    obj.Properties(strName) = varSetting
    obj.Properties.Refresh
    SetProperty = True
    Exit Function

    ErrHandler:
    If Err = conPropertyNotFound Then
    ' Create property and add to Properties collection.
    obj.Properties.Append obj.CreateProperty(strName, intType, varSetting)
    Resume Next
    Else
    MsgBox Err.Description, vbExclamation
    End If
    End Function

    <img src=/w3timages/blueline.gif width=33% height=2>

    Call the code like this (the names and values are only meant as examples):

    Sub ModifyTables()
    SetFieldProperty "ID", "Description", "Unique identifier"
    SetFieldProperty "LastName", "Caption", "Last name"
    SetFieldProperty "FirstName", "Caption", "First name"
    SetFieldProperty "Income", "Description", "Household income over 2002 before taxes"
    End Sub

    If you want only certain tables to be affected, you'll have to add code to select those tables.

  9. #9
    New Lounger
    Join Date
    Apr 2003
    Location
    Salem, Oregon, USA
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field Description Properties (2002/XP)

    Thanks, Hans, and Charlotte too. Charlotte taught me here that I need to be VERY specific in the problem, reason, and description, and to just let the silly stuff go. I guess if I'd paid more attention to what I was doing when I did it, I wouldn't have had to ask. My fault... <img src=/S/frown.gif border=0 alt=frown width=15 height=15>

    But thanks to both of you for your help and guidance. I do appreciate it...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •