Fields Updating Message

[MHamid]

Hello,

I was wondering if it would be possible to have a message box pop up after an update to a record has been made stating what field or fields have been updated. I have update 20 fields that can be possibly updated, but not all will be updated. Instead of showing a generic "Record has been updated successfully", I would like the message box to say what field(s) was updated.
Is this possible? If so, how can I achieve this?


Thank you

 

[welshgasman]

Hhm, it would be quite lengthy, but just create a loop or simple code like below and concatenate a string. I'd probably use vbcrlf

strUpdated=""

If field1 updated then
    strUpdated = Field1.name & vbCrLf
end if
If field2 updated then
    strUpdated = strUpdated & Field2.name & vbCrLf
end if

at the end of all the ifs then

MSGBOX strUpdated

How you determine what was updated is up to you.

This would be one way.

HTH

 

[Micron]

You could consider the OldValue property if your form and its controls meet the requirements. If not, you'd need a variable for each field that can be updated and a code block to set each one to the starting value. You'd also need a way to compare each field to each matching control, plus a loop to go through them all to perform this comparison. I think looping would be required regardless of using the OldValue property or not. To facilitate this, I'd use the tag property for those controls and in my loop, only check those whose tag is "CheckMe" or whatever value you use. You'd also have to build the message as you go, since several field edits need to be concatenated into one list.

Not overly difficult - just maybe more coding than you originally expected.

 

[MHamid]

Hello,

This is my current code:

Private Sub Update_Record_Click()
Dim rs As Recordset
Dim varRefID As Variant
If IsNull(DLookup("RefID", "QAMaster", "RefID='" & Me.RefID.Value & "'")) Then
    MsgBox "RefID does not exist ... Goodbye!", vbInformation, "Error!"
    Exit Sub
Else
    varRefID = Me.RefID
End If
Set rs = CurrentDb.OpenRecordset("QAMaster")
With rs
    .Edit
    If Me.EnteredBy.OldValue <> Me.EnteredBy Then
        .Fields("Entered By") = Me.EnteredBy
    End If
    If Me.CycleMonth.OldValue <> Me.CycleMonth Then
        .Fields("Cycle Month") = Me.CycleMonth
    End If
    If Me.ReportType.OldValue <> Me.ReportType Then
        .Fields("Report Type") = Me.ReportType
    End If
    If Me.DateReviewed.OldValue <> Me.DateReviewed Then
    .Fields("Date Reviewed") = Me.DateReviewed
    End If
    If Me.ReviewerType.OldValue <> Me.ReviewerType Then
        .Fields("Reviewer Type") = Me.ReviewerType
    End If
    If Me.Reviewer.OldValue <> Me.Reviewer Then
        .Fields("Reviewer") = Me.Reviewer
    End If
    If Me.ReviewerReportArea.OldValue <> Me.ReviewerReportArea Then
        .Fields("Reviewer Report Area") = Me.ReviewerReportArea
    End If
    If Me.MainSection.OldValue <> Me.MainSection Then
        .Fields("Main Section") = Me.MainSection
    End If
    If Me.TopicSection.OldValue <> Me.TopicSection Then
        .Fields("Topic Section") = Me.TopicSection
    End If
    If Me.Ownership.OldValue <> Me.Ownership Then
        .Fields("Ownership") = Me.Ownership
    End If
    If Me.txtCount.OldValue <> Me.txtCount Then
        .Fields("Count") = Me.txtCount
    End If
    If Me.PriorityLevel.OldValue <> Me.PriorityLevel Then
        .Fields("Priority") = Me.PriorityLevel
    End If
    If Me.L1.OldValue <> Me.L1 Then
        .Fields("L1") = Me.L1
    End If
    If Me.L2.OldValue <> Me.L2 Then
        .Fields("L2") = Me.L2
    End If
    If Me.L3.OldValue <> Me.L3 Then
        .Fields("L3") = Me.L3
    End If
    If Me.Exception.OldValue <> Me.Exception Then
        .Fields("Exception") = Me.Exception
    End If
    If Me.Notes.OldValue <> Me.Notes Then
        .Fields("Notes") = Me.Notes
    End If
    If Me.Outliers.OldValue <> Me.Outliers Then
        .Fields("Outliers") = Me.Outliers
    End If
    .Update
End With
MsgBox "Record Updated Successfully", vbInformation, "SUCCESS!"
exitHere:
rs.Close
Set rs = Nothing
Exit Sub
errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume exitHere
End Sub

What would be the best option from this point?

 

[Micron]

I've had company staying for a few days and spent zero time on computer. You still want help with this? If so, what happens when you run that code, because getting and keeping the old value can be tricky? If you move off the record, it is gone and you're not saying if this is a datasheet, continuous form or what. You might have to assign the values to variables in the Current event of the form. I also don't see any use of the Tag property of controls (as suggested) so I'm not sure what you've got.

 

[MHamid]

Hello,

Yes, I still need some help with this issue. When I run the code I get the message "Record Updated Successfully". However, when I look at the record that I updated, I don't see my updates on the table. I am making the change in the Form by scrolling to the record ID. I have a RefID field that is the primary key on the form so I can scroll through records.

That's the whole code I have for the update button on my form.

 

[Micron]

when I look at the record that I updated, I don't see my updates on the table.

You need to figure out if the update is actually working, or if you're sure it's not, say how you know. If you have the table open when you click the button to run that code, then go back to look at the table, you won't see any changes until you refresh (from the ribbon) or close/reopen the table. So I'm not understanding if you're just not seeing changes or the updates aren't happening. Assuming not happening,

Put a break on this line: If Me.EnteredBy.OldValue <> Me.EnteredBy Then
and click the button to run this event. When the code stops at this line, check the values you expect to be coming from the form controls by mousing over them (e.g. Me.EnteredBy). If mousing over doesn't work, type ?Me.EnteredBy and ?Me.EnteredBy.OldValue in the immediate window and hit return. If you get only a blank line (carriage return but no text) for either one, the control is not passing the value or the old value to the code. Alternatively, you could use message boxes for both values (msgbox Me.EnteredBy) for all steps if you prefer, but I would still use the break. Let's be sure you're getting both .OldValue and the current value before moving on.

Your code doesn't look anything like I would have expected - i.e. no loop, no tags.
Did you read the link re old value to ensure you can make use of it?
This button is not on a continuous or datasheet form I hope.

 

[MHamid]

Hello,

When I run the code, the table is closed. So I would open the table after I run the code. I don't see the updates made on the form going into the table.

I placed a break after the line as you suggested and after I click the button I hovered over the fields and I see the Old Value as being the old value and I see the new value. But I still don't see it being updated on the table. when
However, I just noticed that after I run the code, I have to click on the next record, then open the table and I will see the change updated. I won't see it if I'm still on the record in the form.

Is this the only way to see the update?
What did you expect the code to look like?

I decided to do the if statement so I can create the message box stating what field(s) had changes updated. Is there a better way to do this?

My original code was

Set rs = CurrentDb.OpenRecordset("QAMaster")
With rs
    .Edit
    .Fields("Entered By") = Me.EnteredBy 
    .Fields("Cycle Month") = Me.CycleMonth

But since I want to do a message box alerting what field was updated, I thought that using the if statement was the way to do this.

 

[Micron]

What did you expect the code to look like?

I thought you wanted a list of edited fields in a message. I see no reason to work with a recordset when you already have a form bound to a table. Why not just let the built in form functionality update the record? To keep on your current path, you probably only have to requery the form to commit the changes, but I think you've chosen a clunky approach there.

I would have used the form BeforeUpdate event to compare the old and new values. If they were not the same, add the field name to a list. However, rather than type code for 20 controls, I'd put the tag on those controls as I suggested and loop through them. If the text box labels are all attached to their controls, we can get the label caption instead of the field name in case the field names are not intuitive. If any are not, then the control name would have to be used. I've never tried to get the name of the field that a control is bound to when they're not the same or the label isn't attached. Assuming the controls involved are all text boxes, then like this

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctl As Control
Dim strMsg As String

If IsNull(DLookup("RefID", "QAMaster", "RefID='" & Me.RefID.Value & "'")) Then
  MsgBox "RefID does not exist ... Goodbye!", vbInformation, "Error!"
  Exit Sub
End If

strMsg = ""
For each ctl in Me.Controls 'start the loop
'Compare (no quotes) is what you entered in the tag property for controls to be checked.
  If ctl.Type = acTextBox And ctl.Tag = "Compare" Then 
    If ctl.OldValue <> ctl Then strMsg = "- " & ctl.Controls(0) & vbCrLf
  End If
Next

'If strMsg is not "", something was edited 
If strMsg <> "" Then MsgBox "You updated:" & vbCrLf & vbCrLf & strMsg, vbInformation, "SUCCESS!"

exitHere:
Exit Sub

errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume exitHere

End Sub

This would fire if the form is a subform and you click off of it (and maybe a single form if you could leave the detail section) or when you navigate to another record, or if it's a data sheet or continuous form and you leave the record, so a button isn't required. If you wanted to allow the user to cancel the changes, you could with this event, but not a button click. Quite a versatile event, no? You can remove the code comments if you find them confusing.

NOTE: as always, test on copies of your db objects. This is air code (from the space between my ears) thus is untested.

 

[MHamid]

Hello,

I am using the below code in the UpdateRecord_Click() button. I know it was suggested to be used in the BeforeUpdate event, but I want this in the UpdateRecord_Click() button. I see that the record is being updated, but I don't see the message popping up letting the user know what fields were updated.

Private Sub UpdateRecord_Click()

Dim ctl As Control
Dim strMsg As String
 
If IsNull(DLookup("RefID", "QAMaster", "txtRefID='" & Me.txtRefID.Value & "'")) Then
    MsgBox "RefID does not exist ... Goodbye!", vbInformation, "Error!"
    Exit Sub

End If
strMsg = ""
For Each ctl In Me.Controls
    With ctl
        Select Case .ControlType
            Case acTextBox, acComboBox, acCheckBox
                If ctl.Tag = "Compare" Then
                    If ctl.OldValue <> ctl Then strMsg = "- " & ctl.Controls(0) & vbCrLf
                End If
        End Select
    End With
Next
If strMsg <> "" Then MsgBox "You updated:" & vbCrLf & vbCrLf & strMsg, vbInformation, "SUCCESS!"
 
exitHere:
Exit Sub

errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume exitHere
End Sub

I did include the word Compare in the Tag property. However, I also have other data in that field as well for other features in the form. Is that going to cause issues? I have the comments in the Tag property separated by a semicolon (.


Thank you