On Form Close Event

[guggd2868]

Hi,

I'm trying to create a sub for when my form closes, a value input on a
control will be tested against existing values and if it does not
match one of them, a box will pop up saying so and CLOSING THE FORM
WILL BE CANCELLED. Here is my code:

Private Sub Form_Close()

Dim Box As String
Dim VarA As String
Dim VarB As String
Dim i As Integer
Dim n As Integer

' Make variable n the highest numbered shelter region
n = DMax("ID", "Shelter Regions")

' Initialize i
i = 1

'Start on line 50
GoSub Here:

'Look at row i in Shelter regions table and return the region in that
row as VarA.
Here: VarA = DLookup("Region", "Shelter Regions", "Region = i")

'Look at the region selected in the Shelter Doc Number Entry form that
is currently open.
VarB = DLookup("Region", "Shelter Regions", "Region = " & Forms!
[Shelter Doc Number Entry]!Region)

'Initialize Box saying that the two regions are not the same, and the
region needs to be added seperately.
Box = MsgBox("This is not an existing Shelter Region. (Chr(13) &
(Chr(10)) Please add this region in Delete Document \ Maintenance
before using this region.", vbOKOnly, "Shelter Region Error")

'If the two Shelter regions are not equal, display the box. If the
box is displayed, cancel the close form action.
If VarA <> VarB Then msg = Box And DoCmd.CancelEvent

'Otherwise, add 1 to i, if i is less then n go back and do the
comparison again with the new i value.
[Else: i = i + 1]
If i < n Then Return

End Sub

I already realize I need to alter it so that it is unless the match is
made, not if it isn't

My question is how to cancel the form close.
I thought DoCmd.CancelEvent would handle it, but it says: The
expression On Click you entered as the event property setting produced
the following error: Expected Function or variable.

 

[Dirk Goldgar]

My question is how to cancel the form close.
I thought DoCmd.CancelEvent would handle it, but it says: The
expression On Click you entered as the event property setting produced
the following error: Expected Function or variable.

You can't cancel the Close event. Use the Unload event instead, which can
be cancelled, thereby preventing the form from closing. The event procedure
for the Unload event has a Cancel argument that you can set to True to
cancel the event -- that's cleaner than using DoCmd.CancelEvent.

 

[guggd2868]

The form close event can't be cancelled but the form unload event can.  Put
your code in the unload event and where suitable put this line of code in:

   Cancel = True

That prevents the form from closing.

Chris
Microsoft MVP

I'm trying to create a sub for when my form closes, a value input on a
control will be tested against existing values and if it does not
match one of them, a box will pop up saying so and CLOSING THE FORM
WILL BE CANCELLED.  Here is my code:

Private Sub Form_Close()

Dim Box As String
Dim VarA As String
Dim VarB As String
Dim i As Integer
Dim n As Integer

' Make variable n the highest numbered shelter region
n = DMax("ID", "Shelter Regions")

' Initialize i
i = 1

'Start on line 50
GoSub Here:

'Look at row i in Shelter regions table and return the region in that
row as VarA.
Here: VarA = DLookup("Region", "Shelter Regions", "Region = i")

'Look at the region selected in the Shelter Doc Number Entry form that
is currently open.
VarB = DLookup("Region", "Shelter Regions", "Region = " & Forms!
[Shelter Doc Number Entry]!Region)

'Initialize Box saying that the two regions are not the same, and the
region needs to be added seperately.
Box = MsgBox("This is not an existing Shelter Region. (Chr(13) &
(Chr(10))  Please add this region in Delete Document \ Maintenance
before using this region.", vbOKOnly, "Shelter Region Error")

'If the two Shelter regions are not equal, display the box.  If the
box is displayed, cancel the close form action.
   If VarA <> VarB Then msg = Box And DoCmd.CancelEvent

'Otherwise, add 1 to i, if i is less then n go back and do the
comparison again with the new i value.
   [Else: i = i + 1]
If i < n Then Return

End Sub

I already realize I need to alter it so that it is unless the match is
made, not if it isn't

My question is how to cancel the form close.
I thought DoCmd.CancelEvent would handle it, but it says: The
expression On Click you entered as the event property setting produced
the following error: Expected Function or variable.

ok, thanks very much!