Looping through properties collection

[dan-cat]

Hello,

What I am trying to do is create a new field which has the exact same properties as another field in a different table. Here is what I have done:



' Declare variables
Dim db As DAO.Database
Dim prp As Property
Dim tdf As TableDef
Dim tdfNew As TableDef
Dim fld As Field
Dim fldNew As Field
Dim i As Integer
Dim prpNew As Property

' Set tabledefs and fields
Set db = CurrentDb
Set tdf = db.TableDefs("tblOld")
Set tdfNew = db.TableDefs("tblNew")
Set fld = tdf.Fields("Experiment")

' Create new field and append into tblNew
With tdfNew
On Error Resume Next
.Fields.Append .CreateField(fld.Name, fld.Type)
.Fields.Refresh
On Error GoTo 0
End With


'Set new field
Set fldNew = tdfNew.Fields("Experiment")

'Append all the properties from old field to new field
With fldNew
For i = 1 To fld.Properties.Count
On Error Resume Next
Set prpNew = fldNew.CreateProperty(fld.Properties(i).Name, fld.Properties(i).Type, fld.Properties(i).Value)
fldNew.Properties.Append prpNew
Next i

'Refresh both fields properties collection
fldNew.Properties.Refresh
fld.Properties.Refresh


'Append all property values to new field
For i = 1 To fld.Properties.Count
fldNew.Properties(i).Value = fld.Properties(i).Value
fldNew.Properties.Refresh
Next i

Everything is ok (I think) until the last block of code.
This is because the properties collection for the old field is not in sync with the properties collection of the new field.

Does anyone know how to sync both collections. They both have the same properties but are not stored in the same order.

Thanks Awfully

Dan

 

[dan-cat]

Worry not my good people - I think I have fixed it.

I amended the final block of code to:

For i = 0 To fldNew.Properties.Count
strName = fldNew.Properties(i).Name
fldNew.Properties(strName).Value = fld.Properties(strName).Value
fldNew.Properties.Refresh
Next i

Doesn't reorder the two collections but used strName as a lookup tool.