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 (AT) stantonfamily (DOT) co.uk> wrote

On 20/09/2010 01:32:52, "Allen Browne" wrote:
Thanks Allen

Perfect

Phil