Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Feb 2003
    Location
    Texas, USA
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    2 Identical Tables - 1 for a History (Access 2002)

    I need to keep track of all transactions on 1 (storage) table so, therefore, I have copied that table structure for my 2nd "storage HISTORY" table. I have a subform for users (approximately 10) to input into the 1st storage table. As users add, edit the subform (1st table), I need a button that then copies that into my 2nd (storage history) table. I don't have experience with VBA code but I fear that it's needed somewhere. I tried creating a command button to copy the record but only see that I can copyobject or copydatabasefile not copy record from 1 table to another. I then tried an append query but that would require a user to run that often to keep the history table current. I need both tables current. The ideal situation would be to have an "Update" button that the user could click that would copy the record in this subform into the History table. Perhaps I'm going about this all the wrong way?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 2 Identical Tables - 1 for a History (Access 2002)

    Unless your table has a huge number of records, I would stick to one table, and add a Yes/No field named History. Put a check box on the form bound to this field. The user only has to tick the check box to indicate that the record is history.

    For forms and reports that should only display the "current" records, you would use as record source a query based on the storage table with False as criteria for the History field, and for forms and reports that should display the "history" records, you would use as record source a query with True as criteria for the History field.

    This is much easier than moving records between tables, and it doesn't require any VBA code.

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: 2 Identical Tables - 1 for a History (Access 2002)

    I would follow Hans' advice, but the strategy you proposed isn't all that difficult to implement either. If you choose to keep two tables, then you want to add a flag field to each record (a Yes/No field) that indicates whether a given record has been copied to the history. Following that, create a query that appends any record that has the flag set to No to the history table. Finally, run a second query (update) that sets the flag for the current transactions table to Yes. All this assumes that you run that process when noone is adding records. If that can't be done, then the problem does get more complex, and you would probably end up needing to use joins to make sure you didn't mark a record as transferred that had been added after you ran the append query. Note that Hans' solution doesn't face any of those problems, so it's a more elegant approach.
    Wendell

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: 2 Identical Tables - 1 for a History (Access 2002)

    I don't understand the purpose of your History table. How does it differ from the regular table?

    It seems to me that you just have 2 tables that you are trying to keep identical up to the second. To make it a user option is a mistake, as you can't rely on the user to always remember. You will have to do it in code, meaning EVERYWHERE that the first table can be maintained (add/edit/delete) you will need to have code to keep the history table updated.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    New Lounger
    Join Date
    Feb 2003
    Location
    Texas, USA
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 2 Identical Tables - 1 for a History (Access 2002)

    Mark you're absolutely right - I was going about this all the wrong way and I definitely can't rely on the users (possibly up to 10) to remember. HansV has provided me with a much better approach - thanks HansV - you're a life saver!!! WendellB thanks for reaffirming HansV's solution because I can't do the second query when no one is on. Thanks to all for helping!!!

Posting Permissions

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