03-22-16, 09:10 #1Registered User
- Join Date
- Jun 2010
Unanswered: Duplicate the record in form and subform - ACCESS 2013
I have been picking away and searching dbforums for advice and insight on this for a couple of weeks...I have finally come to a workable solution that I am stuck on
Thank you for reading!
I have an orders/order details database and invoices that are uploaded from an outside order system through excel.
I wrote a series of queries to match the invoices to the orders and determine if it is (still open)backordered. Then I isolate my backorder and my backorder details into make table query
Using Allen Browne example in Northwinds, I need to duplicate the back order/back order details into the orders/order details tables (with new ID's - for the next invoice).
I want to have a back order form/back order details subform providing the records to duplicate, with record sources from my make tables called mtBO, mtBODetails. With the OnClick VBA, I want the duplcates to save in my orders/order details tables
I am trying to adapt his vba with this variation........but I'm having trouble making the loop.
I attached tables - mtBO, 10 records (main form record source) and mtBODetails, 20 records (subform record source, frmsAMZBO) are true records of the first back orders I am returning to the tblAOpenOrder and tblAOpenOrderdetails
You will see the mtBO on page 5 of the tblAOpenOrder table. Each of those lines needs to be "duplicated" with the mtBO values under a new AOOID in tblAOpenOrder
The mtBODetails are the foreign key matches to the new AOOID to be saved under new AODID in tblAOpenOrderdetails
1.) How would I adapt duplicating the main record in recordsetClone to save to a table that is not the record source of the form, mtBO ?
2.) To duplicate the related records in an append query, do I name a blank query and VBA does the rest? Are the INSERT INTO and FROM functions referring to the append query name or the table ? In this code, I don't think I have looped the tables correctly, and I haven't even referenced an append query - I haven't created it yet.
Private Sub Command7_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 lngID As Long '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 !NENO = Me.NENO !CUSTPO = Me.CUSTPO !BO = Me.BO !ORDERDATE = Date !INVNOTE = Me.INVNOTE !DROPSHIP = Me.DROPSHIP !DELCANCEL = Me.DELCANCEL 'etc for other fields. .UPDATE 'Save the primary key value, to use as the foreign key for the related records. .Bookmark = .LastModified lngID = !AOOID 'Duplicate the related records: append query. If Me.[frmsAMZBO].Form.RecordsetClone.RecordCount > 0 Then strSql = "INSERT INTO [tblAOpenOrderdetails] ( AOOID, AODID, APID, PARTNO, QTYORDERED ) " & _ "SELECT " & lngID & " As NewID, AODID, APID, PARTNO, QTYORDERED " & _ "FROM [tblAOpenOrder] WHERE AOOID = " & Me.AOOID & ";" 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, , "Command7_Click" Resume Exit_Handler End Sub
03-31-16, 07:00 #2Registered User
Provided Answers: 23
- Join Date
- Apr 2014
For 99% of access, you don't need to write code.
Data is added to a table in a form. No code.
You can move data in queries. No code.
Same with your need above. Use a query to pull data from 1 table and put in another.
03-31-16, 08:51 #3Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
why are you duplicating data in the first palce?
if an item is on back order, its on order. just hasn't arrived as yet, or there is no predicted arrival dateI'd rather be riding on the Tiger 800 or the Norton