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

[Tom_F]

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 "RecordSour ce" of the given form. For example,
if my string variable is called "PriKeyFiel ds", 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

[Wayne Gillespie]

On 2 Feb 2007 14:00:25 -0800, "Tom_F" <fr*****@aol.co mwrote:

>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 "RecordSour ce" of the given form. For example,
if my string variable is called "PriKeyFiel ds", 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

The following function will return the field list of the PK index of the passed
table. It should give you something to work with.

?fFindPrimaryKe yFields(Current DB.TableDefs("M yTable"))
+Field01;+Field 01;+Field03

Function fFindPrimaryKey Fields(tdf As TableDef) As String
Dim idx As Index

On Error GoTo HandleIt

For Each idx In tdf.Indexes
If idx.Primary Then
fFindPrimaryKey Fields = 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"
fFindPrimaryKey Fields = vbNullString
Resume OutHere
End Select

End Function

Wayne Gillespie
Gosford NSW Australia

[Tom_F]

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(re csource)

pri_key = fFindPrimaryKey Fields(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...@NOhotm ailSPAM.com.au>
wrote:

On 2 Feb 2007 14:00:25 -0800, "Tom_F" <[e-mail] mwrote:

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 "RecordSour ce" of the given form. For example,
if my string variable is called "PriKeyFiel ds", 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

The following function will return the field list of the PK index of the passed
table. It should give you something to work with.

?fFindPrimaryKe yFields(Current DB.TableDefs("M yTable"))
+Field01;+Field 01;+Field03

Function fFindPrimaryKey Fields(tdf As TableDef) As String
Dim idx As Index

On Error GoTo HandleIt

For Each idx In tdf.Indexes
If idx.Primary Then
fFindPrimaryKey Fields = 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"
fFindPrimaryKey Fields = vbNullString
Resume OutHere
End Select

End Function

Wayne Gillespie
Gosford NSW Australia