Solved

Need to edit last updated script to exclude certain critera

Posted on 2011-02-25
8
232 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
  • 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
 
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 28

Accepted Solution

by:
omgang earned 250 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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now