title: How can I get the value from a control using the name of the control source?
body: <p>I am trying to write some code to audit changes made via a form. I have a function that works to do this:</p>
  
  
  
  <pre class="lang-vb prettyprint-override"><code>Function WriteChanges()
  
  Dim f As Form
  Dim c As Control
  Dim user As String
  Dim db As DAO.Database
  Dim cnn As ADODB.Connection
  Dim MySet As ADODB.Recordset
  Dim tbld As TableDef
  Dim recsource As String
  
  Set f = Screen.ActiveForm
  Set db = CurrentDb
  Set cnn = CurrentProject.Connection
  Set MySet = New ADODB.Recordset
  recsource = f.RecordSource
  Set tbld = db.TableDefs(recsource)
  
  pri_key = fFindPrimaryKeyFields(tbld)
  Debug.Print "pri_key: "; pri_key
  user = Environ("username")
  
  
  MySet.Open "Audit", cnn, adOpenDynamic, adLockOptimistic, adCmdTable
  
  For Each c In f.Controls
      Select Case c.ControlType
          Case acTextBox, acComboBox, acListBox, acOptionGroup
              If c.Value &lt;&gt; c.OldValue Then
                  With MySet
                      .AddNew
                          ![EditDate] = Now
                          ![user] = user
                          ![SourceTable] = f.RecordSource
                          ![SourceField] = c.ControlSource
                          ![BeforeValue] = c.OldValue
                          ![AfterValue] = c.Value
                      .update
                  End With
              End If
      End Select
  Next c
  
  MySet.Close
  Set MySet = Nothing
  Set f = Nothing
  Set db = Nothing
  
  End Function
  </code></pre>
  
  <p>I use this function on the before update property of various forms and it populates the Audit table with the details of the changes to values in each of the controls. I need to also get the value from the primary key field to add to the Audit table. I can use the following code to identify the name of the primary key within the form's record source:</p>
  
  <pre class="lang-vb prettyprint-override"><code>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 = Replace(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 &amp; " " &amp; Err.Description, vbCritical + vbOKOnly, "Error"
      fFindPrimaryKeyFields = vbNullString
      Resume OutHere
  End Select
  
  End Function
  </code></pre>
  
  <p>How can I use this to get the value from the control (text box) that has the identified primary key as its control source.</p>
  
  <p>Please forgive any silly errors in my code as I'm a relative novice. Thanks in advance for any help.</p>