How to tell if fields on a form have changed

[dhstein]

I have a form for adding a product to the product table. Because there are
many restrictions and requirements for the new item, I don't have the form
bound to the table. The user enters all the information and then clicks "Add
Item". This works fine. However, I also provide an "Exit" button and I'd
like to give him a confirmation before exiting - in case he forgot to click
"Add Item" Something like "Are you sure". Is there a way that I can tell if
he entered data in any fields. I don't want to have to set a global boolean
and put code in each control - I'm looking for something easier. Thanks for
any help on this.

 

[Allen Browne]

For unbound controls, you will have to manage your own variables to handle
the old values.

You may know what you are doing, but I didn't follow the bit about needing
to use an unbound form here. With a bound form, any checks you need to run
can be done in Form_BeforeUpdate, and any actions you need to take in
response to the new entry can be made in Form_AfterUpdate. And then you can
use the OldValue of the controls (with the exception of multi-valued
fields.)

 

[Arvin Meyer [MVP]]

Try adding something like this (untested) code to your form's Unload event:

Dim ctl As Control
For Each ctl In Me.Controls
If ctl.ControlType = acTextbox Then
If Len(ctl & vbNullString) >= 1 Then
If (MsgBox "Save?", vbYesNo) = vbNo Then
Cancel = True
End If
End If
End If
Next

 

[dhstein]

Allen and Arvin,

Thanks for your replies. You said: "You may know what you are doing" -
well I only wish it were true. I thought from some previous experiments and
posts that the bound form did not give me the chance to verify all fields
before the update. It looks like that was wrong. In any event, that's why I
created the "unbound" form for the "new product" entry. So now I can either
change this form or try Arvin's method. Either way, I might learn something.

David

 

[Arvin Meyer [MVP]]

Keep in mind that the code I wrote only has to find 1 textbox with data in
it. Also I discovered a mistake (see I told you it was untested)

If (MsgBox "Save?", vbYesNo) = vbNo Then

should read:

If (MsgBox "Save?", vbYesNo) = vbYes Then

because we want to cancel the unloading of the form if the answer is yes, so
we can either edit, add, or save.

 

[dhstein]

Arvin,

Thanks again. I used your code to see what happens to fields like this :

Dim ctl As Control
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
If Len(ctl & vbNullString) >= 1 Then
MsgBox ctl.Name & " Has New Data"
Else
MsgBox ctl.Name & " Has Old Data"

End If
End If

So I can use this test to figure out whether or not to give the user the
message and that solves my problem. So thank you for that. And as I said, I
did learn quite a lot (for me) from this. I see that "Len" will give the
length of data in the field. But why do we need Len(ctl & VBNullString) and
not just Len(ctl) ?

David

 

[Allen Browne]

Len(ctl) returns Null if the value of the control is null. Concatenating a
zero-length string to it (with the ampersand operator) generates a
zero-length string, and so Len() will yield zero instead of null.

These expressions are all the same:
If Len(ctl & vbNullString) = 0 Then
If Nz(Len(ctl), 0) = 0 Then
If IsNull(ctl) OR ctl = vbNullString Then

The important things to know are:
- Null and zero-length string are not the same.
- Null doesn't equal anything, i.e. this line will NEVER be true:
If ctl = Null Then