Can't assign a field's Description property in VBA

[Thooom]

Hi. This is driving me nuts. I've written a small routine to update users' tables by adding some new fields to their existing tables. My code iterates through the CurrentDb's tables collection, compares each table, field by field, with a remote table's fields. If the remote table is missing one of the local (CurrentDb) fields, the code appends the field to the remote table. I set the field name, type, size when I create the (identical) field in the remote table. This works fine.

However, I cannot find a way to "copy" the source (local, CurrentDb) table field's _Description_ to the remote table. I finally was able to extract the local table's field's Description property (I think it was fld.properties.Item("Description").Value) but I cannot get this simple value assigned to another table/field in the remote table! I've tried the excellent Allen Browne resourcehttp://allenbrowne.com/func-DAO.html#SetFieldDescription), using:

obj.Properties.Append obj.CreateProperty(strPropertyName, intType, varValue)

but the code simply runs this line, produces no error, and does not append a new property to my field. I've rewritten the lines a dozen different ways, but it just won't set the remote table field property (just Description; the others are assigned fine). Not sure what I'm doing wrong. If anyone can help, I'd sure appreciate it. Thanks.


Thooom

 

[Guus2005]

Perhaps you can try this code. It adds the number of records in a table to the description

Public Sub SetAantalRecordsPerTable(Optional blnRemove As Boolean = False)
    
    Dim i                   As Long
    Dim dbs                 As Database
    Dim hrg                 As New clsHourglass
    Dim myTable             As TableDef
    Dim lngRecords          As String
    Dim strRecords          As String
    Dim strTablename        As String
    Dim strDescription      As String
    Dim intAantalTables     As Integer
    
    On Error GoTo Err_SetAantalRecordsPerTable

    hrg.HourGlassOn

    Set dbs = CurrentDb

    intAantalTables = dbs.TableDefs.Count
    
    SysCmd acSysCmdInitMeter, "Calculating tables ", intAantalTables
    
    'check if tables are connected
    For i = 0 To intAantalTables - 1
        SysCmd acSysCmdUpdateMeter, i
        
        Set myTable = dbs.TableDefs(i)
        strTablename = myTable.Name

        lngRecords = DCount("*", strTablename)

        strDescription = myTable.Properties("Description").Value
        strDescription = SuppressPreviousRecords(strDescription)
        
        If blnRemove Then
            SetProperty myTable, "Description", strDescription
        Else
            SetProperty myTable, "Description", "(" & Format(lngRecords, "0#####") & ") " & Trim(strDescription)
        End If
    Next

Exit_SetAantalRecordsPerTable:
    SysCmd acSysCmdRemoveMeter
    Exit Sub

Err_SetAantalRecordsPerTable:
    Select Case Err.Number
    Case 3270:  'Missing description property
        strDescription = " "
        SetProperty myTable, "Description", " " 'strDescription
        Resume Next
    Case 3385: strDescription = " " & strDescription: Resume
    Case Else
        MsgBox Err.Description, vbCritical
    End Select
    Resume Exit_SetAantalRecordsPerTable
    Resume

End Sub

Public Function SuppressPreviousRecords(strDescription As String) As String
'Strip previous records in the description
'Format: (0*) one or more number in between parenthesis
    Dim strPart        As String
    Dim lngRecords     As Long
    Dim intCloseParens As Integer
    
    SuppressPreviousRecords = strDescription
    
    If VBA.Left$(strDescription, 1) <> "(" Then Exit Function
    'Strip first character "(" and get number
    strPart = VBA.Mid$(strDescription, 2)
    lngRecords = Val(strPart)
    If lngRecords = 0 Then
        If VBA.Left$(strPart, 1) <> "0" Then
            Exit Function
        End If
    End If
    'lngrecords >= 0
    intCloseParens = VBA.InStr(1, strDescription, ")") 'Search for ")" parenthesis
    If intCloseParens = 0 Then
        Exit Function
    End If
        
    SuppressPreviousRecords = VBA.Trim$(VBA.Mid$(strDescription, intCloseParens + 1))
    
End Function
Private Sub SetProperty(tblDef As TableDef, strName As String, strValue As String)

    Dim prpNew  As Property
    Dim errLoop As Error
    
    If Len(strValue) = 0 Then strValue = " "
    
    ' Attempt to set the specified property.
    On Error GoTo Err_SetProperty
    tblDef.Properties(strName) = strValue
    On Error GoTo 0
    
Exit_SetProperty:
    Exit Sub
    
Err_SetProperty:
    ' Error 3270 means that the property was not found.
    Select Case DBEngine.Errors(0).Number
    Case 3270
        ' Create property, set its value, and append it to the Properties collection.
        Set prpNew = tblDef.CreateProperty(strName, dbText, strValue)
        tblDef.Properties.Append prpNew
        Resume Next
    Case 3385
        'User created property can't contain null value
        strValue = " " & strValue
        Resume
    Case Else
        ' If different error has occurred, display message.
        For Each errLoop In DBEngine.Errors
            MsgBox "Error number: " & errLoop.Number & vbCr & errLoop.Description
        Next errLoop
        Resume Exit_SetProperty
    End Select

End Sub

Enjoy!

 

[Thooom]

Perhaps you can try this code. It adds the number of records in a table to the description

Public Sub 

End Sub

Enjoy!

Thanks for the code, but it is similar to some of my other attempts, and didn't do the job. I finally figured it out though. I'm not at my work machine now, but here is the basic sequence of steps my (non-working) code was doing:

- open the table
- add new field (and set name, type, size at the same time)
- add the Description property and the value to the new field
- append the new field

Below is the sequence that finally worked:

- open the table
- add new field (and set name, type, size at the same time)
- append the new field
- go back and add the new Description property to the just-created field
- set the value of the field's Description property

Finally worked. I guess you can't create a new field property (at least this one) and assign a value to it until AFTER appending the field to the table. Appending seemed to "commit" the change to the table; then you can refer to it by name, and assign your value.

Lesson learned; thanks again.


Thooom