Kopiere relaterede records med når specifik record kopieres

I understand what you want to do but I think a bit more info is neccessary before a solution can be given.

Is it only one record at a time from the main table which gets copied and then a number of related records from another table?

And the foreign key in the secondary table is the new autonumber (primary key) from the main table?

Is Contractnr=34 a way of identifying the main record other than the primary key (autonumber)?

What I am thinking of is if you can identify the record using say Contractnr then it will possible to find the newest record with that Contractnr by selecting TOP 1 and sorting DESCending on the autonumber field.

So now you have the OLD and NEW autonumbers.

Then you can open a recordset on the related table using the OLD autonumber and a recordset using the new. The new recordset will be empty from the start.

Now loop through the OLD recordset and add records to the new recordset making sure that all fields are the same appart from the NEW foreign key which you have.

Make sence?

Hi Terry
Yes one record and records from related sub tables.
Jeg bruger følgende til at finde NEW CONTRACTNR lige efter :

Set rstIdentity = CurrentDb.OpenRecordset("SELECT @@IDENTITY FROM TblCommonContract")
intRecordID = rstIdentity.Fields(0)
rstIdentity.Close
Set rstIdentity = Nothing


Jeg har 5 sub tables hvoraf kun en record fra en a tables er udfyldt.

Jeg regner med at lave en sub copyProperty2NewContract(ExistContractNr,intRecordID )

Mit umiddelbare problem med
for each f rs1.fields

er, at den første er et autonummer og skal ikke kopieres med, men det kan vel klares med et ... if fields(0) then nothing

Håber der er lidt klarere ( Du kan se, at jeg er ved at løse op på problemet)

OK so you have the new ID (autonumber)

So there are 5 other tables related to the one you have just copied?

For each related table I would open a recordset using the OLD (original) ID to find the correct records. You have to loop through this recordset to get the data for each field. If you know that the first field is always the foreign key then it should be easy enough to replace the original (old value) with the new autonumber.

So it sounds as though you are on the right track?

Ja - jeg tror, at jeg er ved at nå ind til kernen af problemet.
Skal blot have flikket det sammen og testet.
Terry - vil du have points ?

No keep the points yourself, I havnet been so helpfull here :o)

OK - jeg har fået styr på - her er løsningen om nogen kunne drage nytte af den:

Sub test()
    CopyContract2New 34
End Sub
Sub CopyContract2New(ExistID)

CurrentDb.Execute "INSERT INTO ...."

Set rstIdentity = CurrentDb.OpenRecordset("SELECT @@IDENTITY FROM TblCommonContract")
intNEWID = rstIdentity.Fields(0)
rstIdentity.Close
Set rstIdentity = Nothing

copyLOB2NewContract "Tbl1", ExistID, intNEWID

End Sub
Sub copyLOB2NewContract(tblname As String, ExistID, NewID)
  Dim cn As ADODB.Connection
  Dim rs As ADODB.Recordset
  Dim f As ADODB.Field
       
  'Use the ADO connection that Access uses
  Set cn = CurrentProject.AccessConnection

  'Create an instance of the ADO Recordset class, and
  'set its properties
  Set rs1 = New ADODB.Recordset
  Set rs2 = New ADODB.Recordset
  With rs1
      Set .ActiveConnection = cn
      .Source = "SELECT * FROM " & tblname & " WHERE (((Contract_ID)=" & ExistID & "));"
      .LockType = adLockOptimistic
      .CursorType = adOpenKeyset
      .Open
  End With
 
If Not rs1.EOF Then
  With rs2
      Set .ActiveConnection = cn
      .Source = "SELECT * FROM " & tblname & " where 1 = 0"
      .LockType = adLockOptimistic
      .CursorType = adOpenKeyset
      .Open
      .AddNew
  End With



    For Each f In rs1.Fields
        If Not f = rs1.Fields(0) Then
            rs2.Fields(f.Name).Value = f.Value
            'txt = txt & f.Name & ", "
        End If
    Next
    rs2.Fields("Contract_ID").Value = NewID
    rs2.Update
    rs2.Close
    Set rs2 = Nothing
End If
rs1.Close
Set rs1 = Nothing

'MsgBox txt
End Sub

dont forget to place an answer and acccept so that you get your points back :o)

you'r right, I might require your help in en weird problem in a complex form

BTW I have a productivity tip which might be helpful:

Complex forms load really slowly

Tools menu -> Options -> General tab page -> Name AutoCorrect. Uncheck Track name AutoCorrect info.