Getting the Primary Key in VBA

[scoobey]

Hi there!

I'm trying to create a template form which has buttons on it which perform record manipulation, eg Add, Delete, Next Record, Previous Record etc. In order to do this I really need to automate most of the processes when the form loads. The only thing I want to change when I re-use the form for another table is the record source.
My problem is this. How do I get the form to obtain the primary key from the current record set? I suspect it has something to do with dao.index but i'm not too sure on the coding?

Any ideas or help would be very much appreciated. Thanks!

 

[RoyVidar]

There was just a thread about something like that, with some suggestions thread705-1199120.

Roy-Vidar

 

[jrbarnett]

Here's one way. You'll need to set a reference to the DAO object library appropriate for your version of Access.

Dim tdf As DAO.Tabledef
Dim idx As DAO.Index
Dim fld As DAO.Field

Set tdf = currentdb.Tabledefs ("YourTable")
For Each idx In tdf.Indexes
    ' Loop through each of the table indexes
    If InStr(idx.Fields, fld.Name) Then
        ' if the field name is in the index and the index is the primary one then it is true
        If idx.Primary = True Then
               ' Its part of the primary key...
            Exit For
        End If
    End If
Next

John

 

[scoobey]

Thanks guys! I've now got it working - cheers!