With your limited comfort for VBA, I am reluctant to do more than discuss options in general. Still, it isn't THAT hard to do. I'll give you the theory of what you need. It will be up to you to approach it in stages.
In the BeforeUpdate event, you have a chance to intercept the data on the form one last time. Not every control on your form will reflect changes. Some controls, for example, are lines, labels, rectangles, and command buttons - none of which have values associated with them. Others are members of an option group. But only the parent group has a value. The individual options are constants somewhat akin to command buttons that just assert a value. Now, here is the point of this discussion. If it has a .Value property, there are good odds that it also has a .OldValue property. In brief, if .Value = .OldValue then the contents of the control haven't been changed and won't be affected by the update operation.
How you record your changes is going to up to you, but if I were doing it the meticulous way, then inside the BeforeUpdate event routine I would add code that does the following in approximately this order:
Open a recordset to my change history table.
Set rsChgHst = {database}.OpenRecordset("funkytablename")
I am going to presume that you have at least two Memo fields in this table. If you have timestamps and other data, that will be your call.
Then start a loop over all controls on the form.
For Each ctlItem in Me.Controls Loop
Then inside that loop, use a select statement based on the type of control. Use object browser, Access library, acControlTypes to see a list of the control names.
Select Case ctlItem.ControlType
I would have a case statement that recognized the things I knew were on the form. Usually, text boxes and combo boxes, but you could have other things, I guess.
Case acTextBox, acComboBox, {other data-input controls}
Inside this routine, I would have a comparison of the old and current values followed by the update to your recordset.
If Nz(ctlItem.Value,"") <> Nz(ctlItem.OldValue,"") then
rsChgHst.AddNew
rsChgHst![AffectedControl]=ctlItem.Name
rsChgHst![NewValue] = CStr(Nz(ctlItem.Value,""))
rsChgHst![PrevValue]= CStr(Nz(ctlItem.OldValue,""))
and of course all the syntax needed to close the loops and the ifs and the select, etc. Then do the recordset update and allow the BeforeUpdate routine to continue what it was doing.
When updating the new and previous values in the recordset, you would also perhaps wish to add a timestamp (see Now() function) and the name of the user (if dealing through workgroups, see CurrentUser() function). You will have to design the contents of the history table before you code any of this, of course.
I would NOT make the time function your primary key by itself because Access moves faster than the clock for many cases. So you would get a primary key constraint. Perhaps time + field + user would make a good compound primary key. But for this history table, I wouldn't actually bother with a key at all. Indexes - maybe. Prime key? No.
