How to prevent Form from closing?

[deko]

I have a pop-up form in which user-defined parameters are entered. The
parameters are entered into a subform datasheet which is bound to a table.
I need to check the product of two parameters to make sure they are valid
before updating them (or closing the popup form), so I use this code:

Private Function BadValue() As Boolean
If Me!Die * Me!Site > 32000 Or Me!Die * Me!Site < 0 Then
MsgBox "Die and/or Site out of range." & vbCrLf & vbCrLf & _
"The product of Die and Site must be" & vbCrLf & _
"between 0 and 32,000 for any given Job File.", vbCritical, _
" Invalid Die or Site Value"
BadValue = True
Else
BadValue = False
End If
End Sub

Private Sub Die_BeforeUpdate(Cancel As Integer)
If BadValue Then DoCmd.CancelEvent
End Sub

Private Sub Site_BeforeUpdate(Cancel As Integer)
If BadValue Then DoCmd.CancelEvent
End Sub

The DoCmd.CancelEvent seems to work - the value is not updated if BadValue
returns True.

But the problem is users generally enter a value and then immediately click
the close cross in the upper right corner of the popup form. This results
in an error message:

"You can't save this record at this time."

Is there any way to avoid this error? I really don't want to create my own
"Close" command button, if I can avoid it. I would rather continue to use
the standard close box to let users close the popup form. Can the form be
prevented from trying to close if BadValue returns True?

Thanks in advance.

[jv]

In the Form's Unload event, you can put
cancel=BadValue

deko wrote:

I have a pop-up form in which user-defined parameters are entered. The
parameters are entered into a subform datasheet which is bound to a table.
I need to check the product of two parameters to make sure they are valid
before updating them (or closing the popup form), so I use this code:

Private Function BadValue() As Boolean
If Me!Die * Me!Site > 32000 Or Me!Die * Me!Site < 0 Then
MsgBox "Die and/or Site out of range." & vbCrLf & vbCrLf & _
"The product of Die and Site must be" & vbCrLf & _
"between 0 and 32,000 for any given Job File.", vbCritical, _
" Invalid Die or Site Value"
BadValue = True
Else
BadValue = False
End If
End Sub

Private Sub Die_BeforeUpdate(Cancel As Integer)
If BadValue Then DoCmd.CancelEvent
End Sub

Private Sub Site_BeforeUpdate(Cancel As Integer)
If BadValue Then DoCmd.CancelEvent
End Sub

The DoCmd.CancelEvent seems to work - the value is not updated if BadValue
returns True.

But the problem is users generally enter a value and then immediately click
the close cross in the upper right corner of the popup form. This results
in an error message:

"You can't save this record at this time."

Is there any way to avoid this error? I really don't want to create my own
"Close" command button, if I can avoid it. I would rather continue to use
the standard close box to let users close the popup form. Can the form be
prevented from trying to close if BadValue returns True?

Thanks in advance.

[deko]

> In the Form's Unload event, you can put

cancel=BadValue

I made the BadValue function public and tried that in both the subform
datasheet and the popup form itself - no luck.

[jv]

The unload event is the place to conditionally prevent form from
closing. You'll need to step through the code (using breakpoints) to
see why it isn't working and the to play with the code.

[deko]

> The unload event is the place to conditionally prevent form from

closing.

With that assurance, I continued to troubleshoot. I took the code out of
the subform. Rather than using the Before_Update event on the subform
datasheet, I just test every set of values when the user attempts to close
the parent form. The table is small enough so it takes very little time.

Thanks for your help.

Private Sub Form_Unload(Cancel As Integer)
If BadValue Then Cancel = True
End Sub

Private Function BadValue() As Boolean
Dim lngDie As Long
Dim lngSite As Long
Dim db As DAO.Database
Dim rst As DAO.Recordset
DoCmd.RunCommand acCmdSaveRecord
Set db = CurrentDb
Set rst = db.OpenRecordset("tblPattern")
Do While Not rst.EOF
lngDie = rst!Die
lngSite = rst!Site
If lngDie * lngSite > 32000 Or lngDie * lngSite < 0 Then
MsgBox "Die and/or Site out of range for " & rst!JobFileName & _
vbCrLf & vbCrLf & "The product of Die and Site must be" & _
vbCrLf & "between 0 and 32,000 for any given Job File.", _
vbCritical, " Invalid Die or Site Value"
BadValue = True
Exit Do
Else
BadValue = False
End If
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set db = Nothing
End Function