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![FIELDNAME] = FIELDNAME.OldValue
rs![FIELDNAME] = FIELDNAME.Value
rs![DATE] = Now()
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:
101 + YYYYMMDD + HHMMSS
If you save this value when the user moves to the record it should be unique.