[AccessD] Leave Subform

Mark,

    From your subsequent posts, it now transpires that your objective is to highlight the current row of a continuous subform, in special color. At the same time, it has to be ensured that even when the user moves out of the subform, to any other control in the parent form, the subform row that was last current, should continue to remain highlighted.

    The following course of action is suggested. (in complete replacement of whatever you have been attempting so far). This is an interesting solution and does not depend upon any primary key value:

    (a) Change the name of your bound text box from "Color" to "Rank". This will hold row numbers inserted programmatically via form's Current and BeforeInsert events. Set the locked property of this control to Yes.

    (b) Place a hidden unbound text box named TxtRef in the header or footer of the subform. It will hold the value of current record inserted programmatically.

    (c) In form's detail section, for the controls required to be highlighted, use the following expression in conditional formatting dialog box:

    ([Rank]=[TxtRef]) Or (Fn_NewRec()=True And [Rank] Is Null)

    (d) Place the code given below, in form's module. Save & compile.

    (e) Open the form in design view, and make sure that in form's properties dialog box (Event tab), Current and BeforeInsert events carry the entry [Event Procedure]. Save and close the form.

    With the above measures, current record in the subform will always get highlighted and stay highlighted even when the focus moves away to the parent form.

    Note:
    (a) The suggested approach as outlined above, avoids using form's BeforeUpdate event. Moreover, it is not dependent upon any primary key value.
    (b) This solution has been tested on Access 2K3. You might like to verify whether it works equally well on Access 97.
    (c) To start with, there is no need to worry even if the Rank field is empty in existing records. The subform will behave correctly as planned, with Rank values getting inserted as & when needed. 

Best wishes,
A.D.Tejpal
-----------

Code in form's module
====================================
Private Sub Form_BeforeInsert(Cancel As Integer)
    ' This event takes care of freshly added record
    
    ' Assign row number to Rank (bound control)
    Me.Rank = Me.CurrentRecord
    
    ' Assign current record number to TxtRef
    Me.TxtRef = Me.CurrentRecord
    
    ' This statement is necessary for clean
    ' display of color highlights (clearing up
    ' hangover of previous highlights - if any)
    Me.Recalc
End Sub
------------------------------------------------------

Private Sub Form_Current()
    ' Assign row number to Rank (bound control)
    ' - if not a new record (This check is meant to
    ' prevent inadvertant adding of fresh records
    ' merely by navigating to a new record)
    If Me.NewRecord = False Then
        Me.Rank = Me.CurrentRecord
    End If
    
    ' Assign current record number to TxtRef
    Me.TxtRef = Me.CurrentRecord
    
    ' This statement is necessary for clean
    ' display of color highlights (clearing up
    ' hangover of previous highlights - if any)
    Me.Recalc
    
    ' Note - TxtRef is an unbond text box in
    '            form header or footer, while Rank
    '            is a bound text box holding row number.
    
    '            The following expression is used for
    '            highlighting the current row through
    '            conditional formatting:
    '            ([Rank]=[TxtRef]) Or _
    '            (Fn_NewRec()=True And [Rank] Is Null)
End Sub
------------------------------------------------------

Private Function Fn_NewRec() As Boolean
    Fn_NewRec = Me.NewRecord
End Function
====================================

  ----- Original Message ----- 
  From: Mark A Matte 
  To: Access Developers discussion and problem solving 
  Sent: Friday, September 21, 2007 20:38
  Subject: Re: [AccessD] Leave Subform

  Thanks A.D.,

  But with using the code below...the Before update still fires.

  Any Ideas?

  Thanks,

  mark

    ----- Original Message ----- 
    From: A.D.TEJPAL 
    To: Access Developers discussion and problem solving 
    Cc: A.D.TEJPAL 
    Sent: Friday, September 21, 2007 11:03
    Subject: Re: [AccessD] Leave Subform


    Mark,

        This has to do with two complementary features:

        (a) Prior to final exit from subform control, current record in the subform gets saved (if the subform is still dirty) and the subform attains non-dirty state.
        (b) Exit from subform control can not take final effect so long as the subform is dirty.

        Sample code as given below, should take care of the problem faced by you. SF_Sub is the name of subform control, while Price is the name of field in the subform, whose value in current record gets set to 100 whenever exiting the subform control.

    Best wishes,
    A.D.Tejpal
    -----------

    Exit event of subform control on the main form
    ===================================
    Private Sub SF_Sub_Exit(Cancel As Integer)
        Me.SF_Sub("Price") = 100
        Me.SF_Sub.Form.Dirty = False
    End Sub
    ===================================

      ----- Original Message ----- 
      From: Mark A Matte 
      To: accessd at databaseadvisors.com 
      Sent: Friday, September 21, 2007 00:51
      Subject: [AccessD] Leave Subform


      Hello All,

      Have an A97 db...I have a subform/continuous...when I leave/exit that 
      subform I want to change a value on the current record of the subform.  I 
      thought I could do this by using the "On Exit" property of the subform.  The 
      problem is when I click out of the subform...the "on Exit" does fire...but 
      if I put code in that references the field I want to change...the click part 
      is cancelled and the subform maintains focus.

      Any suggestions?

      Thanks,

      Mark

      P.S...I can't use the "Before Update" of the subform itself...because going 
      to a new record in the form does something different.