Problem Saving Changed Data

[JamesJ]

I'm trying to use the following code to confirm changes to the data.
Calling from the Before Update of the form.

Public Function ConfirmDataChange(Cancel As Integer)

Set frm = Screen.ActiveForm

Select Case MsgBox("Record(s) have been added or changed." _
& vbCrLf & "Save the Changes?", vbYesNo, "Record
Change")

Case vbYes 'go ahead and save.
'frm.Refresh

Case vbNo 'undo the changes.
Cancel = True
frm.Undo

End Select

End Function

When data is dirty and I close the form Access 2007 gives me the following
error:

Run Time 2475
You entered an expression which requires the form to be the active window
Any way around this??
It runs fine when I simply go to another record.
I'm setting the active form 'cause I have several forms that require
data change confirmation.

 

[Rick Brandt]

I'm trying to use the following code to confirm changes to the data.
Calling from the Before Update of the form.

Public Function ConfirmDataChange(Cancel As Integer)

Set frm = Screen.ActiveForm

Select Case MsgBox("Record(s) have been added or changed." _
& vbCrLf & "Save the Changes?", vbYesNo, "Record
Change")

Case vbYes 'go ahead and save.
'frm.Refresh

Case vbNo 'undo the changes.
Cancel = True
frm.Undo

End Select

End Function

When data is dirty and I close the form Access 2007 gives me the
following error:

Run Time 2475
You entered an expression which requires the form to be the active
window Any way around this??
It runs fine when I simply go to another record.
I'm setting the active form 'cause I have several forms that require
data change confirmation.

I dont think you can issue a save in the BeforeUpdate event. Just have your
function return a True or False to the BeforeUpdate code and use that to set the
Cancel argument of that event.

 

[JamesJ]

Isn't that what the code is doing?

James

I dont think you can issue a save in the BeforeUpdate event. Just have
your function return a True or False to the BeforeUpdate code and use that
to set the Cancel argument of that event.

 

[Rick Brandt]

Isn't that what the code is doing?

James

No you are calling code (a function) from a sub-routine that has a Cancel
argument and the function you are calling also has a Cancel argument. The
argument you are setting to true is the ine for the funtion, not the one for the
BeforeUpdate event.

All you need to do in the BeforeUpdate event is set Cancel=True when you don't
want the save to happen and do NOTHING when you do want it to save. There
should be no reason to explicitly attempt saving since that is what's going to
happen anyway if you don't cancel it.

 

[JamesJ]

In other words put the function in the BeforeUpdate event as a form
function?

 

[Rick Brandt]

In other words put the function in the BeforeUpdate event as a form
function?

You can still call a separate function if you prefer, but all you need to have
that function do is display the MsgBox and return True or False based on the
user's response. It doesn't need to "do" anything else. In your BeforeUpdate
event you can then have one line...

Cancel = ConfirmDataChange()

You would of course set up the function so that it returns False when the user
indicates that they want to save the changes and have it return True when they
indicate that they don't want to save them.

This could just as easily all be done right in the BeforeUpdate event with no
separate function needed. A separate function stored in a standard module would
have the advantage of be usable in multiple forms.

 

[JamesJ]

I'm getting more confused by the minute.

I put the following code in the BeforeUpdate of the form:

Select Case MsgBox("Record(s) have been added or changed." _
& vbCrLf & "Save the Changes?", vbYesNo, "Record
Change")

Case vbYes 'go ahead and save.
Me.Refresh

Case vbNo 'undo the changes.
Cancel = True
Me.Undo

End Select

Is this or is this not return yes or no depending on the user's response???
Now I'm getting another error: 2115

The Macro or Function set to the BeforeUpdate or ValidationRule (I have
none)
property for this field is preventing MyDb from saving the data in the
field.
The error occurs in I press Yes.

One more thing.
I sure wish these developers would come up with a way to copy and paste
these error messages they write!!!!

James

 

[Rick Brandt]

I'm getting more confused by the minute.

I put the following code in the BeforeUpdate of the form:

Select Case MsgBox("Record(s) have been added or changed." _
& vbCrLf & "Save the Changes?", vbYesNo, "Record
Change")

Case vbYes 'go ahead and save.
Me.Refresh

Case vbNo 'undo the changes.
Cancel = True
Me.Undo

End Select

Is this or is this not return yes or no depending on the user's
response??? Now I'm getting another error: 2115

The Macro or Function set to the BeforeUpdate or ValidationRule (I
have none)
property for this field is preventing MyDb from saving the data in the
field.
The error occurs in I press Yes.

One more thing.
I sure wish these developers would come up with a way to copy and
paste these error messages they write!!!!

James

As I stated previously, all you have to do is set Cancel = True to NOT save the
changes and DO NOTHING if you do want the changes saved.

You are in the BeforeUpdate event. A save WILL happen unless you do something
to stop it. By trying to call a refresh in this event you are getting an error
because that event is not an appropriate place to call a refresh. Just remove
the refresh line from your code and it should work.

 

[JamesJ]

OK I removed the Me.Refresh. Now when I try to close the form the
message box comes up and when I click yes I get:

Update or CancelUpdate without Addnew or Edit.
If I choose to close the object the changes don't get saved

 

[Rick Brandt]

OK I removed the Me.Refresh. Now when I try to close the form the
message box comes up and when I click yes I get:

Update or CancelUpdate without Addnew or Edit.
If I choose to close the object the changes don't get saved

Are you closing with the standard X in the upper right or with a custom close
button?

 

[JamesJ]

Form's close (X) is being used.

Are you closing with the standard X in the upper right or with a custom
close button?

 

[Rick Brandt]

Form's close (X) is being used.

So let's review. If you open the form and just close it (without changing any
data) do you get an error then?

If you open the form, make a change and then move to a different record, do you
get the prompt that you expect?

If yes, does it properly save the record and move to the next when you indicate
that you DO want to save the changes?

How about when you indicate NOT to save the changes?

I'm wondering whether the problem is with the BeforeUpdate regardless of how it
is triggered or if only when you close the form.

 

[JamesJ]

The only time I get an error message now is when I change
data and click the Form's (X) close button and then select Yes
to save the changes. Clicking No, moving to another record
or just closing the form produces no error.

You put a bug in my ear. I set the form's Close Button property
to No and placed a custom Close button on the form calling DoCmd.Close.
Now though I get a message if I click No in the message box saying:
The Close Action was canceled. Otherwise the data gets saved with
no problem.

 

[Rick Brandt]

The only time I get an error message now is when I change
data and click the Form's (X) close button and then select Yes
to save the changes. Clicking No, moving to another record
or just closing the form produces no error.

You put a bug in my ear. I set the form's Close Button property
to No and placed a custom Close button on the form calling
DoCmd.Close. Now though I get a message if I click No in the message
box saying: The Close Action was canceled. Otherwise the data gets
saved with no problem.

Before DoCmd.Close add...

Me.Dirty=False

 

[JamesJ]

Error 2101: The Setting isn't valid for this property.
When I click no. Yes is fine.

 

[Rick Brandt]

Error 2101: The Setting isn't valid for this property.
When I click no. Yes is fine.

That would indicate that there is some reason that the record cannot be saved
like missing a required field or similar.

 

[JamesJ]

From an existing record?

That would indicate that there is some reason that the record cannot be
saved like missing a required field or similar.

 

[Rick Brandt]

From an existing record?

Sounds strange but setting the Dirty property to False is one way to save a
record. If you get an error when setting that property is suggests that ther is
something about the record that is not "legal" to save.

Try replacing Me.Dirty=False with...

DoCmd.RunCommand acCmdSaveRecord

....which does the same thing, but which might produce a better error message.

 

[JamesJ]

Sorry 'bout quitting last night. I got tired.
..
This db is a 2007 Beta This is occurring on all forms.
These forms are split forms (which I like tremendously)
I saved my 2003 db and I'm not getting any errors... whether
I click Yes or No and close the form using the Form's (X), no problem.
btw. Using CmdSaveRecord the error is it was canceled.

James

 

[Rick Brandt]

Sorry 'bout quitting last night. I got tired.
.
This db is a 2007 Beta This is occurring on all forms.
These forms are split forms (which I like tremendously)
I saved my 2003 db and I'm not getting any errors... whether
I click Yes or No and close the form using the Form's (X), no problem.
btw. Using CmdSaveRecord the error is it was canceled.

Sounds like a bug in the beta then. Sorry, but I have not not done anything
with the beta so I can't help you with that.

A different strategy might be in order. I am personally in the camp of
developers who feel that "Do you want to save your changes?" prompts are at best
unnecessary and at worst very annoying to users. The only time I have ever used
them was when the users (or more likely their manager) asked me to put them in.

If you are worried about accidental changes then lock the form and provide an
[Edit] button that unlocks it. That way the user is clearly indicating intent
BEFORE making any changes which should eliminate any need to ask them after they
make them. Then simply train users that changes are saved by default and not to
go willy-nilly on the record unless they expect those changes to stick. Also
show them how the <escape> key works.

Another big advantage of this strategy is that it teaches them how Access
actually works. If your apps do a lot of extra hand-holding then they might
very well expect ALL Access apps to work that way.