Want: Bound Form. Don't Want: Real-Time Updates

[duluter]

Hi, everyone.

I want my form to save the record only when the user clicks the Save button. If the form is closed in any other way, then discard the changes to the record.

I found a thread on bytes.com that addresses this:
http://bytes.com/groups/ms-access/835113-force-user-save-form-data

Basically, the respondent indicates that there are two ways to go about this:

Use an unbound form with ADO code behind
OR
Use a bound form with a boolean variable that gets set to true when the user clicks the save button. In the form's Unload event, check if the variable is true. If it's true, then just close the form. If it's false, use Me.Undo to undo whatever was done on the form, then close the form.


The respondent says that they prefer the first option because it is "cleaner and safer."


I am leaning the other way--I am trying to get rid of most of my unbound forms in an effort to take advantage of Access's ability to handle record manipulations through bound forms.

Are there other preferred ways to go about this? Why would it be "safer" to use ADO rather than the Me.Undo statement? I would like to use bound forms, so I would like a robust solution for a bound form.


Thanks,

Duluter

 

[boblarson]

You would use the BEFORE UPDATE event to save the record or not. You can still set a flag to save using the save button and if no flag is set then just undo things.

 

[duluter]

Thanks, Bob. Will do. In regards to it being "safer" to use ADO rather than this method, do you buy that? I don't see any reason to believe it.


Duluter

 

[HiTechCoach]

Don't Want: Real-Time Updates

I think you really want real time updates. You just do not want them to be automatic with any confirmation Before the Update.


As Bob pointed out already, I also like to use the Before Update Event.

Example:

Private Sub Form_BeforeUpdate(Cancel As Integer)

    If Me.NewRecord Then
        If MsgBox("Data will be saved, Are you Sure?", vbYesNo, "Confirm") = vbNo Then
            Cancel = True
        Else
            ' run code for new record before saving
        
        End If
    
    
    Else
        If MsgBox("Data will be modified, Are you Sure?", vbYesNo, "Confirm") = vbNo Then
            Cancel = True
        Else
           ' run code before an existing record is saved
           ' example: update date last modified
            
        End If
    End If

End Sub

 

[boblarson]

Thanks, Bob. Will do. In regards to it being "safer" to use ADO rather than this method, do you buy that? I don't see any reason to believe it.


Duluter

What would be "safer" about it. You have to code something anyway and if you code the Before Update event right (with less code) then you are likely just as well off, if not better off, because you don't have so much code to maintain.

So, I believe the "safer" argument is actually a bit of hogwash to me.

 

[HiTechCoach]

What would be "safer" about it. You have to code something anyway and if you code the Before Update event right (with less code) then you are likely just as well off, if not better off, because you don't have so much code to maintain.

So, I believe the "safer" argument is actually a bit of hogwash to me.

Bob,

I agree with you.

Maybe by "safer" they were thinking Job security. Since an unbound form is a whole lot harder to code, maintain, and a lot more testing. Extra code requires more testing since there are more places to introduce bugs/issues.

I also avoid unbound forms because the sure can drive up the cost of a project with no real benefit to the data integrity or users/client.

 

[gemma-the-husky]

have you done this

not checked every post, but try the unload event - as you can cancel the form close there

if me.dirty then
 msgbox("sorry - please use the save button, or press <esc> to undo your changes ")
 cancel = vbcancel
 exit sub
end if

 

[missinglinq]

That won't work, gemma! By the time Form_Unload fires the record has already been saved. The Form_BeforeUpdate is the event to use.

Also, IMHO, using unbound forms for data input in Access essentially does away with about 98% of the advantage of using Access! If you're going to go to all the extra trouble involved in using unbound forms, as Boyd has pointed out, you'd be far better off using Visual Basic with another back end. The resulting front end is an executable file, can be run on any PC, without having Access installed, and you don't have to worry about users mucking about with your design.

 

[Pat Hartman]

duluter,
As others have said - the person who told you it was "safer" to use ADO didn't understand how bound forms work. Once you understand that the form's BeforeUpdate is the line of demarkation between unsaved and saved, you have absolute control over whether or not a record gets saved. You'll see references all over the net to the LostFocus event, the unload event, etc. But the ONLY event where you can control saving in a SINGLE place is the form's BeforeUpdate event.

 

[duluter]

Great feedback everyone. Thank you. Yes, I suspected that the poster on the other forum was not entirely sure about what they were advising re "safer", but I wanted to get your input.

I'm feeling the love on the bound form issue. I developed a database for a family member's business. The data structures I think are all sound. But I wasn't all that familiar with Access, so I went the unbound form route because it enabled me to do exactly what I wanted without pausing to learn about bound forms. On the one hand, I now am very comfortable with ADO. On the other hand, inevitably, there have been changes that have cropped up, and every time anything needs changing it involves a lot of work, as many here have stated. My goal is to eventually migrate as many of the forms as I can to be bound. I think I'm more prepared to do that now than I once was (largely because of all the help from folks on this forum).


Thanks much,

Duluter

 

[LPurvis]

To be fair - the reference in the thread which alluded to using ADO is all about the unbound nature of that form. Whether ADO or DAO is used as the data access API for writing the data back to the database really isn't at all relevant.
As has since been indicated, and you should focus on, the crux of the issue is about the development effort of an unbound form verses understanding and controlling bound forms.
(ADO could play a part in either scenario really, as can DAO. So being comfortable with ADO or not isn't really a concern as you are, quite rightly, already questioning).

There's a perceived "control" that comes with unbound forms.
While it's true to a discernable extent, too many folks become reliant on it. Operating unbound becomes their security blanket and eventually the only way they're willing to develop. Bound forms begin to feel dirty or bloated by comparison to them for some reason (a type of data anorexia almost).

As I've said before (and no doubt will again) I believe unbound forms absolutely have their place in Access applications. At times they allow a UI that simply isn't possible otherwise, including affording friendly normalised data and yes, some areas of control and efficiency. Using them doesn't necessarily detract from what Access is or what it offers offers.
However using them exclusively (or even in the majority) really does.
(The exception being if you've enough experience behind you to write your own custom, generic form handling routines to implement in your projects. Few have gone to such lengths).

The hacks of rolling back bound form updates (as I've demo'd in examples list "Transactions In Forms") is... hackish at best. I'd never implement it in a live application.
The BeforeUpdate event as mentioned is pretty standard (exampled in "Cancel Undo").
Of course if you have a subform in your entry form then navigation between that and the parent could cause problems with the users being frustrated at not being allowed to enter data therein (with an implicit commit being issued with that navigation which might irritate them - "Why can't I add my sub records and then click Save").

There are reasons to go unbound or dip into hackish transactions.
But they would need to be for the right reasons and not just because of client demands when a quicker, cheaper alternative exists.

Cheers.

 

[RuralGuy]

Well said Leigh, as usual.

 

[gemma-the-husky]

missinglinq

you are right

i use this to control exiting forms

Private Sub Form_Unload(Cancel As Integer)
    If MsgBox("Do you really want to exit " & APPTITLE, vbYesNo + vbQuestion + vbDefaultButton2, "Confirm Exit") = vbNo Then
        Cancel = vbCancel
    End If
End Sub

i thought i also used it to intercept record changes, but clearly not

 

[LPurvis]

Hi.
That's different to your earlier code though Gemma.
As missinglinq said - the Dirty property has been inevitably reset to False by form closing time (as a record update will have already happened by either Unload or Close events - the latter of which can't be cancelled anyway).

You can certainly, as you have just now, confirm for the closing of the form - but that won't affect the update of the record that was previously dirty.
The BeforeUpdate event will have already fired and the decision been made therein.

Cheers.

 

[RuralGuy]

I'm pretty sure Linq is correct that the Unload Event is too late to stop updates to the RecordSource of the form. Use the Form's BeforeUpdate event. Key in EVENTS to Access help (not VBA Help) for the Event Sequence.

 

[gemma-the-husky]

i knew i used unload to stop an app or form closing - i thought i had used it to manage an edit, but i guess not - i tested it, and obviously the save comes before the unload

shame, cause it would have bene an eaasy way to kill an edit

--------
in that case, i think you have to use an unbound form, dont you? - but it needs a lot more code

 

[LPurvis]

Well, killing an edit is pretty trivial... using the BeforeUpdate event. ;-)

What's more of an effort is preventing the form from closing after having prevented that write of the data.
As a user can easily make changes, request to close the form and be asked from the BeforeUpdate event procedure if they want to commit changes. If they answer no - the data isn't written - but, by default, the form can still close.

So I understand what you mean - it would be an easy way to kill and still offer the form to the user to make changes to their existing edit and then try again...

You can use form level variables as flags to determine if you want to proceed (as in the earlier mentioned "Cancel Undo" example MDB).
You can then prevent updates and the form from closing if you're so inclined.

Going unbound (the ultimate extent) is the final stage in the progression (again, very simply implemented in the progression of forms in the afore mentioned example file).
It's a simple example though of course. (Hence not much code).

Cheers.

 

[gemma-the-husky]

course, the other way is to remove the form close button

but even then you need something in the before update event, or users could still save the record by clicking another form, or by closing access (i think)

 

[LPurvis]

>> remove the form close button
Absolutely. In the example I mentioned I refer to that method as "Bound Cheat". ;-)

>> clicking another form
A subform absolutely. Leaving the form altogether doesn't actually commit (ironically). Navigating record does - though it can actually be undone.

>> closing access
Canceling the closing of a (usually hidden) form is an old trick to prevent Access from closing until we're quite ready.

Cheers.

 

[LPurvis]

Just thought, I should mention for completeness that navigating to another record leaves your original record updated but able to be undone (i.e. an implied transaction) - until that next record is dirtied!

Cheers.