Create VBA variable = primary-key fields of RecordSource table?

To comp.databases.ms-access:

In the VBA code behind a form, I would like to create a string
variable, composed of the names of the field(s) in the primary key of
the table which is the "RecordSource" of the given form. For example,
if my string variable is called "PriKeyFields", and the primary key of
the RecordSource table is (Field01, Field02, Field03), then I would
like PriKeyFields to be set to the string "Field01 Field02 Field03".

I am using Microsoft Access 2003, on a PC running Windows XP
Professional.

Is there any way to do this? Thanks very much!

--Tom
email: frethoa AT aol DOT com

re: Create VBA variable = primary-key fields of RecordSource table?

On 2 Feb 2007 14:00:25 -0800, "Tom_F" <frethoa@aol.comwrote:
The following function will return the field list of the PK index of the passed
table. It should give you something to work with.

?fFindPrimaryKeyFields(CurrentDB.TableDefs("MyTabl e"))
+Field01;+Field01;+Field03



Function fFindPrimaryKeyFields(tdf As TableDef) As String
Dim idx As Index

On Error GoTo HandleIt

For Each idx In tdf.Indexes
If idx.Primary Then
fFindPrimaryKeyFields = idx.Fields
GoTo OutHere
End If
Next idx

OutHere:
Set idx = Nothing
Exit Function

HandleIt:
Select Case Err
Case 0
Resume Next
Case Else
Beep
MsgBox Err & " " & Err.Description, vbCritical + vbOKOnly, "Error"
fFindPrimaryKeyFields = vbNullString
Resume OutHere
End Select

End Function

Wayne Gillespie
Gosford NSW Australia

re: Create VBA variable = primary-key fields of RecordSource table?

Mr. Gillespie --

thanks so much, this worked very well! I put your function in a
module, where it could be called from any given form. Then, in order
to actually create a VBA string variable ("pri_key") consisting of the
primary-key fields of a form's RecordSource, I added this piece of
code to each form (as part of a BeforeUpdate "Private Sub"):

-----------------------------------------------------------------------------------------------
Dim db As DAO.Database
Set db = CurrentDb

Dim tbld As TableDef

Dim pri_key As String

Dim recsource As String
recsource = Me.RecordSource

Set tbld = db.TableDefs(recsource)

pri_key = fFindPrimaryKeyFields(tbld)
Debug.Print
Debug.Print "pri_key: "; pri_key
-----------------------------------------------------------------------------------------------

Thank you again! Best,

--Tom
email: frethoa AT aol DOT com

On Feb 2, 7:09 pm, Wayne Gillespie <best...@NOhotmailSPAM.com.au>
wrote: