Duplicate Record

[Marianna_Air]

It might be the location of the button, its on the Parent form
The Error is a Compile, method or data member not found

[Missinglinq]

I apologize, Marianna_Air! I completely missed the part about duplicating a Record from the Main Form and related Records from a SubForm, and the posted code will not work, for that!

I have never needed to do this and therefor have no experience with it. I've seen hacks around, for this task, but never had enough interest to even archive any of them. I will look around and see if I can find one for you.

Just for the record, are we talking about a Main Form Record and one Record from the Subform, or Multiple Records from the Subform?

Linq ;0)>

[pbaldy]

I think we were on the right track before "You're really over-engineering this thing". You might start again at that point.

[orange]

I've been out. Just came back to notice the form, subform was a real situation as suspected after your earlier post.

I think Paul is right --there's probably an easier way to do what you want.

[Marianna_Air]

I agree, this beast is growing, I just keep adding to it to fix one problem and a new one pops up. I'm too much of a novice!!! urggggg
This is where I ended up previously. I'm getting an error of "too few parameters. Expected 2." and debug highlites the code in red below

Private Sub Command169_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 strID As String      'Primary key value of the new record.
    
    'Save any 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
                !EmployeeID = Me.EmployeeID
                !RequestDate = Date
                !RequestforQuotesID = GetNextSN("RFQ")
                'etc for other fields.
            .Update
            
            'Save the primary key value, to use as the foreign key for the related records.
            .Bookmark = .LastModified
           strID = !RequestforQuotesID
            
            'Duplicate the related records: append query.
            If Me.[Request for Quotes Subform].Form.RecordsetClone.RecordCount > 0 Then
                strSql = "INSERT INTO [Inventory Transactions] (RequestforquotesID, ProductID, UnitsOrderedConv) " & _
                    "SELECT " & strID & " As requestforquotesID, ProductID, UnitsOrderedConv " & _
                    "FROM [Inventory Transactions] WHERE requestforquotesID = " & Me.RequestforQuotesID & ";"
                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

[pbaldy]

Yes, and right after you posted that before I posted a method (post 8) to help us debug:

http://www.baldyweb.com/ImmediateWindow.htm

And said "Offhand, I suspect you need to surround that value in single quotes since it's text not numeric (in both places)."

[Missinglinq]

I think we were on the right track before "You're really over-engineering this thing".

I agree, and wouldn't have said that if I realized that we were talking about duplicating Form and Subform Records! I probably should stay off-line until my daughter's wedding is over with! Way too much multi-tasking for a man well into his sixth decade on the planet!

Linq ;0)>

[Marianna_Air]

I ran my code on a record that has no sub records and it worked. It even gave me the massage that the "Main Record was Duplicated, but there were no related records" so it seems to work up to that point, but as soon as I try to duplicate a record that has related records(sub records) I get the error of "too few parameters. Expected 2." and it points to the same issue I previously highlighted "DBEngine(0)(0).Execute strSql, dbFailOnError"

[orange]

You don't have to use this specific statement/syntax
"DBEngine(0)(0).Execute strSql, dbFailOnError"

For an action query Insert/Update/Delete you could use

yourDatabasereference.Execute yourActionQuerySQL,dbFailOnError

could be Currentdb.execute strSQL,dbFailOnError
also as Paul has been mentioned, you could have a
Debug.Print strSQL before the ...execute statement to write the "rendered SQL" to the immediate window.

The "too few parameters...." can signal spelling errors in some cases.

The debug.print should show us the rendered SQL.

I think your Me.subform.... is incorrect syntax.

Form subform reference can be found at
http://access.mvps.org/access/forms/frm0031.htm

[pbaldy]

I ran my code on a record that has no sub records and it worked. It even gave me the massage that the "Main Record was Duplicated, but there were no related records" so it seems to work up to that point, but as soon as I try to duplicate a record that has related records(sub records) I get the error of "too few parameters. Expected 2." and it points to the same issue I previously highlighted "DBEngine(0)(0).Execute strSql, dbFailOnError"

Are posts 8 and 21 not showing up on your PC?

[Marianna_Air]

I wonder if I can make this work for me, http://support.microsoft.com/kb/208824
Any thoughts?

I'm giving up on what I have I've tried everything "debug.print" and everything else, I just can't figure it out. I think it's time to start over fresh, maybe give what the link above shows a try if ya'll think it might work?

[pbaldy]

You say you've tried the debug.print, but you haven't posted the SQL here, nor addressed whether you've added the quotes I mentioned.

[orange]

Please show us the Debug.Print results

[Marianna_Air]

Sorry I went in another direction already so I can't show you that info Orange, Pbaldy I apologize.

I've made some headway with this problem but alas I'm up against a wall again. I've come up with a way to get the records from the Form and subform to duplicate and they show in their individual tables, but I can't seem to figure out 2 things 1:how to get them to show on the newly created form/subform, and 2: I have not been able to figure out how to give the new record a new ID like"RFQ1234" I'm going to paste both the VBA code and SQL

Private Sub btnDuplicate_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![RequestforQuotesID]
' Add new record to end of Recordset object.
With Rst
   .AddNew
      !EmployeeID = Me!EmployeeID
      !Originator = Me!Originator
   .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 Request For Quotes Details"
DoCmd.SetWarnings True
'Requery the subform to display the newly appended records.
Me![request for quotes subform].Requery
Exit_btnduplicate_Click:
Exit Sub
Err_btnDuplicate_Click:
MsgBox Error$
Resume Exit_btnduplicate_Click:
End Sub

INSERT INTO [Inventory Transactions] ( TransactionID, ProductID, TransactionDescription, TransferFromID, CustomerID, UnitPrice, UnitsOrdered, UnitsReceived, UnitsReceivedFG, UnitsSold, UnitsShipped, UnitsShippedFG, UnitsShrinkage, PPnumber, DateRequired, DatePromised, SerialNumber, SalesOrderID, ModelNumber, Control, ShippedDate, ShippingMethodID, ShippingTracking, SalesTracking, CustomerPO, Ordered, ETA, Delivered, [Userdefinable 1], [Userdefinable 2], [Userdefinable 3], [Currency], Levelnumber, PP, InvoiceNumber, InvoiceDate, Explode, delevel, accountID, DepartmentAutoID, OrderLine, PackingOrderID, ShipFromID, ShipToID, UnitsOfMeasure, [WOB/O], TaxRate, isExported, GLAccount, SalesUnitPriceConv, qbTaxCode, SalesDescription, qbExportedDate, ChosenCustomerID, ChosenShipToID, BOMAmount, UnitPriceConv, UnitsOrderedConv, UnitsShippedConv, UnitsShippedFGConv, UnitsReceivedConv, UnitsReceivedFGConv, Categories, ChosenTransferFromID, ChosenShipFromID, SourceTransactionID, LineAttributes, LineType, QtyPerParent, Cogs )
SELECT [Inventory Transactions].TransactionID, [Inventory Transactions].ProductID, [Inventory Transactions].TransactionDescription, [Inventory Transactions].TransferFromID, [Inventory Transactions].CustomerID, [Inventory Transactions].UnitPrice, [Inventory Transactions].UnitsOrdered, [Inventory Transactions].UnitsReceived, [Inventory Transactions].UnitsReceivedFG, [Inventory Transactions].UnitsSold, [Inventory Transactions].UnitsShipped, [Inventory Transactions].UnitsShippedFG, [Inventory Transactions].UnitsShrinkage, [Inventory Transactions].PPnumber, [Inventory Transactions].DateRequired, [Inventory Transactions].DatePromised, [Inventory Transactions].SerialNumber, [Inventory Transactions].SalesOrderID, [Inventory Transactions].ModelNumber, [Inventory Transactions].Control, [Inventory Transactions].ShippedDate, [Inventory Transactions].ShippingMethodID, [Inventory Transactions].ShippingTracking, [Inventory Transactions].SalesTracking, [Inventory Transactions].CustomerPO, [Inventory Transactions].Ordered, [Inventory Transactions].ETA, [Inventory Transactions].Delivered, [Inventory Transactions].[Userdefinable 1], [Inventory Transactions].[Userdefinable 2], [Inventory Transactions].[Userdefinable 3], [Inventory Transactions].Currency, [Inventory Transactions].Levelnumber, [Inventory Transactions].PP, [Inventory Transactions].InvoiceNumber, [Inventory Transactions].InvoiceDate, [Inventory Transactions].Explode, [Inventory Transactions].delevel, [Inventory Transactions].accountID, [Inventory Transactions].DepartmentAutoID, [Inventory Transactions].OrderLine, [Inventory Transactions].PackingOrderID, [Inventory Transactions].ShipFromID, [Inventory Transactions].ShipToID, [Inventory Transactions].UnitsOfMeasure, [Inventory Transactions].[WOB/O], [Inventory Transactions].TaxRate, [Inventory Transactions].isExported, [Inventory Transactions].GLAccount, [Inventory Transactions].SalesUnitPriceConv, [Inventory Transactions].qbTaxCode, [Inventory Transactions].SalesDescription, [Inventory Transactions].qbExportedDate, [Inventory Transactions].ChosenCustomerID, [Inventory Transactions].ChosenShipToID, [Inventory Transactions].BOMAmount, [Inventory Transactions].UnitPriceConv, [Inventory Transactions].UnitsOrderedConv, [Inventory Transactions].UnitsShippedConv, [Inventory Transactions].UnitsShippedFGConv, [Inventory Transactions].UnitsReceivedConv, [Inventory Transactions].UnitsReceivedFGConv, [Inventory Transactions].Categories, [Inventory Transactions].ChosenTransferFromID, [Inventory Transactions].ChosenShipFromID, [Inventory Transactions].SourceTransactionID, [Inventory Transactions].LineAttributes, [Inventory Transactions].LineType, [Inventory Transactions].QtyPerParent, [Inventory Transactions].Cogs
FROM [Inventory Transactions]
WHERE ((([Inventory Transactions].RequestforQuotesID)=[forms]![Request for Quotes(inventory)].[tag]));