~~Help please~~

[Geordie2008]

Afternoon all,

I have a form with 7 columns of data. When a user updates any part of this data I want to repopulate a new row in my table, leaving the old one untouched.

Access usually updates the row directly in the table, how do I prevent this and keep the old and keep the new?

Im really confused as I thought this would be an obvious one....
Mandy

 

[namliam]

Try doing a search on both "Unbound" and "audit trail" both are ways of beeing able to do this one way or another...

Good luck !
(you will probably need it!)

P.S. Great title !

 

[missinglinq]

If you update an existing record, you update an existing record! If you want a new record you have to create a new record. You cannot alter an existing record and instruct Access to save it as a new record.

Perhaps if you explain exactly what you're trying to do we can help you. There are ways of copying the data from existing records to new records.

 

[Geordie2008]

I need to be able to track changes users make to their details.

e.g. for every staff member (who has a unique StaffID) they may change building / desk / team / leave / move internally / go from Full Time to Part-time etc.

I need the user to be able to update their details, and store the update in a table, but I also need to be able to store what the details were before so I can run reports saying:

Of all the staff that were FT in Dec-06, 95% are still FT and 5% are now PT.

I was aiming to do this by timestamping all changes to the database, hence having the following:

Mandy|Smith|FT|25/01/2008
Mandy|Smith|PT|28/03/2008

I just want to have 1 table with the old data and the updated data in it....

I keep searching on "audit trail" etc.... but its not actually an audit trail that I need as such (at least not the ones that I have been able to find on this forum using the search function)

To give a longer example:



Mandy|Smith|London|Holborn|Desk2A|FT|Active Employee|01/02/2007
Mandy|jones|London|London Bridge|Desk15C|FT|Secondment Out|01/12/2007
Mandy|jones|London|London Bridge|Desk16C|FT|Secondment Out|01/01/2008
Mandy|jones|London|Holborn|Desk2C|FT|Active Employee|25/02/2008
Mandy|jones|London|Holborn|Desk2C|FT|Career Break|26/03/2008

The time stamp is the date that the amendment was made in the form by the user.

I will populate the details in the form by using the max function on the timestamp date...

If anyone can let me know a link or a previous post that does something similar I'd be v grateful.... I have been searching through posts for hours and cant seem to find anything similar to this.... most people want to just log what field has changed and not populate the whole row of data into the table.

Im happy using union queries if I need to have a "new" and a "changed" table.

Also - I do need to be able to add in new employees.... although at the moment... Im happy focusing on this issue first!

Thanks,
Mandy

 

[namliam]

This IS an audit trail tho maybe an audit trail is not what you perse want...

You could do something with the "on dirty" event of the form...
On dirty is triggered when a change is made, then simply run a query to "send" the original record (which is still in the table at that point) into a seperate "changes table"

 

[neileg]

The way I would do this is to copy the existing record to a temporary table and open a form bound to that table. On completion of the changes, I would then write the record back to the main table with the approriate time stamp.

I know you can do this using an unbound form but I have a preference for SQL over VBA!

 

[missinglinq]

Everyone needs to be aware that this is the second thread started by Geordie2008 on this subject in less than 24 hours.

http://www.access-programmers.co.uk/forums/showthread.php?t=146252

 

[Geordie2008]

Hi missingling - sorry about double post - I though I wasn't explaining myself very well, hence tried a second time. The penny has dropped that this will not be as simple as I had thought it would be however. Apologies.

Neileg - I followed through your idea and I have split it down into several steps...

Have 2x tables:

tblDataCurrent
tblDataHistoric

To amend a current row of data within the database (as opposed to adding a new record):

frmAmend_Current is bound to the tbl_DataCurrent. It is populated by this table. As soon as a user opens the form (OnOpen) I will write / find some code that will copy the selected row of data and write it into tblDataHistoric keeping all of the fields populated exactly as they were (I will use a combination of keys on Staff Number and Record_Date (timestamp - which will be when the row was last updated - not todays date) so that this table can accept multiple records per employee).

Any changes the user makes will be populated back to the tblDataCurrent table, which I can then query for most up-to-date record values.

For change on month / year data I can union the two tables and run reports off this....

Would you recommend this as an approach?
Thanks again,
Mandy

 

[neileg]

Mmmm... No, I wouldn't, though it will work. I think I would have only one table. The most recent timestamp will identify the current record for each Staff Number, or you could have a flag that is set to show the current record, in case the clocks on your work stations are not syncronised.

 

[namliam]

Mmmm... No, I wouldn't, though it will work. I think I would have only one table. The most recent timestamp will identify the current record for each Staff Number, or you could have a flag that is set to show the current record, in case the clocks on your work stations are not syncronised.

I would tho...
The advantage of having 2 tables is you dont have to worry about "current" record.... Current = one table... not a part of it.

By adding a autonumber to the history table you can make sure you can get the changes made in the proper order.

 

[Geordie2008]

Ahh..... I think I am with you now....

By taking a copy into a temp table, I am leaving the old record alone and happily sitting in the "main" table... hence I cant override it when I write back the "new" record from the temp table to the main table.

Is that what you mean?

And I will have to set up a mixture of "time-stamp" and "staffID" as keys on the table?

.... Im starting to see the light at the end of a long and dark tunnel! (If Im understanding you correctly!)

Mandy

 

[neileg]

Namlian, I don't disagree. I would prefer one table if there is a need to use current and historic data for reporting, which the OP suggests there is. Down to personal preference rather than a right and a wrong way.

 

[neileg]

Ahh..... I think I am with you now....

By taking a copy into a temp table, I am leaving the old record alone and happily sitting in the "main" table... hence I cant override it when I write back the "new" record from the temp table to the main table.

Is that what you mean?

Yup

And I will have to set up a mixture of "time-stamp" and "staffID" as keys on the table?

If there's a chance that you could have two users attempting to save an amendment to the same staff number, yes. Otherwise I wouldn't. I have a strong preference for meaningless keys (autonumber).

.... Im starting to see the light at the end of a long and dark tunnel! (If Im understanding you correctly!)

Mandy

That light is attached to the train heading in your direction...

 

[Geordie2008]

Mmmm... Uber unsure now.... although in a good way as now I have 2 solutions rather than none!

As I am a newbie to all of this and I have to next generate "Access Reports" (so a different forum will be bored by me!) are either of these solutions easier to set-up / maintain or easier to write reports from? Although I'm tempted to go crazy and give both a whirl....!

Many thanks for support with this,
Mandy

 

[namliam]

Namlian, I don't disagree. I would prefer one table if there is a need to use current and historic data for reporting, which the OP suggests there is. Down to personal preference rather than a right and a wrong way.

I agree, it is mostly a personal thing... there is more than one way to skin a cat right

.... Im starting to see the light at the end of a long and dark tunnel! (If Im understanding you correctly!)

I think you got the picture... kindoff

I have a strong preference for meaningless keys (autonumber).

That must be the understatement of the month!

Rule #1: Allways use a meaningless primary key
Rule #2: Yes rule one says ALLWAYS !

I have been burned to many times by "natural keys" that have been "guaranteed" by users to be primary keys... to only find out later on months even years later that it isnt and offcourse the customer feels burned by me ! Because I made a crappy product as per HIS request.
Autnumber for sure!

 

[namliam]

Mmmm... Uber unsure now.... although in a good way as now I have 2 solutions rather than none!

As I am a newbie to all of this and I have to next generate "Access Reports" (so a different forum will be bored by me!) are either of these solutions easier to set-up / maintain or easier to write reports from? Although I'm tempted to go crazy and give both a whirl....!

Many thanks for support with this,
Mandy

Each of the solutions have their own problems... as allways there are 2 sides to the same coin.

One table => Easier to report and stuff, harder to operate and do maintenance (in my oppionion only offcourse )
Two tables => Exactly the reverse.

 

[Geordie2008]

I may have a further complication....

When the user updates a field..... I need to restrict the entiry they are allowed to make....

e.g. if they change from being Part-Time to Full-Time....The following values are the only acceptable ones....

FT
PT

I cannot allow the user to free-type Part-time otherwise any reports I run off the data will be useless.....

I know you can use combo-boxes... but I thought you could only do this for a new field and not an amendment to a field....

I hope Im wrong..... otherwise....that train is speeding towads me....!

Mandy

 

[neileg]

Combos work all the time. They are part of the form, not the table.

Please, please, please don't use table level lookups. They seem to be an easy way of having combo box functionality, but they cause huge problems.

As for one table/two tables, namliam and I are on two sides of the fence. Both will work, both have ups and downs. You choose!

And if you need a reason for choosing, I'm a Geordie!

 

[Geordie2008]

Latest silly Q:

What are tables level lookups? Im kind of hoping that they are not where you have several tables which the combo box looks up to as this is exactly what I had in mind....

How do table level lookups cause problems? and what is the alternative... would it be setting them directly in the properties section of the combo box?

Oh and Im really chuffed I didn't set my usename as mackem (aside from the fact I'd have been disowned!)

M

 

[neileg]

Table level lookups are defined in the table properties and are not part of the combo definition in a form. They cause problems in lots of ways but it is mostly because you get the result of the lookup when you query the data, not the data itself. If you don't know what they are, keep it that way!

I was actually born in Sunderland...