Logging form changes

[Someone]

Hello

I'd like to incorporate a way of logging notes as to what fields have been
changed on an existing record.

The following is an example. Using AfterUpdate in a field called Status, it
updates a subform called Notes.

With Me.frm_Notes_Subform.Form.RecordsetClone
.AddNew
!Notes = ("Status Changed to " & Me.Status)
.Update
DoCmd.Save
End With

While this works and the notes are updating with text when the Status
changes, the notes are not saved. The Notes table has a relationship with
the main table (one record from the main to many notes), but I just can't
get the details to save.

Can someone tell me where I'm going wrong?

Thanks
M

 

[tina]

if tblNotes has a foreign key field that links it to the primary key field
in tblMain, and if the main form is bound to tblMain and the subform is
bound to tblNotes, and if the subform control's LinkChildFields and
LinkMasterFields are set correctly with the foreign key/primary key pair
from those two tables...then all you should need to do is set the value of
the Notes control in the subform, as

Me!frm_Notes_Subform!Notes = "Status Changed to " & Me!Status

just make sure that you're using the name of the subform *control* in the
main form.

if any elements of the above scenario are not "true", then please explain
your setup in more detail.

hth

 

[Someone]

Hi Tina

Thanks for your reply.

You're spot on with the setup that I have.

I think I may have written my original post slightly incorrectly. The very
first change I make to the record does populate the notes field with the
text describing the change and it will save. However, any further changes
just over-write the original entry, rather than adding a new entry to the
list. What have I missed out?

Many thanks
M

 

[tina]

hmm, well, try this instead

With Me!frm_Notes_Subform
DoCmd.RunCommand acCmdRecordsGoToNew
Me!frm_Notes_Subform!Notes = "Status Changed to " & Me!Status
End With

an alternate solution might be to open the subform object (from the database
window), and set the form's DataEntry property to Yes. then use the code i
originally posted.

hth

 

[Someone]

Hi Tina

Thanks for this.

I've amended the code slightly and have now got it working.

M

 

[tina]

you're welcome

Hi Tina

Thanks for this.

I've amended the code slightly and have now got it working.

M