Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2006
    Posts
    25

    Unanswered: Access 97-Archiving records to a separate table.

    Hello All.

    I built a form that pulls info from a table. I wanted to archive the record that is to be edited onto an archive table and allow users to update afterwards.

    Is there a way to copy the original record (before edits) and paste it into an archive table by way of a click() and then allow the others to make edits after securing the original info? This has been bugging me all week.

    Thanks in advance.

  2. #2
    Join Date
    Jul 2004
    Location
    Blackburn, UK
    Posts
    169
    Create a table with the exact same table structure and upon record creation (Completion) trigger an event that appends that line only to the archive table.

    Either create a query that appends the line or create the sql query.

    If you are not very Access clued up i suggest the query creation and if you dont know VBA then create a Macro that opens and actions the append query.
    Based on DAO 3.6 and Access 2000 + 2003
    VB.Net 2005 + ADO.Net

  3. #3
    Join Date
    Jun 2006
    Posts
    25
    Thanks ChrisGolden for the quick reply.

    What's getting me is the "trigger." Is there a way to "trigger" the record that is SHOWING on my form to append to the archive table?

    Say that I have 200 records but I want record 176 to be appended (copied) to the archive table without having the user to enter the specific seq number as a criteria, then allow the user to update or edit the original seq 176 afterwards. Kind of like looping on and on so that I can keep track of all the changes.

    I have thought of duplicating the record and do a find duplicates (excluding my primary key(seq)) and taking the MIN of date and append that to my archive table and then deleting it from my original and reassigning it the original seq. There has to be an easier way to protect my original record prior to an edit without all those queries in the background.

    Any more thoughts?

  4. #4
    Join Date
    Jul 2004
    Location
    Blackburn, UK
    Posts
    169
    When you create an append query there is a criteria under it, make the criteria something like "Forms!FormUserIsWorkingOn!ID" and put this in the ID criteria

    The thing is that you would have to make some code that checks to make sure the record has not already been placed into the table.

    My suggestion is giving it a revision number. On completing the form the revision number increments by one, it originally is set to zero, make some code that checks if the revision number is equal to zero, if it is then append the record.

    The thing you have to remember is, the data has to be saved before it can be appended.

    What are you like with code?
    Based on DAO 3.6 and Access 2000 + 2003
    VB.Net 2005 + ADO.Net

  5. #5
    Join Date
    Jun 2006
    Posts
    25
    I am decent with code, considering it has been 5 years ago since I've last used vb. I'm not sure what you meant when I put "Forms!FormUserIsWorkingOn!ID" in the criteria.

    Do you mean, when I want to edit a specific record, I can should a duplicate record in the same Table and it's new increment is set to 0? By that I can set my query criteria to pull the item that is equal to 0. The #0 record is archived but that brings the user back to the duplicated record in the Original Table/Form. I guess the user can either delete that or edit it from there and I will have a save cmdButton that saves it and updates it with the orginal seq number? Does that sound too complicated?

  6. #6
    Join Date
    Jul 2004
    Location
    Blackburn, UK
    Posts
    169
    The revision number really is for the benefit of the archive procedure.

    If the revision is equal to zero then arichive it to another table (Append) and if not then just increment the revision number, you also know then how many times it has been revised.

    The Forms!BlahBlah is where it refers to the reacord that you want to append to the archive table so that is only archives that record.

    If you have written queries before you will notice a criteria field in the query. The criteria for the record ID needs to be filled in with that Forms!FormUserIsWorkingOn!ID, this basially tells the query to look on a certain form at a certain field to tell it what record you want to archive.

    Say the form is called ArchiveMe and the ID is just ID it would be

    Forms!ArchiveMe!ID

    Like i say, this will just narrow the query down to that record and append that record to another table
    Based on DAO 3.6 and Access 2000 + 2003
    VB.Net 2005 + ADO.Net

  7. #7
    Join Date
    Jun 2006
    Posts
    25
    Thanks Chris for all your assistance.

    Got one more (two part) question. Say I got all those working..... Is there a way to GRAY the records as users scroll through the records and only allow users to update/edit when they click the duplicate cmdButton? Also give them a confirmation message box after they save or continue to scroll through records saying, "Do you want to save this?"

  8. #8
    Join Date
    Jul 2004
    Location
    Blackburn, UK
    Posts
    169
    You can set the fields to enabled = false

    When the click is clicked then run some code like this

    control1.enabled = true
    control2.enabled = true

    Where control is the name of the field that you want to enable

    Also when you have finished editing the form run some code like this
    If Me.Dirty = True Then
    If MsgBox("Do you wish to save the record", vbYesNo + vbQuestion, "Save?") = vbYes Then
    DoCmd.Save
    Else
    'Some code to undo all the changes that have been made in that form
    End If

    I would suggest getting you head back into the code, dont be afraid to ask for help or ellaboration on this

    I hope this puts you on track to what you need to find a solution
    Last edited by ChrisGolden; 06-06-06 at 16:16.
    Based on DAO 3.6 and Access 2000 + 2003
    VB.Net 2005 + ADO.Net

  9. #9
    Join Date
    Jun 2006
    Posts
    25
    This puts me right on track. Thanks for all your support.

  10. #10
    Join Date
    Jul 2004
    Location
    Blackburn, UK
    Posts
    169
    Sorry that code should be

    If Me.Dirty = True Then
    If MsgBox("Do you wish to save the record", vbYesNo + vbQuestion, "Save?") = vbYes Then
    DoCmd.Save
    Else
    'Some code to undo all the changes that have been made in that form
    End If
    End If
    Based on DAO 3.6 and Access 2000 + 2003
    VB.Net 2005 + ADO.Net

Posting Permissions

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