Stuart McLachlan
stuart at lexacorp.com.pg
Sun Feb 8 02:59:50 CST 2009
I'm logging record changes on a form into an audit trail/diary. Text boxes are simple: strchanges = "" For Each ctl In Me.Controls Select Case Left(ctl.Name, 3) Case "txt" If Nz(ctl.Value, "") <> Nz(ctl.OldValue, "") Then strchanges = strchanges + Mid$(ctl.Name, 4) & " changed from '" & Nz(ctl.OldValue, "BLANK") & "'-to-'" & Nz(ctl.Value, "BLANK") & "'" & vbCrLf End If Case Else End Select Next If strchanges > "" Then strchanges = Replace(strchanges, "'", "''") strSQL = "Insert into tblDiary (DTG,TenderID,creator,comments) values (Now()," _ & TenderID & ",'" & GetUserName() & "','" & strchanges & "')" End If CurrentDb.Execute strSQL There are a number of Combo Boxes (cboFieldName) as well. In each case, the row source is in the form: Select Descr,ID from tblLookup If I use the same method as per "txt", I get the Indexes in the diary ie Fieldname changed from "6"-to-"4" I can use use ctl.text to get Fieldname changed from "6"-to-"New Description" Getting "Old Description" insead of "6" is the problem I've considered Building Dlookup()s from cbo.Rowsource with a cbo.Oldvalue filter Using a DAO,.Recordset to do the same, again based on the cbo.Rowsource (easier than Dlookup, because I can use Fieldnumbers in the returned record) Both seem a bit of a PITA since there are a lot of fields on the form. Ideally I'd like something like ctl.oldvalue.text but that doesn't work. Anyone got any other ideas? -- Stuart