Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Archive clients (Access 2000)

    I have a table called TblClients.In this table i have a field called Obsolete.I want to make a function that when
    If Me!Obsolete = True , to copy the whole row to the table TblObsoleteClients and then to delete it from the table TblClients.How to do it from the form ?
    The two tables are identical and the autonumber is ClientID

    I use the following function :
    Public Function XferObsolete()
    Dim db As DAO.Database, SQL As String

    Set db = CurrentDb

    SQL = "INSERT INTO tblObsoleteClients " & _
    "SELECT * " & _
    "FROM tblClients " & _
    "WHERE [Obsolete] = True;"
    db.Execute SQL, dbFailOnError

    DoEvents

    SQL = "DELETE [Obsolete] " & _
    "FROM tblClients " & _
    "WHERE ([Obsolete] = True);"
    db.Execute SQL, dbFailOnError

    Set db = Nothing

    End Function

    However, when I choose for example 3 clients to be dcopied and dleleted, only two or them are copied and the last on stays, even though it is marked with obsolete.I apply an example where this could be sen. For example, if ou mark the clients number 36,39 and 41, the last number 41 stays.
    How could I mend this, and is there a better way to archive my obsolete clients?
    Attached Files Attached Files

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

    Re: Archive clients (Access 2000)

    It isn't necessary to have a table TblObsoleteClients and to move records to that table.
    You can create a query based on TblClients that selects the records for which Obsolete is False, and use that as record source for forms, reports etc. You'll only see the "active" clients in those forms and reports.
    When you tick the Obsolete check box for a client, it will disappear from the forms and reports.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Archive clients (Access 2000)

    Thank you ! I thought of that idea but it seemed to me it might be better to discard that clutter in the table which will never come into use again and thus have a smaller table. What do you think , is it better or not ? I will follow your advice anyway

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

    Re: Archive clients (Access 2000)

    It depends. In most situations I would leave the obsolete records in the original table, because it makes it easier to view historic data.
    But if you really want to archive the obsolete records, I'd create an append query to add the obsolete records to the archive table, and a delete query to remove them from the clients table. You can deisign these queries in the queries section of the database window. You can run them, one after another, from time to time, say once or twice a year.

Posting Permissions

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