Help, Master! How to write VBA to compare between 2 tables and update the old one

[Louislam]

Hi,
Here is a problem frustrating me and I decide to look for help here:

I attached a demo for your reference:View attachment Update Old Table.accdb

The Relationship structure is like this:


There is a query "Style_Color_Materials_New" functions as New data and a Table "Style_Color_Materials_Old" as old records.

With a Form "Main", there shows two Subforms - "Style_Color_Materials_New" and "Style_Color_Materials_Old", and a button named "Update", pic as below:


What I want to do with this update button is by comparison of "Style_Color_Materials_New" and "Style_Color_Materials_Old" using "Style_Color_No" and "Style_Materials_No" as criteria:
1, Insert new records into table "Style_Color_Materials_Old"
2, Delete no longer exit records from table "Style_Color_Materials_Old"

How to write a VBA code for this button to materialize what I want?

MANY THANKS FOR YOUR HELP!!!!!!!!

 

[namliam]

You typicaly wouldnt do that with VBA, you would do it with queries.... (possibly triggered to run from vba)

Just run an unmatched query the one way to add new records and an unmatched the otherway to delete them.
Then all you do in vba is:
Currentdb.Execute "YourAppendQueryName"
Currentdb.Execute "YourDeleteQueryName"

 

[Louislam]

Many Thanks namliam,
I just read this link, like you say, It may work.

office.microsoft.com/en-us/access-help/compare-two-tables-and-find-records-without-matches-HA010205132.aspx

Now I have just created two unmatched queries for Append and for Delete purpose, but how to write the Append and Delete SQL to do so? I am not good at coding ;P

 

[namliam]

The step by step process is described in the link... however you dont need to "write" any SQL at all... It is just a simple few clicks and drags in the query designer....

 

[Louislam]

Hi, namliam. Please see the pic, since the query that compares with the old table has no primary key, even though I create an unmatched query to find out the records, I can not delete them. What should I do?

 

[namliam]

You can still delete anything depending on anything, no real need to have a pk just to append or delete.

I guess the combination of the two fields would be your "key" or identifier.

 

[Louislam]

Thanks namliam, anyway I have used a stupid way to make it.
Many thanks

 

[stopher]

I think I would be inclined to have only one table for both old and new records and just have an OldNew column indicating whether the record is "old" or "new". By doing this, moving from new to old becomes trivial as you only need to change the OldNew column value from new to old for the relevant records.

hth
Chris