Copy table and subtable into other table and subtable (1 Viewer)

KoskeKos

KoskeKos

New member
Local time
Today, 11:26
Joined
May 4, 2021
Messages
28
Hi all.
I have several days fight (with myself :)). Problem is this: need to create Invoice from Offer...lets call it like this.
So, from tbl_Pon and tbl_StavkePon need to create copy to tbl_Rn and tbl_StavkeRn with same data.
Document numbers don't need to be the same.
I can copy "header" but fail to copy detalis.
Can someone help explain the logic behind....
1626346677452.png
 
Uncle Gizmo

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 10:26
Joined
Jul 9, 2003
Messages
16,281
What changes to the data do you make that require it to be put in a new table? Please explain. This is not a frivolous question, depending on your answer, it could provide you with a simple way of achieving your goal.
 
Last edited:
KoskeKos

KoskeKos

New member
Local time
Today, 11:26
Joined
May 4, 2021
Messages
28
I hope this image will explain.
I need for example this fields to be copied.
I can copy tbl_Pon data but i cant get table to copy with details(tbl_StavkePon to tbl_StavkeRn)

1626352947826.png
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:26
Joined
May 7, 2009
Messages
19,241
create a function in a module:
Code: 
Public Function fnCopyRecordMasterChild( _
        ByVal SourceMaster As String, _
        ByVal SourceChild As String, _
        ByVal SourceMasterPKName As String, _
        ByVal SourceChildKFName As String, _
        ByVal SourceMasterPKValue As Variant, _
        ByVal TargetMaster As String, _
        ByVal TargetChild As String, _
        ByVal TargetMasterPKName As String, _
        ByVal TargetChildFKName As String)
        
    Dim db As DAO.Database
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim fd As DAO.field
    Dim strFieldType As String
    Dim strCriteria1 As String
    Dim strCriteria2 As String
    Dim varNewPK As Variant
    
    SourceMaster = Replace$(Replace$("[" & SourceMaster & "]", "[[", "["), "]]", "]")
    SourceChild = Replace$(Replace$("[" & SourceChild & "]", "]]", "]"), "[[", "[")
    SourceMasterPKName = Replace$(Replace$("[" & SourceMasterPKName & "]", "]]", "]"), "[[", "[")
    SourceChildKFName = Replace$(Replace$("[" & SourceChildKFName & "]", "]]", "]"), "[[", "[")
    TargetMaster = Replace$(Replace$("[" & TargetMaster & "]", "]]", "]"), "[[", "[")
    TargetChild = Replace$(Replace$("[" & TargetChild & "]", "]]", "]"), "[[", "[")
    
    Set db = CurrentDb
    
    strFieldType = TypeName(SourceMasterPKValue)
    'expect strFieldType to be either number or String
    If strFieldType = "String" Then
        strCriteria1 = SourceMasterPKName & " = '" & SourceMasterPKValue & "'"
        strCriteria2 = SourceChildKFName & " = '" & SourceMasterPKValue & "'"
    Else
        strCriteria1 = SourceMasterPKName & " = " & SourceMasterPKValue
        strCriteria2 = SourceChildKFName & " = " & SourceMasterPKValue
    End If
    Set rs1 = db.OpenRecordset( _
            "select * from " & SourceMaster & " where " & strCriteria1 & ";", dbOpenSnapshot, dbReadOnly)
    
    'since not all fieldname are the same
    On Error Resume Next
    
    With rs1
        If Not (.BOF And .EOF) Then
            .MoveFirst
            Set rs2 = db.OpenRecordset( _
                    "select * from " & TargetMaster & " where (1=0);", dbOpenDynaset)
            rs2.AddNew
            For Each fd In .Fields
                If fd.Attributes And dbAutoIncrField Then
                    'do nothing
                Else
                    rs2(fd.Name) = fd.Value
                Next
            Next
            rs2.Update
            rs2.Bookmark = rs2.LastModified
            varNewPK = rs2(TargetMasterPKName)
            rs2.Close
        End If
        .Close
    End With
    
    Set rs1 = db.OpenRecordset("select * from " & SourceChild & " where " & strCriteria2 & ";", _
                    dbOpenSnapshot, dbReadOnly)
    With rs1
        If Not (.BOF And .EOF) Then
            .MoveFirst
            
            Set rs2 = db.OpenRecordset("select * from " & TargetChild & " where (1=0);", dbOpenDynaset)
            Do Until .EOF
                rs2.AddNew
                rs2(TargetChildFKName) = varNewPK
                For Each fd In .Fields
                    If fd.Attributes And dbAutoIncrField Then
                    Else
                        rs2(fd.Name) = fd.Value
                    End If
                Next
                rs2.Update
                .MoveNext
            Loop
            rs2.Close
        End If
        .Close
    End With
    
    Set rs1 = Nothing
    Set rs2 = Nothing
    Set db = Nothing

End Function

to use it:

Call fnCopyRecordMasterChild("tbl_Pon","tbl_StavkePon","PonID","PonID",<thePonID_Number>,"tbl_Rnt","tbl_StavkeRn","RnID","RnID")

note: untested.
 
KoskeKos

KoskeKos

New member
Local time
Today, 11:26
Joined
May 4, 2021
Messages
28
arnelgp said:
create a function in a module:
Code: 
Public Function fnCopyRecordMasterChild( _
        ByVal SourceMaster As String, _
        ByVal SourceChild As String, _
        ByVal SourceMasterPKName As String, _
        ByVal SourceChildKFName As String, _
        ByVal SourceMasterPKValue As Variant, _
        ByVal TargetMaster As String, _
        ByVal TargetChild As String, _
        ByVal TargetMasterPKName As String, _
        ByVal TargetChildFKName As String)
       
    Dim db As DAO.Database
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim fd As DAO.field
    Dim strFieldType As String
    Dim strCriteria1 As String
    Dim strCriteria2 As String
    Dim varNewPK As Variant
   
    SourceMaster = Replace$(Replace$("[" & SourceMaster & "]", "[[", "["), "]]", "]")
    SourceChild = Replace$(Replace$("[" & SourceChild & "]", "]]", "]"), "[[", "[")
    SourceMasterPKName = Replace$(Replace$("[" & SourceMasterPKName & "]", "]]", "]"), "[[", "[")
    SourceChildKFName = Replace$(Replace$("[" & SourceChildKFName & "]", "]]", "]"), "[[", "[")
    TargetMaster = Replace$(Replace$("[" & TargetMaster & "]", "]]", "]"), "[[", "[")
    TargetChild = Replace$(Replace$("[" & TargetChild & "]", "]]", "]"), "[[", "[")
   
    Set db = CurrentDb
   
    strFieldType = TypeName(SourceMasterPKValue)
    'expect strFieldType to be either number or String
    If strFieldType = "String" Then
        strCriteria1 = SourceMasterPKName & " = '" & SourceMasterPKValue & "'"
        strCriteria2 = SourceChildKFName & " = '" & SourceMasterPKValue & "'"
    Else
        strCriteria1 = SourceMasterPKName & " = " & SourceMasterPKValue
        strCriteria2 = SourceChildKFName & " = " & SourceMasterPKValue
    End If
    Set rs1 = db.OpenRecordset( _
            "select * from " & SourceMaster & " where " & strCriteria1 & ";", dbOpenSnapshot, dbReadOnly)
   
    'since not all fieldname are the same
    On Error Resume Next
   
    With rs1
        If Not (.BOF And .EOF) Then
            .MoveFirst
            Set rs2 = db.OpenRecordset( _
                    "select * from " & TargetMaster & " where (1=0);", dbOpenDynaset)
            rs2.AddNew
            For Each fd In .Fields
                If fd.Attributes And dbAutoIncrField Then
                    'do nothing
                Else
                    rs2(fd.Name) = fd.Value
                Next
            Next
            rs2.Update
            rs2.Bookmark = rs2.LastModified
            varNewPK = rs2(TargetMasterPKName)
            rs2.Close
        End If
        .Close
    End With
   
    Set rs1 = db.OpenRecordset("select * from " & SourceChild & " where " & strCriteria2 & ";", _
                    dbOpenSnapshot, dbReadOnly)
    With rs1
        If Not (.BOF And .EOF) Then
            .MoveFirst
           
            Set rs2 = db.OpenRecordset("select * from " & TargetChild & " where (1=0);", dbOpenDynaset)
            Do Until .EOF
                rs2.AddNew
                rs2(TargetChildFKName) = varNewPK
                For Each fd In .Fields
                    If fd.Attributes And dbAutoIncrField Then
                    Else
                        rs2(fd.Name) = fd.Value
                    End If
                Next
                rs2.Update
                .MoveNext
            Loop
            rs2.Close
        End If
        .Close
    End With
   
    Set rs1 = Nothing
    Set rs2 = Nothing
    Set db = Nothing

End Function

to use it:

Call fnCopyRecordMasterChild("tbl_Pon","tbl_StavkePon","PonID","PonID",<thePonID_Number>,"tbl_Rnt","tbl_StavkeRn","RnID","RnID")

note: untested.
Click to expand...
Thank you very much for effort! Ill try and report back :) 🍻🍻
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:26
Joined
May 7, 2009
Messages
19,241
there is an error and i fixed it.
try replacing this part:
Code: 
   'since not all fieldname are the same
    On Error Resume Next
    
    With rs1
        If Not (.BOF And .EOF) Then
            .MoveFirst
            Set rs2 = db.OpenRecordset( _
                    "select * from " & TargetMaster & " where (1=0);", dbOpenDynaset)
            rs2.AddNew
            For Each fd In .Fields
                If fd.Attributes And dbAutoIncrField Then
                    'do nothing
                Else
                    rs2(fd.Name) = fd.Value
                End If 'Next
            Next
            rs2.Update
            rs2.Bookmark = rs2.LastModified
            varNewPK = rs2(TargetMasterPKName)
            rs2.Close
        End If
        .Close
    End With
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:26
Joined
May 7, 2009
Messages
19,241
found another error, hope this is the last version:
Code: 
Public Function fnCopyRecordMasterChild( _
        ByVal SourceMaster As String, _
        ByVal SourceChild As String, _
        ByVal SourceMasterPKName As String, _
        ByVal SourceChildKFName As String, _
        ByVal SourceMasterPKValue As Variant, _
        ByVal TargetMaster As String, _
        ByVal TargetChild As String, _
        ByVal TargetMasterPKName As String, _
        ByVal TargetChildFKName As String)
        
    Dim db As DAO.Database
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim fd As DAO.field
    Dim strFieldType As String
    Dim strCriteria1 As String
    Dim strCriteria2 As String
    Dim varNewPK As Variant
    
    SourceMaster = Replace$(Replace$("[" & SourceMaster & "]", "[[", "["), "]]", "]")
    SourceChild = Replace$(Replace$("[" & SourceChild & "]", "]]", "]"), "[[", "[")
    SourceMasterPKName = Replace$(Replace$("[" & SourceMasterPKName & "]", "]]", "]"), "[[", "[")
    SourceChildKFName = Replace$(Replace$("[" & SourceChildKFName & "]", "]]", "]"), "[[", "[")
    TargetMaster = Replace$(Replace$("[" & TargetMaster & "]", "]]", "]"), "[[", "[")
    TargetChild = Replace$(Replace$("[" & TargetChild & "]", "]]", "]"), "[[", "[")
    
    Set db = CurrentDb
    
    strFieldType = TypeName(SourceMasterPKValue)
    'expect strFieldType to be either number or String
    If strFieldType = "String" Then
        strCriteria1 = SourceMasterPKName & " = '" & SourceMasterPKValue & "'"
        strCriteria2 = SourceChildKFName & " = '" & SourceMasterPKValue & "'"
    Else
        strCriteria1 = SourceMasterPKName & " = " & SourceMasterPKValue
        strCriteria2 = SourceChildKFName & " = " & SourceMasterPKValue
    End If
    Set rs1 = db.OpenRecordset( _
            "select * from " & SourceMaster & " where " & strCriteria1 & ";", dbOpenSnapshot, dbReadOnly)
    
    'since not all fieldname are the same
    On Error Resume Next
    
    With rs1
        If Not (.BOF And .EOF) Then
            .MoveFirst
            Set rs2 = db.OpenRecordset( _
                    "select * from " & TargetMaster & " where (1=0);", dbOpenDynaset)
            rs2.AddNew
            For Each fd In .Fields
                If fd.Attributes And dbAutoIncrField Then
                    'do nothing
                Else
                    rs2(fd.Name) = fd.Value
                End If 'Next
            Next
            rs2.Update
            rs2.Bookmark = rs2.LastModified
            varNewPK = rs2(TargetMasterPKName)
            rs2.Close
        End If
        .Close
    End With
    
    Set rs1 = db.OpenRecordset("select * from " & SourceChild & " where " & strCriteria2 & ";", _
                    dbOpenSnapshot, dbReadOnly)
    With rs1
        If Not (.BOF And .EOF) Then
            .MoveFirst
            
            Set rs2 = db.OpenRecordset("select * from " & TargetChild & " where (1=0);", dbOpenDynaset)
            Do Until .EOF
                rs2.AddNew
                For Each fd In .Fields
                    If fd.Attributes And dbAutoIncrField Then
                    Else
                        rs2(fd.Name) = fd.Value
                    End If
                Next
                rs2(TargetChildFKName) = varNewPK
                rs2.Update
                .MoveNext
            Loop
            rs2.Close
        End If
        .Close
    End With
    
    Set rs1 = Nothing
    Set rs2 = Nothing
    Set db = Nothing

End Function
 
KoskeKos

KoskeKos

New member
Local time
Today, 11:26
Joined
May 4, 2021
Messages
28
Thank you very much. It works without error!!!! You saved meeee!🥳🎉👯‍♀️👯‍♂️
I have 1 more question:
can <thePonID_Number> or SourceMasterPKValue be set to look at current (opened) form PonID value?
Something like "Forms!Opened_form!PonID"?
arnelgp said:
create a function in a module:
Code: 
Public Function fnCopyRecordMasterChild( _
        ByVal SourceMaster As String, _
        ByVal SourceChild As String, _
        ByVal SourceMasterPKName As String, _
        ByVal SourceChildKFName As String, _
        ByVal SourceMasterPKValue As Variant, _
        ByVal TargetMaster As String, _
        ByVal TargetChild As String, _
        ByVal TargetMasterPKName As String, _
        ByVal TargetChildFKName As String)
      
    Dim db As DAO.Database
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim fd As DAO.field
    Dim strFieldType As String
    Dim strCriteria1 As String
    Dim strCriteria2 As String
    Dim varNewPK As Variant
  
    SourceMaster = Replace$(Replace$("[" & SourceMaster & "]", "[[", "["), "]]", "]")
    SourceChild = Replace$(Replace$("[" & SourceChild & "]", "]]", "]"), "[[", "[")
    SourceMasterPKName = Replace$(Replace$("[" & SourceMasterPKName & "]", "]]", "]"), "[[", "[")
    SourceChildKFName = Replace$(Replace$("[" & SourceChildKFName & "]", "]]", "]"), "[[", "[")
    TargetMaster = Replace$(Replace$("[" & TargetMaster & "]", "]]", "]"), "[[", "[")
    TargetChild = Replace$(Replace$("[" & TargetChild & "]", "]]", "]"), "[[", "[")
  
    Set db = CurrentDb
  
    strFieldType = TypeName(SourceMasterPKValue)
    'expect strFieldType to be either number or String
    If strFieldType = "String" Then
        strCriteria1 = SourceMasterPKName & " = '" & SourceMasterPKValue & "'"
        strCriteria2 = SourceChildKFName & " = '" & SourceMasterPKValue & "'"
    Else
        strCriteria1 = SourceMasterPKName & " = " & SourceMasterPKValue
        strCriteria2 = SourceChildKFName & " = " & SourceMasterPKValue
    End If
    Set rs1 = db.OpenRecordset( _
            "select * from " & SourceMaster & " where " & strCriteria1 & ";", dbOpenSnapshot, dbReadOnly)
  
    'since not all fieldname are the same
    On Error Resume Next
  
    With rs1
        If Not (.BOF And .EOF) Then
            .MoveFirst
            Set rs2 = db.OpenRecordset( _
                    "select * from " & TargetMaster & " where (1=0);", dbOpenDynaset)
            rs2.AddNew
            For Each fd In .Fields
                If fd.Attributes And dbAutoIncrField Then
                    'do nothing
                Else
                    rs2(fd.Name) = fd.Value
                Next
            Next
            rs2.Update
            rs2.Bookmark = rs2.LastModified
            varNewPK = rs2(TargetMasterPKName)
            rs2.Close
        End If
        .Close
    End With
  
    Set rs1 = db.OpenRecordset("select * from " & SourceChild & " where " & strCriteria2 & ";", _
                    dbOpenSnapshot, dbReadOnly)
    With rs1
        If Not (.BOF And .EOF) Then
            .MoveFirst
          
            Set rs2 = db.OpenRecordset("select * from " & TargetChild & " where (1=0);", dbOpenDynaset)
            Do Until .EOF
                rs2.AddNew
                rs2(TargetChildFKName) = varNewPK
                For Each fd In .Fields
                    If fd.Attributes And dbAutoIncrField Then
                    Else
                        rs2(fd.Name) = fd.Value
                    End If
                Next
                rs2.Update
                .MoveNext
            Loop
            rs2.Close
        End If
        .Close
    End With
  
    Set rs1 = Nothing
    Set rs2 = Nothing
    Set db = Nothing

End Function

to use it:

Call fnCopyRecordMasterChild("tbl_Pon","tbl_StavkePon","PonID","PonID",<thePonID_Number>,"tbl_Rnt","tbl_StavkeRn","RnID","RnID")

note: untested.
Click to expand...
 
mike60smart

mike60smart

Registered User.
Local time
Today, 10:26
Joined
Aug 6, 2017
Messages
1,904
Hi
It would be a lot easier if you had a field for Status ( Quote or Order) and then enter a Date for each of the following as appropriate.
QuoteDate
OrderDate
 
KoskeKos

KoskeKos

New member
Local time
Today, 11:26
Joined
May 4, 2021
Messages
28
Hi.
Situation is next: tbl_StavkeRn (target child) is populated but tbl_RnT(target master) is not.
1626427568094.png


I use this for run:
Code: 
fnCopyRecordMasterChild("PonT","StavkePonT","PonID","PonID",[PonID].Value,"RnT","StavkeRnT","RnID","RnID")

Help.
 
KoskeKos

KoskeKos

New member
Local time
Today, 11:26
Joined
May 4, 2021
Messages
28
mike60smart said:
Hi
It would be a lot easier if you had a field for Status ( Quote or Order) and then enter a Date for each of the following as appropriate.
QuoteDate
OrderDate
Click to expand...
Thanks Mike 4 replay but now I'm getting confused cause I'm not excellent in VBA.
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:26
Joined
May 7, 2009
Messages
19,241
your have this:

fnCopyRecordMasterChild("PonT","StavkePonT","PonID","PonID",[PonID].Value,"RnT","StavkeRnT","RnID","RnID")

should it be:

fnCopyRecordMasterChild("PonT","StavkePonT","PonID","PonID",[PonID].Value,"tbl_RnT","StavkeRnT","RnID","RnID")
 
KoskeKos

KoskeKos

New member
Local time
Today, 11:26
Joined
May 4, 2021
Messages
28
arnelgp said:
your have this:

fnCopyRecordMasterChild("PonT","StavkePonT","PonID","PonID",[PonID].Value,"RnT","StavkeRnT","RnID","RnID")

should it be:

fnCopyRecordMasterChild("PonT","StavkePonT","PonID","PonID",[PonID].Value,"tbl_RnT","StavkeRnT","RnID","RnID")
Click to expand...
Its not that. I've changed names of tables...Sorry didn't mentioned before. :oops:
 
You must log in or register to reply here.

Similar threads

A
Query for Pass/Fail/Totals
Replies
5
Views
190
ashleymac
A
kstaelens
Transpose the result of a query and add it to another table
Replies
7
Views
278
kstaelens
kstaelens
K
Query based on multiple tables
2
Replies
21
Views
779
mike60smart
mike60smart
S
Solved I used to be able to copy objects from one database to another but now I get error code 3734
2
Replies
29
Views
660
spaLOGICng
S
A
Update the second table with the same item # and description using a query
2
Replies
22
Views
859
arnelgp
arnelgp

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom