473,324 Members | 2,193 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

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

Feb 2 '07 #1
2 6889
On 2 Feb 2007 14:00:25 -0800, "Tom_F" <fr*****@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
Feb 3 '07 #2
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

Feb 6 '07 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Andrew V. Romero | last post by:
I have been working on a function which makes it easier for me to pull variables from the URL. So far I have: <script language="JavaScript"> var variablesInUrl; var vArray = new Array(); ...
4
by: I_AM_DON_AND_YOU? | last post by:
There is one more problem I am facing but didn't get the solution. In my Setup Program I am not been able to create 2 things (when the program is intalled on the client machine ) : (1) create...
7
by: GrandpaB | last post by:
I would appreciate assistance learning how to create a Desktop shortcut in my setup project. In the left pane of the Setup/File System window I right-clicked User's Desktop. From the contex...
2
by: Jake Barnes | last post by:
Using javascript closures to create singletons to ensure the survival of a reference to an HTML block when removeChild() may remove the last reference to the block and thus destory the block is...
5
by: Deecrypt | last post by:
Hi, I would like to pickup a value from a textbox and make it available to be used by every method within a class. I havent done anything like this since VB 6. Any suggestions? Cheers ...
9
by: Schraalhans Keukenmeester | last post by:
I have some C functions (with variable length argument lists) that use void pointers as arguments. Is there a way to determine at runtime what type of parameter is actually passed on to the...
5
by: John | last post by:
Which variable type (c#) can whole the largest whole number? I know this sounds silly but as double and decimal are made for numbers with decimals I am not sure. Also if anybody knows of any...
10
by: John Passaniti | last post by:
(Note: This is not the same message I posted a week or so ago. The problem that prevented my previous attempt to work was a silly error in the template system I was using. This is a problem...
3
by: sanghavi | last post by:
how to create a set up project in vb.net..how to run an application on a different machine
4
by: illegal.prime | last post by:
Hi all, I'm getting unexpected results when trying to preload assemblies into an AppDomain I'm creating. Upon creation of the AppDomain - I attach an AssemblyResolve to both my current AppDomain...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.