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:
On 2 Feb 2007 14:00:25 -0800, "Tom_F" <fret...@aol.comwrote:
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
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