Duplicating Form and Sub data with button

[maxxev]

Hi i've found this code:

http://allenbrowne.com/ser-57.html

And I think i get how enough of it works to muddle through it, however it assumes the use of a set ID as opposed to an auto-number.

Can someone please explain (in detail as my brain hates programming) how I can remove the code for the ID and replace it (or just leave out enough) with Auto-number details.

Here is my eddited version.

Private Sub cmdDupe_Click()
'On Error GoTo Err_Handler
    'Purpose:   Duplicate the main form record and related records in the subform.
    Dim strSql As String    'SQL statement.
    Dim RecipeID As Long       'Primary key value of the new record.
    
    'Save and edits first
    If Me.Dirty Then
        Me.Dirty = False
    End If
    
    'Make sure there is a record to duplicate.
    If Me.NewRecord Then
        MsgBox "Select the record to duplicate."
    Else
        'Duplicate the main record: add to form's clone.
        With Me.RecordsetClone
            .AddNew
                ! VersionNumber = Me.VersionNumber
                ! [Active?] = Me.[Active?]
                ! ProductCode = Me.ProductCode
                ! ProductName = Me.ProductName
                ! RecipeDate = Date
            .Update
            
            'Save the primary key value, to use as the foreign key for the related records.
            .Bookmark = .LastModified
            RecipeID = !OrderID
            
            'Duplicate the related records: append query.
            If Me.[S-frmIngredientsInRecipe].Form.RecordsetClone.RecordCount > 0 Then
                strSql = "INSERT INTO [Recipe Details] (RecipeID, Ing_codeID, [Quantity(g)], QUID ) " & _
                    "SELECT " & RecipeID & " As NewID, RecipeID, Ing_codeID, [Quantity(g)], QUID " & _
                    "FROM [Recipe Details] WHERE RecipeID = " & Me. RecipeID & ";"
                DBEngine(0)(0).Execute strSql, dbFailOnError
            Else
                MsgBox "Main record duplicated, but there were no related records."
            End If
            
            'Display the new duplicate.
            Me.Bookmark = .LastModified
        End With
    End If

Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
    Resume Exit_Handler
End Sub

Also my understanding is that the "[recipe details]" should be an append query. I don't know how to make one of those... (I assume it is different from an "update" query?).

Cheers

 

[jrbarnett]

[Recipe Details] will most likely be a table - it is the target of your append query - The line starting strSql = "INSERT INTO [Recipe Details]"

An update query changes data that already exists in the tables, whereas an append query is one that adds data to a table (ie data gets appended to the current content).

The SQL for an append query uses either INSERT INTO table (field1, field2, field3) VALUES (value1, value2, value3) syntax - adding specific values, or a set of data - INSERT INTO table (field1, field2, field3) SELECT value1, value2, value3 FROM anothertable

Your code uses the latter syntax to duplicate the recipe details record.

John



John

 

[maxxev]

Hi, thank you for that so it looks like the only thing I have an issue with now then will be the auto-number in my table as opposed to the what the code wants to do here:

'Save the primary key value, to use as the foreign key for the related records.            
.Bookmark = .LastModified            
RecipeID = !OrderID

 

[jrbarnett]

All you need to make sure is:
- in the target fields, leave the autonumber field out of the field list to insert into - Access will populate it when the record is added.

To use the ID number from an existing record to go into a non ID field the target needs to be definded as a long integer value, and you can then use the SELECT or VALUES field to retreieve the source value -eg:

INSERT INTO targettable (field1, field2, field3)
SELECT autonumberfield, field2, field3
FROM anothertable
WHERE field1 = 'X'

this will put the contents of autonumberfield into the field1 field in target table.

John