Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Location
    Columbia, MO
    Posts
    57

    Unanswered: Creating a "log" table everytime a record is added/edited

    How can I create a "log table" that puts all the additions and edits to a dataset from a form into a new row on this table. Every change or addition gets added to the bottom of this table. I want to be able to go back and look and see if a purchase order got changed, and see what changes have been made to it.

  2. #2
    Join Date
    Jan 2003
    Location
    Aberdeen, Scotland, UK
    Posts
    168

    Re: Creating a "log" table everytime a record is added/edited

    I did this to record changes to a contact database, to see when records were ammended and by who.

    You need to enter the code under the afterUpdate or OnChange events.

    Dim db as database
    Dim rs as recordset

    set db = currentdb()
    set rs = db.openrecordset("Select * from [tblRecordChanges]")

    rs.addnew
    rs![oldvalueoffield] = txtwhatever.oldvalue
    rs![newvalueoffield] = txtwhatever.value
    rs.update


    -------------------------
    hope helps

  3. #3
    Join Date
    Jan 2003
    Location
    Columbia, MO
    Posts
    57
    John,
    Thanks for the reply. I'm not to prolific with code yet, so I am still ignorant about a lot of this. I tried this, but I got an error on the
    Dim db as database code. It wouldn't recognize 'database' as a data type. Also, I need some clarification on

    'set rs = db.openrecordset("Select * from [tblRecordChanges]")

    rs.addnew
    rs![oldvalueoffield] = txtwhatever.oldvalue
    rs![newvalueoffield] = txtwhatever.value
    rs.update'

    Is tblRecordChanges the new 'log' table?
    Is [oldvalueoffield] the form value or the actual query field that the form is running on? Which one is the 'txtwhatever.oldvalue'?

    Again, I apologize for my ignorance. I need to start learning more about the coding behind Access. Thanks for the input.

    David

  4. #4
    Join Date
    Jan 2003
    Location
    Aberdeen, Scotland, UK
    Posts
    168
    In order for Access to Recognize the Database data type you must have then correct references set in access. To do this open a code window (module or form code) and click tools, references. Make sure these 3 are tickedin 97 anyway)

    1)Microsoft Access 8.0 object library
    2)Microsoft DAO2.5
    3)Visual Basic for Applications

    I think that should take care of the data type.

    tblRecordChanges should be the name of your new log table.

    rs![oldvalueoffield] = name of field on log table where you want to store the old value of the field (before it was changed)

    rs![newvalueoffield] = name of field on log table where you want to store the new value of the field (after it was changed)

    txtwhatever = the name of the field on the form that you want to trck changes on.

    If you want to track changes to more than one field just add more fields onto the log table and follow the same procedure.


    I hope this clears it up (a bit at least :-) )

    John

  5. #5
    Join Date
    Jan 2003
    Location
    Columbia, MO
    Posts
    57
    John,
    Thanks for the patience. I do appreciate it. I have 1 more question (hopefully) for you. I believe I have everything like I want it. However when I use the form and go to the next record,
    I get an Run-time error '13': Type mismatch. When I hit debug, it highlights the line:

    set rs = db.openrecordset("Select * from [tblRecordChanges]")

    Do you have any suggestions on what to do now?

    Thanks.

    David

  6. #6
    Join Date
    Dec 2002
    Location
    Columbus, GA
    Posts
    81
    Heres an easy way.

    What I did was add the fields to the table CATCHUSER and CATCHWHEN.

    Then Add these fields to your form and make them hidden

    Make a macro that says SET VALUE and choose

    Item=[CATCHUSER]
    Expression=CurrentUser()

    Put in another SET VALUE line and say

    Item=[CATCHWHEN]
    Expression=Date(Now())

    This will store the info in the table and tell you the last person and the time and date. Save this as CATCHCHANGES

    Now what you need to do is go to each field on your form and go to properties and you will see AFTER UPDATE

    Put in your Macro name Here "CATCHCHANGES"

    MAKE SURE you go to each field and chosoe this for AFTER UPDATE.

    Now, you can add these same 2 fields to ANY table and run this same macro from EVERY FORM that has a table with these fields and it will catch changes.

Posting Permissions

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