formatting in vba? 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?