Fields Updating Message

M

MHamid

Active Member
Joined
Jan 31, 2013
Messages
472
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
 
Sort by date Sort by votes
The message box appears when only one field has been updated. However, if the user updates more than one filed, the message box will not appear at all.
That's what I mean by it does nothing.


Therefore, I need to find a way to write in the code for all updated fields to appear in the message box when more than 1 field has been updated. Not just 1 field alone.
 
Upvote 0

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
OK, I can see a problem.

Intsr returns a number for the position of the text being searched for, so you should be testing for > 0

Then when you have it working then concatenate the other fields.

FWIW I cannot see it producing anything with what you have at present?, not even one control
 
Upvote 0
I believe that test is being made (post 17 or so).
MHamid: when you post what the current situation is, you are far too vague. Not working, etc. is of no help to us. You are not thinking of it in terms of what we get to look at (which is far less than you). You might get more focused responses and quicker solutions if you treat your audience more like children and less like scientists who are working right along side of you, seeing everything you see. I don't say this just because of this thread - I've noticed it's the way you provide information in many of your posts.

You might have to provide access to a copy of your db if you can't provide more than cursory explanations of what happens.
 
Upvote 0
Micron said:
I believe that test is being made (post 17 or so).
.
Click to expand...

Yes, I see I missed that, sorry. The O/P still needs to walk through the code step by step I believe. I always do that when code 'should' be doing something and it is not. That way i find where my 'silly' error is.
 
Upvote 0
Re F8: that is a perfect example of what I'm talking about. See post 19's response to that suggestion.
 
Upvote 0
Hello,

I am not sure as to how else to explain the issue.

When I go into the code and press F8, I don't see anything happening. There is no error message, there is no indication that it's going through the code step by step. Please explain how I'm supposed to be going through the code step by step using F8. What am I expected to see happen when F8 is pressed? I am a novice with VBA coding and I don't know all of the terminology.

As for the message box, I see that the code itself works when I test it for one field in the form being updated. I update just ONE field and I see my message box letting know what field was updated. Let's say I just updated the Entered By field ONLY, then I will see my message box stating "You updated - Entered By". However, when I update 2 or more fields and once, I don't see the message box at all letting me know all of these fields that have been updated. From what I understand, I'm supposed to concatenate the fields that have been updated. But I don't understand how I'm supposed to concatenate these updated fields so the message box could say:
"You updated
- Entered By
Main Section
Topic Section
Notes"

Can you please give me an example as to how I'm supposed to concatenate the updated fields and place it in my message box? Or direct me to a link that will give me similar information about how to do something like this?
 
Upvote 0
Ok, I am trying to concatenate the string for the updated fields.


This is the code that I have now. Yes, the code is long now due to the attempt of concatenation. I am getting the error message "Run-Time error '91': Object variable or With block variable not set". What did I do wrong?

Code: 
Dim ctl As Control
Dim strMsg As String
Dim strUpdated As String

strUpdated = ""

If ctl.OldValue <> Me.EnteredBy Then
    strUpdated = Me.EnteredBy.Name & vbCrLf
End If
If ctl.OldValue <> Me.DateEntered Then
    strUpdated = strUpdated & Me.DateEntered.Name & vbCrLf
End If
If ctl.OldValue <> Me.Approved Then
    strUpdated = strUpdated & Me.DateEntered.Name & vbCrLf & Me.Approved.Name & vbCrLf
End If
If ctl.OldValue <> Me.CycleMonth Then
    strUpdated = strUpdated & Me.DateEntered.Name & vbCrLf & Me.Approved.Name & vbCrLf & Me.CycleMonth.Name & vbCrLf
End If
If ctl.OldValue <> Me.DateReviewed Then
    strUpdated = strUpdated & Me.DateEntered.Name & vbCrLf & Me.Approved.Name & vbCrLf & Me.CycleMonth.Name & vbCrLf & Me.DateReviewed.Name & vbCrLf
End If
If ctl.OldValue <> Me.ReportType Then
    strUpdated = strUpdated & Me.DateEntered.Name & vbCrLf & Me.Approved.Name & vbCrLf & Me.CycleMonth.Name & vbCrLf & Me.DateReviewed.Name & vbCrLf & Me.ReportType.Name & vbCrLf
End If
If ctl.OldValue <> Me.MainSection Then
    strUpdated = strUpdated & Me.DateEntered.Name & vbCrLf & Me.Approved.Name & vbCrLf & Me.CycleMonth.Name & vbCrLf & Me.DateReviewed.Name & vbCrLf & Me.ReportType.Name & vbCrLf & Me.MainSection.Name & vbCrLf
End If
If ctl.OldValue <> Me.TopicSection Then
    strUpdated = strUpdated & Me.DateEntered.Name & vbCrLf & Me.Approved.Name & vbCrLf & Me.CycleMonth.Name & vbCrLf & Me.DateReviewed.Name & vbCrLf & Me.ReportType.Name & vbCrLf & Me.MainSection.Name & vbCrLf & Me.TopicSection.Name & vbCrLf
End If
If ctl.OldValue <> Me.ReviewerType Then
    strUpdated = strUpdated & Me.DateEntered.Name & vbCrLf & Me.Approved.Name & vbCrLf & Me.CycleMonth.Name & vbCrLf & Me.DateReviewed.Name & vbCrLf & Me.ReportType.Name & vbCrLf & Me.MainSection.Name & vbCrLf & Me.TopicSection.Name & vbCrLf & Me.ReviewerType.Name & vbCrLf
End If
If ctl.OldValue <> Me.Reviewer Then
    strUpdated = strUpdated & Me.DateEntered.Name & vbCrLf & Me.Approved.Name & vbCrLf & Me.CycleMonth.Name & vbCrLf & Me.DateReviewed.Name & vbCrLf & Me.ReportType.Name & vbCrLf & Me.MainSection.Name & vbCrLf & Me.TopicSection.Name & vbCrLf & Me.ReviewerType.Name & vbCrLf & Me.Reviewer.Name & vbCrLf
End If
If ctl.OldValue <> Me.ReviewerReportArea Then
    strUpdated = strUpdated & Me.DateEntered.Name & vbCrLf & Me.Approved.Name & vbCrLf & Me.CycleMonth.Name & vbCrLf & Me.DateReviewed.Name & vbCrLf & Me.ReportType.Name & vbCrLf & Me.MainSection.Name & vbCrLf & Me.TopicSection.Name & vbCrLf & Me.ReviewerType.Name & vbCrLf & Me.Reviewer.Name & vbCrLf & Me.ReviewerReportArea.Name & vbCrLf
End If
If ctl.OldValue <> Me.Individual Then
    strUpdated = strUpdated & Me.DateEntered.Name & vbCrLf & Me.Approved.Name & vbCrLf & Me.CycleMonth.Name & vbCrLf & Me.DateReviewed.Name & vbCrLf & Me.ReportType.Name & vbCrLf & Me.MainSection.Name & vbCrLf & Me.TopicSection.Name & vbCrLf & Me.ReviewerType.Name & vbCrLf & Me.Reviewer.Name & vbCrLf & Me.ReviewerReportArea.Name & vbCrLf & Me.Individual.Name & vbCrLf
End If
If ctl.OldValue <> Me.Team Then
    strUpdated = strUpdated & Me.DateEntered.Name & vbCrLf & Me.Approved.Name & vbCrLf & Me.CycleMonth.Name & vbCrLf & Me.DateReviewed.Name & vbCrLf & Me.ReportType.Name & vbCrLf & Me.MainSection.Name & vbCrLf & Me.TopicSection.Name & vbCrLf & Me.ReviewerType.Name & vbCrLf & Me.Reviewer.Name & vbCrLf & Me.ReviewerReportArea.Name & vbCrLf & Me.Individual.Name & vbCrLf & vbCrLf & Me.Team.Name & vbCrLf
End If
If ctl.OldValue <> Me.txtCount Then
    strUpdated = strUpdated & Me.DateEntered.Name & vbCrLf & Me.Approved.Name & vbCrLf & Me.CycleMonth.Name & vbCrLf & Me.DateReviewed.Name & vbCrLf & Me.ReportType.Name & vbCrLf & Me.MainSection.Name & vbCrLf & Me.TopicSection.Name & vbCrLf & Me.ReviewerType.Name & vbCrLf & Me.Reviewer.Name & vbCrLf & Me.ReviewerReportArea.Name & vbCrLf & Me.Individual.Name & vbCrLf & vbCrLf & Me.Team.Name & vbCrLf & Me.txtCount.Name & vbCrLf
End If
If ctl.OldValue <> Me.Repeat Then
    strUpdated = strUpdated & Me.DateEntered.Name & vbCrLf & Me.Approved.Name & vbCrLf & Me.CycleMonth.Name & vbCrLf & Me.DateReviewed.Name & vbCrLf & Me.ReportType.Name & vbCrLf & Me.MainSection.Name & vbCrLf & Me.TopicSection.Name & vbCrLf & Me.ReviewerType.Name & vbCrLf & Me.Reviewer.Name & vbCrLf & Me.ReviewerReportArea.Name & vbCrLf & Me.Individual.Name & vbCrLf & vbCrLf & Me.Team.Name & vbCrLf & Me.txtCount.Name & vbCrLf & Me.Repeat.Name & vbCrLf
End If
If ctl.OldValue <> Me.Exception Then
    strUpdated = strUpdated & Me.DateEntered.Name & vbCrLf & Me.Approved.Name & vbCrLf & Me.CycleMonth.Name & vbCrLf & Me.DateReviewed.Name & vbCrLf & Me.ReportType.Name & vbCrLf & Me.MainSection.Name & vbCrLf & Me.TopicSection.Name & vbCrLf & Me.ReviewerType.Name & vbCrLf & Me.Reviewer.Name & vbCrLf & Me.ReviewerReportArea.Name & vbCrLf & Me.Individual.Name & vbCrLf & vbCrLf & Me.Team.Name & vbCrLf & Me.txtCount.Name & vbCrLf & Me.Repeat.Name & vbCrLf & Me.Exception.Name & vbCrLf
End If
If ctl.OldValue <> Me.L1 Then
    strUpdated = strUpdated & Me.DateEntered.Name & vbCrLf & Me.Approved.Name & vbCrLf & Me.CycleMonth.Name & vbCrLf & Me.DateReviewed.Name & vbCrLf & Me.ReportType.Name & vbCrLf & Me.MainSection.Name & vbCrLf & Me.TopicSection.Name & vbCrLf & Me.ReviewerType.Name & vbCrLf & Me.Reviewer.Name & vbCrLf & Me.ReviewerReportArea.Name & vbCrLf & Me.Individual.Name & vbCrLf & vbCrLf & Me.Team.Name & vbCrLf & Me.txtCount.Name & vbCrLf & Me.Repeat.Name & vbCrLf & Me.Exception.Name & vbCrLf & Me.L1.Name & vbCrLf
End If
If ctl.OldValue <> Me.L2 Then
    strUpdated = strUpdated & Me.DateEntered.Name & vbCrLf & Me.Approved.Name & vbCrLf & Me.CycleMonth.Name & vbCrLf & Me.DateReviewed.Name & vbCrLf & Me.ReportType.Name & vbCrLf & Me.MainSection.Name & vbCrLf & Me.TopicSection.Name & vbCrLf & Me.ReviewerType.Name & vbCrLf & Me.Reviewer.Name & vbCrLf & Me.ReviewerReportArea.Name & vbCrLf & Me.Individual.Name & vbCrLf & vbCrLf & Me.Team.Name & vbCrLf & Me.txtCount.Name & vbCrLf & Me.Repeat.Name & vbCrLf & Me.Exception.Name & vbCrLf & Me.L1.Name & vbCrLf & Me.L2.Name & vbCrLf
End If
If ctl.OldValue <> Me.L3 Then
    strUpdated = strUpdated & Me.DateEntered.Name & vbCrLf & Me.Approved.Name & vbCrLf & Me.CycleMonth.Name & vbCrLf & Me.DateReviewed.Name & vbCrLf & Me.ReportType.Name & vbCrLf & Me.MainSection.Name & vbCrLf & Me.TopicSection.Name & vbCrLf & Me.ReviewerType.Name & vbCrLf & Me.Reviewer.Name & vbCrLf & Me.ReviewerReportArea.Name & vbCrLf & Me.Individual.Name & vbCrLf & vbCrLf & Me.Team.Name & vbCrLf & Me.txtCount.Name & vbCrLf & Me.Repeat.Name & vbCrLf & Me.Exception.Name & vbCrLf & Me.L1.Name & vbCrLf & Me.L2.Name & vbCrLf & Me.L3.Name & vbCrLf
End If
If ctl.OldValue <> Me.txtOther Then
    strUpdated = strUpdated & Me.DateEntered.Name & vbCrLf & Me.Approved.Name & vbCrLf & Me.CycleMonth.Name & vbCrLf & Me.DateReviewed.Name & vbCrLf & Me.ReportType.Name & vbCrLf & Me.MainSection.Name & vbCrLf & Me.TopicSection.Name & vbCrLf & Me.ReviewerType.Name & vbCrLf & Me.Reviewer.Name & vbCrLf & Me.ReviewerReportArea.Name & vbCrLf & Me.Individual.Name & vbCrLf & vbCrLf & Me.Team.Name & vbCrLf & Me.txtCount.Name & vbCrLf & Me.Repeat.Name & vbCrLf & Me.Exception.Name & vbCrLf & Me.L1.Name & vbCrLf & Me.L2.Name & vbCrLf & Me.L3.Name & vbCrLf & Me.txtOther.Name & vbCrLf
End If
If ctl.OldValue <> Me.PriorityLevel Then
    strUpdated = strUpdated & Me.DateEntered.Name & vbCrLf & Me.Approved.Name & vbCrLf & Me.CycleMonth.Name & vbCrLf & Me.DateReviewed.Name & vbCrLf & Me.ReportType.Name & vbCrLf & Me.MainSection.Name & vbCrLf & Me.TopicSection.Name & vbCrLf & Me.ReviewerType.Name & vbCrLf & Me.Reviewer.Name & vbCrLf & Me.ReviewerReportArea.Name & vbCrLf & Me.Individual.Name & vbCrLf & vbCrLf & Me.Team.Name & vbCrLf & Me.txtCount.Name & vbCrLf & Me.Repeat.Name & vbCrLf & Me.Exception.Name & vbCrLf & Me.L1.Name & vbCrLf & Me.L2.Name & vbCrLf & Me.L3.Name & vbCrLf & Me.txtOther.Name & vbCrLf & Me.PriorityLevel.Name & vbCrLf
End If
If ctl.OldValue <> Me.Notes Then
    strUpdated = strUpdated & Me.DateEntered.Name & vbCrLf & Me.Approved.Name & vbCrLf & Me.CycleMonth.Name & vbCrLf & Me.DateReviewed.Name & vbCrLf & Me.ReportType.Name & vbCrLf & Me.MainSection.Name & vbCrLf & Me.TopicSection.Name & vbCrLf & Me.ReviewerType.Name & vbCrLf & Me.Reviewer.Name & vbCrLf & Me.ReviewerReportArea.Name & vbCrLf & Me.Individual.Name & vbCrLf & vbCrLf & Me.Team.Name & vbCrLf & Me.txtCount.Name & vbCrLf & Me.Repeat.Name & vbCrLf & Me.Exception.Name & vbCrLf & Me.L1.Name & vbCrLf & Me.L2.Name & vbCrLf & Me.L3.Name & vbCrLf & Me.txtOther.Name & vbCrLf & Me.PriorityLevel.Name & vbCrLf & Me.Notes.Name & vbCrLf
End If
If ctl.OldValue <> Me.Outliers Then
    strUpdated = strUpdated & Me.DateEntered.Name & vbCrLf & Me.Approved.Name & vbCrLf & Me.CycleMonth.Name & vbCrLf & Me.DateReviewed.Name & vbCrLf & Me.ReportType.Name & vbCrLf & Me.MainSection.Name & vbCrLf & Me.TopicSection.Name & vbCrLf & Me.ReviewerType.Name & vbCrLf & Me.Reviewer.Name & vbCrLf & Me.ReviewerReportArea.Name & vbCrLf & Me.Individual.Name & vbCrLf & vbCrLf & Me.Team.Name & vbCrLf & Me.txtCount.Name & vbCrLf & Me.Repeat.Name & vbCrLf & Me.Exception.Name & vbCrLf & Me.L1.Name & vbCrLf & Me.L2.Name & vbCrLf & Me.L3.Name & vbCrLf & Me.txtOther.Name & vbCrLf & Me.PriorityLevel.Name & vbCrLf & Me.Notes.Name & vbCrLf & Me.Outliers.Name & vbCrLf
End If
If ctl.OldValue <> Me.txtOther2 Then
    strUpdated = strUpdated & Me.DateEntered.Name & vbCrLf & Me.Approved.Name & vbCrLf & Me.CycleMonth.Name & vbCrLf & Me.DateReviewed.Name & vbCrLf & Me.ReportType.Name & vbCrLf & Me.MainSection.Name & vbCrLf & Me.TopicSection.Name & vbCrLf & Me.ReviewerType.Name & vbCrLf & Me.Reviewer.Name & vbCrLf & Me.ReviewerReportArea.Name & vbCrLf & Me.Individual.Name & vbCrLf & vbCrLf & Me.Team.Name & vbCrLf & Me.txtCount.Name & vbCrLf & Me.Repeat.Name & vbCrLf & Me.Exception.Name & vbCrLf & Me.L1.Name & vbCrLf & Me.L2.Name & vbCrLf & Me.L3.Name & vbCrLf & Me.txtOther.Name & vbCrLf & Me.PriorityLevel.Name & vbCrLf & Me.Notes.Name & vbCrLf & Me.Outliers.Name & vbCrLf & Me.txtOther2.Name & vbCrLf
End If
If ctl.OldValue <> Me.txtHyperlink Then
    strUpdated = strUpdated & Me.DateEntered.Name & vbCrLf & Me.Approved.Name & vbCrLf & Me.CycleMonth.Name & vbCrLf & Me.DateReviewed.Name & vbCrLf & Me.ReportType.Name & vbCrLf & Me.MainSection.Name & vbCrLf & Me.TopicSection.Name & vbCrLf & Me.ReviewerType.Name & vbCrLf & Me.Reviewer.Name & vbCrLf & Me.ReviewerReportArea.Name & vbCrLf & Me.Individual.Name & vbCrLf & vbCrLf & Me.Team.Name & vbCrLf & Me.txtCount.Name & vbCrLf & Me.Repeat.Name & vbCrLf & Me.Exception.Name & vbCrLf & Me.L1.Name & vbCrLf & Me.L2.Name & vbCrLf & Me.L3.Name & vbCrLf & Me.txtOther.Name & vbCrLf & Me.PriorityLevel.Name & vbCrLf & Me.Notes.Name & vbCrLf & Me.Outliers.Name & vbCrLf & Me.txtOther2.Name & vbCrLf & Me.txtHyperlink.Name & vbCrLf
End If
 
strMsg = ""

For Each ctl In Me.Controls
    With ctl
        Select Case .ControlType
            Case acTextBox, acComboBox, acCheckBox
                If InStr(ctl.Tag, "Compare") > 0 Then
                    'If ctl.OldValue <> ctl.Value Then strMsg = "- " & ctl.Controls(0).Name & vbCrLf
                    If ctl.OldValue <> ctl.Value Then strMsg = "- " & strUpdated & 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
 
Upvote 0
It goes to the beginning of the code. Since it states that it was missing a with statement, I added it

But I just updated that code and tested and the message box gives me all field names, but I just need the names of the fields that were updated. Looking at the code closely I see that it's wrong anyways and will not give me what I need. As it stands now, the code below will give me a message box with all fields even if it was not updated ... back to the drawing board.

Code: 
For Each ctl In Me.Controls
    With ctl
        Select Case .ControlType
            Case acTextBox, acComboBox, acCheckBox
                If ctl.OldValue <> Me.EnteredBy Then
                    strUpdated = Me.EnteredBy.Name & vbCrLf
                End If
                If ctl.OldValue <> Me.DateEntered Then
                    strUpdated = strUpdated & Me.DateEntered.Name & vbCrLf
                End If
                If ctl.OldValue <> Me.Approved Then
                    strUpdated = strUpdated & Me.DateEntered.Name & vbCrLf & Me.Approved.Name & vbCrLf
                End If
                If ctl.OldValue <> Me.CycleMonth Then
                    strUpdated = strUpdated & Me.DateEntered.Name & vbCrLf & Me.Approved.Name & vbCrLf & Me.CycleMonth.Name & vbCrLf
                End If
                If ctl.OldValue <> Me.DateReviewed Then
                    strUpdated = strUpdated & Me.DateEntered.Name & vbCrLf & Me.Approved.Name & vbCrLf & Me.CycleMonth.Name & vbCrLf & Me.DateReviewed.Name & vbCrLf
                End If
                If ctl.OldValue <> Me.ReportType Then
                    strUpdated = strUpdated & Me.DateEntered.Name & vbCrLf & Me.Approved.Name & vbCrLf & Me.CycleMonth.Name & vbCrLf & Me.DateReviewed.Name & vbCrLf & Me.ReportType.Name & vbCrLf
                End If
                If ctl.OldValue <> Me.MainSection Then
                    strUpdated = strUpdated & Me.DateEntered.Name & vbCrLf & Me.Approved.Name & vbCrLf & Me.CycleMonth.Name & vbCrLf & Me.DateReviewed.Name & vbCrLf & Me.ReportType.Name & vbCrLf & Me.MainSection.Name & vbCrLf
                End If
                If ctl.OldValue <> Me.TopicSection Then
                    strUpdated = strUpdated & Me.DateEntered.Name & vbCrLf & Me.Approved.Name & vbCrLf & Me.CycleMonth.Name & vbCrLf & Me.DateReviewed.Name & vbCrLf & Me.ReportType.Name & vbCrLf & Me.MainSection.Name & vbCrLf & Me.TopicSection.Name & vbCrLf
                End If
                If ctl.OldValue <> Me.ReviewerType Then
                    strUpdated = strUpdated & Me.DateEntered.Name & vbCrLf & Me.Approved.Name & vbCrLf & Me.CycleMonth.Name & vbCrLf & Me.DateReviewed.Name & vbCrLf & Me.ReportType.Name & vbCrLf & Me.MainSection.Name & vbCrLf & Me.TopicSection.Name & vbCrLf & Me.ReviewerType.Name & vbCrLf
                End If
                If ctl.OldValue <> Me.Reviewer Then
                    strUpdated = strUpdated & Me.DateEntered.Name & vbCrLf & Me.Approved.Name & vbCrLf & Me.CycleMonth.Name & vbCrLf & Me.DateReviewed.Name & vbCrLf & Me.ReportType.Name & vbCrLf & Me.MainSection.Name & vbCrLf & Me.TopicSection.Name & vbCrLf & Me.ReviewerType.Name & vbCrLf & Me.Reviewer.Name & vbCrLf
                End If
                If ctl.OldValue <> Me.ReviewerReportArea Then
                    strUpdated = strUpdated & Me.DateEntered.Name & vbCrLf & Me.Approved.Name & vbCrLf & Me.CycleMonth.Name & vbCrLf & Me.DateReviewed.Name & vbCrLf & Me.ReportType.Name & vbCrLf & Me.MainSection.Name & vbCrLf & Me.TopicSection.Name & vbCrLf & Me.ReviewerType.Name & vbCrLf & Me.Reviewer.Name & vbCrLf & Me.ReviewerReportArea.Name & vbCrLf
                End If
                If ctl.OldValue <> Me.Individual Then
                    strUpdated = strUpdated & Me.DateEntered.Name & vbCrLf & Me.Approved.Name & vbCrLf & Me.CycleMonth.Name & vbCrLf & Me.DateReviewed.Name & vbCrLf & Me.ReportType.Name & vbCrLf & Me.MainSection.Name & vbCrLf & Me.TopicSection.Name & vbCrLf & Me.ReviewerType.Name & vbCrLf & Me.Reviewer.Name & vbCrLf & Me.ReviewerReportArea.Name & vbCrLf & Me.Individual.Name & vbCrLf
                End If
                If ctl.OldValue <> Me.Team Then
                    strUpdated = strUpdated & Me.DateEntered.Name & vbCrLf & Me.Approved.Name & vbCrLf & Me.CycleMonth.Name & vbCrLf & Me.DateReviewed.Name & vbCrLf & Me.ReportType.Name & vbCrLf & Me.MainSection.Name & vbCrLf & Me.TopicSection.Name & vbCrLf & Me.ReviewerType.Name & vbCrLf & Me.Reviewer.Name & vbCrLf & Me.ReviewerReportArea.Name & vbCrLf & Me.Individual.Name & vbCrLf & vbCrLf & Me.Team.Name & vbCrLf
                End If
                If ctl.OldValue <> Me.txtCount Then
                    strUpdated = strUpdated & Me.DateEntered.Name & vbCrLf & Me.Approved.Name & vbCrLf & Me.CycleMonth.Name & vbCrLf & Me.DateReviewed.Name & vbCrLf & Me.ReportType.Name & vbCrLf & Me.MainSection.Name & vbCrLf & Me.TopicSection.Name & vbCrLf & Me.ReviewerType.Name & vbCrLf & Me.Reviewer.Name & vbCrLf & Me.ReviewerReportArea.Name & vbCrLf & Me.Individual.Name & vbCrLf & vbCrLf & Me.Team.Name & vbCrLf & Me.txtCount.Name & vbCrLf
                End If
                If ctl.OldValue <> Me.Repeat Then
                    strUpdated = strUpdated & Me.DateEntered.Name & vbCrLf & Me.Approved.Name & vbCrLf & Me.CycleMonth.Name & vbCrLf & Me.DateReviewed.Name & vbCrLf & Me.ReportType.Name & vbCrLf & Me.MainSection.Name & vbCrLf & Me.TopicSection.Name & vbCrLf & Me.ReviewerType.Name & vbCrLf & Me.Reviewer.Name & vbCrLf & Me.ReviewerReportArea.Name & vbCrLf & Me.Individual.Name & vbCrLf & vbCrLf & Me.Team.Name & vbCrLf & Me.txtCount.Name & vbCrLf & Me.Repeat.Name & vbCrLf
                End If
                If ctl.OldValue <> Me.Exception Then
                    strUpdated = strUpdated & Me.DateEntered.Name & vbCrLf & Me.Approved.Name & vbCrLf & Me.CycleMonth.Name & vbCrLf & Me.DateReviewed.Name & vbCrLf & Me.ReportType.Name & vbCrLf & Me.MainSection.Name & vbCrLf & Me.TopicSection.Name & vbCrLf & Me.ReviewerType.Name & vbCrLf & Me.Reviewer.Name & vbCrLf & Me.ReviewerReportArea.Name & vbCrLf & Me.Individual.Name & vbCrLf & vbCrLf & Me.Team.Name & vbCrLf & Me.txtCount.Name & vbCrLf & Me.Repeat.Name & vbCrLf & Me.Exception.Name & vbCrLf
                End If
                If ctl.OldValue <> Me.L1 Then
                    strUpdated = strUpdated & Me.DateEntered.Name & vbCrLf & Me.Approved.Name & vbCrLf & Me.CycleMonth.Name & vbCrLf & Me.DateReviewed.Name & vbCrLf & Me.ReportType.Name & vbCrLf & Me.MainSection.Name & vbCrLf & Me.TopicSection.Name & vbCrLf & Me.ReviewerType.Name & vbCrLf & Me.Reviewer.Name & vbCrLf & Me.ReviewerReportArea.Name & vbCrLf & Me.Individual.Name & vbCrLf & vbCrLf & Me.Team.Name & vbCrLf & Me.txtCount.Name & vbCrLf & Me.Repeat.Name & vbCrLf & Me.Exception.Name & vbCrLf & Me.L1.Name & vbCrLf
                End If
                If ctl.OldValue <> Me.L2 Then
                    strUpdated = strUpdated & Me.DateEntered.Name & vbCrLf & Me.Approved.Name & vbCrLf & Me.CycleMonth.Name & vbCrLf & Me.DateReviewed.Name & vbCrLf & Me.ReportType.Name & vbCrLf & Me.MainSection.Name & vbCrLf & Me.TopicSection.Name & vbCrLf & Me.ReviewerType.Name & vbCrLf & Me.Reviewer.Name & vbCrLf & Me.ReviewerReportArea.Name & vbCrLf & Me.Individual.Name & vbCrLf & vbCrLf & Me.Team.Name & vbCrLf & Me.txtCount.Name & vbCrLf & Me.Repeat.Name & vbCrLf & Me.Exception.Name & vbCrLf & Me.L1.Name & vbCrLf & Me.L2.Name & vbCrLf
                End If
                If ctl.OldValue <> Me.L3 Then
                    strUpdated = strUpdated & Me.DateEntered.Name & vbCrLf & Me.Approved.Name & vbCrLf & Me.CycleMonth.Name & vbCrLf & Me.DateReviewed.Name & vbCrLf & Me.ReportType.Name & vbCrLf & Me.MainSection.Name & vbCrLf & Me.TopicSection.Name & vbCrLf & Me.ReviewerType.Name & vbCrLf & Me.Reviewer.Name & vbCrLf & Me.ReviewerReportArea.Name & vbCrLf & Me.Individual.Name & vbCrLf & vbCrLf & Me.Team.Name & vbCrLf & Me.txtCount.Name & vbCrLf & Me.Repeat.Name & vbCrLf & Me.Exception.Name & vbCrLf & Me.L1.Name & vbCrLf & Me.L2.Name & vbCrLf & Me.L3.Name & vbCrLf
                End If
                If ctl.OldValue <> Me.txtOther Then
                    strUpdated = strUpdated & Me.DateEntered.Name & vbCrLf & Me.Approved.Name & vbCrLf & Me.CycleMonth.Name & vbCrLf & Me.DateReviewed.Name & vbCrLf & Me.ReportType.Name & vbCrLf & Me.MainSection.Name & vbCrLf & Me.TopicSection.Name & vbCrLf & Me.ReviewerType.Name & vbCrLf & Me.Reviewer.Name & vbCrLf & Me.ReviewerReportArea.Name & vbCrLf & Me.Individual.Name & vbCrLf & vbCrLf & Me.Team.Name & vbCrLf & Me.txtCount.Name & vbCrLf & Me.Repeat.Name & vbCrLf & Me.Exception.Name & vbCrLf & Me.L1.Name & vbCrLf & Me.L2.Name & vbCrLf & Me.L3.Name & vbCrLf & Me.txtOther.Name & vbCrLf
                End If
                If ctl.OldValue <> Me.PriorityLevel Then
                    strUpdated = strUpdated & Me.DateEntered.Name & vbCrLf & Me.Approved.Name & vbCrLf & Me.CycleMonth.Name & vbCrLf & Me.DateReviewed.Name & vbCrLf & Me.ReportType.Name & vbCrLf & Me.MainSection.Name & vbCrLf & Me.TopicSection.Name & vbCrLf & Me.ReviewerType.Name & vbCrLf & Me.Reviewer.Name & vbCrLf & Me.ReviewerReportArea.Name & vbCrLf & Me.Individual.Name & vbCrLf & vbCrLf & Me.Team.Name & vbCrLf & Me.txtCount.Name & vbCrLf & Me.Repeat.Name & vbCrLf & Me.Exception.Name & vbCrLf & Me.L1.Name & vbCrLf & Me.L2.Name & vbCrLf & Me.L3.Name & vbCrLf & Me.txtOther.Name & vbCrLf & Me.PriorityLevel.Name & vbCrLf
                End If
                If ctl.OldValue <> Me.Notes Then
                    strUpdated = strUpdated & Me.DateEntered.Name & vbCrLf & Me.Approved.Name & vbCrLf & Me.CycleMonth.Name & vbCrLf & Me.DateReviewed.Name & vbCrLf & Me.ReportType.Name & vbCrLf & Me.MainSection.Name & vbCrLf & Me.TopicSection.Name & vbCrLf & Me.ReviewerType.Name & vbCrLf & Me.Reviewer.Name & vbCrLf & Me.ReviewerReportArea.Name & vbCrLf & Me.Individual.Name & vbCrLf & vbCrLf & Me.Team.Name & vbCrLf & Me.txtCount.Name & vbCrLf & Me.Repeat.Name & vbCrLf & Me.Exception.Name & vbCrLf & Me.L1.Name & vbCrLf & Me.L2.Name & vbCrLf & Me.L3.Name & vbCrLf & Me.txtOther.Name & vbCrLf & Me.PriorityLevel.Name & vbCrLf & Me.Notes.Name & vbCrLf
                End If
                If ctl.OldValue <> Me.Outliers Then
                    strUpdated = strUpdated & Me.DateEntered.Name & vbCrLf & Me.Approved.Name & vbCrLf & Me.CycleMonth.Name & vbCrLf & Me.DateReviewed.Name & vbCrLf & Me.ReportType.Name & vbCrLf & Me.MainSection.Name & vbCrLf & Me.TopicSection.Name & vbCrLf & Me.ReviewerType.Name & vbCrLf & Me.Reviewer.Name & vbCrLf & Me.ReviewerReportArea.Name & vbCrLf & Me.Individual.Name & vbCrLf & vbCrLf & Me.Team.Name & vbCrLf & Me.txtCount.Name & vbCrLf & Me.Repeat.Name & vbCrLf & Me.Exception.Name & vbCrLf & Me.L1.Name & vbCrLf & Me.L2.Name & vbCrLf & Me.L3.Name & vbCrLf & Me.txtOther.Name & vbCrLf & Me.PriorityLevel.Name & vbCrLf & Me.Notes.Name & vbCrLf & Me.Outliers.Name & vbCrLf
                End If
                If ctl.OldValue <> Me.txtOther2 Then
                    strUpdated = strUpdated & Me.DateEntered.Name & vbCrLf & Me.Approved.Name & vbCrLf & Me.CycleMonth.Name & vbCrLf & Me.DateReviewed.Name & vbCrLf & Me.ReportType.Name & vbCrLf & Me.MainSection.Name & vbCrLf & Me.TopicSection.Name & vbCrLf & Me.ReviewerType.Name & vbCrLf & Me.Reviewer.Name & vbCrLf & Me.ReviewerReportArea.Name & vbCrLf & Me.Individual.Name & vbCrLf & vbCrLf & Me.Team.Name & vbCrLf & Me.txtCount.Name & vbCrLf & Me.Repeat.Name & vbCrLf & Me.Exception.Name & vbCrLf & Me.L1.Name & vbCrLf & Me.L2.Name & vbCrLf & Me.L3.Name & vbCrLf & Me.txtOther.Name & vbCrLf & Me.PriorityLevel.Name & vbCrLf & Me.Notes.Name & vbCrLf & Me.Outliers.Name & vbCrLf & Me.txtOther2.Name & vbCrLf
                End If
                If ctl.OldValue <> Me.txtHyperlink Then
                    strUpdated = strUpdated & Me.DateEntered.Name & vbCrLf & Me.Approved.Name & vbCrLf & Me.CycleMonth.Name & vbCrLf & Me.DateReviewed.Name & vbCrLf & Me.ReportType.Name & vbCrLf & Me.MainSection.Name & vbCrLf & Me.TopicSection.Name & vbCrLf & Me.ReviewerType.Name & vbCrLf & Me.Reviewer.Name & vbCrLf & Me.ReviewerReportArea.Name & vbCrLf & Me.Individual.Name & vbCrLf & vbCrLf & Me.Team.Name & vbCrLf & Me.txtCount.Name & vbCrLf & Me.Repeat.Name & vbCrLf & Me.Exception.Name & vbCrLf & Me.L1.Name & vbCrLf & Me.L2.Name & vbCrLf & Me.L3.Name & vbCrLf & Me.txtOther.Name & vbCrLf & Me.PriorityLevel.Name & vbCrLf & Me.Notes.Name & vbCrLf & Me.Outliers.Name & vbCrLf & Me.txtOther2.Name & vbCrLf & Me.txtHyperlink.Name & vbCrLf
                End If
        End Select
    End With
Next
 
Upvote 0
I have also changed the code to the one below and now I get a message box letting me know that the last field was updated. For instance, if I updated Count and Priority, then the message will tell me that Priority was updated ... not Count and Priority.

Code: 
Private Sub UpdateRecord_Click()
Dim ctl As Control
Dim strMsg As String

strMsg = ""
For Each ctl In Me.Controls
    With ctl
        Select Case .ControlType
            Case acTextBox, acComboBox, acCheckBox
                If InStr(ctl.Tag, "Compare") > 0 And Nz(ctl.Value) <> Nz(ctl.OldValue) Then
                    If ctl.OldValue <> ctl.Value Then strMsg = "- " & ctl.ControlSource & 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
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Automaticaly Sum multiple fields in a form
Replies
9
Views
510
welshgasman
welshgasman
K
  • Question
Help with converting Form record source from Table to Query
Replies
1
Views
255
welshgasman
welshgasman
D
  • Question
Record Order in Access
Replies
6
Views
258
jackd
J
K
  • Question
Query that can filter using field name
Replies
1
Views
446
Micron
M
C
  • Solved
Access Querry Seeking Records that has 0 usage in Three Consecutive Months
Replies
5
Views
230
Chris101
C

Forum statistics

Threads
1,214,952
Messages
6,122,457
Members
449,083
Latest member
Ava19

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top