• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 247
  • Last Modified:

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
0
napsternova
Asked:
napsternova
  • 6
  • 2
1 Solution
 
omgangCommented:
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
 
napsternovaAuthor 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
 
omgangCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
omgangCommented:
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
 
omgangCommented:
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
 
omgangCommented:
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
 
napsternovaAuthor 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
 
omgangCommented:
Which one did you try?  Let me know if you are receiving errors.
OM Gang
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now