Solved

Access VBA "Was any data modified?"

Posted on 2014-01-02
14
1,631 Views
Last Modified: 2014-01-03
An Edit form is opened.  I populate the text boxes using a recordset in VBA. The user moves through the text boxes.  Makes a change.  Clicks on Exit.  If any changes were made in the text boxes I want a pop up saying "Changes were made.  Save?"

All of my text boxes are unbound and the data is saved using With recordset...

Thank you,
Brooks
0
Comment
Question by:gbnorton
  • 5
  • 4
  • 2
  • +2
14 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39751794
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).
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39751798
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 ...
0
 

Author Comment

by:gbnorton
ID: 39751882
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.
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 50 total points
ID: 39751886
do your table have primary keys?
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 450 total points
ID: 39751941
So, you are now using Access with a SQL Server backend?

On your terminal server:

1.  Was the application and data all in one file, or did you have it split as Front End (application) and Back End (data)?
2.  were all of your users using the same Front End?  or did they each have their own copy of the application?

These are probably the two biggest problems with using Access in a multi-user environment.  Every user needs their own copy of Access (either on their PC or in their personal profile in Terminal Server), and you must split the application (FE) and data (BE).

It sounds like you now have the application split, but if you are on a LAN, there is no need to use unbound forms, you can simply link the SQL tables to Access and work with them as though they were resident in Access, including bound forms.

You can prevent users from closing your bound forms without first cancelling or saving the form with one simple technique.

Declare a Public variable in the Forms declaration section:

PUBLIC AllowClose as Boolean

Then, in the forms UNLOAD event, use code similar to:
Private Sub Form_Unload(Cancel as integer)

    Cancel = Not AllowClose
    if Cancel = True then
        msgbox "You must 'Save' or 'Cancel' this operation before closing the form!"
    endif

End Sub

Open in new window

Then you add a close button on the form, I put mine in the upper left corner, and in the Click event of that button, you can test for whether the form is dirty and ask the user if they want to save the record before proceeding.  You would also use that Click event to set the AllowClose variable value to True, before issuing the command to close the form.
Private Sub cmd_Close_Click

    if me.dirty Then
        msgbox "Press 'Save' or 'Cancel' to save or clear this form!"
    Else
        AllowClose = True
        docmd.close acform, me.name
    End If

End Sub

Open in new window

0
 

Author Comment

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

Author Comment

by:gbnorton
ID: 39751965
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.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 34

Expert Comment

by:PatHartman
ID: 39751995
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.
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 450 total points
ID: 39752010
See the attached form template.

This is the form I start with for every form I create.

Note that I have the ControlBox and CloseButton properties set to No, and the Min/Max Buttons set to None.

This means that the only X that will be visible is the ACCESS X, not the ones on individual forms.  But when you try to close Access, it will display the message
FormTemplate.accdb
0
 
LVL 30

Expert Comment

by:hnasr
ID: 39752039
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

Open in new window

0
 

Author Comment

by:gbnorton
ID: 39752409
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.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39752428
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

Open 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.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39753666
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.
0
 

Author Comment

by:gbnorton
ID: 39753780
Thanks for the followup, Pat and Dale!
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now