Need to edit last updated script to exclude certain critera

Hello,
I have this code

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.EditDate = Date
End Sub

It updates the date the record was last updated but, can I single out certain fields?  If the end user edits say their name I wouldn't want the Date field to auto update.

Thank you
napsternovaDeveloperAsked:
Who is Participating?
 
omgangConnect With a Mentor IT ManagerCommented:
Actually, if you have more than one or two controls that you want to ignore (even if the user changes data in those fields) it'll be simpler perhaps to use the Tag property of the control to test.

For the controls you want to ignore add this string to the Tag property
ignore

Then the Sub would look like

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Form_BeforeUpdate

    Dim ctl As Control
    Dim blUpdateDate As Boolean
    Dim intCount As Integer
    Dim strCtlName As String
   
    For Each ctl In Me.Controls
        If ctl.ControlType <> acLabel Then
                'check every control except for controls Tag'd with 'ignore'
            If ctl.Properties("Tag") <> "ignore" Then
                strCtlName = ctl.Name
                    'only update boolean variable if current and old values don't match
                If Me(strCtlName).Value <> ctl.OldValue Then blUpdateDate = True
            End If
        End If
    Next ctl
   
        'only update EditDate control if boolean variable is True
    'If blUpdateDate Then Me.EditDate = Date()
   
    MsgBox blUpdateDate, , "Update Date Field?"

Exit_Form_BeforeUpdate:
    Exit Sub

Err_Form_BeforeUpdate:
    MsgBox Err.Number & ", " & Err.Description, , "Error in Sub Form_BeforeUpdate of VBA Document Form_Form A"
    Resume Exit_Form_BeforeUpdate
   
End Sub


Make sense?
OM Gang
0
 
omgangIT ManagerCommented:
There are, of course, many ways to accomplish what you want but here's an idea
Create a form level public variable

Public blUpdateDate As Boolean


Now, for the form controls where if the user makes a change you WANT to update the EditDate field do this
Private Sub ControlName_AfterUpdate()
        'set value of public variable
    blUpdateDate = True
End Sub


Now change your existing sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
If blUpdateDate Then Me.EditDate = Date
End Sub

Make sense?
OM Gang
0
 
napsternovaDeveloperAuthor Commented:
Maybe instead of adding this to every field (there are approx 50) can I just omit the one I don't want to trigger the update?
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
omgangIT ManagerCommented:
Think about that for a second.  What we're concerned with is whether the user edited any of the other fields on the form.  If the user edits their name AND one of the other fields you'll still want to update the EditDate field.

You could instead add a routine to check the value of every field on the form to determine if any of them were changed, e.g. compare current value to the fields OldValue.  I'll try and post and example here is a bit.
OM Gang
0
 
omgangIT ManagerCommented:
OK, a couple of things to note:
I commented out the line that updates the EditDate field on your form and instead have it displaying a message box letting you know if the EditDate field would have been udpated or not.  This is for testing.

The first conditonal is checking to see if the current control is a label and only proceeding if False, e.g. not a lable control.  This means all other controls will be evaluated including list boxes, combo boxes, lines, rectangles, etc.  You may need to add addtional checks to ignore other control types on your form.  See this link for the various types of form controls http://msdn.microsoft.com/en-us/library/aa224135(v=office.11).aspx

The second conditional checks to see if the current control is the one you want to ignore.  You need to change the code there to include the actual name of the control on your form.  You mentioned Name in your original post so that's what I used.

Obviously if you have other controls on the form that should also be ignored you'll need to modify the code to handle them.  Either add more criteria to the conditional
If strCtlName <> "Name" And strCtlName <> "SomeOtherField" Then
or you can change from to a Select Case construct
Select Case strCtlName
    Case "Name"
        'do nothing

    Case "SomeOtherField"
        'do nothing

    Case "AnotherFieldIWantToIgnore"
        'do nothing

    Case Else
            'only update boolean variable if current and old values don't match
        If Me(strCtlName).Value <> ctl.OldValue Then blUpdateDate = True

End Select

Let me know if you have any questions
OM Gang




Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Form_BeforeUpdate

    Dim ctl As Control
    Dim blUpdateDate As Boolean
    Dim intCount As Integer
    Dim strCtlName As String
   
    For Each ctl In Me.Controls
        If ctl.ControlType <> acLabel Then
            strCtlName = ctl.Name
                'check every control except for the Name control
            If strCtlName <> "Name" Then            '<------- you need to change 'Name" to whatever the actual name of the control is on your form
                    'only update boolean variable if current and old values don't match
                If Me(strCtlName).Value <> ctl.OldValue Then blUpdateDate = True
            End If
        End If
    Next ctl
   
        'only update EditDate control if boolean variable is True
    'If blUpdateDate Then Me.EditDate = Date()
   
    MsgBox blUpdateDate, , "Update Date Field?"

Exit_Form_BeforeUpdate:
    Exit Sub

Err_Form_BeforeUpdate:
    MsgBox Err.Number & ", " & Err.Description, , "Error in Sub Form_BeforeUpdate of VBA Document Form_Form A"
    Resume Exit_Form_BeforeUpdate
   
End Sub
0
 
omgangIT ManagerCommented:
OK, guess I should have also added a line to destroy the object variable

Exit_Form_BeforeUpdate:
    Set ctl = Nothing
    Exit Sub

Err_Form_BeforeUpdate:
    MsgBox Err.Number & ", " & Err.Description, , "Error in Sub Form_BeforeUpdate of VBA Document Form_Form A"
    Resume Exit_Form_BeforeUpdate
   
End Sub

OM Gang
0
 
napsternovaDeveloperAuthor Commented:
Yeah because I have a temporary "print" field that gets cleared automatically.  But whenever someone selects the "print" check box to add the data to the report the "Last Updated" field updates.  I am going to give your code another look as it didn't work the first time.  Most likely my error.
0
 
omgangIT ManagerCommented:
Which one did you try?  Let me know if you are receiving errors.
OM Gang
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.