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 resource:(http://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

Perhaps you can try this code. It adds the number of records in a table to the descriptionPublic 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!

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