Copy record to different table

[Patience]

Hi,

I have two tables with the same key "Item number".

When I create a new record in table 1, I want the same record (Item number only) to be created automatically in table 2.



How can this be done?

Thank you very much in advance!

[RuralGuy]

I would be remiss if I did not mention that having two tables with identical records seems to be a normalization violation. What is the purpose of Table2?

[Patience]

I agree that this is not a normal situation. However, I am dealing with a very complex product which requires A LOT of fields in order to describe each item number. Therefore it has been necessary to create two tables which both describes the same item number. Of course the fields in each table are different from each other.

When a new item number is created in table 1, I want this item number to be created in table 2 at the same time. After completing putting data into table 1, I can then start putting data into table 2 without first having to create the item number. I can just do a simple search. It should be mentioned that the item number is a complex combination of letters and numbers, so it would really ease the process if the item number was automatically created in table 2.

I hope you have a solution to this particular issue. Let me worry about the unnormalized situation

[RuralGuy]

I assume you are creating the new record in a form. The BeforeUpdate event of the form would be the place to create the new record in Table2 when Me.NewRecord was true.

[Patience]

Yes, Table 2 is located in another form.

I am not strong in programming, so I would be VERY happy if you could write the entire coding here.

Thank you very much in advance!

[kimmer]

Keep in mind there are many many fields.

She said yesterday that she split her 400 fields across 2 tables because she hit the maximum number of fields in a single table.

[RuralGuy]

Just for the record, tables are not "in" forms. Forms are simply a "window" into a table. Your forms should be using a query of a table as the RecordSource of the form and not the table directly which can cause locking problems. What is the name of the Field with ItemNumber in Table 2 along with the actual name of Table2 and what is the name of the Control on the current form that contains this ItemNumber.

[RuralGuy]

kimmer: It is obviously two tables with a 1:1 relationship and I will have to take the posters word for it that it is necessary. All we will be doing is adding a new record to Table2 and filling in the ItemNumber field in that table.
Hmm...you made me realize that if the relationship has been defined then we will probably have to use the AfterUpdate event of the form rather that the BeforeUpdate event. A little more complicated but not tough.

[RuralGuy]

kimmer: I forgot to thank you on pointing out the other thread. Thank you!

[kimmer]

You're welcome.

[Patience]

Hi guys,

So I finally found this piece of SQL which actually does the trick:

Dim SQL_Text As String

SQL_Text = "Insert into Tabel2 ([Item number]) Select [Item number] from Tabel1;"
DoCmd.RunSQL (SQL_Text)

This is great. However, each time this code runs it will add all item numbers from table 1 to table 2. This of course gives a lot of errors from Access since all exept the new item number already exists in table 2. How can I tweak this to only add a record to table 2 if it does not already exist???

Furthermore, I would like to be able to edit an item number in table 1 after it has been created both in table 1 and in table 2. When the code runs, the corresponding item number in table 2 must be updated with the changes. Now it just adds a new item number in table 2, which I do not want.

[Patience]

Forgot to mention that when I delete the record in table 1 it must be deleted in table 2 as well.

I really hope that you can figure this one out.

[RuralGuy]

You are going to want something like this:
SQL_Text = "Insert into Tabel2([Item number]) Values(""" & [Item number] & """);"
...in the AfterUpdate event of your form if you just created a new record in Table1.

[Patience]

Thanks Rural!

Works perfect. How do I switch off the "You are about to add a new..."? (My version is in Danish so I do not know what the pop-up says in English)

Do you have a solution for the other issues as well? Edit and deleting posts?

[RuralGuy]

If you will use CurrentDB.Execute SQL_Text, dbFailOnError
...you will not get those warnings. Deleting is just as easy.
"Delete * From Table2 WHERE [Item number] = " & [Item number]