HELP - I have a question about saving a form

[sweyer via AccessMonster.com]

I'm using Access 2003.

I have a form "Employees" that has several subforms. Everytime you make a
change to any field, it automatically updates the underlying table(s). I
would like to change my form so that whenever you exit the form or go to
another record, it will prompt the user to save the changes. If you say no,
everything on the form and the subforms will go back to what it was before
the user made any changes. If you say yes, then it will save all the changes
(to the form and the subforms). I don't want it to prompt me everytime I
enter the subform, I want it to wait until the form is closed or the user
goes to the next record.

Can anyone help me?????

 

[Arvin Meyer [MVP]]

A bit of code something like this:

Sub Form_BeforeUpdate (Cancel As Integer)
If MsgBox("Do you want to save the Change?", vbYesNo, "Save?") = vbNo Then
Me.Undo
Else
DoCmd.RunCommand acCmdSave Record
End If
End Sub

In the form's before update event, ought to do the trick. You'll have to do
this in the main form as well as each subform, as each is independent in its
save procedures.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[sweyer via AccessMonster.com]

I assume you didn't mean to have a space between Save and Record. I put it in
without the space, and this is the error I get:

Run-time error '2115':

The macro of function set to the BeforeUpdate or ValidationRule property for
this field is preventing Microsoft Office Access from saving the data in the
field.

With options to End, Debug, or Help.

When I debug, it brings me to the code, which is:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If MsgBox("Do you want to save the changes?", vbYesNo, "Save?") = vbNo Then
Me.Undo
Else
DoCmd.RunCommand acCmdSaveRecord
End If
End Sub

The line under Else - DoCmd.RunCommand acCmdSaveRecord - is the line that is
highlighted.

I'm new to Access, so bear with me. What could be the problem? What do you
suggest I do?

A bit of code something like this:

Sub Form_BeforeUpdate (Cancel As Integer)
If MsgBox("Do you want to save the Change?", vbYesNo, "Save?") = vbNo Then
Me.Undo
Else
DoCmd.RunCommand acCmdSave Record
End If
End Sub

In the form's before update event, ought to do the trick. You'll have to do
this in the main form as well as each subform, as each is independent in its
save procedures.

I'm using Access 2003.

[quoted text clipped - 11 lines]

Can anyone help me?????

 

[Arvin Meyer [MVP]]

You're correct, the space didn't belong there. Error 2115 is a validation
error which usually is caused by data not conforming to a table's or form's
validation rule. If you're sure the data's correct, sometimes, it can be
caused by a timing problem (i.e. the computer can't validate the data fast
enough) If that's the case, try throwing a delay in the code. I use the
following function in a standard module, and call it for all sorts of issues
where I have a slower process.

Public Function Delay(dblInterval As Double)
On Error GoTo Err_Handler
Dim Timer1 As Double
Dim Timer2 As Double

Timer1 = Timer()
Do Until Timer2 >= Timer1 + dblInterval
DoEvents
Timer2 = Timer()
Loop

Exit_Here:
Exit Function

Err_Handler:
Resume Exit_Here

End Function

Then I call it like:

Delay(0.5) ' 1/2 second
DoCmd.RunCommand acCmdSaveRecord
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

I assume you didn't mean to have a space between Save and Record. I put it
in
without the space, and this is the error I get:

Run-time error '2115':

The macro of function set to the BeforeUpdate or ValidationRule property
for
this field is preventing Microsoft Office Access from saving the data in
the
field.

With options to End, Debug, or Help.

When I debug, it brings me to the code, which is:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If MsgBox("Do you want to save the changes?", vbYesNo, "Save?") = vbNo
Then
Me.Undo
Else
DoCmd.RunCommand acCmdSaveRecord
End If
End Sub

The line under Else - DoCmd.RunCommand acCmdSaveRecord - is the line that
is
highlighted.

I'm new to Access, so bear with me. What could be the problem? What do you
suggest I do?

A bit of code something like this:

Sub Form_BeforeUpdate (Cancel As Integer)
If MsgBox("Do you want to save the Change?", vbYesNo, "Save?") = vbNo Then
Me.Undo
Else
DoCmd.RunCommand acCmdSave Record
End If
End Sub

In the form's before update event, ought to do the trick. You'll have to
do
this in the main form as well as each subform, as each is independent in
its
save procedures.

I'm using Access 2003.

[quoted text clipped - 11 lines]

Can anyone help me?????

 

[sweyer via AccessMonster.com]

Where do I put this code? In the Before Update code? Does it go within the
code you gave me originally or seperate (before or after)?

I know it should be caused by a validation rule because I don't have any
validation rules in effect, at least not that I know of.

You're correct, the space didn't belong there. Error 2115 is a validation
error which usually is caused by data not conforming to a table's or form's
validation rule. If you're sure the data's correct, sometimes, it can be
caused by a timing problem (i.e. the computer can't validate the data fast
enough) If that's the case, try throwing a delay in the code. I use the
following function in a standard module, and call it for all sorts of issues
where I have a slower process.

Public Function Delay(dblInterval As Double)
On Error GoTo Err_Handler
Dim Timer1 As Double
Dim Timer2 As Double

Timer1 = Timer()
Do Until Timer2 >= Timer1 + dblInterval
DoEvents
Timer2 = Timer()
Loop

Exit_Here:
Exit Function

Err_Handler:
Resume Exit_Here

End Function

Then I call it like:

Delay(0.5) ' 1/2 second
DoCmd.RunCommand acCmdSaveRecord

I assume you didn't mean to have a space between Save and Record. I put it
in

[quoted text clipped - 48 lines]

 

[Arvin Meyer [MVP]]

You'd put that code in a standard module as mentioned in my last post. Call
it as shown. There is all kinds of validation, some of which may not be in a
validation rule, like a field which may be required. Also in re-reading your
question, I see that you don't want to be prompted when you move from form
to subform. As soon as you move into a subform, the mainform data is saved.
Your only way out of that is to unbind your forms from the data and directly
use code to update the tables.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Where do I put this code? In the Before Update code? Does it go within the
code you gave me originally or seperate (before or after)?

I know it should be caused by a validation rule because I don't have any
validation rules in effect, at least not that I know of.

You're correct, the space didn't belong there. Error 2115 is a validation
error which usually is caused by data not conforming to a table's or
form's
validation rule. If you're sure the data's correct, sometimes, it can be
caused by a timing problem (i.e. the computer can't validate the data fast
enough) If that's the case, try throwing a delay in the code. I use the
following function in a standard module, and call it for all sorts of
issues
where I have a slower process.

Public Function Delay(dblInterval As Double)
On Error GoTo Err_Handler
Dim Timer1 As Double
Dim Timer2 As Double

Timer1 = Timer()
Do Until Timer2 >= Timer1 + dblInterval
DoEvents
Timer2 = Timer()
Loop

Exit_Here:
Exit Function

Err_Handler:
Resume Exit_Here

End Function

Then I call it like:

Delay(0.5) ' 1/2 second
DoCmd.RunCommand acCmdSaveRecord

I assume you didn't mean to have a space between Save and Record. I put
it
in

[quoted text clipped - 48 lines]

Can anyone help me?????

 

[sweyer via AccessMonster.com]

But you still didn't answer my question. What is a standard module? Do you
mean a macro? Or in an event? Which event? I'm extremely new to this, I don't
know much about Access. So give me a "for dummies" version.

You'd put that code in a standard module as mentioned in my last post. Call
it as shown. There is all kinds of validation, some of which may not be in a
validation rule, like a field which may be required. Also in re-reading your
question, I see that you don't want to be prompted when you move from form
to subform. As soon as you move into a subform, the mainform data is saved.
Your only way out of that is to unbind your forms from the data and directly
use code to update the tables.

Where do I put this code? In the Before Update code? Does it go within the
code you gave me originally or seperate (before or after)?

[quoted text clipped - 41 lines]

 

[Rick Brandt]

But you still didn't answer my question. What is a standard module? Do you
mean a macro? Or in an event? Which event? I'm extremely new to this, I don't
know much about Access. So give me a "for dummies" version.

The db window is divided into several views. There is one for forms, one for
reports, etc., as well as one for modules. Go to that view and press the [New]
button and you will have created your first module which is nothing more than a
container for VBA code.

In a module you can define public variables, create VBA sub-routines, and VBA
functions, and then use them within your app.

 

[sweyer via AccessMonster.com]

Thanks. I have never used modules before, so I had no idea what you were
talking about. I tried it out and the delay didn't help. The only field on
the form that is required is the GroupName field, and it has a value. It
shouldn't be giving me an error on that. Any other ideas?

But you still didn't answer my question. What is a standard module? Do you
mean a macro? Or in an event? Which event? I'm extremely new to this, I don't
know much about Access. So give me a "for dummies" version.

The db window is divided into several views. There is one for forms, one for
reports, etc., as well as one for modules. Go to that view and press the [New]
button and you will have created your first module which is nothing more than a
container for VBA code.

In a module you can define public variables, create VBA sub-routines, and VBA
functions, and then use them within your app.

 

[sweyer via AccessMonster.com]

So I played with the code a little bit and this seems to work:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If MsgBox("Do you want to save the changes?", vbYesNo, "Save?") = vbNo Then
Me.Undo
End If
End Sub

Do you think that I should keep this code?

Thanks. I have never used modules before, so I had no idea what you were
talking about. I tried it out and the delay didn't help. The only field on
the form that is required is the GroupName field, and it has a value. It
shouldn't be giving me an error on that. Any other ideas?
[quoted text clipped - 7 lines]

In a module you can define public variables, create VBA sub-routines, and VBA
functions, and then use them within your app.