vbYesNoCancel

[JamesJ]

I'm calling the following in the BeforeUpdate of my form to confirm
data changes.

Public Function ConfirmDataChange(Cancel As Integer)

Set frm = Screen.ActiveForm

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

Case vbYes

Case vbNo
Cancel = True
frm.Undo

End Select

End Function

The Yes and No works fine. This form has no buttons and I'm
closing it by using the form's close (x) button. In both cases the
the form closes, but I want the vbCancel to prevent the from from
closing so the user can return to editing the record. What needs to
be put in the Case for the vbCancel the accomplish this?

Any help will be appreciated,
James

 

[Dorian]

The Cancel from a Msgbox does nothing other than allow a third return value
(vbcancel).
There is a cancel returnable from BeforeUpdate to cancel the update. But
that will not help preventing your form closing if you have the X showing.
Its best to disallow the X and have your own Close button where you can
insert code to do what you want (or not do what you don't want).
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[Marshall Barton]

I'm calling the following in the BeforeUpdate of my form to confirm
data changes.

Public Function ConfirmDataChange(Cancel As Integer)

Set frm = Screen.ActiveForm

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

Case vbYes

Case vbNo
Cancel = True
frm.Undo

End Select

End Function

The Yes and No works fine. This form has no buttons and I'm
closing it by using the form's close (x) button. In both cases the
the form closes, but I want the vbCancel to prevent the from from
closing so the user can return to editing the record. What needs to
be put in the Case for the vbCancel the accomplish this?

I don't see what setting your function's Cancel argument has
to do with the vbNo case. Assuming you can remove that line
try using:

Case vbCancel
Cancel = True 'must be passed on to Form's BeforeUpdate

and adding code to the calling procedure to cancel the
form's BeforeUpdate event. There is a bug(?) that
sometimes(?) let's the form close anyway. If that happens,
save another value to be used to cancel the form's Unload
event.

 

[JamesJ]

I've modified it to the following but it's not working
I've copied an example from help to cancel the OnUnload event of the form.
The example code works fine. Now, somehow I need to call the form's OnUnload
event
from the following code that I call form the forms' BeforUpdate event??
I'm getting an Invalid use of Property error on the 'frm.OnUnload (0)' line.

Set frm = Screen.ActiveForm

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

Case vbYes

Case vbNo
frm.Undo

Case vbCancel
frm.OnUnload (0)


End Select

 

[Marshall Barton]

I've modified it to the following but it's not working
I've copied an example from help to cancel the OnUnload event of the form.
The example code works fine. Now, somehow I need to call the form's OnUnload
event
from the following code that I call form the forms' BeforUpdate event??
I'm getting an Invalid use of Property error on the 'frm.OnUnload (0)' line.

Set frm = Screen.ActiveForm

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

Case vbYes

Case vbNo
frm.Undo

Case vbCancel
frm.OnUnload (0)
End Select

OnUnload is a form ***property***, not a procedure. The
procedure is named Form_Unload. But that is irrelevant
because Access calls the Unload event procedure when a user
attempts to close the form.

What you should be able to do is set a module level variable
at the top of the module, before any procedure:

Private bolCancelClose As Boolean

and then add code to the Unload event that uses the variable
to cancel the unload (and thus the close) or let the close
proceed:

. . .
bolCancelClose = False
Select Case MsgBox( ...
. . .
Case vbCancel
bolCancelClose = True

Then the form's Unload event procedure could look like:

Cancel = bolCancelClose

 

[JamesJ]

I 'put' (for lack of a proper term) the variable in the module that has the
Code which is a public function:

Public Function ConfirmDataChange(Cancel As Integer)

Set frm = Screen.ActiveForm

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

Case vbYes

Case vbNo
frm.Undo

Case vbCancel
bolCancelClose = True

End Select

End Function

But, I also needed to 'put' the variable in the top of the form's OnUnload
property.
Problem is the form still closes when if I select Cancel.

James

 

[Marshall Barton]

I 'put' (for lack of a proper term) the variable in the module that has the
Code which is a public function:

Public Function ConfirmDataChange(Cancel As Integer)

Set frm = Screen.ActiveForm

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

Case vbYes

Case vbNo
frm.Undo

Case vbCancel
bolCancelClose = True

End Select

End Function

But, I also needed to 'put' the variable in the top of the form's OnUnload
property.

You can not declare a module level variable when you make it
local to a procedure. The procedure level local dec;ation
will override the module level declaration in that
procedure. When you do that, os courcse the form woll close
because the value from the ConfirmDataChange function will
not be used and the value of the local bolCancelClose
variable has the initial value of False.

Just in case you are unclear about the meaning of module
level declarations, they must be at the top of the entire
module, ABOVE the first Sub or Function declaration.

 

[JamesJ]

I call the ConfirmDataChange function from the BeforeUpdate of the form:
=ConfirmDataChange(0)

Private bolCancelClose As Bolean I inserted into the top
of the module containing the ConfirmDataChange below the Option Explicit-
before any functions. That I understand.
I then needed to put Cancel = bolCancelClose in the OnUnload of the form.
Which required me to declare bolCancelClose either in the form's module
declaration
or in the OnUnload. In the OnUnload of the form I have Cancel =
bolCancelClose.
What am I doing wrong?
I hope I get the terminology correct.

Thanks for your patience
James

 

[Marshall Barton]

I call the ConfirmDataChange function from the BeforeUpdate of the form:
=ConfirmDataChange(0)

Private bolCancelClose As Bolean I inserted into the top
of the module containing the ConfirmDataChange below the Option Explicit-
before any functions. That I understand.
I then needed to put Cancel = bolCancelClose in the OnUnload of the form.
Which required me to declare bolCancelClose either in the form's module
declaration
or in the OnUnload. In the OnUnload of the form I have Cancel =
bolCancelClose.

If you put the variable declaration in the Unload event
procedure, it will not work.

I'm getting the impression that the ConfirmDataChange
function is not in the form's module. If the function is in
a standard module, then you have to make it a global
variable so all modules can refer to it. To make it global,
change Private to Public.

Using global valriables is a poor practice and I had assumed
the function was local to the form. I recommend this unless
you have a good reason (e.g. used by several forms) to make
available to you entire application.

 

[JamesJ]

Sorry, I should have mentioned that the function is not a in the form's
module.
I use it with several other forms. If need be I could put this into the
form's module
But I'll leave it like it is for now.
I changed the declaration to Public and have Cancel = bolCancelClose in the
OnUnload but the form still closes when I click Cancel.
Again, I'm closing the form using the Form's x close button. Not sure if it
matters.

James

 

[JamesJ]

Disrequerd the earlier response.
I had inadvertently deleted the vb in front of the vbCancel.
Selecting cancel keeps the form open but unless the form is dirty nothing
happens when I click the form's x button.
Also I added bolCancelClose = False the the other 2 cases.

James

 

[Marshall Barton]

Disrequerd the earlier response.
I had inadvertently deleted the vb in front of the vbCancel.
Selecting cancel keeps the form open but unless the form is dirty nothing
happens when I click the form's x button.

When the form is not dirty, the form should close because
bolCancelClose is False. If that's not happening, look for
some other code that could be doing something odd (probably
in the Unload or Close event procedures). OTOH, I don't
know what the code looks like now, so I guess there could be
something wrong there too.

Also I added bolCancelClose = False the the other 2 cases.

Good idea. I should have thought of that.

I'm still bothered by the use of a global variable. Since
the function is not returning a value, you should be able to
return the True or False value as the function's return
value and save it in a module level private variable in each
form instead of using one global variable for all the forms.

 

[JamesJ]

I've move the function to the form module. In the BeforeUpdate of the form I
have:

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

Case vbYes
bolCancelClose = False

Case vbNo
Me.Undo
bolCancelClose = False

Case vbCancel
bolCancelClose = True

End Select

In the On Unload of the form I have:

Cancel = bolCancelClose

Now, when I open the form and don't make any changes and I click the form's
x button the form closes
without the message box because, I'm assuming, since the form doesn't become
dirty the BeforeUpdate doesn't fire.
If I make a change to the data then click the x button and click Cancel I am
unable to close the form at all. I need to
bring up Windows Task Manager to close Access.

James

Disrequerd the earlier response.
I had inadvertently deleted the vb in front of the vbCancel.
Selecting cancel keeps the form open but unless the form is dirty nothing
happens when I click the form's x button.

When the form is not dirty, the form should close because
bolCancelClose is False. If that's not happening, look for
some other code that could be doing something odd (probably
in the Unload or Close event procedures). OTOH, I don't
know what the code looks like now, so I guess there could be
something wrong there too.

Also I added bolCancelClose = False the the other 2 cases.

Good idea. I should have thought of that.

I'm still bothered by the use of a global variable. Since
the function is not returning a value, you should be able to
return the True or False value as the function's return
value and save it in a module level private variable in each
form instead of using one global variable for all the forms.
--
Marsh
MVP [MS Access]

"Marshall Barton" wrote

 

[Marshall Barton]

I've move the function to the form module. In the BeforeUpdate of the form I
have:

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

Case vbYes
bolCancelClose = False

Case vbNo
Me.Undo
bolCancelClose = False

Case vbCancel
bolCancelClose = True

End Select

In the On Unload of the form I have:

Cancel = bolCancelClose

Now, when I open the form and don't make any changes and I click the form's
x button the form closes
without the message box because, I'm assuming, since the form doesn't become
dirty the BeforeUpdate doesn't fire.
If I make a change to the data then click the x button and click Cancel I am
unable to close the form at all. I need to
bring up Windows Task Manager to close Access.

But, I thought using cancel means you want to continue
editing. In that case, you need to do something to finish
editing the current record and/or try to close the form
again. When the msgbox appears again, you have to use
either the yes or no.

Maybe you are describing something else??

Did you move the global variable to the form too?

Are you really using OnUnload or did you mean the Unload
event procedure?

If the form does not display the msgbox the second time you
try to close the form, then I conclude there is some other
code either in those procedures or somewhere else that is
getting in the way.

 

[JamesJ]

I see now. I need to continue to edit otherwise the code won't fire once I
select cancel once. Duh!
I just assumed something was terribly wrong when nothing happened
when I click the x button second time.
I believe it's working now.

Thanks much, again, for your time and patience
James