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]

Try ctl.value for the current value
Also you need to concatenate each field altered to the message string, so would need

If ctl.OldValue <> ctl Then strMsg = strMsh &  ctl.Controls(0) & vbCrLf

HTH

 

[Micron]

value is the default property of those types of controls, so it shouldn't matter. The fact that you've included other things in the tag property is definitely an issue. The tag property is then not "apple" it is "apple; orange; lemon". Having multiple values is OK; in fact it's not unheard of to me. If you must have that, you will have to maintain an order of tag values throughout all such controls, grab the list in code, and extract the needed value by its index or (perhaps easier and doesn't require specific order of values) use a string function like Instr to find "orange". You could research "extract word from string" or similar, or here is one way.

 

[MHamid]

I've updated the code, but I am still not seeing my message box. Do I really need to concatenate each field altered to the message string?

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 InStr(ctl.Tag, "Compare") = "Compare" Then
                    If ctl.OldValue <> ctl.Value Then strMsg = "- " & ctl.Controls(0) & vbCrLf
                End If
        End Select
    End With
Next
If strMsg <> "" Then MsgBox "You updated:" & vbCrLf & vbCrLf & strMsg, vbInformation, "SUCCESS!"

 

[MHamid]

updated the Instr function, but the message box is still not popping up.

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 InStr(ctl.Tag, "Compare") > 0 Then
                    If ctl.OldValue <> ctl.Value Then strMsg = "- " & ctl.Controls(0) & vbCrLf
                End If
        End Select
    End With
Next

 

[Micron]

I see 2 message boxes in one code, 1 in the other, so I don't know which one you're referring to. If the first and you expect it should, then your expression is not evaluating to what you think it is. So typical troubleshooting: put a break on the message box line since the expression it's based on has been evaluated (you cannot evaluate a line where code has stopped on - you must allow that line to execute in order to perform any calculations). Run the code. When execution stops on the break, you test the expression/sql/calculation - whichever it is. Options:
- making the expression a test message. If a blank message pops up, it either evaluated to Null or perhaps an empty string. You get whatever the expression evaluated to.
- copying the expression to the immediate window, as in ?IsNull(DLookup("RefID", "QAMaster", "txtRefID='" & Me.txtRefID.Value & "'")) and hit Enter. In this case, you should get either true, false. I would not expect 1 or 0 but they are eqivalent
- debug.print IsNull(DLookup("RefID", "QAMaster", "txtRefID='" & Me.txtRefID.Value & "'")). Should be the same result. For a 1 time check, I'd use ?
You preface any immediate window test with ? and hit Enter for the evaluation. Note: if you ever need to add a line wrap in the window, it is Ctrl+Enter

If you're referring to the second message not prompting, you could apply the same test on the Instr expression. I'll give you a hint: it will never evaluate to "Compare" in the first code. As for the 2nd, you have not provided the delimiter. Take a look at the syntax for the expression again. Seeing as how there's no message code there, I don't really follow what the problem is.

I will be on the road tomorrow morning and will have limited opportunity to continue responding. Perhaps at night from a hotel, but no promises. You might want to post what you have put into the tag property if the above doesn't help you solve it. Good luck!

 

[MHamid]

Hello,

I'm not worried about the first message box. I'm actually going to delete because RefID will never be duplicated since it's an auto-generated number. Therefore, that code is pointless.

As for the second one. This is the actual full code for it.

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 Then
                    If ctl.OldValue <> ctl.Value 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 will take a look at what you're suggesting for the second message box and will post with any issues.

 

[MHamid]

I tweaked the code very little and tested it with multiple fields and I'm not see the message box letting me know all of the fields that were updated.
However, when I only update 1 field, I see the message box.

What do I need to do now to have the message box list all the fields that were updated?

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 Then
                     If ctl.OldValue <> ctl.Value Then strMsg = "- " & ctl.Controls(0).Name & 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

 

[welshgasman]

Walk through the code step by step with F8. That will show you what the problem is.
No point looking at it and thinking 'It should go through this path'

The obvious reason is the string is not being filled.

 

[MHamid]

I try to walkthrough the code with F8 but nothing happens.

Also, I believe you previously said that I should concatenate each field altered to the message string. How would I go about doing that?

 

[welshgasman]

I did, but that was only to show ALL the controls that were modified, your code at present will only show the last one modified, but no point changing that until you get the display working.
You say 'nothing happens', what does that mean?

Does it even get to

If InStr(ctl.Tag, "Compare") > 0 Then