Need to get subform records copied from previous record

[LadyL]

I have working code in a button that lets me fill the form's data from the previous record:

Private Sub Command83_Click()


 Dim strMsg As String
        Call CarryOver(Me, strMsg, "Spoornummer")
        If strMsg <> vbNullString Then
            MsgBox strMsg, vbInformation
        End If
    End Sub

Public Function CarryOver(frm As Form, strErrMsg As String, ParamArray avarExceptionList()) As Long
On Error GoTo Err_Handler
    'Purpose: Carry over the same fields to a new record, based on the last record in the form.
    'Arguments: frm               = the form to copy the values on.
    '           strErrMsg         = string to append error messages to.
    '           avarExceptionList = list of control names NOT to copy values over to.
    'Return:    Count of controls that had a value assigned.
    'Usage:     In a form's BeforeInsert event, excluding Surname and City controls:
    '               Call CarryOver(Me, strMsg, "Surname", City")
    Dim rs As DAO.Recordset         'Clone of form.
    Dim ctl As Control              'Each control on form.
    Dim strForm As String           'Name of form (for error handler.)
    Dim strControl As String        'Each control in the loop
    Dim strActiveControl As String  'Name of the active control. Don't assign this as user is typing in it.
    Dim strControlSource As String  'ControlSource property.
    Dim lngI As Long                'Loop counter.
    Dim lngLBound As Long           'Lower bound of exception list array.
    Dim lngUBound As Long           'Upper bound of exception list array.
    Dim bCancel As Boolean          'Flag to cancel this operation.
    Dim bSkip As Boolean            'Flag to skip one control.
    Dim lngKt As Long               'Count of controls assigned.


    'Initialize.
    strForm = frm.Name
    strActiveControl = frm.ActiveControl.Name
    lngLBound = LBound(avarExceptionList)
    lngUBound = UBound(avarExceptionList)


    'Must not assign values to the form's controls if it is not at a new record.
    If Not frm.NewRecord Then
        bCancel = True
        strErrMsg = strErrMsg & "Cannot carry values over. Form '" & strForm & "' is not at a new record." & vbCrLf
    End If
    'Find the record to copy, checking there is one.
    If Not bCancel Then
        Set rs = frm.RecordsetClone
        If rs.RecordCount <= 0& Then
            bCancel = True
            strErrMsg = strErrMsg & "Cannot carry values over. Form '" & strForm & "' has no records." & vbCrLf
        End If
    End If


    If Not bCancel Then
        'The last record in the form is the one to copy.
        rs.MoveLast
        'Loop the controls.
        For Each ctl In frm.Controls
            bSkip = False
            strControl = ctl.Name
            'Ignore the active control, those without a ControlSource, and those in the exception list.
            If (strControl <> strActiveControl) And HasProperty(ctl, "ControlSource") Then
                For lngI = lngLBound To lngUBound
                    If avarExceptionList(lngI) = strControl Then
                        bSkip = True
                        Exit For
                    End If
                Next
                If Not bSkip Then
                    'Examine what this control is bound to. Ignore unbound, or bound to an expression.
                    strControlSource = ctl.ControlSource
                    If (strControlSource <> vbNullString) And Not (strControlSource Like "=*") Then
                        'Ignore calculated fields (no SourceTable), autonumber fields, and null values.
                        With rs(strControlSource)
                            If (.SourceTable <> vbNullString) And ((.Attributes And dbAutoIncrField) = 0&) _
                                And Not (IsCalcTableField(rs(strControlSource)) Or IsNull(.Value)) Then
                                If ctl.Value = .Value Then
                                    'do nothing. (Skipping this can cause Error 3331.)
                                Else
                                    ctl.Value = .Value
                                    lngKt = lngKt + 1&
                                End If
                            End If
                        End With
                    End If
                End If
            End If
        Next
    End If


    CarryOver = lngKt


Exit_Handler:
    Set rs = Nothing
    Exit Function


Err_Handler:
    strErrMsg = strErrMsg & Err.Description & vbCrLf
    Resume Exit_Handler
End Function


Private Function IsCalcTableField(fld As DAO.Field) As Boolean
    'Purpose: Returns True if fld is a calculated field (Access 2010 and later only.)
On Error GoTo ExitHandler
    Dim strExpr As String


    strExpr = fld.Properties("Expression")
    If strExpr <> vbNullString Then
        IsCalcTableField = True
    End If


ExitHandler:
End Function


Public Function HasProperty(obj As Object, strPropName As String) As Boolean
    'Purpose: Return true if the object has the property.
    Dim varDummy As Variant


    On Error Resume Next
    varDummy = obj.Properties(strPropName)
    HasProperty = (Err.Number = 0)
End Function

However, now I need a button that will fill in the subforms records (all of them) that belong to the main form's previous record. Any advice on how to do this?

[ItsMe]

Why not bind your subform to a table or query?

[LadyL]

I don't know how to do this using a query. I just need it to take the values from the previous records when I press the button. Is that possible? In other cases I need them to be filled in by hand.

[ItsMe]

Anything is possible. Sorry for questioning your approach. It, just, seems unconventional. So I am having difficulty understanding how to provide a recommendation. I suppose I could spend 15 or 20 minutes reading your code. However, providing an answer to my question may speed the process along and I can, then, use the code you provided as a reference.

[LadyL]

Sorry, which question? Why I do't use a query? My subform is linked to its own table of course that saves the data and links it to the main form with an ID. I merely want the subform to show its data from the subform table that was filled in in the main forms last record. Am I making any sense?

[ItsMe]

... I merely want the subform to show its data from the subform table that was filled in in the main forms last record...

It may be as simple as requerying your subform. I believe this isthe correct syntax.
Me.SubFormControlName.Form.Requery

If not, try ...
Me.SubFormControlName.Requery

[LadyL]

Well no, I need it to show the records from the mainforms last record, not the current (new) record.

[ItsMe]

Well no, ....

OK then ...

[LadyL]

I am now trying to just copy the entire mainform and the subform data into a new record so you don't have to fill out all the same data again. However if I use the macro to copy this record then it does copy the record and its subform data, but when I change the Id to the next, the subform data disappears.


I also this code:

Private Sub Command91_Click()Dim dbs As DAO.Database, Rst As DAO.Recordset
Dim F As Form


' Return Database variable pointing to current database.
Set dbs = CurrentDb
Set Rst = Me.RecordsetClone


On Error GoTo Err_btnDuplicate_Click


' Tag property to be used later by the append query.
Me.Tag = Me![spoornummer]


' Add new record to end of Recordset object.
With Rst
            .AddNew
                !spoornummer = Me.spoornummer
                !datum = Me.datum
                !Werkput = Me.Werkput
                !Vlak = Me.Vlak
                !tekeningnummer = Me.tekeningnummer
                !lengte = Me.lengte
                !breedte = Me.breedte
                !diepte = Me.diepte
                !vorm = Me.vorm
                
                !relatie = Me.relatie
                !interpretatie = Me.interpretatie
                !coupe = Me.coupe
                !NAP = Me.NAP
                !opmerking = Me.opmerking
                
                
                'etc for other fields.
        
           .Update                     ' Save changes.
   .Move 0, .LastModified
End With
Me.Bookmark = Rst.Bookmark


' Run the Duplicate Order Details append query which selects all
' detail records that have the OrderID stored in the form's
' Tag property and appends them back to the detail table with
' the OrderID of the duplicated main form record.


DoCmd.SetWarnings False
DoCmd.OpenQuery "Duplicate Order Details"
DoCmd.SetWarnings True


'Requery the subform to display the newly appended records.
Me![Vullinspoornieuw].Requery


Exit_btnduplicate_Click:
Exit Sub


Err_btnDuplicate_Click:
MsgBox Error$
Resume Exit_btnduplicate_Click:
End Sub

But then it gives the error that these changes would result in duplicate values in the primary key....

I see that I still need to perform this:

• Create a new query based on the Order Details table.
• On the Query menu, click Append Query. In the Append dialog box, select Order Details in the Table Name box, and then click OK.
• Double-click the title bar of the Order Details field list to select all the fields in the list. Drag the fields to the first column of the QBE grid.
• Delete OrderID from the Append To row of the OrderID column and type the following line in the Criteria row:[Forms]![Orders].[Tag]
  This criteria selects the detail records where the OrderID is the value found in the Orders form's Tag property, the source OrderID.
• In an empty QBE grid column, create a new column by typing the following line in the Field row:NewOrderID: CLng([Forms]![Orders]![OrderID])
• In the new column, type the following line in the Append To row:[OrderID]
  and then save the query as Duplicate Order Details and close it.

However, do they mean to make the query for the subform table or the main table? This is a bit unclear.

Edit: I made the query using the subform's table. However still the same message, that it would result in duplicate entries. Is this because my primary key is not an autonumber field? If so, how do I increase it by 1 for the new record?

Does anyone have any suggestions?

[LadyL]

I have already fixed it, I used .AddNew !spoornummer = DMax("[spoornummer]", "Sporen") + 1
!datum = Me.datum
!Werkput = Me.Werkput
!Vlak = Me.Vlak
!tekeningnummer = Me.tekeningnummer
!lengte = Me.lengte
!breedte = Me.breedte
!diepte = Me.diepte
!vorm = Me.vorm

now it works fine