It would help if you could tell me exactly what you're working with, what the situations are, and what the code differences are that you need to execute. Yes, it's possible. We just need to figure out the context so I can best describe how you should handle it.

I have 2 tables, one where i record budgetting figures and one where i put the actual numbers. I want to secure that when a new budget record is created or changed the same record will be either created or changed in the "actual" table. I have four fields i use to identify the records. Its the year, headgroup, subgroup and purpose. Those are the four field i use when i create a new record in the "actual" table. For now it is done by a query which add the differences between the two tables - but that i a manual process and only handles the differences - not when i change, for example, a subgroup.

volgen :)

I still can't wrap my head around what you've got. Some screenshots would help, but it sounds like what you need is just a couple of buttons that will do what you want to do. Like 1 button that runs a little append query and copies the data that you want and the other table.

Access has a built-in way to check if a record is new or not.  A form's NewRecord property returns true if the current record is new, even if the user hasn't typed anything into the blank record.  But as soon as the record is updated and saved, it is no longer new, and NewRecord returns false.  So in order for this to be useful, you likely need to store NewRecord's value in a global variable.  See picture below as an example.  In the Current event, I retrieve the value of NewRecord for the current record.  After the record is updated, the AfterUpdate event is able to tell if the record is new or not.

This is a highly irregular way to pass data from one table to another, nor is it reliable.  What if the user enters a new record but doesn't finish it, saves it, then later comes back to finish the data entry.  By then, the record isn't new anymore (NewRecord will be false) and Access has no way to tell if it was once new.

The more common way to deal with two tables is via main form, subform, and relationships.  I suggest you find out if a relationship can be had between the two tables, then do it in a more traditional and reliable way.

Hi Kevin and Richard
@Kevin - Thank you for the answer - regarding user doesn't finish the record but saves it - thats why i ask if it is possible to detect if the record is changed.
If user saves the record without any content, as a new record, the record will be created in the second table without content, but if it is changed in the primary table then I would update the secondary table.
@Richard - Sorry but i dont want any buttons to initiate the code, i want it to be initiated when the record is created or changed.

How i see the process:
1. When i enter a NEW record I note that is a new record, as Kevin mentioned.
2. When the record is saved (or more precisely, before the save) I get the values from the four field and then the record is saved.
3. Then i create a new record in the secondary table based on the values from the four fields.

CHANGED record:
1. I note that this is not a new record, why the process for a new record is not executed.
2. When changes are made, and before saving, I get the values from the four fields and then the record is saved.
3. Then i locate the record in the secondary table and save the changed values

For that i would need the possibility to check if it is new record (Kevin solved that) and the possibility to check if the record have been changed. I would also need the event(s) to use and when.

From the above i think i can create the code and eventually put it here for your comment.

Why not just relate the tables?
Add the Primary Key from the first as a Foreign Key in the second, then you don't need to be copying any info and can just create a Query to get the data.

Hi Alex
I allready get the data with a Query, but it is a manual process and i would like the copying to happen when the new/changed data is saved.
The Query i have created so far, is only locating the difference between the Primary and secondary table, and then create the differences in the records. I have not figured out how to update the changes in the primary table to the secondary table.
I see that the manual process of running the query is missed sometimes, resulting in differences between the tables.

Can you share an example so we can picture it.

For instance Customers and Orders.

Sorry - what kind of example ?

Can you relate it to something that we understand so we have some context? Just saying the primary and secondary table doesn't help us visualize what you've got going on. If you could relate it to something everyone can understand, that would be helpful. We don't know your business or what you're trying to do. See All About Context

Sorry for not being able to explain better and the length of this message - i have now tried myself and have come to the result below. I have the part working that copy the four fields to the table "FaktiskBudgetposterT" when it is a new record, but when I try to make the code work for updating (change) an existing record, the code stop at this line : rstFaktisk.FindFirst "UndergruppeID = " & Subgroup & " AND Budget'r = '" & BudgetYear & "' AND Hovedgruppe = " & Headgroup & " AND Budgetomr'de = " & Budgetarea & " AND Budgetposttype = " & Budgettype and i get the errormessage : Run-time error '3070' There is an syntax error, because an operator is missing in an expression
In order to go on i need someone to explain what is wrong with errorline ?

Here is the code:
First i declare a variable (is this a global variable when at the top of all code ?)

Option Compare Database
Dim blnNewRecord As Boolean

Then i set the variable blnNewRecord

Details

Private Sub Form_Current()
    ' Check if it is a new record
    If Me.NewRecord Then
        blnNewRecord = True
    Else
        blnNewRecord = False
    End If
End Sub

Then i have this code for creating/changing a record in the table "FaktiskBudgetposterT"

Details

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim db As DAO.Database
    Dim rstFaktisk As DAO.Recordset
    Dim BudgetYear As String
    Dim Headgroup As Integer
    Dim Undergrp As Integer
    Dim Budgetarea As Integer
    Dim Budgettype As Integer
    
    Databasesti = DLookup("[Databasesti]", "[Initialiseringdata]")
    
    Set db = OpenDatabase(Databasesti)
    Set rstFaktisk = db.OpenRecordset("FaktiskBudgetposterT", dbOpenDynaset)
    
    Undergrp = UndergruppeId
    BudgetYear = Budget'r
    Headgroup = HovedgruppeDD
    Budgetarea = Budgetomr'deDD
    Budgettype = Ramme71


    If Me.Dirty Then
        ' Data was changed
        If blnNewRecord Then
            ' Code for new record
            With rstFaktisk
                .AddNew
                .Fields("UndergruppeId") = Undergrp
                .Fields("Budget'r") = BudgetYear
                .Fields("Hovedgruppe") = Headgroup
                .Fields("Budgetomr'de") = Budgetarea
                .Fields("Budgetposttype") = Budgettype
                .Update
            End With
        Else
            ' Code for existing record
            ' Find record based on kriteria
            ' below the codeline with the error
            rstFaktisk.FindFirst "UndergruppeID = " & Subgroup & " AND Budget'r = '" & BudgetYear & "' AND Hovedgruppe = " & Headgroup & " AND Budgetomr'de = " & Budgetarea & " AND Budgetposttype = " & Budgettype ' (This is one line in the code)

            ' check if record is found
            If Not rsFaktisk.NoMatch Then
                ' Record was found
                With rsFaktisk
                    .Edit
                        .Fields("UndergruppeId") = Undergrp
                        .Fields("Budget'r") = BudgetYear
                        .Fields("Hovedgruppe") = Headgroup
                        .Fields("Budgetomr'de") = Budgetarea
                        .Fields("Budgetposttype") = Budgettype
                        .Update
                End With
                MsgBox "Dataene blev gemt med succes.", vbInformation
            Else
                ' Record was not found
                MsgBox "Ingen matchende record fundet."
            End If
        End If
    End If
    
End Sub

Please disregard the above post - rookiemistake - wrong variablename (Subgroup instead of Undergrp)