Testing whether any controls excluding one are dirty...

[pollardski]

Hello all. I have a form with many text box controls and a couple combo box controls. I would like to test whether any of the controls are dirty--except for one, which I know is dirty. I have tried using the Tag property of the one control to exclude it in a For Each loop where I test if ctl.Value = ctl.OldValue. Can't get that working. I think there's a problem when it runs into the unbound controls on my form. But I don't really "know" vba. Just been stealing code and adapting. Any Help? Thanks.

 

[Banana]

Exactly what happens when you use For Each... loop? I'd imagine it would work.

Perhaps if you also posted what you want to accomplish (e.g. why do you need to test whether they are dirty), there could be a better way...

 

[RuralGuy]

Using the Tag property to "tag" which control you want to check should work. Post what code you used that did not work.

 

[pollardski]

Actually I am using the tag property for other controls already, so I tried tagging "the one" control with the tag "check" and excluding it which I can't get to work.

Private Sub Test_Click()
    Dim ctl As Control
  
    For Each ctl In Me.Controls
        If (ctl.Tag <> "check") Then
            If (ctl.Value <> ctl.OldValue) Then
                Me.lblItemBanner.Caption = "dirty test"
            End If
        End If
    Next ctl
End Sub

Also, I'm pretty sure I could get this done by just going through every control separately, but I have 30 on the form and I really don't want to do that.

 

[RuralGuy]

I understand why that code does not work. Labels are controls as well but will blow up when you try and look at their .Value or .OldValue property since they don't have them. Are you using any of the unbound controls for calculations or is their ControlSource empty? As Banana queried:

Perhaps if you also posted what you want to accomplish (e.g. why do you need to test whether they are dirty), there could be a better way...

It may be easier than we have it now.

 

[pollardski]

It just hit me that it shouldn't be hard to do using the tags of the controls I want to test because there are only a couple different ones. So I tried this...

Private Sub Test_Click()
    Dim ctl As Control
  
    For Each ctl In Me.Controls
        If (ctl.Tag = "editable") Or (ctl.Tag = "once") Then
            If (ctl.Value <> ctl.OldValue) Then
                Me.lblItemBanner.Caption = "check dirty"
            End If
        End If
    Next ctl
End Sub

But it didn't work.

So this is what I'm doing with the form. I've put a field called "Control" in the table that the form is based on. Control is yes/no and is set to "yes" during Form_Current so I can run what I want during Before_Update. That way I can control record updates and the mouse wheel. Here's the code I use for that...

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If (Me.chkControl.Value = -1) Then
        DoCmd.CancelEvent
    End If
End Sub

Then if I want to update, I just throw in "Me.chkControl.Value = 0" right before the code and set it back to -1 right after. I guess I'll add to my first question, anyone see a problem doing that?

 

[Banana]

Are you trying to make a log of what users does with your data? If that is the case, there's already better solutions out there; search on "Audit Trails".

Also don't forget to take a look at Ghudson's "Better Mousetrap" to prevent your users from going out of a dirty record.

The above two method is tried and true, and not that hard to implement. Give it a whirl.

If that's not the case (and mind, I am still not sure why you want to do this), I suppose it could work:

For each ctl in Me.Controls
    If ctl.ControlType = acTextBox Or ctl.ControlType = acListbox Then
         'Do something
    End If
Next ctl

Adding an test (I am not positive about the exact syntax, but know you can test for what type of control this is) will prevent you from doing something to labels or other controls that doesn't need to be tested.

HTH.

 

[RuralGuy]

Have you single stepped this code to see what is happening? It looks like it would work to me.

 

[pollardski]

Okay, I got the code to work by making the first part of the control's tag "dirty" and whatever I want after that to make it more unique. And this code:

    Dim Ctl As Control
    Dim ctlTag As String
  
    For Each Ctl In Me.Controls
        ctlTag = Ctl.Tag
        If InStr(1, ctlTag, "dirty", vbBinaryCompare) Then
            If (Ctl.Value <> Ctl.OldValue) Then
                Me.lblItemBanner.Caption = "check dirty"
            End If
        End If
    Next Ctl

But now I realized that it doesn't work if the control started out as Null or "". Any ideas about that?

 

[RuralGuy]

As Banana said, have you looked at ghudson's Better Mouse Trap code? I'm still not quite sure why you are doing this but the Nz() function should take care of any Null situations.

 

[Banana]

Did you even try to test for controltype?

Set ctls = Me.Controls

ctlcount = ctls.Count

For i = 0 To ctls.Count - 1
    If ctls(i).ControlType = acTextBox Then
        Debug.Print ctls(i)
    End If
Next i

 

[pollardski]

Well I don't have a problem targeting the right controls now. But it seems that a control that was Null and then had data entered doesn't register using "Value <> OldValue". And I'd rather not have a default value for every field. There's got to be a way of checking whether a control changed from Null to something. Am I missing something obvious?

 

[Banana]

Just so we know, wouldn't Ghudson's mouse trap (RuralGuy gave you a link) do what you need to? I still don't know why you want to do this and can't help but wonder if there's a simpler way to do this.

That said, I'd do a test on OldValue first:

If Not IsNull(OldValue) Then
   If Value <> OldValue Then
       'Do whatever it was you wanted to do
   End If
End If