[AccessD] Increment Child #s

Arthur,

    Apparently, while making data entry into a subform linked to parent form vide field CustomerOrder (PK on parent and FK on subform), you wish that for each CustomerOrder, default value for bound control CustomerOrderItem on the subform should reflect a series of sequential numbers starting at 1.

    If the subform is sorted as per CustomerOrderItem, sample public subroutine named P_SetDefValue() as given below (at the end of this post), should get you the desired results. It is called from Load and AfterUpdate events of the subform, as well as Current event of parent form.

    Sample code in parent form's module would be as follows (SF_Sub is the name of subform control serving as container for the subform):
'========================
Private Sub Form_Current()
    Me.SF_Sub.Form.P_SetDefValue
End Sub
'========================

    Sample code in subform's module is given below.

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

' Code in subform's module
'======================
Private Sub Form_AfterUpdate()
    P_SetDefValue
End Sub
'---------------------------------

Private Sub Form_Load()
    P_SetDefValue
End Sub
'---------------------------------

Public Sub P_SetDefValue()
    With Me.RecordsetClone
        If .RecordCount > 0 Then
            .MoveLast
            Me.CustomerOrderItem.DefaultValue = _
                        .Fields("CustomerOrderItem") + 1
        Else
            Me.CustomerOrderItem.DefaultValue = 1
        End If
    End With
End Sub
'=================================

  ----- Original Message ----- 
  From: Arthur Fuller 
  To: Access Developers discussion and problem solving 
  Sent: Thursday, November 18, 2010 00:26
  Subject: [AccessD] Increment Child #s


  Assume:

  T1 - parent file, say CustomerOrders
  T2 - child file, say CustomerOrderItems

  Goal:

  Inherit the PK from T1, but add an Item# column that enumerates the Items on
  said Order, so...

  CO1
      Item 1
      Item 2
  CO2
      Item 1
      Item 2

  I wrote some code to do a Dmax() call using the Parent PK but yuk! I'm
  trying to find a slicker way to do this, ideally so that I can default the
  Item# value for the next new record. Any ideas how this might be done?

  TIA
  A.