[AccessD] Parent form PK changed (VALUE)

    Sample code given below, placed in subform's current event, detects and provides an alert whenever master field's value in the parent form changes. It is applicable to both situations i.e. when the master field control on the parent form is a bound one and also when it is unbound.

    In case of bound master field, each time the parent record changes, current event of subform fires twice (In fact, the child linked control's value gets updated fractionally earlier than that of master liked control).  The code as suggested, prevents duplicate alerts on this account, at the same time eliminating redundant message when the form loads initially.

A.D.Tejpal
---------------

Subform's Code Module 
(Product_ID is the name of Linked Child Field)
=====================================
' General Declarations Section
Private LinkFieldValue As Variant

Private Sub Form_Current()
    If Len(LinkFieldValue) > 0 And _
                    Product_ID <> LinkFieldValue Then
        MsgBox "Parent Record Has Changed"
    End If
    
    LinkFieldValue = Product_ID
End Sub
=====================================

  ----- Original Message ----- 
  From: John Colby 
  To: 'Access Developers discussion and problem solving' 
  Sent: Wednesday, March 15, 2006 07:29
  Subject: [AccessD] Parent form PK changed (VALUE)


  Is there a way (built into Access) to tell if the parent form record changed
  from the subform?  IOW, in the case of a M-M table, implemented in a
  subform, one of the fields of the M-M would be the parent ID.

  tblCompany
  CO_ID
  CO_Name
  CO_Etc

  tblPeople
  PE_ID
  PE_Lname
  PE_Fname
  PE_Etc

  tblEmployees
  EM_ID
  EM_IDPE
  EM_IDCO

  If the parent form is the company, the child form is the employee, the
  "link" is the EM_IDCO with a combo representing the people.  I want to
  monitor when the parent ID changes (CO_ID) from the subform sfrmEmployee.  I
  know that the subform control has a Link Child Field / Link Master Field
  pair of properties, but is there a property that feeds the VALUE of the Link
  Master Field into the subform for it's use?  The subform filters its
  recordset based on the Link Master Field VALUE so it must be able to see it.

  In fact all I want to know is THAT the Link Master Field VALUE changed so
  that I can requery a dependent combo in the child form.

  John W. Colby
  www.ColbyConsulting.com