Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2004
    Posts
    44

    Unanswered: Saving data while NOT altering old data

    I used the search function, but nothing came up; in which case I apologize if this was asked somewhere before.

    We recently had a meeting and obtained some feedback on what will be required of the database beyond its current iteration. One thing that is being asked is to have forms that will enable data to be altered and saved, but at the same time for the source data to be unaltered, or saved as an older version.

    For instance, if my childs allowance for each day of the week is:

    Sun - $1
    Mon - $1
    Tues - $1
    Wed - $1
    Thurs - $1
    Fri - $1
    Sat - $3

    (Date 3/8/04)

    I need to be able to use a form, alter Monday to $2 and have the date field change. In this case (Date 3/9/04). However, I also want to be able to see what the old data was before it was changed.

    What I'm thinking is that I need to have 2 tables. One with the data that Form A will access, and another that would copy the data over from table 1 when Form A was opened? In other words, table 2 would be making a record of table 1 before any alterations were made to table 1 through the form.

    Does this sound plausible, or is there a better and easier way to do this? Or am I out of luck and stuck with getting whatever my changed value is?

    Thanks in advance!

    David

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Disconnected recordset and unbound form ...

  3. #3
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Question Re: Saving data while NOT altering old data

    Originally posted by Superman07
    I used the search function, but nothing came up; in which case I apologize if this was asked somewhere before.

    We recently had a meeting and obtained some feedback on what will be required of the database beyond its current iteration. One thing that is being asked is to have forms that will enable data to be altered and saved, but at the same time for the source data to be unaltered, or saved as an older version.

    For instance, if my childs allowance for each day of the week is:

    Sun - $1
    Mon - $1
    Tues - $1
    Wed - $1
    Thurs - $1
    Fri - $1
    Sat - $3

    (Date 3/8/04)

    I need to be able to use a form, alter Monday to $2 and have the date field change. In this case (Date 3/9/04). However, I also want to be able to see what the old data was before it was changed.

    What I'm thinking is that I need to have 2 tables. One with the data that Form A will access, and another that would copy the data over from table 1 when Form A was opened? In other words, table 2 would be making a record of table 1 before any alterations were made to table 1 through the form.

    Does this sound plausible, or is there a better and easier way to do this? Or am I out of luck and stuck with getting whatever my changed value is?

    Thanks in advance!

    David
    Do you just want to store just the last change to a particular record or all changes? And, do you want to store just values that are contained in certain fields or the whole record?
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  4. #4
    Join Date
    Jan 2004
    Posts
    44
    To be on the safe side I would say all changes as well as the whole record. We'll probably put in a field to make notes for explanations for why there were changes to the numbers, so we'd need that to be saved as well as any changes to the numbers.

  5. #5
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile

    Originally posted by Superman07
    To be on the safe side I would say all changes as well as the whole record. We'll probably put in a field to make notes for explanations for why there were changes to the numbers, so we'd need that to be saved as well as any changes to the numbers.
    If the fields contained in the record are going to be the same each time, which it sounds like they are, you can create a table as you suggested and just append the values through recordset operations. The question would be what would trigger the appending of a new record to your "Backup" table. I tend to like manual control over operations like that with a message to indicate that the record has changed but you could probably use the Forms BeforeUpdate event to trigger an automatic append if you wanted.

    Also, you mentioned the date. I'm not clear what you are trying to do with the date changing statement.

    Let me know if I can help.
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  6. #6
    Join Date
    Jan 2004
    Posts
    44
    I don't think the BeforeUpdate will necessarily work for the simple reason of the "date changing statement". I'll use my example above.

    Let's say the form has a field for each day of the week, and that each field contains a numeric value for the allowance given that day.
    There is also a field for "notes", and a field for "date changed".

    What I need to happen is for somebody to be able to go in and change, say Tuesday's value, write why they changed it, and when they save/close the form, the date needs to change to that day automatically if something was changed.

    Then, with the "Backup" table, I need to be able to view each change as a seperate entity, including what the values were before, and what the change was.

    Thinking off the top of my head here, could a auto generated report based on the change be more effective here instead of a "backup" table? That way there would be a report for each change? A potential problem that I see with that route though is, again using the example above, I'm dealing with multiple "kids", and potentially chaning the values frequently. That being the case the reports could build up a lot quicker than the "backup" table.

    I'd appreciate some thoughts.

    Thanks again.

  7. #7
    Join Date
    Jan 2004
    Posts
    44
    I tried running an append query and it did what I wanted it to, and more, which I don't want. But it also failed.

    I created the apend query for the table that I wanted to "backup" and pointed to the "backup" table as the table to append to. I then assigned the macro to run "On Load" for the form that draws its information from the original table. On opening the form I was prompted if I wanted to run the query or not. I clicked yes and the query ran and was appended to the table. This is where I come into my problems.

    First, is there any way to get rid of the prompt message?

    Second, why is the query appending all 14 items in the table? I only want it to append the item that is currently being viewed. Perhaps I should set the query to happen "On Close" instead of open? Or will this not change what item is appended?

    Third, when I opened the form a second time the query would not run corrently, and/or it overwrote what was already in the "backup" table. If it did indeed overwrite the backup files, how can I avoid this? Instead I wanted to add to the backup table ad infinitum even if the "records" are duplicated. Could conflicts with primary keys be casuing this? However, I would think if that is the case it would be the same problem even if I added items that have an autogenerate primary key. Would leaving the primary key field out of the query solve this?

    Thanks again for any help.

  8. #8
    Join Date
    Feb 2004
    Posts
    142
    If what you want is a change log that notes only the changes made then in Access you will have to work at it a bit.

    This kind of operation is fairly common for server-based tables for audit trails. The second table should not mirror the field definitions of the first table.

    It should be more like
    Record_ID = Record Primary Key of changed record
    DateOfChange = Date that record was changed
    FieldName = Name of field changed
    Reason = user entered reason for change
    NewValue = Must convert New field contents to a string
    OldValue = Must convert Old Field Contents to a string
    Datatype = Datatype of field (used in case code is used to rollback changes
    User = who did it
    and some primary key for this record

    if you need to test for changes you can loop through the controls in the form and compare .oldvalue to current value in control to determine what got changed.

    This is by no means complete but should give you an idea for a way to do it.
    KC

  9. #9
    Join Date
    Jan 2004
    Posts
    44
    I'm working on something right now, so I'll see if that works. If not I'll take a look at what you suggested on Monday.

    Thanks!

  10. #10
    Join Date
    Jan 2004
    Posts
    44
    Something else I just thought of. I may not have explained this in enough detail before.

    With my example above, if I change Tuesday's amount on Thursday, I want to see what Tuesday's was before and why I changed it (note section), but after making a subsequent change on Friday, I want to be able to see what the values were before for both Tuesday and Thursday, etc. I DO NOT want a record in the "backup" table to be overwritten. Maybe I shouldn't even call it a backup table, but more of a log.

    Don't know if that helps communicate better what I'm trying to accomplish.

  11. #11
    Join Date
    Feb 2004
    Posts
    142
    Originally posted by Superman07
    Something else I just thought of. I may not have explained this in enough detail before.

    With my example above, if I change Tuesday's amount on Thursday, I want to see what Tuesday's was before and why I changed it (note section), but after making a subsequent change on Friday, I want to be able to see what the values were before for both Tuesday and Thursday, etc. I DO NOT want a record in the "backup" table to be overwritten. Maybe I shouldn't even call it a backup table, but more of a log.

    Don't know if that helps communicate better what I'm trying to accomplish.
    The change log or Audit Trail I described above will document all changes made, whenever they were made. Each change should create a new record so that you have a complete history of all changes. On a server it is easy because you have update triggers on the table to work with.

    In Access you would probibly need to use the before update event or if you have a save button it might be easier there. In a bound form it will be a bit of a challenge to trap all senerios that could allow a user to post changes, such as moving from one record to the next (test the me.dirty property in the on current to prevent moving if something needs to be changed.) the form close event will have to check the me.dirty property and cancel form close until the changes have been documented in the log table.
    KC

  12. #12
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445
    Originally posted by Superman07
    Something else I just thought of. I may not have explained this in enough detail before.

    With my example above, if I change Tuesday's amount on Thursday, I want to see what Tuesday's was before and why I changed it (note section), but after making a subsequent change on Friday, I want to be able to see what the values were before for both Tuesday and Thursday, etc. I DO NOT want a record in the "backup" table to be overwritten. Maybe I shouldn't even call it a backup table, but more of a log.

    Don't know if that helps communicate better what I'm trying to accomplish.
    Along the lines of what AZ KC mentioned concerning when to log a record. I like to have an explicit command button or other means to control the addition of a new backup entry. If you use this method, you can prevent a change to an existing record without the addition of the new record by using a boolean flag of some sort. You could also enable/disable the button depending on the current state of the record, whether unchanged or changed. You could even verify whether the new record values were the same as the old record values in case the user changed them back to the original.

    Just some thoughts. I still like the recordset idea, but then again, that's what I use most.
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  13. #13
    Join Date
    Jan 2004
    Posts
    44
    I found the following on a wesite:

    Private Sub Form_AfterUpdate()
    Dim db As Database

    Set db = CurrentDb
    db.Execute "INSERT INTO [TableName] " _
    & " SELECT * FROM [TableName] WHERE " _
    & " [TableName].[TableID]=" & Me![TableID] & ";"
    Set db = Nothing
    End Sub

    However, due to the nature of what I am working with I have the following tables and need the following ot happen.

    Main Form, button click ---> Open Form A (linked to table A [Not Editable]), button click ---> Open Form A1

    Form A1 is what lets me alter data that is stored in table A, but when I open form A1 I want the current data from table A to be backed up to the "backup" table and the subsequent changes made to form A1 to become the data in table A.

    If the above code works, I would want that to serve as the "On Open" for form A1, but it would be in reference to Table A and the backup table, correct?

    Sorry, just trying to think through some stuff and I am rather exhausted.

    As usual, appreciate the feedback and thoughts.

    Almost forgot, if I am correct in where the code is supposed to be place, the part I am stumped on is how does "Set db= CurrentDb" fit in? Is this speaking of the entire database, or is it making the data to be changed between tables the "database" in question.
    Last edited by Superman07; 03-18-04 at 16:21.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •