duplicate main record + sub and sub -related records (1 Viewer)

Jamster45

New member
Local time
Today, 16:02
Joined
Aug 15, 2018
Messages
7
Hi,

I moved with my tables to linked tables and everything is working pretty awesome but only one thing.

The duplicate function.

I hope someone can help me.

Set up:
Set new_tbl_main = db.OpenRecordset("factuur") -> main form
Set new_tbl_sub_B = db.OpenRecordset("factuur_1e_regel") -> linked to table "factuur"
Set new_tbl_subsub_BB = db.OpenRecordset("factuur_meerdere_regels") -> linked to factuur_1e_regel

Primarykey on "factuur" = factuurid
Primarykey on "factuur_1e_regel" = Accesskey
Primarykey on "factuur_meerdere_regels" = FRMid

I have a button that copy all related records in "factuur_1e_regel" (related to "factuur")
Then copy all related records in "factuur_meerdere_regels". Those are linked to "factuur_1e_regel"

Before i moved to linked table, everything was fine.

Problem when you push the button
Access copies all the "factuur_1e_regel" -> pretty good
Access copies only the "factuur_meerdere_regels" records and linked them to the copied "factuur_1e_Regel".

So, if there is more records in "factuur_1e_regel" then access only copies the first one and linked those records to the others.

I hope its clear (its diffecult to explain).
Here is my code, i hope someone can help (and understand).

!!linked tables to MySQL!!

Code:
Private Sub Knop_duplicate_Click()

' New Line
Dim NL As String * 2
' String for display in MsgBox
Dim smsg As String
'**********************************************************
 
'Declare variables.
Dim db As Database
Dim rec As Recordset
Dim rec1 As Recordset
Dim rec2 As Recordset

Dim new_tbl_main As Recordset
Dim new_tbl_sub_A As Recordset
Dim new_tbl_sub_B As Recordset
Dim new_tbl_subsub_BB As Recordset
Dim new_tbl_subsub_BB2 As Recordset
 
Set db = CurrentDb()
Set new_tbl_main = db.OpenRecordset("factuur")
Set new_tbl_sub_B = db.OpenRecordset("factuur_1e_regel")
Set new_tbl_subsub_BB = db.OpenRecordset("factuur_meerdere_regels")


 
'Locate Main record to be copied.
Set rec = db.OpenRecordset("select * from factuur where factuurid=" & Me.AccessID)

'Insert record into Main table.
With new_tbl_main
    .AddNew
    !FactuurFactuurdatum = Date
    !FactuurBedrijfIntern = rec!FactuurBedrijfIntern
    !FactuurRelatiecode_klant = rec!FactuurRelatiecode_klant
    !FactuurRelatiecode_FN = rec!FactuurRelatiecode_FN
    !FactuurRecruiter = rec!FactuurRecruiter
    !FactuurBronkandidaat = rec!FactuurBronkandidaat
    !FactuurAangemaakt_door = TempVars("gebruikersnaam")
    !FactuurBijgewerktOp = Now
    !FactuurCreditNota = rec!FactuurCreditNota
    !FactuurFactuurOmschrijving = rec!FactuurFactuurOmschrijving
        .Update
    .Move 0, .LastModified


End With
rec.Close
 
Dim nieuwfactuur As String


nieuwfactuur = DMax("factuurid", "factuur")


 
'Insert records into Sub Table B.
With new_tbl_sub_B
    'Locate Sub Table B records to be copied.
    Set rec = db.OpenRecordset("select * from factuur_1e_regel where Gelinkt_aan_factuurnr=" & Me.AccessID)
    If rec.BOF = False Or rec.EOF = False Then
        rec.MoveFirst
        Do While Not rec.BOF And Not rec.EOF
            .AddNew
            ![Bedrag] = rec![Bedrag]
            ![Factuurregel] = rec![Factuurregel]
            ![BTW] = rec![BTW]
            ![Gelinkt_aan_factuurnr] = nieuwfactuur
            .Update
                    Dim nieuwlink As String
    nieuwlink = DMax("Accesskey", "factuur_1e_regel")
    MsgBox nieuwlink
            

            'Locate Sub-Sub Table BB records to be copied.
            Set rec1 = db.OpenRecordset("select * from factuur_meerdere_regels where FMRGelinkt_aan_factuur_Regel=" & Me!Factuur_ee_regel.Form.AccessKey)
            If rec1.BOF = False Or rec1.EOF = False Then
                rec1.MoveFirst
                Do While Not rec1.BOF And Not rec1.EOF
                'Insert records into Sub-Sub BB table.
                    new_tbl_subsub_BB.AddNew
                    new_tbl_subsub_BB![FMRFactuurregel] = rec1![FMRFactuurregel]
                    new_tbl_subsub_BB![FMRGelinkt_aan_factuur_Regel] = nieuwlink
                    new_tbl_subsub_BB.Update
                    rec1.MoveNext
                Loop
            End If
             nieuwlink = "0"
        rec.MoveNext
    Loop
    End If
 
End With
rec.Close
rec1.Close
new_tbl_main.Close
new_tbl_sub_B.Close
new_tbl_subsub_BB.Close
Me.FilterOn = False
 
'Refreshes the data on the form.
MsgBox ("Kopieëren is geslaagd. De factuur staat in de afvinklijst!")




globals.logging "[form]factuur maken[actie]duplicate[resultaat]succesvol[AccessID]" & Me.FactuurID & "[factuurtitel]" & Me.FactuurFactuurOmschrijving


 

'**Inserted for Error Handling*****************************
Exit_cmd_Duplicate_All_Click:
Exit Sub

 

ErrorHandler:
    smsg = "An unexpected situation arose in your program."
    MsgBox smsg, 16, "LogError()"
    Resume Exit_cmd_Duplicate_All_Click
'**********************************************************
End Sub
 
Last edited by a moderator:

isladogs

MVP / VIP
Local time
Today, 23:02
Joined
Jan 14, 2017
Messages
18,186
Instead of looping through recordsets, use append queries to add new records to each of your linked tables. This will be faster & much less code is required.
Just one INSERT (append) query or SQL statement for each linked table
 

June7

AWF VIP
Local time
Today, 15:02
Joined
Mar 9, 2014
Messages
5,423
I have several duplication procedures. None of them loop recordset. One uses INSERT actions. Another opens a single-record recordset and sets values of controls in new record row on form.
 

Users who are viewing this thread

Top Bottom