Determining through code if a field is unique (indexed)

[Rosebud22]

Hello,

I've searched the site and haven't seen this question posted and I'm hoping someone has a tip for me. I'm trying to create a form that will show for a specific table in my db the table characteristics. So for each field, I'd like to show field name, field size, data type, and whether the field is indexed with no duplicates. I can't seem to figure out how to write code to determine if a field is indexed no duplicates or unique. I've tried the following:

For Each idx In dbCurr.TableDefs(datasource).Indexes
Debug.Print idx.Name
Next

but instead of returing the name of the field that is indexed, it returns the value "PrimaryKey", which the field is the primary key but I'd like to get a field name or simply be able to loop through the fields and determine if they are unique. Does anyone have any ideas?

Thanks,
Rosebud

 

[EMP]

idx.Unique will return True or False.

You can loop through the Fields collection of each Index to get the field names.

   Dim db As DAO.Database
   Dim idx As Index
   Dim fld As Field
   Dim sFld As String
   
   Set db = CurrentDb
   
   For Each idx In db.TableDefs("Table1").Indexes
      
      MsgBox "index name " & idx.Name
      MsgBox "unique " & idx.Unique
     
      sFld = "fields "
      For Each fld In idx.Fields
         sFld = sFld & fld.Name & ", "
      Next fld
      MsgBox Left(sFld, Len(sFld) - 2)
   
   Next idx

 

[Rosebud22]

Thank you so much EMP, your code worked great!