Solved Get PK Field Name of a Table Bound to a Form (1 Viewer)

[Pac-Man]

Hi,

I have a form frmMeeting bound to a table tblMeeting. Structure of the table is below:
MeetingID: PK, Long
MeetingDate: Date Time
MeetingDesc: Text
ClientID: FK, Long
EmployeeID: FK, Long

I want to know the primary key name on form OnLoad event using VBA. The reason why I need this programmatically is I need to input it in initialize sub's parameter of a class module. I can give field name manually for the main form but it can't be for the subform as it iterate through all the controls of the form and initialize new instance if control is a subform.
Can you please guide me how can I get name of PK field of the record source table of a form? Thanks in advance.

Best Regards

 

[MajP]

Public Function isPK(tblDef As DAO.TableDef, strField As String) As Boolean
  Dim idx As DAO.Index
  Dim fld As DAO.Field
  For Each idx In tblDef.Indexes
    If idx.Primary Then
      For Each fld In idx.Fields
        If strField = fld.Name Then
          isPK = True
          Exit Function
        End If
      Next fld
    End If
  Next idx
End Function

Public Function GetPK(TableName As String) As String
  Dim tblDef As DAO.TableDef
  Dim fld As DAO.Field
  Dim db As DAO.Database
 
  Set db = CurrentDb
  Set tblDef = db.TableDefs(TableName)
  For Each fld In tblDef.Fields
    If isPK(tblDef, fld.Name) Then
      GetPK = fld.Name
      Exit Function
    End If
  Next fld
End Function


Public Sub test()
  Debug.Print GetPK("Categories")
End Sub

 

[KitaYama]

Public Function Whats_PrimaryKey(tbl As String) As String
   
    Dim db As DAO.Database
    Dim objIndex As index
    Dim tb As DAO.TableDef
       
    Set db = CurrentDb
    Set tb = db.TableDefs(tbl)
   
    For Each objIndex In tb.Indexes
        If objIndex.Primary Then
            Whats_PrimaryKey = Right(objIndex.Fields, Len(objIndex.Fields) - 1)
            Exit Function
        End If
    Next

End Function

 

[Pac-Man]

Thanks a lot @MajP and @KitaYama for so quick response. I have one more associated question. I have a form who recordsource is a query which have main table that is being written plus one Inner join table to show names of emoloyees like:

From tblEmployees INNER JOIN tblMeeting ON tblEmployees.EmployeeID = tblMeeting.EmployeeID

Table being written/modified in the form is tblMeeting. Is it possible to know which table is actual table being modified by the form and then know its PK field name?

 

[CJ_London]

Use a pk suffix for the pk and a fk suffix for the foreign key

tblEmployees.EmployeePK = tblMeeting.EmployeeFK

 

[MajP]

If the recordsource is a stored query (querydef) then maybe.
Taking the stored qdf you can return the fields collection. Then you can loop those and determine which table they come from the sourcetable property. From there you can get the PK as previously done.
If it is not a query def, but just a sql string in the recordsource then I am not sure. I guess you could create a temp qdf based on the stored SQL in the rowsource and do the same as above.
However if your query returns two or more primary keys not sure there is an easy way.

 

[ebs17]

The procedure is quite unstructured.
tblEmployees has a primary key, tblMeeting certainly also has a primary key. Which one exactly do you need, and how is a code supposed to know which table it should check? Two tables become a problem, four tables in the query become a bigger problem.

If you know which PK in which RecordSource you need, then you would simply write a small mapping table and look it up there.

 

[MajP]

I am thinking this is a 98 percent solution. In general the PK you care about is going to be listed first. If not then make sure to do that.

Public Function GetQueryPK(RowSource As String) As String
  Dim qdf As QueryDef
  Dim db As DAO.Database
  Dim tdf As TableDef
  Dim fld As DAO.Field
  Dim FieldSource As String

  Set db = CurrentDb
  DeleteQueryDef "tempQDF"
  If Left(RowSource, 7) <> "Select " Then
   Set qdf = db.QueryDefs(RowSource)
  Else
    Set qdf = db.CreateQueryDef("tempQDF", RowSource)
  End If

  For Each fld In qdf.Fields
    FieldSource = fld.SourceField
    Set tdf = db.TableDefs(fld.SourceTable)
    If isPK(tdf, FieldSource) Then
      Debug.Print fld.Name
      GetQueryPK = fld.Name
      Exit Function
    End If
  Next fld
    DeleteQueryDef "tempQDF"
End Function
Public Sub DeleteQueryDef(qdfName As String)
  Dim myquerydef As QueryDef
  For Each myquerydef In CurrentDb.QueryDefs
    If myquerydef.Name = qdfName Then
     CurrentDb.QueryDefs.Delete (qdfName)
     Exit For
    End If
   Next
End Sub

Public Sub TestQuery()
  Debug.Print GetQueryPK("query1")
  Debug.Print GetQueryPK("SELECT Orders.OrderID, Orders.CustomerID AS Cust, Customers.CompanyName FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID")
End Sub

This also does not handle composite keys, but handles a stored query and a rowsource SQL.

 

[MajP]

This may be more like an 85% solution. This will not work with queries based on other queries. Unless the PK you are looking for is not based on a query and listed first.

 

[Pac-Man]

Thanks you very much @MajP, @CJ_London for valuable suggestions. I'm truly grateful. That will suffice my requirement.