Stop Close if Dropdown not chosen

[George Atkins]

Acc2007. I have a form with several fields, one which is required. It is a
dropdown. I use a command button to close the form. I want the form to remain
open if - and only if - the record is edited and the dropdown field is not
filled in. But, if the user merely opens and closes the form without editing,
nothing should happen, even if the field is still blank.

I put the following code into the form's BeforeUpdate event:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Referral_Source = "" Then
MsgBox "You must select a referral source!", vbCritical +
vbDefaultButton1, "Missing Data"
Me.Referral_Source.SetFocus
DoCmd.CancelEvent
Else
Cancel = False
End If
End Sub

This much works, but the form still closes. What do I need to do to cancel
the close event, whether it occurs from the command button or the X?

 

[John W. Vinson]

On Fri, 15 May 2009 11:39:01 -0700, George Atkins <George

Acc2007. I have a form with several fields, one which is required. It is a
dropdown. I use a command button to close the form. I want the form to remain
open if - and only if - the record is edited and the dropdown field is not
filled in. But, if the user merely opens and closes the form without editing,
nothing should happen, even if the field is still blank.

I put the following code into the form's BeforeUpdate event:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Referral_Source = "" Then
MsgBox "You must select a referral source!", vbCritical +
vbDefaultButton1, "Missing Data"
Me.Referral_Source.SetFocus
DoCmd.CancelEvent
Else
Cancel = False
End If
End Sub

This much works, but the form still closes. What do I need to do to cancel
the close event, whether it occurs from the command button or the X?

Rather than DoCmd.CancelEvent, set Cancel to True. The BeforeUpdate event will
only fire if the form has been dirtied by the user changing some value.

You should probably also use

If IsNull(me.Referral_Source) Then

instead of comparing it to a zero length string. Even better:

If Me.Referral_Source & "" = ""

to cover both possibilities.

 

[George Atkins]

John,
Thanks for the quick reply. I restored the cancel=true statement as you
suggested (good tip). I then fixed the condition to Me.Referral_Source &
""="" This works if I try to do something like manually save the edit. But if
I close the form instead, I get the message and the form still closes.

So I need a way to keep it open if this even fires.

 

[George Atkins]

John,
Thanks for the quick reply. I restored the cancel=true statement as you
suggested (good tip). I then fixed the condition to Me.Referral_Source &
""="" This works if I try to do something like manually save the edit. But if
I close the form instead, I get the message and the form still closes.

So I need a way to keep it open if this even fires.