How to check for when a changed value in a Combo Box is saved to the table

[TonyD1016]

Hello everyone. Here is my current situation:

I am working with a database that I set up with an Audit trail as described in this blog post. It includes rich text fields so I am using the VBA function method described in the comment section. I have two fields that I am auditing called "DCDocuments" and "ClinicalUpdates"

I recently broken out a piece of information from the ClinicalUpdates field into a combo box "cboRloC", but I still need that information included in the ClinicalUpdates audit trail if changes are made to it. I grab the information from the combo box, concatenate it to rest of the data in the appropriate field, and feed that new variable it into the AuditTrail function using the following code:

    Dim varNewMix As Variant
    Dim strRLoC As String
    
    strRLoC = Me.cboRLoC.Column(1)     
    varNewMix = "<div><strong>Recommended level of care:</strong> " & strRLoC & "</div>" & Screen.ActiveControl
    
    Call AuditTrail(Screen.ActiveForm.RecordSource, Screen.ActiveControl.ControlSource, Me.[CASE #], Screen.ActiveControl.OldValue, varNewMix)

The issue I am running into is that this code is placed in the BeforeUpdate property of the ClinicalUpdates textbox and so it will only fire if a change is made to that textbox on the form. I also want to be able to execute the same code if a user changes the value of the combo box without also making a change to the ClinicalUpdates textbox.

At first I tried use the above code on the BeforeUpdate property of the combo box with the following change to the function call:

Call AuditTrail(Screen.ActiveForm.RecordSource, "ClinicalUpdates", Me.[CASE #], Me.ClinicalUpdates.Value, varNewMix)

This made the entry in the table that I needed, but it did so as soon as the value of the Combo Box was changed. I only want an audit entry made if the value in combo box is changed on the form and that change is saved to the database.
How can I accomplish this?

 

[JonXL]

Use the BeforeUpdate event of the  form. Then you can check if the combobox's .Value is different from the combobox's .OldValue with typical If ... Then and only do they logging when the values don't match.

Make sure to handle nulls correctly.

 

[TonyD1016]

Use the BeforeUpdate event of the  form. Then you can check if the combobox's .Value is different from the combobox's .OldValue with typical If ... Then and only do they logging when the values don't match.

Make sure to handle nulls correctly.

Apologies that it took me so long to respond to this but I wanted to follow up with a huge thank you for this advice.
This actually helped me uncover and resolve a problem with the VBA method listed in my linked post. By calling the audit trail function in the BeforeUpdate event of the control rather than the form itself, it caused the audit trail to log the changes made to the field every time the control lost focus, rather than when the changes were actually committed to the table which is what I wanted.

Thank you very much for your reply.