Access VBA "Was any data modified?"

Any particular reason you are using unbound forms with Access?  Not that this is wrong, but one of the strengths of Access is avoiding all of the effort it takes to work with unbound forms.

If they are editing text, then the code in your "cmd_Exit_Click" event will have to compare the current value of those unbound forms to the value that was put in them when you loaded the form, and then compare the two (for all of the controls) to determine whether anything was changed.  If so, then you would popup your messagebox.

If you are keeping the recordset that populated the form open, then you could simply compare the current value of the controls to the appropriate field in the recordset.  If you are closing the recordset after populating the form, you would need to reopen it, or store those "original" values somewhere (Tag property of an unbound control might be one option, a Variant array or collection might be another option).

if the form were bound, you could use the Dirty property:
http://msdn.microsoft.com/en-us/library/office/ff194309.aspx

if it's unbound, you have to compare each and every user input control (the text boxes) with the value before the user could do any changes.

you could "simply" by having 1 boolean variable that says "isDirty", and change it to true as soon as any of the text values would be changed .... but this would NOT work "properly" if the user changes "back" to the original value(s)

the alternative would be to actually do create a recordset "by code" and bound the form to that ...

I start by admitting how I got here:  When I started I created the database, the query, then used the query as my datasource for bound text boxes.  My applications are used on a LAN, with a terminal server.  Many users access them at the same time.  Using bound text boxes causes the database to remain open.  I kept getting errors.  The users would close the app by clicking the Red X with the form open.  I would get bad data in my records.  The engineers would scowl and scoff and my weak Access skill!!!  In addition, on the terminal server, there was a speed timing problem causing crashes with Access.  I was able to eliminate 80% by going to unbound boxes and saving via recordsets.  The other 20% was eliminated by moving to SqlServer(but thats another story).  Anyway, that is how I arrived and using recordsets.  While not the easiest, it has proven the most stable.  Any comments about creating stability/good data with bound text boxes would be appreciated.

In this app, the table does not have a primary key.  I'm accessing a table created by someone else.

Fyed,
The app is split into front and backend.  This particular app is not moved to Sql Server yet.  Does this method deal with users closing Access using the Red X with the form open?
Thanks for the guidance.

The form's dirty property isn't set when the form is unbound.  In fact, you will get few useful events regarding the state of the data when the form is unbound.

If you can add a primary key to the table, you should.  Tables should always have a primary key even if it is an autonumber/identity column.

Once the table has a PK, you can use a bound form which will be infinitely easier and now that your app is split correctly and each user has his OWN copy of the FE, will work quite well.  All your original problems were caused by having a monolithic application.  All proper Access apps have separate front ends (forms/reports/queries/macros/modules/LINKED tables) and back ends (tables only) PLUS every user has his own personal copy of the FE even in a terminal services/Citrix environment.

One way:
Use a public variable to hold field's value on enter and checks at exit to set a flag of modification.
Here is code for one unbound field uf.

Public dataModified As Boolean 'flag of data modification
Public ef As Variant 'holds current value of unbound field

'button click to save record
Private Sub Command40_Click()
    If dataModified Then
        MsgBox ("Your data has changed")
    End If
    dataModified = False
Exit Sub

Private Sub uf_Enter()
    If dataModified Then
    Else
        ef = uf
    End If
End Sub

Private Sub uf_Exit(Cancel As Integer)
    If dataModified Then
    Else
        If ef <> uf Then
            dataModified = True
        End If
    End If
End Sub

Select allOpen in new window

I'm going with Dale's suggestion.  I'll convert my form back to bound text boxes.  I'll add the primary key.  And use the form Unload event.  

Thanks to all for your suggestions.

STOP THE PRESSES!!!!

If you are going back to a bound form, the unload event is the wrong event to use.  By the time it runs, the data has already been saved.  The suggested code will pop up the annoying message but it is already too late since if you check while the code is stopped there, you will see that it is too late.  The data has been saved.

The correct event for this purpose is the FORM's BeforeUpdate event.

If Msgbox("Are you sure you want to save this record?",vbYesNo) = vbYes Then
Else
    Me.Undo
    Cancel = True
    Exit Sub
End if

Select allOpen in new window

Note that this is also the event you should use for most of your validation.   When you don't want to save the record (for whatever reason), you cancel the BeforeUpdate event using Cancel = True but that doesn't get rid of the changes that were made so you also need to undo the update with Me.Undo to reset the form's Dirty flag so the BeforeUpdate event won't try to run again.

Agree with Pat,

placing that code in the Unload procedure will ensure that the form does not close when the user clicks the X in the Access window, but will in fact attempt to write the data that is in that form before doing so.

I always put code in my Form_BeforeUpdate event that checks the status of controls that must be filled in, and use the Cancel = True in that event if a required field has not been entered.

Thanks for the followup, Pat and Dale!