Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2004
    Posts
    35

    Unanswered: can ANYONE explain this further?

    I have the following code which places values in a table to keep track of changes made (tblPAIRSCHANGES). Sometimes only one field is changed and sometime multiple fields are changed. When it writes this info to the new table (tblPAIRSCHANGES), it makes a new line for each change. Is it possible to make it put all changes in one line?

    Private Sub FIELDNAME_AfterUpdate()
    Dim db As Database
    Dim rs As Recordset
    Set db = CurrentDb()
    Set rs = db.openrecordset("Select*from[tblPAIRSCHANGES]")
    rs.AddNew
    rs![FIELDNAME] = FIELDNAME.OldValue
    rs![FIELDNAME] = FIELDNAME.Value
    rs![DATE] = Now()
    rs.Update
    I

    marshals
    View Public Profile
    Send a private message to marshals
    Send email to marshals
    Find all posts by marshals
    Add marshals to Your Buddy List

    #2 11-23-04, 17:01
    DCKunkle
    Registered User Join Date: Feb 2004
    Location: Chicago, IL
    Posts: 261

    You couldn't know how many pairs you would have to save. So you would have to save space in a record for the maximum number of pairs there could be. Doing so would not be very good database design. Another way you can keep the changes 'together' is to assign a ChangeID to each change. I am assuming you are keeping track of changes to a record. I would set a variable during the On Current event of the form. Then save that variable with every change that was made. The variable could be set with a combination of the User ID and the time stamp.

    So when user 101 modified the record the variable might be:
    10120041123135543

    101 + YYYYMMDD + HHMMSS

    If you save this value when the user moves to the record it should be unique.

    DCKunkle





    marshals
    I already have a time stamp on this code... would I just have to update that?

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    He's saying to create another table so you can group your changes together

    Ie:

    tblChanges
    change_id
    change_date
    misc_memo

    tblChangeDetails
    change_detail_id
    change_id
    old_value
    new_value

    This allows you to assign any number of changes to the same "change_id" or transaction or whatever you want to call it.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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