Go Back   Access World Forums > Apps and Windows > Visual Basic

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-22-2019, 07:37 AM   #1
Jamster45
Newly Registered User
 
Join Date: Aug 2018
Posts: 7
Thanks: 3
Thanked 0 Times in 0 Posts
Jamster45 is on a distinguished road
duplicate main record + sub and sub -related records

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 pbaldy; 04-22-2019 at 07:48 AM. Reason: Added code tags
Jamster45 is offline   Reply With Quote
Old 04-24-2019, 04:26 AM   #2
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,461
Thanks: 106
Thanked 2,537 Times in 2,330 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: duplicate main record + sub and sub -related records

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 04-24-2019, 09:40 AM   #3
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,960
Thanks: 0
Thanked 463 Times in 459 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: duplicate main record + sub and sub -related records

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.

__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Duplicate related records (Versioning) jerem Queries 3 09-25-2015 03:04 AM
[Help] Mass duplicate main records and related subform records cnstarz Queries 5 06-01-2014 08:08 AM
Duplicate Record and related records mtn Modules & VBA 1 11-28-2010 02:13 PM
deleting all related sub records when deleting the "main" record. Roy riggsjø General 1 04-13-2008 02:35 AM
Duplicate record with related records capsula4 Forms 13 02-29-2008 12:09 PM




All times are GMT -8. The time now is 12:55 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World