Creating a table(table2) that keeps the old record of another table(table1)

[arizwan]

I have two tables table1 and Audit1. I am really new to VBA and so my question is it is possible if the user updates any of the fields in a row of table1 to make a copy of the old record in Audit1.

So for example I have 10 columns in table1 and if the user makes changes in a row to one or even multiple fields for that record I want just one record to be created in the Audit1 table with only the old fields form table1.

Can someone please help me with this I have searched for weeks and have been unable to figure this out.

Thanks

[twinnyfo]

arizwan,

To begin, you will want both of the Table to be identical, except that the Table1 will have an autonumber primary Key, and Audit1 will have no primary key, but just store Table1's primary Key in a field with a similar data type.

One idea would be to create Form-specific variables that would hold the values of the fields in the current record. Then, in the after update event of all of your controls, check to see if any of the values on the form differ from the values in those local variables. If any have changed, copy those variables to the new Table. and reset those variables to the current data on the form.

I hope this gets you pointed in the right direction.

[arizwan]

twinnyfo,

thanks for the quick reply. I have already set up the Audit1 table and Table1 exactly in the way that you stated. the thing is that table1 is being updated from an external application and thus I do not know if the use of form's would work.
Is their any other way like creating a data macro or something along those lines that would work for this solution?
Again I am extremely new to ms access and vba so I am not really sure about this.

[Rabbit]

If you're using a version before Access 2010, you would need to modify the external application to insert a copy of the data to the other table.

If you're using Access 2010 or later, you can try data macros. But I don't know if they run when it's being accessed through an external application.

[twinnyfo]

Even if Table1 does not have an autonumber primary key, there must still be a way for you to access this data in MS Access, correct? How are you accessing the data right now?

Using that same method, the VBA will merely act as a go-between.

Is the issue that you are having problems connecting to the data table? It looks like you can already access that Table.

Have you tried anything so far? We will not write the code for you but will walk you through steps to get you going in the right direction.

BTW, I always avoid macros, because VBA is so much more flexible and powerful.

Please provide more information to help us help you.

[arizwan]

Rabbit,
I am using access 2010 and the macro's are working with the external application. So my question now is how do I create a macro to fulfill my need since a simple after update macro will not work since I can have 2 field's on the same record being updated and that would create the same row multiple times in the Audit table.

Is their a way to create a macro that will loop through the Audit1 table when new records are inserted and delete the record if the record already exists?

[arizwan]

twinnyfo,

I have an ODBC connection with the ms access database and yes you are right I do already have access to table1 from my application and any changes made from that application are already being recorded in table1.

All, I want is to create either a VBA code or a macro that will run when a record in table1 is updated and that macro or vba code will create the exact old record in the Audit1 table.

Which way using the vba code or macro would you say can accomplish this task and out of the two which would be easier for someone like me who has no experience with VBA?

[twinnyfo]

I wouldn't know where to start with a Macro, so I say VBA. But if you know very little about VBA, then you have to stick with what you know. The key would be that before you move to a new record, see if anything has changed (keep in mind that someone may have modified a record--making it "dirty" without actually changing a record). If there is a change, then save the old values to the Audit Table.

Macros have problems with global and local variables, so you might be able to set these values using TempVars. There are topics out there on TempVars.

Otherwise, VBA can easily got through all the values of your record, check for differences, and save the values if anything has changed (see above).

If you are working with this table on a Form, it should be relatively easy to do--but without VBA experience, may be quite challenging. My apologies if we are talking over your head, but sometimes DB development is a completely different language (and VBA is a language...)

[Rabbit]

@twinnyfo, data macros are a new feature in Access 2010 that offers functionality similar to triggers in other DMBS'.

I don't have 2010 so confirm but I don't believe you can get the same trigger functionality in VBA like you can with a data macro.

@arizwan, I don't have 2010 so I haven't used data macros but what you want to do should be possible. But you shouldn't have two duplicate rows. If you make 2 separate updates, each row should only contain the values for one update each. They shouldn't be a duplicate of each other.

But in case they are, you should be able to use the data macro to run a query to delete the duplicate or run a query to see if there is a duplicate before inserting.