?
Solved

Need to edit last updated script to exclude certain critera

Posted on 2011-02-25
8
Medium Priority
?
243 Views
Last Modified: 2012-08-13
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
Comment
Question by:napsternova
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
8 Comments
 
LVL 28

Expert Comment

by:omgang
ID: 34981288
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
 

Author Comment

by:napsternova
ID: 34981427
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
 
LVL 28

Expert Comment

by:omgang
ID: 34981531
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.

 
LVL 28

Expert Comment

by:omgang
ID: 34981925
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
 
LVL 28

Accepted Solution

by:
omgang earned 1000 total points
ID: 34982041
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
 
LVL 28

Expert Comment

by:omgang
ID: 34982063
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
 

Author Comment

by:napsternova
ID: 35111971
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
 
LVL 28

Expert Comment

by:omgang
ID: 35112068
Which one did you try?  Let me know if you are receiving errors.
OM Gang
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question