Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    History of entry (2000 sr 1)

    From a macro in the userform i insert many value in the field NOTE_BOU, SPESE, DATA_ATT, COD, and NOTA_LIB of every table (TOTALE and CDI_50).
    My problem is to maintain an history of various entry for each record (in the every table is present a unique value SERVIZIO)

    For example:

    Today i insert from a userform a value in the record with the index 55-01010-03400-2107997836 (is the line 87) in the field NOTE_BOU, SPESE, DATA_ATT, COD, and NOTA_LIB

    The next day from the same record i insert a new value in the field NOTE_BOU, SPESE, DATA_ATT, COD, and NOTA_LIB

    My problem is to store the old value from each filed NOTE_BOU, SPESE, DATA_ATT, COD, and NOTA_LIB in a new table named STORIA_TOTALE if the DB in question is TOTALE and maintan the must new value in the original table...

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

    Re: History of entry (2000 sr 1)

    Instead of keeping a separate history table, I would add a field DateModified of type Date/Time to the TOTALE (and CDI_50) tables, with a Default Value property of =Now(). Each time you modify the valies in the userform, add a new record to TOTALE (or CDI_50).
    You can create queries in the database to retrieve the most recent record for each SERVIZIO; this will be the "current" record.

    Note: if you want to attach a userform, you must include both the .frm and the .frx file.

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: History of entry (2000 sr 1)

    ok Hans...
    but for me is the first work in access and excel...
    Preraphs for me, to make this modify sure 3 month of work...

    If you like, modify me, the 2 table with your suggestion, sure is a good idea.

    Note: i have modified the attech and insert the .frx

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

    Re: History of entry (2000 sr 1)

    Modifying the tables is not difficult, and it will take only a minute or two.
    - Open the database.
    - Open the Totale table.
    - Scroll down to the last field.
    - In the first empty row below, enter DateModified (or the Italian equivalent) in the Field Name box.
    - Select Date/Time in the Data Type column.
    - In the lower half of the window, enter =Now() in the Default Value property. (You should NOT translate Now into Italian, it is a VBA function)
    - Close the table and reply Yes to the prompt to save it.
    - Repeat for the CDI_50 table.

    You don't need to set the DateModified field in the code behind your Excel userform.

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: History of entry (2000 sr 1)

    Please controll if i make a correct modifiy?

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

    Re: History of entry (2000 sr 1)

    Yes, that is correct. That wasn't so hard, was it?

  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: History of entry (2000 sr 1)

    tKS, ;-) you are for me o wonderfull teacher....
    2 step) make a query...

  8. #8
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: History of entry (2000 sr 1)

    Hans,
    I am probably misunderstanding the context of Sal21's query. But let me ask this question anyways! If you want to keep a history of changes per record by adding a "date stamp" on the record, isn't it better to use the Date() function that is static rather than the Now() function that will change on a daily basis with the computer clock?!
    I may be mis-reading Sal21's query, i'm certainly not querying your expertise! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Regards,
    Rudi

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

    Re: History of entry (2000 sr 1)

    The Date() function is neither more nor less static than the Date() function. The difference is that Now results in a date + time value and Date - as the name implies - in a date value only. By setting the Defauly Value property of a field in an Access table to =Date() or =Now(), the value of this field is set the moment the record is created; it will remain the same after that (unless the user changes the value manually.)

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

    Re: History of entry (2000 sr 1)

    I can help you with that, but these are not Excel questions any more. Please post a new thread in the Access forum.

  11. #11
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: History of entry (2000 sr 1)

    Another question
    1. Is this applicable to all objects in Access (that Now() is static), or is it different in queries and forms
    2. Does this differ in Excel? As far as I have known, =Now() in Excel is volatile, as well as the =Today() function. Each time you switch on the PC in the morning, the functions value is the current date!
    Regards,
    Rudi

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

    Re: History of entry (2000 sr 1)

    If you set =Now() or =Date() or =Time() as the Default Value property of a field in a table or of a control on a form in Access, the value will be assigned when the record is created, and remain the same after that. The functions are used here in a completely different way from a worksheet function in Excel.

    If you use =Now() or =Date() or =Time() in a calculated field in a query, or in the Control Source of a text box on a form or report, the value will change each time the query is opened, the user moves to a new record in the form, or to a new page in the report. This is comparable to the NOW and TODAY worksheet functions in Excel.

  13. #13
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: History of entry (2000 sr 1)

    Perfect. Thanx Hans...this is clear =now() <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Thanx for that explanation. I have =now() learned another interesting thing =today()
    Thanx for your =time()...Much abliged!
    Regards,
    Rudi

  14. #14
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: History of entry (2000 sr 1)

    hans, in order to open a new question can i insert only a link of this question and not repeat all in a new post?

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

    Re: History of entry (2000 sr 1)

    Yes, you can refer to <!t>[Post#=428870]<!/t> (it will be displayed as <post#=428870>post 428870</post#>)

Page 1 of 2 12 LastLast

Posting Permissions

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