If...Then...ElseIf

[Guest]

I've read numerous posts about this, but I still can't figure out what I'm
doing wrong. It's simple I'm sure, but I'm a novice at coding, so please
forgive me.

I have a form that has a date field (dtmMo). If this field is left blank
and the user tries to close the form, I want a message box to pop up telling
them that the record will be deleted (due to no date) and give them the
option to close the form and delete the record, or stop the close process and
fill in the date. Here's what I have so far:

Private Sub Form_Close()

Dim Cancel As Integer

Confirm = MsgBox("Date field is null. Continuing will cause this record
to be lost." & vbCrLf & "If you wish to continue, click OK." & vbCrLf & "If
you wish to save this record, click Cancel and enter a date.", vbOKCancel,
"Confirm")
If Confirm = vbCancel Then
Me.dtmMo.SetFocus
ElseIf Confirm = vbOK Then
DoCmd.Close acForm, "frmSummary", acSaveNo
End If

End Sub

I realize the message box pops up before checking to see if the date field
is null or not and that's one of my problems. Setting up the code this way
is the only way I could get the box to pop up at all. Also, regardless of
which button I click, Cancel or Ok, the form closes anyway.

Any help is appreciated!

 

[Guest]

I'm not an MVP or anything, but it looks like there are a couple of problems
in your code. First, you declared the variable "Cancel" but you don't really
use it anywhere in your code. Second you have the variable called "Confirm"
that hasn't been declared. You could try something like this;

dim Confirm as Integer

If isNull (me.dtmMo) Then

Confirm = msgbox (insert your message info)

If Confirm = vbCancel Then
Me.dtmMo.SetFocus
exit sub

Else
Me.Undo (this should undo any unsaved changes to the record)
DoCmd.Close acForm, Me.Name

End If
End If (two If statements, so you have to end both)

HTH

 

[Guest]

Thanks for the reply. I guess I should've looked at my code a little more
closely before posting it. Those errors are due to my frustrated but
continued efforts to get it to work. I posted that I could get the popup box
to open, and at one point I could, just not with the code I actually posted.
Sorry! Anyway, fixing the errors you pointed out got me back to where I was
before...sort of. I can't get it to work at all on my bound textbox, but if
I use an unbound textbox (just to practice the code on)the message box pops
up. Before, it closed the form no matter what I clicked on.
However now, if I hit No, it closes the form (the opposite of what I want it
to do) and if I hit Yes, it comes up with the Access Run-Time error 2501 "The
close action was cancelled, with the option to End or Debug. It will NOT set
the focus on the dtmMO field.

Any other ideas???

I'm stumped!

 

[fredg]

I've read numerous posts about this, but I still can't figure out what I'm
doing wrong. It's simple I'm sure, but I'm a novice at coding, so please
forgive me.

I have a form that has a date field (dtmMo). If this field is left blank
and the user tries to close the form, I want a message box to pop up telling
them that the record will be deleted (due to no date) and give them the
option to close the form and delete the record, or stop the close process and
fill in the date. Here's what I have so far:

Private Sub Form_Close()

Dim Cancel As Integer

Confirm = MsgBox("Date field is null. Continuing will cause this record
to be lost." & vbCrLf & "If you wish to continue, click OK." & vbCrLf & "If
you wish to save this record, click Cancel and enter a date.", vbOKCancel,
"Confirm")
If Confirm = vbCancel Then
Me.dtmMo.SetFocus
ElseIf Confirm = vbOK Then
DoCmd.Close acForm, "frmSummary", acSaveNo
End If

End Sub

I realize the message box pops up before checking to see if the date field
is null or not and that's one of my problems. Setting up the code this way
is the only way I could get the box to pop up at all. Also, regardless of
which button I click, Cancel or Ok, the form closes anyway.

Any help is appreciated!

For sure it's the wrong event.
I'll assume you must wish to do this only when the form is closing
(not just when you are adding a new record or changing an existing
record... in which case the Form's BeforeUpdate event would be
better).

Use the Form's Unload event, which does have a Cancel argument.
First check to see if the date field is indeed Null.
If so, present the message. If Cancel is selected, cancel closing the
form and return focus to the Date field.
Otherwise simply close the form without the message, as it's not
needed if the Date field is filled in.

Private Sub Form_Unload(Cancel As Integer)

If IsNull([DateField]) Then
If MsgBox("Date field is null. Continuing will cause this record to
be lost." & vbCrLf & "If you wish to exit without saving this record ,
click OK." & vbCrLf & "If you wish to save this record, click Cancel
and enter a date.", vbOKCancel, "Confirm") = vbCancel Then
Cancel = True
Me.DateField.SetFocus
End If
End If

End Sub

 

[Bob Quintal]

I've read numerous posts about this, but I still can't figure
out what I'm doing wrong. It's simple I'm sure, but I'm a
novice at coding, so please forgive me.

I have a form that has a date field (dtmMo). If this field is
left blank and the user tries to close the form, I want a
message box to pop up telling them that the record will be
deleted (due to no date) and give them the option to close the
form and delete the record, or stop the close process and fill
in the date. Here's what I have so far:

Private Sub Form_Close()

Dim Cancel As Integer

Confirm = MsgBox("Date field is null. Continuing will
cause this record
to be lost." & vbCrLf & "If you wish to continue, click OK." &
vbCrLf & "If you wish to save this record, click Cancel and
enter a date.", vbOKCancel, "Confirm")
If Confirm = vbCancel Then
Me.dtmMo.SetFocus
ElseIf Confirm = vbOK Then
DoCmd.Close acForm, "frmSummary", acSaveNo
End If

End Sub

I realize the message box pops up before checking to see if
the date field is null or not and that's one of my problems.
Setting up the code this way is the only way I could get the
box to pop up at all. Also, regardless of which button I
click, Cancel or Ok, the form closes anyway.

Any help is appreciated!

The Form_Close event is too late in the event chain to do what
you want here.
Move it to the form's beforeUpdate event.
The before updateEvent has a cancel parameter, you don't declare
it.

Test for the missing date

If Isnull(me.txtDateBox)
Confirm = MsgBox("Date field is null. " _
& "Continuing will cause this record to be lost." & vbCrLf
& "If you wish to continue, click OK." & vbCrLf
& "If you wish to save this record, click Cancel " _
& "and enter a date.", _
vbOKCancel, "Confirm")

If Confirm = vbCancel Then
Cancel = true
Me.dtmMo.SetFocus
Else
DoCmd.Close acForm, "frmSummary", acSaveNo
End If
end if

 

[Guest]

My apologies, I guess that type of code won't work in the On Close event. You
could use the Unload event like Fredg suggested. You could also turn off the
forms close button in the properties sheet, then ad your own close button and
put the code in the On Click event of your close button

 

[Guest]

Thanks! Your changes worked perfectly!!

I've read numerous posts about this, but I still can't figure out what I'm
doing wrong. It's simple I'm sure, but I'm a novice at coding, so please
forgive me.

I have a form that has a date field (dtmMo). If this field is left blank
and the user tries to close the form, I want a message box to pop up telling
them that the record will be deleted (due to no date) and give them the
option to close the form and delete the record, or stop the close process and
fill in the date. Here's what I have so far:

Private Sub Form_Close()

Dim Cancel As Integer

Confirm = MsgBox("Date field is null. Continuing will cause this record
to be lost." & vbCrLf & "If you wish to continue, click OK." & vbCrLf & "If
you wish to save this record, click Cancel and enter a date.", vbOKCancel,
"Confirm")
If Confirm = vbCancel Then
Me.dtmMo.SetFocus
ElseIf Confirm = vbOK Then
DoCmd.Close acForm, "frmSummary", acSaveNo
End If

End Sub

I realize the message box pops up before checking to see if the date field
is null or not and that's one of my problems. Setting up the code this way
is the only way I could get the box to pop up at all. Also, regardless of
which button I click, Cancel or Ok, the form closes anyway.

Any help is appreciated!

For sure it's the wrong event.
I'll assume you must wish to do this only when the form is closing
(not just when you are adding a new record or changing an existing
record... in which case the Form's BeforeUpdate event would be
better).

Use the Form's Unload event, which does have a Cancel argument.
First check to see if the date field is indeed Null.
If so, present the message. If Cancel is selected, cancel closing the
form and return focus to the Date field.
Otherwise simply close the form without the message, as it's not
needed if the Date field is filled in.

Private Sub Form_Unload(Cancel As Integer)

If IsNull([DateField]) Then
If MsgBox("Date field is null. Continuing will cause this record to
be lost." & vbCrLf & "If you wish to exit without saving this record ,
click OK." & vbCrLf & "If you wish to save this record, click Cancel
and enter a date.", vbOKCancel, "Confirm") = vbCancel Then
Cancel = True
Me.DateField.SetFocus
End If
End If

End Sub

 

[Guest]

Thanks...

I used Fredg's suggestion and it worked great. I may use your suggestion
about creating a custom close button in the future though!

 

[OldPro]

I've read numerous posts about this, but I still can't figure out what I'm
doing wrong. It's simple I'm sure, but I'm a novice at coding, so please
forgive me.

I have a form that has a date field (dtmMo). If this field is left blank
and the user tries to close the form, I want a message box to pop up telling
them that the record will be deleted (due to no date) and give them the
option to close the form and delete the record, or stop the close process and
fill in the date. Here's what I have so far:

Private Sub Form_Close()

Dim Cancel As Integer

Confirm = MsgBox("Date field is null. Continuing will cause this record
to be lost." & vbCrLf & "If you wish to continue, click OK." & vbCrLf & "If
you wish to save this record, click Cancel and enter a date.", vbOKCancel,
"Confirm")
If Confirm = vbCancel Then
Me.dtmMo.SetFocus
ElseIf Confirm = vbOK Then
DoCmd.Close acForm, "frmSummary", acSaveNo
End If

End Sub

I realize the message box pops up before checking to see if the date field
is null or not and that's one of my problems. Setting up the code this way
is the only way I could get the box to pop up at all. Also, regardless of
which button I click, Cancel or Ok, the form closes anyway.

Any help is appreciated!

First of all, use the form_unload event. It supplies a system
variable called "Cancel". Setting Cancel to true will abort the
closing of the form.

Second, to test a field to see if it has been left blank, use the
isnull( ) function.