I've got a bit of a problem/scenario with the Me.Dirty statement.
I need to update the Status field with "Corrected" if any of the fields on the form has changed, in other words Me.Dirty = True, except if the Notes field has been updated.
The Me.Dirty = True does the job 100% but I would like to exclude the Notes field without checking each field individually (e.g Nz(Me.Energy_Type.OldValue, "") <> Nz(Me.Energy_Type, "").
I've got about 25 forms to change and if I'm sure there must be a more effective way rather than to include the above check for each field in an IF statement for every form.
Use a function with a loop through the controls. This function can be applied to any form. Put it on a Standard Module.
Aircode (ie untested) follows:
Code:
Public Function FlagChange(TargetForm As Form, ExcludeFieldName as String) As Boolean
Dim ctrl as Control
For Each ctrl In TargetForm
With ctrl
If .ControlType = acTextBox _
And .Name <> ExcludeFieldName _
And Nz(.OldValue) <> Nz(.Value) Then
FlagChange = True
Exit For
End If
End With
Next
End Function
Call this function from any form:
Code:
If FlagChange(Me, NotesFieldName) Then: Me.Status = "Corrected"
Notice how the field type is detected. This skips buttons, subforms, etc.
You may need to add others to be included eg combos.
If by change you mean edited, which is to say the Old Value is replaced by a New Value, you can do it without addressing each Control by name, by simply looping thru all data displaying Controls except the Notes Textbox.
You can also do it without using Me. = Dirty; you just need your code in the Form_BeforeUpdate event, which fires anytime a record is dirtied and saved.
To accomplish your task you need to
In Design View select the Notes Textbox then go to Properties - Other and enter an Asterisk (*) in the Tag Property box
Dim a Variable as Type Control
Dim a Variable as Type Integer and set it to Zero
Exclude New Records
Loop thru all Controls that can hold data except the Notes Textbox
If any other Control has been changed add 1 to the Integer Variable
After all Controls except Notes have been looped thru, if the Integer Variable is Greater Than Zero set the Status Textbox to "Corrected"
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctr As Control
Dim CCount As Integer
CCount = 0
If Not Me.NewRecord Then
For Each ctr In Me.Controls
If ctr.Tag <> "*" Then
Select Case ctr.ControlType
Case acTextBox, acComboBox, acListBox, acCheckBox
If ctr.Value <> ctr.OldValue Then CCount = CCount + 1
End Select
End If
Next
If CCount > 0 Then Me.Status = "Corrected"
End If
End Sub
If by 'change' you mean to also include a circumstance in which a Control originally left blank is now filled in, I cannot think of a way to do it without addressing each Control by name. Perhaps someone more talented (or less tired) than myself can come up with a way.
I have to say I'm somewhat curious about you having to do this same type of thing to 25 different forms! It makes me wonder somewhat about your overall database design. But this is a generic hack that can be implemented in multiple forms without changing anything at all except, perhaps, which Control'sTag Property you need to place the Asterisk in.
Linq ;0)> Late Note: Dang! Stepped out to walk the dog and the Man from Down Under stepped in!
Linq has provided the detail of pretty much everything that I glossed over in my function. Notice the use of Case to choose the controltypes.
The function is a better way to impliment it. Linq shows where the function should be called and how to avoid doing it for a new record.
You don't really need to count the number of changes. In mine I just continue to set the boolean value to True.
You could also include Exit For after finding a change since only one instance of true is required. This could save some milliseconds if it finds the change early in the function.
Sorry Glaxiom, but I went with Linq's solution. I'm sure yours work just a well.
@Linq; I was not involved with the db design, but I basically need to check the status of each record changed by the user. There are DataStatus and Notes field for each form/table. I'm sure there is a better way of doing this, but at the moment the above will do.
Linq's solution and mine were quite simliar in the way they handled the actual processing. However you missed a key factor in my use of a function which I assume you didn't understand.
Linq's solution required pasting the same code into each of your 25 forms probably editing it to change the name of the NoteField in each case. My solution put the code in one place and called it with a single line in each form.
This approach is far more elegant, much easier to set up and maintain since any change to the process requires the change just once in the function rather than the 25 different locations (without making a mistake in any of the edits).
It is also vastly easier to read because the detailed process is encapsulated in the function.
In my own coding I would probably have gone one step further. I tend to avoid putting code blocks into the event's procedures but prefer to hold the block in a separate sub.
In this case the structure would be
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
FlagChanges
' Call Subs for any other actions required at this point.
End Sub
Private Sub FlagChanges()
If FlagChange(Me, NotesFieldName) Then: Me.Status = "Corrected"
End Sub
Now anyone reading the BeforeUpdate procedure knows at a glance that the code is going to flag changes as the record is saved without having to wade through the detailed code. This particular case is marginally trivial since there is only one sub to run but where several procedures (such as validations) are run it is far tidier code.
If the person maintaining the code wants to know how the flaging (or a particular validation) is achieved, they simply look at the relevant sub. In the case of Flag Changes they the can see it sets Status field using a function.
They will also notice the NoteField is an argument of the function, drawing their attention to its importance rather than analysing the detailed code to see what is significant. They will also notice that argument is called ExcludeFieldName which is probaly all they need to know at that. (Perhaps they were wondering why that field was not triggering the flag.)
If the need to know the full detail they consult the function.
Now if they deside to, say, add another excluded field they simply make the change in the function and where it is called.
With sensible naming of the subs and functions this structure is self commenting. If all that flagging code and validation was shoved into the BeforeUpdate procedure it would need to be commented to indicate what each block was doing.
I urge all developers to think hard about how they structure their code. The use functions and separate subs to encapsulate blocks of code is a big part of what makes professional coding.
