>This is long, but I wanted to be as clear as possible...

>I am trying to mirror what I think is fairly standard window behavior in an
>Access 97 form, and I am having no luck finding a clean way to do it.

>I have a form that is used only for editing existing records of a single
>table.  When the user closes the form, I want to generate the typical "Do
>you want to save changes?" message box with Yes, No, and Cancel buttons on
>it.

>As you might expect, I want the following actions to occur based on the
user
>response:
>  -  If the user clicks Yes, the form closes after saving the data.
>  -  If the user clicks No, the form closes without saving the data.
>  -  If the user clicks Cancel, the form stays open in the state it was
>right before the user closed the form, and the  underlying source record is
>left untouched.

>I have been able to produce most of this behavior, but I am stuck on the
>Cancel response.  I put the msgbox code in the Form_BeforeUpdate event
>because it occurs just before the Unload --> Deactivate --> Close sequence.

>When the Cancel button is clicked, I cancel the BeforeUpdate event.  But
>then I get a built-in Access message that says "You can't save this record
>at this time.  Microsoft Access may have encountered an error while trying
>to save a record.  If you close this object now, the data changes you made
>will be lost.  Do you want to close the database object anyway?  Yes/No"

>When I click No, the form does revert back to the way it was right before I
>closed the form, just the way I want it to.  The problem is that I do not
>want the users to see this Access message.  I can't suppress it with Echo
or
>SetWarnings.  I also used SendKeys to send CTRL+N, which did close the
>Access message before the user really has time to see it, but that is a
>hokey workaround.

>Is there a way to suppress this message?  More importantly, is there an
>easier/cleaner way to accomplish the whole thing?

>Thanks for you consideration.

>-Kingsley Allen

> There is something you're doing that is messing with the usual
functioning.

> Cancel=True

> in the Before Update event will cancel the update and return you to the
> form.  So it's something else that is causing your error.

> How are you getting / testing for the user input and then processing it?
> Show us your Before Update code.

> Keri

> >This is long, but I wanted to be as clear as possible...

> >I am trying to mirror what I think is fairly standard window behavior in
an
> >Access 97 form, and I am having no luck finding a clean way to do it.

> >I have a form that is used only for editing existing records of a single
> >table.  When the user closes the form, I want to generate the typical "Do
> >you want to save changes?" message box with Yes, No, and Cancel buttons
on
> >it.

> >As you might expect, I want the following actions to occur based on the
> user
> >response:
> >  -  If the user clicks Yes, the form closes after saving the data.
> >  -  If the user clicks No, the form closes without saving the data.
> >  -  If the user clicks Cancel, the form stays open in the state it was
> >right before the user closed the form, and the  underlying source record
is
> >left untouched.

> >I have been able to produce most of this behavior, but I am stuck on the
> >Cancel response.  I put the msgbox code in the Form_BeforeUpdate event
> >because it occurs just before the Unload --> Deactivate --> Close
sequence.

> >When the Cancel button is clicked, I cancel the BeforeUpdate event.  But
> >then I get a built-in Access message that says "You can't save this
record
> >at this time.  Microsoft Access may have encountered an error while
trying
> >to save a record.  If you close this object now, the data changes you
made
> >will be lost.  Do you want to close the database object anyway?  Yes/No"

> >When I click No, the form does revert back to the way it was right before
I
> >closed the form, just the way I want it to.  The problem is that I do not
> >want the users to see this Access message.  I can't suppress it with Echo
> or
> >SetWarnings.  I also used SendKeys to send CTRL+N, which did close the
> >Access message before the user really has time to see it, but that is a
> >hokey workaround.

> >Is there a way to suppress this message?  More importantly, is there an
> >easier/cleaner way to accomplish the whole thing?

> >Thanks for you consideration.

> >-Kingsley Allen

>Here is my BeforeUpdate code...

>Private Sub Form_BeforeUpdate(Cancel As Integer)
>        Dim Result As Integer
>        Result = PromptSaveData(Me.Caption)
>        Select Case Result
>        Case vbYes
>        Case vbNo
>            Me.Undo
>        Case vbCancel
>            Cancel = True
>        End Select
>End Sub

>The Yes/No/Cancel message box is generated by a PromptSaveData()
>function that I put in a module...

>Function PromptSaveData(ChangedItem As String) As Integer
>    Dim strPrompt, strTitle As String
>    Dim intButtons As Integer

>    strPrompt = "Do you want to save changes to the " & ChangedItem & "?"
>    intButtons = vbYesNoCancel + vbInformation + vbDefaultButton1
>    strTitle = "Client Database"

>    PromptSaveData = MsgBox(strPrompt, intButtons, strTitle)
>End Function

>After I read the response I thought there might be something about the way
I
>customized my form that was throwing it off, so I built a new one using the
>form
>wizard, added the code above, and got the same result.

>Thanks,
>Kingsley

>> There is something you're doing that is messing with the usual
>functioning.

>> Cancel=True

>> in the Before Update event will cancel the update and return you to the
>> form.  So it's something else that is causing your error.

>> How are you getting / testing for the user input and then processing it?
>> Show us your Before Update code.

>> Keri

>> >This is long, but I wanted to be as clear as possible...

>> >I am trying to mirror what I think is fairly standard window behavior in
>an
>> >Access 97 form, and I am having no luck finding a clean way to do it.

>> >I have a form that is used only for editing existing records of a single
>> >table.  When the user closes the form, I want to generate the typical
"Do
>> >you want to save changes?" message box with Yes, No, and Cancel buttons
>on
>> >it.

>> >As you might expect, I want the following actions to occur based on the
>> user
>> >response:
>> >  -  If the user clicks Yes, the form closes after saving the data.
>> >  -  If the user clicks No, the form closes without saving the data.
>> >  -  If the user clicks Cancel, the form stays open in the state it was
>> >right before the user closed the form, and the  underlying source record
>is
>> >left untouched.

>> >I have been able to produce most of this behavior, but I am stuck on the
>> >Cancel response.  I put the msgbox code in the Form_BeforeUpdate event
>> >because it occurs just before the Unload --> Deactivate --> Close
>sequence.

>> >When the Cancel button is clicked, I cancel the BeforeUpdate event.  But
>> >then I get a built-in Access message that says "You can't save this
>record
>> >at this time.  Microsoft Access may have encountered an error while
>trying
>> >to save a record.  If you close this object now, the data changes you
>made
>> >will be lost.  Do you want to close the database object anyway?  Yes/No"

>> >When I click No, the form does revert back to the way it was right
before
>I
>> >closed the form, just the way I want it to.  The problem is that I do
not
>> >want the users to see this Access message.  I can't suppress it with
Echo
>> or
>> >SetWarnings.  I also used SendKeys to send CTRL+N, which did close the
>> >Access message before the user really has time to see it, but that is a
>> >hokey workaround.

>> >Is there a way to suppress this message?  More importantly, is there an
>> >easier/cleaner way to accomplish the whole thing?

>> >Thanks for you consideration.

>> >-Kingsley Allen

> I just added this code to a form and it worked exactly as you'd like if I
> moved out of the record using the nav buttons.   I think it might have to
do
> with the action you are taking to cause the Before Update event.  Are you,
> for example, clicking a "Save" button or something like that?

> Keri

> >Here is my BeforeUpdate code...

> >Private Sub Form_BeforeUpdate(Cancel As Integer)
> >        Dim Result As Integer
> >        Result = PromptSaveData(Me.Caption)
> >        Select Case Result
> >        Case vbYes
> >        Case vbNo
> >            Me.Undo
> >        Case vbCancel
> >            Cancel = True
> >        End Select
> >End Sub

> >The Yes/No/Cancel message box is generated by a PromptSaveData()
> >function that I put in a module...

> >Function PromptSaveData(ChangedItem As String) As Integer
> >    Dim strPrompt, strTitle As String
> >    Dim intButtons As Integer

> >    strPrompt = "Do you want to save changes to the " & ChangedItem & "?"
> >    intButtons = vbYesNoCancel + vbInformation + vbDefaultButton1
> >    strTitle = "Client Database"

> >    PromptSaveData = MsgBox(strPrompt, intButtons, strTitle)
> >End Function

> >After I read the response I thought there might be something about the
way
> I
> >customized my form that was throwing it off, so I built a new one using
the
> >form
> >wizard, added the code above, and got the same result.

> >Thanks,
> >Kingsley

> >> There is something you're doing that is messing with the usual
> >functioning.

> >> Cancel=True

> >> in the Before Update event will cancel the update and return you to the
> >> form.  So it's something else that is causing your error.

> >> How are you getting / testing for the user input and then processing
it?
> >> Show us your Before Update code.

> >> Keri

> >> >This is long, but I wanted to be as clear as possible...

> >> >I am trying to mirror what I think is fairly standard window behavior
in
> >an
> >> >Access 97 form, and I am having no luck finding a clean way to do it.

> >> >I have a form that is used only for editing existing records of a
single
> >> >table.  When the user closes the form, I want to generate the typical
> "Do
> >> >you want to save changes?" message box with Yes, No, and Cancel
buttons
> >on
> >> >it.

> >> >As you might expect, I want the following actions to occur based on
the
> >> user
> >> >response:
> >> >  -  If the user clicks Yes, the form closes after saving the data.
> >> >  -  If the user clicks No, the form closes without saving the data.
> >> >  -  If the user clicks Cancel, the form stays open in the state it
was
> >> >right before the user closed the form, and the  underlying source
record
> >is
> >> >left untouched.

> >> >I have been able to produce most of this behavior, but I am stuck on
the
> >> >Cancel response.  I put the msgbox code in the Form_BeforeUpdate event
> >> >because it occurs just before the Unload --> Deactivate --> Close
> >sequence.

> >> >When the Cancel button is clicked, I cancel the BeforeUpdate event.
But
> >> >then I get a built-in Access message that says "You can't save this
> >record
> >> >at this time.  Microsoft Access may have encountered an error while
> >trying
> >> >to save a record.  If you close this object now, the data changes you
> >made
> >> >will be lost.  Do you want to close the database object anyway?
Yes/No"

> >> >When I click No, the form does revert back to the way it was right
> before
> >I
> >> >closed the form, just the way I want it to.  The problem is that I do
> not
> >> >want the users to see this Access message.  I can't suppress it with
> Echo
> >> or
> >> >SetWarnings.  I also used SendKeys to send CTRL+N, which did close the
> >> >Access message before the user really has time to see it, but that is
a
> >> >hokey workaround.

> >> >Is there a way to suppress this message?  More importantly, is there
an
> >> >easier/cleaner way to accomplish the whole thing?

> >> >Thanks for you consideration.

> >> >-Kingsley Allen

> I just added this code to a form and it worked exactly as you'd like if I
> moved out of the record using the nav buttons.   I think it might have to
do
> with the action you are taking to cause the Before Update event.  Are you,
> for example, clicking a "Save" button or something like that?

> Keri

> >Here is my BeforeUpdate code...

> >Private Sub Form_BeforeUpdate(Cancel As Integer)
> >        Dim Result As Integer
> >        Result = PromptSaveData(Me.Caption)
> >        Select Case Result
> >        Case vbYes
> >        Case vbNo
> >            Me.Undo
> >        Case vbCancel
> >            Cancel = True
> >        End Select
> >End Sub

> >The Yes/No/Cancel message box is generated by a PromptSaveData()
> >function that I put in a module...

> >Function PromptSaveData(ChangedItem As String) As Integer
> >    Dim strPrompt, strTitle As String
> >    Dim intButtons As Integer

> >    strPrompt = "Do you want to save changes to the " & ChangedItem & "?"
> >    intButtons = vbYesNoCancel + vbInformation + vbDefaultButton1
> >    strTitle = "Client Database"

> >    PromptSaveData = MsgBox(strPrompt, intButtons, strTitle)
> >End Function

> >After I read the response I thought there might be something about the
way
> I
> >customized my form that was throwing it off, so I built a new one using
the
> >form
> >wizard, added the code above, and got the same result.

> >Thanks,
> >Kingsley

> >> There is something you're doing that is messing with the usual
> >functioning.

> >> Cancel=True

> >> in the Before Update event will cancel the update and return you to the
> >> form.  So it's something else that is causing your error.

> >> How are you getting / testing for the user input and then processing
it?
> >> Show us your Before Update code.

> >> Keri

> >> >This is long, but I wanted to be as clear as possible...

> >> >I am trying to mirror what I think is fairly standard window behavior
in
> >an
> >> >Access 97 form, and I am having no luck finding a clean way to do it.

> >> >I have a form that is used only for editing existing records of a
single
> >> >table.  When the user closes the form, I want to generate the typical
> "Do
> >> >you want to save changes?" message box with Yes, No, and Cancel
buttons
> >on
> >> >it.

> >> >As you might expect, I want the following actions to occur based on
the
> >> user
> >> >response:
> >> >  -  If the user clicks Yes, the form closes after saving the data.
> >> >  -  If the user clicks No, the form closes without saving the data.
> >> >  -  If the user clicks Cancel, the form stays open in the state it
was
> >> >right before the user closed the form, and the  underlying source
record
> >is
> >> >left untouched.

> >> >I have been able to produce most of this behavior, but I am stuck on
the
> >> >Cancel response.  I put the msgbox code in the Form_BeforeUpdate event
> >> >because it occurs just before the Unload --> Deactivate --> Close
> >sequence.

> >> >When the Cancel button is clicked, I cancel the BeforeUpdate event.
But
> >> >then I get a built-in Access message that says "You can't save this
> >record
> >> >at this time.  Microsoft Access may have encountered an error while
> >trying
> >> >to save a record.  If you close this object now, the data changes you
> >made
> >> >will be lost.  Do you want to close the database object anyway?
Yes/No"

> >> >When I click No, the form does revert back to the way it was right
> before
> >I
> >> >closed the form, just the way I want it to.  The problem is that I do
> not
> >> >want the users to see this Access message.  I can't suppress it with
> Echo
> >> or
> >> >SetWarnings.  I also used SendKeys to send CTRL+N, which did close the
> >> >Access message before the user really has time to see it, but that is
a
> >> >hokey workaround.

> >> >Is there a way to suppress this message?  More importantly, is there
an
> >> >easier/cleaner way to accomplish the whole thing?

> >> >Thanks for you consideration.

> >> >-Kingsley Allen

>Yes, the nav buttons do work, but my form doesn't have nav buttons (and
>won't).

>Using the close button (X) in the upper right corner of the form is what is
>triggering BeforeUpdate in this case, and is the root of my problem.  If
you
>copy the code to a form and then use the Close button instead of the navs,
>you should get the same result I do.

>Thanks.
>Kingsley

>> I just added this code to a form and it worked exactly as you'd like if I
>> moved out of the record using the nav buttons.   I think it might have to
>do
>> with the action you are taking to cause the Before Update event.  Are
you,
>> for example, clicking a "Save" button or something like that?

>> Keri

>> >Here is my BeforeUpdate code...

>> >Private Sub Form_BeforeUpdate(Cancel As Integer)
>> >        Dim Result As Integer
>> >        Result = PromptSaveData(Me.Caption)
>> >        Select Case Result
>> >        Case vbYes
>> >        Case vbNo
>> >            Me.Undo
>> >        Case vbCancel
>> >            Cancel = True
>> >        End Select
>> >End Sub

>> >The Yes/No/Cancel message box is generated by a PromptSaveData()
>> >function that I put in a module...

>> >Function PromptSaveData(ChangedItem As String) As Integer
>> >    Dim strPrompt, strTitle As String
>> >    Dim intButtons As Integer

>> >    strPrompt = "Do you want to save changes to the " & ChangedItem &
"?"
>> >    intButtons = vbYesNoCancel + vbInformation + vbDefaultButton1
>> >    strTitle = "Client Database"

>> >    PromptSaveData = MsgBox(strPrompt, intButtons, strTitle)
>> >End Function

>> >After I read the response I thought there might be something about the
>way
>> I
>> >customized my form that was throwing it off, so I built a new one using
>the
>> >form
>> >wizard, added the code above, and got the same result.

>> >Thanks,
>> >Kingsley

>> >> There is something you're doing that is messing with the usual
>> >functioning.

>> >> Cancel=True

>> >> in the Before Update event will cancel the update and return you to
the
>> >> form.  So it's something else that is causing your error.

>> >> How are you getting / testing for the user input and then processing
>it?
>> >> Show us your Before Update code.

>> >> Keri

>> >> >This is long, but I wanted to be as clear as possible...

>> >> >I am trying to mirror what I think is fairly standard window behavior
>in
>> >an
>> >> >Access 97 form, and I am having no luck finding a clean way to do it.

>> >> >I have a form that is used only for editing existing records of a
>single
>> >> >table.  When the user closes the form, I want to generate the typical
>> "Do
>> >> >you want to save changes?" message box with Yes, No, and Cancel
>buttons
>> >on
>> >> >it.

>> >> >As you might expect, I want the following actions to occur based on
>the
>> >> user
>> >> >response:
>> >> >  -  If the user clicks Yes, the form closes after saving the data.
>> >> >  -  If the user clicks No, the form closes without saving the data.
>> >> >  -  If the user clicks Cancel, the form stays open in the state it
>was
>> >> >right before the user closed the form, and the  underlying source
>record
>> >is
>> >> >left untouched.

>> >> >I have been able to produce most of this behavior, but I am stuck on
>the
>> >> >Cancel response.  I put the msgbox code in the Form_BeforeUpdate
event
>> >> >because it occurs just before the Unload --> Deactivate --> Close
>> >sequence.

>> >> >When the Cancel button is clicked, I cancel the BeforeUpdate event.
>But
>> >> >then I get a built-in Access message that says "You can't save this
>> >record
>> >> >at this time.  Microsoft Access may have encountered an error while
>> >trying
>> >> >to save a record.  If you close this object now, the data changes you
>> >made
>> >> >will be lost.  Do you want to close the database object anyway?
>Yes/No"

>> >> >When I click No, the form does revert back to the way it was right
>> before
>> >I
>> >> >closed the form, just the way I want it to.  The problem is that I do
>> not
>> >> >want the users to see this Access message.  I can't suppress it with
>> Echo
>> >> or
>> >> >SetWarnings.  I also used SendKeys to send CTRL+N, which did close
the
>> >> >Access message before the user really has time to see it, but that is
>a
>> >> >hokey workaround.

>> >> >Is there a way to suppress this message?  More importantly, is there
>an
>> >> >easier/cleaner way to accomplish the whole thing?

>> >> >Thanks for you consideration.

>> >> >-Kingsley Allen

> Yep, that did it, and it is a trappable error after all, a form error.  In
> the form's On Error property, put [Event Procedure].  Here is your code:

> Private Sub Form_Error(DataErr As Integer, Response As Integer)
> If DataErr = 2169 Then
>     Response = acDataErrContinue
> Else
>     Response = acDataErrDisplay
> End If
> End Sub

> Keri

> >Yes, the nav buttons do work, but my form doesn't have nav buttons (and
> >won't).

> >Using the close button (X) in the upper right corner of the form is what
is
> >triggering BeforeUpdate in this case, and is the root of my problem.  If
> you
> >copy the code to a form and then use the Close button instead of the
navs,
> >you should get the same result I do.

> >Thanks.
> >Kingsley

> >> I just added this code to a form and it worked exactly as you'd like if
I
> >> moved out of the record using the nav buttons.   I think it might have
to
> >do
> >> with the action you are taking to cause the Before Update event.  Are
> you,
> >> for example, clicking a "Save" button or something like that?

> >> Keri

> >> >Here is my BeforeUpdate code...

> >> >Private Sub Form_BeforeUpdate(Cancel As Integer)
> >> >        Dim Result As Integer
> >> >        Result = PromptSaveData(Me.Caption)
> >> >        Select Case Result
> >> >        Case vbYes
> >> >        Case vbNo
> >> >            Me.Undo
> >> >        Case vbCancel
> >> >            Cancel = True
> >> >        End Select
> >> >End Sub

> >> >The Yes/No/Cancel message box is generated by a PromptSaveData()
> >> >function that I put in a module...

> >> >Function PromptSaveData(ChangedItem As String) As Integer
> >> >    Dim strPrompt, strTitle As String
> >> >    Dim intButtons As Integer

> >> >    strPrompt = "Do you want to save changes to the " & ChangedItem &
> "?"
> >> >    intButtons = vbYesNoCancel + vbInformation + vbDefaultButton1
> >> >    strTitle = "Client Database"

> >> >    PromptSaveData = MsgBox(strPrompt, intButtons, strTitle)
> >> >End Function

> >> >After I read the response I thought there might be something about the
> >way
> >> I
> >> >customized my form that was throwing it off, so I built a new one
using
> >the
> >> >form
> >> >wizard, added the code above, and got the same result.

> >> >Thanks,
> >> >Kingsley

> >> >> There is something you're doing that is messing with the usual
> >> >functioning.

> >> >> Cancel=True

> >> >> in the Before Update event will cancel the update and return you to
> the
> >> >> form.  So it's something else that is causing your error.

> >> >> How are you getting / testing for the user input and then processing
> >it?
> >> >> Show us your Before Update code.

> >> >> Keri

> >> >> >This is long, but I wanted to be as clear as possible...

> >> >> >I am trying to mirror what I think is fairly standard window
behavior
> >in
> >> >an
> >> >> >Access 97 form, and I am having no luck finding a clean way to do
it.

> >> >> >I have a form that is used only for editing existing records of a
> >single
> >> >> >table.  When the user closes the form, I want to generate the
typical
> >> "Do
> >> >> >you want to save changes?" message box with Yes, No, and Cancel
> >buttons
> >> >on
> >> >> >it.

> >> >> >As you might expect, I want the following actions to occur based on
> >the
> >> >> user
> >> >> >response:
> >> >> >  -  If the user clicks Yes, the form closes after saving the data.
> >> >> >  -  If the user clicks No, the form closes without saving the
data.
> >> >> >  -  If the user clicks Cancel, the form stays open in the state it
> >was
> >> >> >right before the user closed the form, and the  underlying source
> >record
> >> >is
> >> >> >left untouched.

> >> >> >I have been able to produce most of this behavior, but I am stuck
on
> >the
> >> >> >Cancel response.  I put the msgbox code in the Form_BeforeUpdate
> event
> >> >> >because it occurs just before the Unload --> Deactivate --> Close
> >> >sequence.

> >> >> >When the Cancel button is clicked, I cancel the BeforeUpdate event.
> >But
> >> >> >then I get a built-in Access message that says "You can't save this
> >> >record
> >> >> >at this time.  Microsoft Access may have encountered an error while
> >> >trying
> >> >> >to save a record.  If you close this object now, the data changes
you
> >> >made
> >> >> >will be lost.  Do you want to close the database object anyway?
> >Yes/No"

> >> >> >When I click No, the form does revert back to the way it was right
> >> before
> >> >I
> >> >> >closed the form, just the way I want it to.  The problem is that I
do
> >> not
> >> >> >want the users to see this Access message.  I can't suppress it
with
> >> Echo
> >> >> or
> >> >> >SetWarnings.  I also used SendKeys to send CTRL+N, which did close
> the
> >> >> >Access message before the user really has time to see it, but that
is
> >a
> >> >> >hokey workaround.

> >> >> >Is there a way to suppress this message?  More importantly, is
there
> >an
> >> >> >easier/cleaner way to accomplish the whole thing?

> >> >> >Thanks for you consideration.

> >> >> >-Kingsley Allen

>Keri, before I continue I just wanted to say thank you very much for your
>help with this problem.  It is great to know that someone out there with
>experience is willing to donate time and effort to those of us at the start
>of the learning curve.

>That said, I'm still not getting the behavior I need.  Your code brings me
>one big step closer because the Access error is now suppressed.  But the
>form closes; I need it to stay open.

>In other words, what I'm after is the same result you would get if you
>choose No when DataErr 2169 comes up and prompts "If you close this object
>now, the data changes you made will be lost.  Do you want to close the
>database object anyway?", but without DataErr 2169 ever coming up and
having
>to click No.

>Any ideas?

>Thanks,
>Kingsley

>> Yep, that did it, and it is a trappable error after all, a form error.
In
>> the form's On Error property, put [Event Procedure].  Here is your code:

>> Private Sub Form_Error(DataErr As Integer, Response As Integer)
>> If DataErr = 2169 Then
>>     Response = acDataErrContinue
>> Else
>>     Response = acDataErrDisplay
>> End If
>> End Sub

>> Keri

>> >Yes, the nav buttons do work, but my form doesn't have nav buttons (and
>> >won't).

>> >Using the close button (X) in the upper right corner of the form is what
>is
>> >triggering BeforeUpdate in this case, and is the root of my problem.  If
>> you
>> >copy the code to a form and then use the Close button instead of the
>navs,
>> >you should get the same result I do.

>> >Thanks.
>> >Kingsley

>> >> I just added this code to a form and it worked exactly as you'd like
if
>I
>> >> moved out of the record using the nav buttons.   I think it might have
>to
>> >do
>> >> with the action you are taking to cause the Before Update event.  Are
>> you,
>> >> for example, clicking a "Save" button or something like that?

>> >> Keri

>> >> >Here is my BeforeUpdate code...

>> >> >Private Sub Form_BeforeUpdate(Cancel As Integer)
>> >> >        Dim Result As Integer
>> >> >        Result = PromptSaveData(Me.Caption)
>> >> >        Select Case Result
>> >> >        Case vbYes
>> >> >        Case vbNo
>> >> >            Me.Undo
>> >> >        Case vbCancel
>> >> >            Cancel = True
>> >> >        End Select
>> >> >End Sub

>> >> >The Yes/No/Cancel message box is generated by a PromptSaveData()
>> >> >function that I put in a module...

>> >> >Function PromptSaveData(ChangedItem As String) As Integer
>> >> >    Dim strPrompt, strTitle As String
>> >> >    Dim intButtons As Integer

>> >> >    strPrompt = "Do you want to save changes to the " & ChangedItem &
>> "?"
>> >> >    intButtons = vbYesNoCancel + vbInformation + vbDefaultButton1
>> >> >    strTitle = "Client Database"

>> >> >    PromptSaveData = MsgBox(strPrompt, intButtons, strTitle)
>> >> >End Function

>> >> >After I read the response I thought there might be something about
the
>> >way
>> >> I
>> >> >customized my form that was throwing it off, so I built a new one
>using
>> >the
>> >> >form
>> >> >wizard, added the code above, and got the same result.

>> >> >Thanks,
>> >> >Kingsley

>> >> >> There is something you're doing that is messing with the usual
>> >> >functioning.

>> >> >> Cancel=True

>> >> >> in the Before Update event will cancel the update and return you to
>> the
>> >> >> form.  So it's something else that is causing your error.

>> >> >> How are you getting / testing for the user input and then
processing
>> >it?
>> >> >> Show us your Before Update code.

>> >> >> Keri

>> >> >> >This is long, but I wanted to be as clear as possible...

>> >> >> >I am trying to mirror what I think is fairly standard window
>behavior
>> >in
>> >> >an
>> >> >> >Access 97 form, and I am having no luck finding a clean way to do
>it.

>> >> >> >I have a form that is used only for editing existing records of a
>> >single
>> >> >> >table.  When the user closes the form, I want to generate the
>typical
>> >> "Do
>> >> >> >you want to save changes?" message box with Yes, No, and Cancel
>> >buttons
>> >> >on
>> >> >> >it.

>> >> >> >As you might expect, I want the following actions to occur based
on
>> >the
>> >> >> user
>> >> >> >response:
>> >> >> >  -  If the user clicks Yes, the form closes after saving the
data.
>> >> >> >  -  If the user clicks No, the form closes without saving the
>data.
>> >> >> >  -  If the user clicks Cancel, the form stays open in the state
it
>> >was
>> >> >> >right before the user closed the form, and the  underlying source
>> >record
>> >> >is
>> >> >> >left untouched.

>> >> >> >I have been able to produce most of this behavior, but I am stuck
>on
>> >the
>> >> >> >Cancel response.  I put the msgbox code in the Form_BeforeUpdate
>> event
>> >> >> >because it occurs just before the Unload --> Deactivate --> Close
>> >> >sequence.

>> >> >> >When the Cancel button is clicked, I cancel the BeforeUpdate
event.
>> >But
>> >> >> >then I get a built-in Access message that says "You can't save
this
>> >> >record
>> >> >> >at this time.  Microsoft Access may have encountered an error
while
>> >> >trying
>> >> >> >to save a record.  If you close this object now, the data changes
>you
>> >> >made
>> >> >> >will be lost.  Do you want to close the database object anyway?
>> >Yes/No"

>> >> >> >When I click No, the form does revert back to the way it was right
>> >> before
>> >> >I
>> >> >> >closed the form, just the way I want it to.  The problem is that I
>do
>> >> not
>> >> >> >want the users to see this Access message.  I can't suppress it
>with
>> >> Echo
>> >> >> or
>> >> >> >SetWarnings.  I also used SendKeys to send CTRL+N, which did close
>> the
>> >> >> >Access message before the user really has time to see it, but that
>is
>> >a
>> >> >> >hokey workaround.

>> >> >> >Is there a way to suppress this message?  More importantly, is
>there
>> >an
>> >> >> >easier/cleaner way to accomplish the whole thing?

>> >> >> >Thanks for you consideration.

>> >> >> >-Kingsley Allen

> Well, I had added some other code when I was playing with this, and I
> thought the error trap alone was solving the problem.  But it seems the
> other code is necessary too.  Sorry about that.  Here is all the form code
> from a test form that behaves as you want.

> '***
> Option Compare Database
> Option Explicit
> Dim OKtoClose

> Private Sub Form_BeforeUpdate(Cancel As Integer)
>         Dim Result As Integer
>         Result = PromptSaveData(Me.Caption)
>         Select Case Result
>         Case vbYes
>           OKtoClose = True
>         Case vbNo
>             OKtoClose = True
>             Me.Undo
>         Case vbCancel
>             OKtoClose = False
>             Cancel = True
>         End Select
> End Sub

> Function PromptSaveData(ChangedItem As String) As Integer
>     Dim strPrompt, strTitle As String
>     Dim intButtons As Integer

>     strPrompt = "Do you want to save changes to the " & ChangedItem & "?"
>     intButtons = vbYesNoCancel + vbInformation + vbDefaultButton1
>     strTitle = "Client Database"

>     PromptSaveData = MsgBox(strPrompt, intButtons, strTitle)
> End Function

> Private Sub Form_Current()
> OKtoClose = True
> End Sub

> Private Sub Form_Error(DataErr As Integer, Response As Integer)
> If DataErr = 2169 Then
>     Response = acDataErrContinue
> Else
>     Response = acDataErrDisplay
> End If
> End Sub

> Private Sub Form_Unload(Cancel As Integer)
> If OKtoClose = False Then
>     Cancel = True
>     OKtoClose = True
> End If
> '***
> End Sub

> >Keri, before I continue I just wanted to say thank you very much for your
> >help with this problem.  It is great to know that someone out there with
> >experience is willing to donate time and effort to those of us at the
start
> >of the learning curve.

> >That said, I'm still not getting the behavior I need.  Your code brings
me
> >one big step closer because the Access error is now suppressed.  But the
> >form closes; I need it to stay open.

> >In other words, what I'm after is the same result you would get if you
> >choose No when DataErr 2169 comes up and prompts "If you close this
object
> >now, the data changes you made will be lost.  Do you want to close the
> >database object anyway?", but without DataErr 2169 ever coming up and
> having
> >to click No.

> >Any ideas?

> >Thanks,
> >Kingsley

> >> Yep, that did it, and it is a trappable error after all, a form error.
> In
> >> the form's On Error property, put [Event Procedure].  Here is your
code:

> >> Private Sub Form_Error(DataErr As Integer, Response As Integer)
> >> If DataErr = 2169 Then
> >>     Response = acDataErrContinue
> >> Else
> >>     Response = acDataErrDisplay
> >> End If
> >> End Sub

> >> Keri

> >> >Yes, the nav buttons do work, but my form doesn't have nav buttons
(and
> >> >won't).

> >> >Using the close button (X) in the upper right corner of the form is
what
> >is
> >> >triggering BeforeUpdate in this case, and is the root of my problem.
If
> >> you
> >> >copy the code to a form and then use the Close button instead of the
> >navs,
> >> >you should get the same result I do.

> >> >Thanks.
> >> >Kingsley

> >> >> I just added this code to a form and it worked exactly as you'd like
> if
> >I
> >> >> moved out of the record using the nav buttons.   I think it might
have
> >to
> >> >do
> >> >> with the action you are taking to cause the Before Update event.
Are
> >> you,
> >> >> for example, clicking a "Save" button or something like that?

> >> >> Keri

> >> >> >Here is my BeforeUpdate code...

> >> >> >Private Sub Form_BeforeUpdate(Cancel As Integer)
> >> >> >        Dim Result As Integer
> >> >> >        Result = PromptSaveData(Me.Caption)
> >> >> >        Select Case Result
> >> >> >        Case vbYes
> >> >> >        Case vbNo
> >> >> >            Me.Undo
> >> >> >        Case vbCancel
> >> >> >            Cancel = True
> >> >> >        End Select
> >> >> >End Sub

> >> >> >The Yes/No/Cancel message box is generated by a PromptSaveData()
> >> >> >function that I put in a module...

> >> >> >Function PromptSaveData(ChangedItem As String) As Integer
> >> >> >    Dim strPrompt, strTitle As String
> >> >> >    Dim intButtons As Integer

> >> >> >    strPrompt = "Do you want to save changes to the " & ChangedItem
&
> >> "?"
> >> >> >    intButtons = vbYesNoCancel + vbInformation + vbDefaultButton1
> >> >> >    strTitle = "Client Database"

> >> >> >    PromptSaveData = MsgBox(strPrompt, intButtons, strTitle)
> >> >> >End Function

> >> >> >After I read the response I thought there might be something about
> the
> >> >way
> >> >> I
> >> >> >customized my form that was throwing it off, so I built a new one
> >using
> >> >the
> >> >> >form
> >> >> >wizard, added the code above, and got the same result.

> >> >> >Thanks,
> >> >> >Kingsley

> >> >> >> There is something you're doing that is messing with the usual
> >> >> >functioning.

> >> >> >> Cancel=True

> >> >> >> in the Before Update event will cancel the update and return you
to
> >> the
> >> >> >> form.  So it's something else that is causing your error.

> >> >> >> How are you getting / testing for the user input and then
> processing
> >> >it?
> >> >> >> Show us your Before Update code.

> >> >> >> Keri

> >> >> >> >This is long, but I wanted to be as clear as possible...

> >> >> >> >I am trying to mirror what I think is fairly standard window
> >behavior
> >> >in
> >> >> >an
> >> >> >> >Access 97 form, and I am having no luck finding a clean way to
do
> >it.

> >> >> >> >I have a form that is used only for editing existing records of
a
> >> >single
> >> >> >> >table.  When the user closes the form, I want to generate the
> >typical
> >> >> "Do
> >> >> >> >you want to save changes?" message box with Yes, No, and Cancel
> >> >buttons
> >> >> >on
> >> >> >> >it.

> >> >> >> >As you might expect, I want the following actions to occur based
> on
> >> >the
> >> >> >> user
> >> >> >> >response:
> >> >> >> >  -  If the user clicks Yes, the form closes after saving the
> data.
> >> >> >> >  -  If the user clicks No, the form closes without saving the
> >data.
> >> >> >> >  -  If the user clicks Cancel, the form stays open in the state
> it
> >> >was
> >> >> >> >right before the user closed the form, and the  underlying
source
> >> >record
> >> >> >is
> >> >> >> >left untouched.

> >> >> >> >I have been able to produce most of this behavior, but I am
stuck
> >on
> >> >the
> >> >> >> >Cancel response.  I put the msgbox code in the Form_BeforeUpdate
> >> event
> >> >> >> >because it occurs just before the Unload --> Deactivate -->
Close
> >> >> >sequence.

> >> >> >> >When the Cancel button is clicked, I cancel the BeforeUpdate
> event.
> >> >But
> >> >> >> >then I get a built-in Access message that says "You can't save
> this
> >> >> >record
> >> >> >> >at this time.  Microsoft Access may have encountered an error
> while
> >> >> >trying
> >> >> >> >to save a record.  If you close this object now, the data
changes
> >you
> >> >> >made
> >> >> >> >will be lost.  Do you want to close the database object anyway?
> >> >Yes/No"

> >> >> >> >When I click No, the form does revert back to the way it was
right
> >> >> before
> >> >> >I
> >> >> >> >closed the form, just the way I want it to.  The problem is that
I
> >do
> >> >> not
> >> >> >> >want the users to see this Access message.  I can't suppress it
> >with
> >> >> Echo
> >> >> >> or
> >> >> >> >SetWarnings.  I also used SendKeys to send CTRL+N, which did
close
> >> the
> >> >> >> >Access message before the user really has time to see it, but
that
> >is
> >> >a
> >> >> >> >hokey workaround.

> >> >> >> >Is there a way to suppress this message?  More importantly, is
> >there
> >> >an
> >> >> >> >easier/cleaner way to accomplish the whole thing?

> >> >> >> >Thanks for you consideration.

> >> >> >> >-Kingsley Allen

> You'll have use a custom close button, and get rid of the Access
> X-in-the-corner.  Then you can test & trap and do all the things you want
> before you even issue the close.  There's only so much you can do with the
> built-in buttons; if those things aren't good enough, you have to roll
your
> own.

> Keri

> >After my last post, I realized that I needed to cancel the Unload after
> >OnError to keep the form open, so I ended up with the same code you did.
> It
> >does keep the form open.  But...

> >...believe it or not, the behavior is still not right because any changes
> >you have made to the data in the form are lost when Unload is cancelled.
> So
> >it doesn't get you the same result as clicking No in response to DataErr
> >2169.  (i.e. it still doesn't act like your typical "Save changes?
> >yes/no/cancel" prompt).

> >When you are designing a form in Access and you click Close, Access asks
if
> >you want to save the changes to the form (yes/no/cancel).  If you choose
> >Cancel, no changes are saved to the form, the form stays open in design
> >mode, and any modifications you have made are left alone.  It's just as
if
> >you never tried to close it in the first place.  This is the behavior I'm
> >trying to reproduce.

> >Thanks,
> >Kingsley

> >> Well, I had added some other code when I was playing with this, and I
> >> thought the error trap alone was solving the problem.  But it seems the
> >> other code is necessary too.  Sorry about that.  Here is all the form
> code
> >> from a test form that behaves as you want.

> >> '***
> >> Option Compare Database
> >> Option Explicit
> >> Dim OKtoClose

> >> Private Sub Form_BeforeUpdate(Cancel As Integer)
> >>         Dim Result As Integer
> >>         Result = PromptSaveData(Me.Caption)
> >>         Select Case Result
> >>         Case vbYes
> >>           OKtoClose = True
> >>         Case vbNo
> >>             OKtoClose = True
> >>             Me.Undo
> >>         Case vbCancel
> >>             OKtoClose = False
> >>             Cancel = True
> >>         End Select
> >> End Sub

> >> Function PromptSaveData(ChangedItem As String) As Integer
> >>     Dim strPrompt, strTitle As String
> >>     Dim intButtons As Integer

> >>     strPrompt = "Do you want to save changes to the " & ChangedItem &
"?"
> >>     intButtons = vbYesNoCancel + vbInformation + vbDefaultButton1
> >>     strTitle = "Client Database"

> >>     PromptSaveData = MsgBox(strPrompt, intButtons, strTitle)
> >> End Function

> >> Private Sub Form_Current()
> >> OKtoClose = True
> >> End Sub

> >> Private Sub Form_Error(DataErr As Integer, Response As Integer)
> >> If DataErr = 2169 Then
> >>     Response = acDataErrContinue
> >> Else
> >>     Response = acDataErrDisplay
> >> End If
> >> End Sub

> >> Private Sub Form_Unload(Cancel As Integer)
> >> If OKtoClose = False Then
> >>     Cancel = True
> >>     OKtoClose = True
> >> End If
> >> '***
> >> End Sub

> >> >Keri, before I continue I just wanted to say thank you very much for
> your
> >> >help with this problem.  It is great to know that someone out there
with
> >> >experience is willing to donate time and effort to those of us at the
> >start
> >> >of the learning curve.

> >> >That said, I'm still not getting the behavior I need.  Your code
brings
> >me
> >> >one big step closer because the Access error is now suppressed.  But
the
> >> >form closes; I need it to stay open.

> >> >In other words, what I'm after is the same result you would get if you
> >> >choose No when DataErr 2169 comes up and prompts "If you close this
> >object
> >> >now, the data changes you made will be lost.  Do you want to close the
> >> >database object anyway?", but without DataErr 2169 ever coming up and
> >> having
> >> >to click No.

> >> >Any ideas?

> >> >Thanks,
> >> >Kingsley

> >> >> Yep, that did it, and it is a trappable error after all, a form
error.
> >> In
> >> >> the form's On Error property, put [Event Procedure].  Here is your
> >code:

> >> >> Private Sub Form_Error(DataErr As Integer, Response As Integer)
> >> >> If DataErr = 2169 Then
> >> >>     Response = acDataErrContinue
> >> >> Else
> >> >>     Response = acDataErrDisplay
> >> >> End If
> >> >> End Sub

> >> >> Keri

> >> >> >Yes, the nav buttons do work, but my form doesn't have nav buttons
> >(and
> >> >> >won't).

> >> >> >Using the close button (X) in the upper right corner of the form is
> >what
> >> >is
> >> >> >triggering BeforeUpdate in this case, and is the root of my
problem.
> >If
> >> >> you
> >> >> >copy the code to a form and then use the Close button instead of
the
> >> >navs,
> >> >> >you should get the same result I do.

> >> >> >Thanks.
> >> >> >Kingsley

> >> >> >> I just added this code to a form and it worked exactly as you'd
> like
> >> if
> >> >I
> >> >> >> moved out of the record using the nav buttons.   I think it might
> >have
> >> >to
> >> >> >do
> >> >> >> with the action you are taking to cause the Before Update event.
> >Are
> >> >> you,
> >> >> >> for example, clicking a "Save" button or something like that?

> >> >> >> Keri

> >> >> >> >Here is my BeforeUpdate code...

> >> >> >> >Private Sub Form_BeforeUpdate(Cancel As Integer)
> >> >> >> >        Dim Result As Integer
> >> >> >> >        Result = PromptSaveData(Me.Caption)
> >> >> >> >        Select Case Result
> >> >> >> >        Case vbYes
> >> >> >> >        Case vbNo
> >> >> >> >            Me.Undo
> >> >> >> >        Case vbCancel
> >> >> >> >            Cancel = True
> >> >> >> >        End Select
> >> >> >> >End Sub

> >> >> >> >The Yes/No/Cancel message box is generated by a PromptSaveData()
> >> >> >> >function that I put in a module...

> >> >> >> >Function PromptSaveData(ChangedItem As String) As Integer
> >> >> >> >    Dim strPrompt, strTitle As String
> >> >> >> >    Dim intButtons As Integer

> >> >> >> >    strPrompt = "Do you want to save changes to the " &
> ChangedItem
> >&
> >> >> "?"
> >> >> >> >    intButtons = vbYesNoCancel + vbInformation +
vbDefaultButton1
> >> >> >> >    strTitle = "Client Database"

> >> >> >> >    PromptSaveData = MsgBox(strPrompt, intButtons, strTitle)
> >> >> >> >End Function

> >> >> >> >After I read the response I thought there might be something
about
> >> the
> >> >> >way
> >> >> >> I
> >> >> >> >customized my form that was throwing it off, so I built a new
one
> >> >using
> >> >> >the
> >> >> >> >form
> >> >> >> >wizard, added the code above, and got the same result.

> >> >> >> >Thanks,
> >> >> >> >Kingsley

> >> >> >> >> There is something you're doing that is messing with the usual
> >> >> >> >functioning.

> >> >> >> >> Cancel=True

> >> >> >> >> in the Before Update event will cancel the update and return
you
> >to
> >> >> the
> >> >> >> >> form.  So it's something else that is causing your error.

> >> >> >> >> How are you getting / testing for the user input and then
> >> processing
> >> >> >it?
> >> >> >> >> Show us your Before Update code.

> >> >> >> >> Keri

> >> >> >> >> >This is long, but I wanted to be as clear as possible...

> >> >> >> >> >I am trying to mirror what I think is fairly standard window
> >> >behavior
> >> >> >in
> >> >> >> >an
> >> >> >> >> >Access 97 form, and I am having no luck finding a clean way
to
> >do
> >> >it.

> >> >> >> >> >I have a form that is used only for editing existing records
of
> >a
> >> >> >single
> >> >> >> >> >table.  When the user closes the form, I want to generate the
> >> >typical
> >> >> >> "Do
> >> >> >> >> >you want to save changes?" message box with Yes, No, and
Cancel
> >> >> >buttons
> >> >> >> >on
> >> >> >> >> >it.

> >> >> >> >> >As you might expect, I want the following actions to occur
> based
> >> on
> >> >> >the
> >> >> >> >> user
> >> >> >> >> >response:
> >> >> >> >> >  -  If the user clicks Yes, the form closes after saving the
> >> data.
> >> >> >> >> >  -  If the user clicks No, the form closes without saving
the
> >> >data.
> >> >> >> >> >  -  If the user clicks Cancel, the form stays open in the
> state
> >> it
> >> >> >was
> >> >> >> >> >right before the user closed the form, and the  underlying
> >source
> >> >> >record
> >> >> >> >is
> >> >> >> >> >left untouched.

> >> >> >> >> >I have been able to produce most of this behavior, but I am
> >stuck
> >> >on
> >> >> >the
> >> >> >> >> >Cancel response.  I put the msgbox code in the
> Form_BeforeUpdate
> >> >> event
> >> >> >> >> >because it occurs just before the Unload --> Deactivate -->
> >Close
> >> >> >> >sequence.

> >> >> >> >> >When the Cancel button is clicked, I cancel the BeforeUpdate
> >> event.
> >> >> >But
> >> >> >> >> >then I get a built-in Access message that says "You can't
save
> >> this
> >> >> >> >record
> >> >> >> >> >at this time.  Microsoft Access

>After my last post, I realized that I needed to cancel the Unload after
>OnError to keep the form open, so I ended up with the same code you did.
It
>does keep the form open.  But...

>...believe it or not, the behavior is still not right because any changes
>you have made to the data in the form are lost when Unload is cancelled.
So
>it doesn't get you the same result as clicking No in response to DataErr
>2169.  (i.e. it still doesn't act like your typical "Save changes?
>yes/no/cancel" prompt).

>When you are designing a form in Access and you click Close, Access asks if
>you want to save the changes to the form (yes/no/cancel).  If you choose
>Cancel, no changes are saved to the form, the form stays open in design
>mode, and any modifications you have made are left alone.  It's just as if
>you never tried to close it in the first place.  This is the behavior I'm
>trying to reproduce.

>Thanks,
>Kingsley

>> Well, I had added some other code when I was playing with this, and I
>> thought the error trap alone was solving the problem.  But it seems the
>> other code is necessary too.  Sorry about that.  Here is all the form
code
>> from a test form that behaves as you want.

>> '***
>> Option Compare Database
>> Option Explicit
>> Dim OKtoClose

>> Private Sub Form_BeforeUpdate(Cancel As Integer)
>>         Dim Result As Integer
>>         Result = PromptSaveData(Me.Caption)
>>         Select Case Result
>>         Case vbYes
>>           OKtoClose = True
>>         Case vbNo
>>             OKtoClose = True
>>             Me.Undo
>>         Case vbCancel
>>             OKtoClose = False
>>             Cancel = True
>>         End Select
>> End Sub

>> Function PromptSaveData(ChangedItem As String) As Integer
>>     Dim strPrompt, strTitle As String
>>     Dim intButtons As Integer

>>     strPrompt = "Do you want to save changes to the " & ChangedItem & "?"
>>     intButtons = vbYesNoCancel + vbInformation + vbDefaultButton1
>>     strTitle = "Client Database"

>>     PromptSaveData = MsgBox(strPrompt, intButtons, strTitle)
>> End Function

>> Private Sub Form_Current()
>> OKtoClose = True
>> End Sub

>> Private Sub Form_Error(DataErr As Integer, Response As Integer)
>> If DataErr = 2169 Then
>>     Response = acDataErrContinue
>> Else
>>     Response = acDataErrDisplay
>> End If
>> End Sub

>> Private Sub Form_Unload(Cancel As Integer)
>> If OKtoClose = False Then
>>     Cancel = True
>>     OKtoClose = True
>> End If
>> '***
>> End Sub

>> >Keri, before I continue I just wanted to say thank you very much for
your
>> >help with this problem.  It is great to know that someone out there with
>> >experience is willing to donate time and effort to those of us at the
>start
>> >of the learning curve.

>> >That said, I'm still not getting the behavior I need.  Your code brings
>me
>> >one big step closer because the Access error is now suppressed.  But the
>> >form closes; I need it to stay open.

>> >In other words, what I'm after is the same result you would get if you
>> >choose No when DataErr 2169 comes up and prompts "If you close this
>object
>> >now, the data changes you made will be lost.  Do you want to close the
>> >database object anyway?", but without DataErr 2169 ever coming up and
>> having
>> >to click No.

>> >Any ideas?

>> >Thanks,
>> >Kingsley

>> >> Yep, that did it, and it is a trappable error after all, a form error.
>> In
>> >> the form's On Error property, put [Event Procedure].  Here is your
>code:

>> >> Private Sub Form_Error(DataErr As Integer, Response As Integer)
>> >> If DataErr = 2169 Then
>> >>     Response = acDataErrContinue
>> >> Else
>> >>     Response = acDataErrDisplay
>> >> End If
>> >> End Sub

>> >> Keri

>> >> >Yes, the nav buttons do work, but my form doesn't have nav buttons
>(and
>> >> >won't).

>> >> >Using the close button (X) in the upper right corner of the form is
>what
>> >is
>> >> >triggering BeforeUpdate in this case, and is the root of my problem.
>If
>> >> you
>> >> >copy the code to a form and then use the Close button instead of the
>> >navs,
>> >> >you should get the same result I do.

>> >> >Thanks.
>> >> >Kingsley

>> >> >> I just added this code to a form and it worked exactly as you'd
like
>> if
>> >I
>> >> >> moved out of the record using the nav buttons.   I think it might
>have
>> >to
>> >> >do
>> >> >> with the action you are taking to cause the Before Update event.
>Are
>> >> you,
>> >> >> for example, clicking a "Save" button or something like that?

>> >> >> Keri

>> >> >> >Here is my BeforeUpdate code...

>> >> >> >Private Sub Form_BeforeUpdate(Cancel As Integer)
>> >> >> >        Dim Result As Integer
>> >> >> >        Result = PromptSaveData(Me.Caption)
>> >> >> >        Select Case Result
>> >> >> >        Case vbYes
>> >> >> >        Case vbNo
>> >> >> >            Me.Undo
>> >> >> >        Case vbCancel
>> >> >> >            Cancel = True
>> >> >> >        End Select
>> >> >> >End Sub

>> >> >> >The Yes/No/Cancel message box is generated by a PromptSaveData()
>> >> >> >function that I put in a module...

>> >> >> >Function PromptSaveData(ChangedItem As String) As Integer
>> >> >> >    Dim strPrompt, strTitle As String
>> >> >> >    Dim intButtons As Integer

>> >> >> >    strPrompt = "Do you want to save changes to the " &
ChangedItem
>&
>> >> "?"
>> >> >> >    intButtons = vbYesNoCancel + vbInformation + vbDefaultButton1
>> >> >> >    strTitle = "Client Database"

>> >> >> >    PromptSaveData = MsgBox(strPrompt, intButtons, strTitle)
>> >> >> >End Function

>> >> >> >After I read the response I thought there might be something about
>> the
>> >> >way
>> >> >> I
>> >> >> >customized my form that was throwing it off, so I built a new one
>> >using
>> >> >the
>> >> >> >form
>> >> >> >wizard, added the code above, and got the same result.

>> >> >> >Thanks,
>> >> >> >Kingsley

>> >> >> >> There is something you're doing that is messing with the usual
>> >> >> >functioning.

>> >> >> >> Cancel=True

>> >> >> >> in the Before Update event will cancel the update and return you
>to
>> >> the
>> >> >> >> form.  So it's something else that is causing your error.

>> >> >> >> How are you getting / testing for the user input and then
>> processing
>> >> >it?
>> >> >> >> Show us your Before Update code.

>> >> >> >> Keri

>> >> >> >> >This is long, but I wanted to be as clear as possible...

>> >> >> >> >I am trying to mirror what I think is fairly standard window
>> >behavior
>> >> >in
>> >> >> >an
>> >> >> >> >Access 97 form, and I am having no luck finding a clean way to
>do
>> >it.

>> >> >> >> >I have a form that is used only for editing existing records of
>a
>> >> >single
>> >> >> >> >table.  When the user closes the form, I want to generate the
>> >typical
>> >> >> "Do
>> >> >> >> >you want to save changes?" message box with Yes, No, and Cancel
>> >> >buttons
>> >> >> >on
>> >> >> >> >it.

>> >> >> >> >As you might expect, I want the following actions to occur
based
>> on
>> >> >the
>> >> >> >> user
>> >> >> >> >response:
>> >> >> >> >  -  If the user clicks Yes, the form closes after saving the
>> data.
>> >> >> >> >  -  If the user clicks No, the form closes without saving the
>> >data.
>> >> >> >> >  -  If the user clicks Cancel, the form stays open in the
state
>> it
>> >> >was
>> >> >> >> >right before the user closed the form, and the  underlying
>source
>> >> >record
>> >> >> >is
>> >> >> >> >left untouched.

>> >> >> >> >I have been able to produce most of this behavior, but I am
>stuck
>> >on
>> >> >the
>> >> >> >> >Cancel response.  I put the msgbox code in the
Form_BeforeUpdate
>> >> event
>> >> >> >> >because it occurs just before the Unload --> Deactivate -->
>Close
>> >> >> >sequence.

>> >> >> >> >When the Cancel button is clicked, I cancel the BeforeUpdate
>> event.
>> >> >But
>> >> >> >> >then I get a built-in Access message that says "You can't save
>> this
>> >> >> >record
>> >> >> >> >at this time.  Microsoft Access may have encountered an error
>> while
>> >> >> >trying
>> >> >> >> >to save a record.  If you close this object now, the data
>changes
>> >you
>> >> >> >made
>> >> >> >> >will be lost.  Do you want to close the database object anyway?
>> >> >Yes/No"

>> >> >> >> >When I click No, the form does revert back to the way it was
>right
>> >> >> before
>> >> >> >I
>> >> >> >> >closed the form, just the way I want it to.  The problem is
that
>I
>> >do
>> >> >> not
>> >> >> >> >want the users to see this Access message.  I can't suppress it
>> >with
>> >> >> Echo
>> >> >> >> or
>> >> >> >> >SetWarnings.  I also used SendKeys to send CTRL+N, which did
>close
>> >> the
>> >> >> >> >Access message before the user really has time to see it, but
>that
>> >is