Hello,

This may sound a bit weird but is it possible to test a form for Me.Dirty with an exception?

I have a form with a command button that when clicked, if any fields except one are changed, I want the routine to proceed. If no fields (or none except one) are changed, then it simply runs Exit Sub. I could write IF Me.fields.dirty statements for each field, but there are numerous ones on the form.

Thanks,
SKK

I am not aware of any syntax that would match your suggestion, "IF Me.fields.dirty". Where did you find a reference to it?

The "Me" in "Me.Dirty" refers to the form. It is the the record which is dirty or not. Changing any field dirties the record.

You can only do a control-by-control comparison to compare the old value to the new value and let the save occur if the values meet your requirement.

Sort of like this:


I have not tested these really thoroughly, so I'm sure there are some tweaks you make to improve it.

George

Thanks George. I'll play with this a bit and keep you posted.

Steve

Good luck with the rest of your project.

George

George,

First off, I’m no programmer so please bear with me.
I don’t expect you to spend a lot of time on this as the problem is actually quite minimal but if you think there’s a simple fix…

I’m receiving a Run-time error 2447
There is an invalid use of . (dot) or ! operator or invalid parentheses.

The code is bombing on line:
'If (Nz(fld.Value, 0) <> Nz(fld.OldValue, 0) And fld.Name <> "YOURQUALIFYINGFIELD") Then

I’m not quite sure what I am suppose to replace "YOURQUALIFYINGFIELD" with. The field that will usually be dirty is ConstYear (named txtConstYear) in a form called frmFacilities. The table ConstYear is in is called tblFacility.

I tried replacing "YOURQUALIFYINGFIELD” with
ConstYear, “ConstYear”, [ConstYear], and “txtConstYear”

Thanks,
Steve

I found a work-around which seems to do fine. So, unless I run into some other glitch, you can disregard this post for now.

Again, thanks for your time,
Steve Kipping

I would just loop through controls with an on error resume, using a boolean starting with false toggling true if any control is dirty. Maybe grabbing the control name and exiting the loop so you can build a message, clear it, etc.

Could you explain briefly?

SKK

Something like this, although if its a textbox you are interested, you could say if controls(i).name <>"Mytextbox" then dr = TRUE

or something like that. Maybe at the end say, if DR =false and is notnull(mytextbox).... something to that effect.

I had to use an on error because the ID seems to have a problem with null. Of course if I start at 1 instead of 0 on my form it skips the autonumber ID.

Private Sub Form_Dirty(Cancel As Integer)
Dim i As Integer, DR As Boolean
DR = False
For i = 0 To Me.Controls.Count - 1
On Error Resume Next
Debug.Print i & " " & Me.Controls(i).ControlType & "/" & Me.Controls(i).Name
Select Case Me.Controls(i).ControlType

Case 109 'text
If Not IsNull(Me.Controls(i).Text) Then
DR = True
End If
Case 111 'combo
If Not IsNull(Me.Controls(i).Value) Then
DR = True
End If
Case 110 'listbox
If Not IsNull(Me.Controls(i).Value) Then
DR = True
End If
Case 106 'check
If Not IsNull(Me.Controls(i).Value) Then
DR = True
End If
Case 100 'label
'ignore
Case 104 'command
'ignore
End Select

Next i
If DR = True Then '
'do something
End If

End Sub

Glad you found your solution. The ones we come up with ourselves are always more satisfying.

Best of luck with the rest of your project.

George

That's an interesting approach, jstiene. In your testing, were you able to distinguish between controls that had the same values in them when the record was opened as when the command button is clicked, as well as when those values were changed (dirtying the record)?

Thanks.

George

Thank you.
I'll have to play with jstiene's code a bit more. However, not being a programmer, this may be beyond my ability (which doesn't take much). If I fail, I'll probably just leave as is - kind of "if it works don't fix it". I'll keep you posted.

Again, thanks for all your time and consideration - it is appreciated.
SKK