Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Archive data (Access 2000)

    Can you give an idea how should i proceed with a table for archiving the outlays in a separate table under a criteria for example lastupdated, or something like that.After that i would like to have the data archived per months
    I think i must a special table to this end

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

    Re: Archive data (Access 2000)

    Unless your database becomes excessively large (and I mean millions and millions of records, not a few thousand), there is no need to archive data. If you don't want old data to clutter up forms and reports, you can always use queries to hide records older than a specified amount of time.
    You'll need to add a Date/Time field LastUpdated to tblData.
    In the form based on tblData, you can set this field in the Before Update event of the form:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.LastUpdated = Date
    End Sub

  3. #3
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Archive data (Access 2000)

    Thank you Hans.I have two questions

    1. How could i add on the different sums for the outlays since the form frmData does not allow duplicate data.For example i cannot add the outlay for water a second time for one and the same office
    2. How can i open the report from the form frmGain for a given month with the corresponding LastUpdate correcions ? When opening the form i must somehow diferentiate the data from the lastupdate field

    regards

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

    Re: Archive data (Access 2000)

    1. You should add another Date/Time field to tblData. The LastUpdated field is meant to be filled in automatically; it'll change each time a record is edited. The new field should identify the date of the outlay. Let's say you name this field OutlayDate.
    When you have added this field, save the table and switch to datasheet view. Enter a date for each record. The dates can be the same or different, that doesn't matter.
    Then switch back to datasheet view and add OutlayDate to the primary key. See screenshot (it is for the Dutch version of Access, but you'll get the idea)
    This means that you can have two or more records with the same afid, OutlayID and OutlayTypeID, as long as their OutlayDates are different.
    Save the table and add the OutlayDate field to the queries, to frmData and to rptOutlays.

    2. In the line of code

    strWhere = " AND Month(InvoiceDate) = " & Me.Monaten

    in the On Click event of the command buttons, replace InvoiceDate with the date field on which you want to filter - LastUpdated or now on OutlayDate.

  5. #5
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Archive data (Access 2000)

    Thank you. The form frmData cannot accept new entries with the error " Index or primary key cannot contain a null value" How should i fill in the field Outlay date through a form ?

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

    Re: Archive data (Access 2000)

    You must enter a value for OutlayDate that is different from the OutlayDate in already existing records with the same afid, OutlayID and OutlayTypeID.

  7. #7
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Archive data (Access 2000)

    Thank you Hans ! Everything works.It is a lovely database now !

Posting Permissions

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