The code below creates a unique index called MemAddID

    With MyTblDef
        Set MyField = .CreateField(FieldName, dbLong)
        MyField.Required = True
        MyField.OrdinalPosition = 0
.Fields.Append s.Append MyField ' Add Field "MemAddID" at top of table OK
        
        Set MyIndex = .CreateIndex(FieldName)
        Set MyField = MyIndex.CreateField(FieldName)
        MyIndex.Required = False
        MyIndex.Unique = True                    ' Don't want this
        MyIndex.Fields.Append MyIndex.CreateField(FieldName)
        .Indexes.Append MyIndex                 ' Add Index
    End With

What I want is to remove the uniqueness of MemAddID but add a unique index
combination of MemAddID and MemHeadOfHouseID. Simple to do in table design
view, but I need to do it in VBA.

Can anyone please help with the code.

Thanks

Phil

There are 3 examples in the CreateIndexesDAO() code here:
    http://allenbrowne.com/func-DAO.html#CreateIndexesDAO

The 3rd illustrates the process for a multi-field index.
To make it unique as well, just add the line:
    .Unique = True
before the End With.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Phil" <phil@stantonfamily.co.uk> wrote in message 
news:W-udnS__Qs25NAvRnZ2dnUVZ8lCdnZ2d@brightview.co.uk...
> The code below creates a unique index called MemAddID
>
>    With MyTblDef
>        Set MyField = .CreateField(FieldName, dbLong)
>        MyField.Required = True
>        MyField.OrdinalPosition = 0
> Fields.Append s.Append MyField ' Add Field "MemAddID" at top of table OK
>
>        Set MyIndex = .CreateIndex(FieldName)
>        Set MyField = MyIndex.CreateField(FieldName)
>        MyIndex.Required = False
>        MyIndex.Unique = True                    ' Don't want this
>        MyIndex.Fields.Append MyIndex.CreateField(FieldName)
>        .Indexes.Append MyIndex                 ' Add Index
>    End With
>
> What I want is to remove the uniqueness of MemAddID but add a unique index
> combination of MemAddID and MemHeadOfHouseID. Simple to do in table design
> view, but I need to do it in VBA.
>
> Can anyone please help with the code.
>
> Thanks
>
> Phil 

On 20/09/2010 01:32:52, "Allen Browne" wrote:
> There are 3 examples in the CreateIndexesDAO() code here:
> http://allenbrowne.com/func-DAO.html#CreateIndexesDAO
> 
> The 3rd illustrates the process for a multi-field index.
> To make it unique as well, just add the line:
> .Unique = True
> before the End With.
> 

Thanks Allen

Perfect

Phil