Fields Updating Message

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Fields Updating Message

  1. #1
    Board Regular
    Join Date
    Jan 2013
    Posts
    130
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Fields Updating Message

     
    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

  2. #2
    Board Regular
    Join Date
    May 2013
    Posts
    245
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Fields Updating Message

    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
    Code:
    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
    Last edited by welshgasman; Dec 2nd, 2017 at 09:20 AM.
    Excel 2007

  3. #3
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,329
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Fields Updating Message

    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.
    Last edited by Micron; Dec 3rd, 2017 at 03:18 PM.
    Tips for posting problems:
    1) "doesn't work" doesn't help. Post error message text/numbers and/or state what's happening.
    2) if posting code or sql, use code tags; specify on which code line errors occur, if applicable
    3) try to be specific; assume we know nothing about your issue - because we don't!

    Make all suggested changes in copies of your database or to its objects.

    "1 out of 1010 people understand binary. The other 1001 don't."

  4. #4
    Board Regular
    Join Date
    Jan 2013
    Posts
    130
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Fields Updating Message

    Hello,

    This is my current code:
    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?

  5. #5
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,329
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Fields Updating Message

    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.

  6. #6
    Board Regular
    Join Date
    Jan 2013
    Posts
    130
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Fields Updating Message

    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.

  7. #7
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,329
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Fields Updating Message

    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.

  8. #8
    Board Regular
    Join Date
    Jan 2013
    Posts
    130
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Fields Updating Message

    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
    Code:
    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.

  9. #9
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,329
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Fields Updating Message

      
    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

    Code:
    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.
    Tips for posting problems:
    1) "doesn't work" doesn't help. Post error message text/numbers and/or state what's happening.
    2) if posting code or sql, use code tags; specify on which code line errors occur, if applicable
    3) try to be specific; assume we know nothing about your issue - because we don't!

    Make all suggested changes in copies of your database or to its objects.

    "1 out of 1010 people understand binary. The other 1001 don't."

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com