Update Query 1

[mespa]

Hi All

Getting into more Access applications ,

I have a table Current and will duplicate it as History .

fields = name hiredate status etc.

Once any field has changed I need to update the History table , Data is being entered or change in a form. Not sure how to handle this, but know that it can be done.

Any tips in the right directions would be appreciated

 

[dhookom]

You could use the After Update event of the form to append a record to your history table. BTW: "Name" is a bad name for an object since every object has a Name property.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[mespa]

Thanks dhookom,

I was just giving a quick example,, NO !! none of the fields are just titled name ):>, It is Lastname, Firstname etc.


Thanks again

 

[mespa]

Need to append to this my last reply.

Would it really be an Append Action??

If any changes on the record in the Master table , I need that change to update in the History table.

We want to keep a tracking report on what changed and I want to add a timestamp .


Thanks again to anyone who can help me.

 

[dhookom]

It isn't clear whether you want to keep a history of all changes or just keep two tables synchronized. If you want to track all changes then you will need to append records/values to the history table.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[dhookom]

It looks like our messages passed each other on the internet highway.
The easiest method would be to pass the entire record to the history table every time it is changed. This includes the first time the record is created. You can add a field in the history table that defaults to Now(). To determine which field values change, compare the previous record field values to any other record field values.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[mespa]

Happy Monday !!!
Duane

I want to do this by way of Form , Once User updates Current table, Update is done in History.

Now, my question is , Will I do this on the form properties and on each field property ?

thanks

 

[dhookom]

My first post suggest which "event" can handle changes to the record.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[mespa]

Hello
My task for creating an append query was put on hold.. Now a month later I am attempting it again.

It is possible for someone to just give me some sample code on: the task at hand.

I need to Update the "Status" in the History Table if
Status changed in the Employee Table; and move Status to Previous Status in the History Table.

Any tips would be highly appreciated.

Thanks

 

[dhookom]

In the after update event of the form, you could run code like:

   Dim strSQL as String
   strSQL = "INSERT INTO tblHistory (fieldA, FieldB, FieldC,...) " & _
       " SELECT FieldA, FieldB, FieldC,... " & _
       " FROM tblMain WHERE PriKeyField = " & Me.PriKeyField
   DoCmd.SetWarnings False
   DoCmd.RunSQL strSQL
   DoCmd.SetWarnings True

YOu can have a date/time field in the table tblHistory that has a default value of Now().

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[mespa]

dhookoom
Thank you so very much . I brought the work home, and I am happy that i was on the right track.

Thanks again for your knowlege and support