Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Duplicate One Subform field onto new record (2003SP2)

    On a subform, I have several fields, one of which is called "Replaces" which is to contain the number of the ID field on previous subform record. That is, when a new subform record is created, I need the most recent or highest ID number to be placed into a field called "Replaces". For example, a record might contain ID "120", Name "Fred", Replaces "93". When I create a new record, it needs to contain ID "147" (or whatever the new new ID will be), Name "Joe", Replaces "120".
    Can anyone help please?
    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Duplicate One Subform field onto new record (2003SP2)


  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate One Subform field onto new record (2003SP2)

    Thanks Hans. I tried the code from the MS KB but couldn't get it to work on a subform. The other issue is I already have code on the subform's current event to refresh a subsequent subform.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Duplicate One Subform field onto new record (2003SP2)

    Instead of entering =AutoFillNewRecord(...) in the On Current property of the subform, add the following line to the existing On Current event procedure:

    AutoFillNewRecord Me

  5. #5
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate One Subform field onto new record (2003SP2)

    I'm getting there! I changed the following code as follows:
    ============================================
    For Each C In F
    ' ' Fill the field if ALL fields are to be filled OR if the
    ' ' ...ControlSource field can be found in the FillFields list.
    If FillAllFields Or InStr(FillFields, ";" & (C.Name) & ";") > 0 Then
    F!Replaces = RS(C.ControlSource)
    End If
    Next
    ============================================
    so that the field "Replaces" is being populated, but there are 4 records on the sub-form and the field "Replaces" is getting its new data from the lowest ID rather than the highest. Where do I go from here?

    Thanks again

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Duplicate One Subform field onto new record (2003SP2)

    What is the sort order of the subform?

  7. #7
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate One Subform field onto new record (2003SP2)

    The ID field is sorted in descending order so that the highest number is at the top for easy viewing upon opening. However the new record is being created at the end of the record list.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Duplicate One Subform field onto new record (2003SP2)

    Since the record with the highest ID is at the beginning, change the line

    RS.MoveLast

    in the function to

    RS.MoveFirst

  9. #9
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate One Subform field onto new record (2003SP2)

    Perfect, that was a simple solution to the last bit and the "Replaces" field is getting the right data now.

    However, when I use the Tab key to move out of the completed record, a new record is automatically being created and (to add a touch or irony), the "Replaces" field is getting a new number from the field I have just finished creating. I've never seen this happen before as the cursor has always ended on a new blank record.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Duplicate One Subform field onto new record (2003SP2)

    If you don't want the new record to be created automatically, put the line

    AutoFillNewRecord Me

    in the Before Insert event of the form instead of in the On Current event of the form. If that doesn't work the way you want either, we'll have to write custom code instead of using the generic AutoFillNewRecord function.

    Note: if you put the line in the Before Insert event, the Replace field won't be populated until you actually start entering data in the new record.

  11. #11
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate One Subform field onto new record (2003SP2)

    That did the trick! Thank you so much!!
    Kind regards.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •