YesNo Problem (1 Viewer)

CraigDouglas · Jul 26, 2017

My name is Craig. Please, could someone help me?

I have a form and in the form an email text field. I want a message to pop up when the person tries to close the form and for there to be a Yes No option. If they choose Yes then I want the form to close if they choose No I want the form to remain, so they can add the missing email. The message would be something like "Do you want to leave without putting an email address in?

The code I have been working on and the code that fails to work is as follows.

'------------------------------------------------------------
' Form_AfterUpdate
'
'------------------------------------------------------------
Private Sub Form_AfterUpdate()
On Error GoTo Form_AfterUpdate_Err

If (IsNull(txtCustomerEmailAddress)) Then
Beep
MsgBox "Do you want to leave without " & vbCrLf & _
"putting an email address in?", vbYesNo + vbExclamation, "Email Missing"
If vbNo Then cancle = True
End If

I have tried all day to solve my problem so any help would be appreciated.

Form_AfterUpdate_Exit:
Exit Sub

Form_AfterUpdate_Err:
MsgBox Error$
Resume Form_AfterUpdate_Exit

End Sub

plog · Jul 26, 2017

The code I have been working on and the code that fails to work is as follows.

Please define 'fails to work'. Error message? Form always closes? Form never closes? Causes hair lose, constipation and vertigo?

isladogs · Jul 26, 2017

Ah plog you made me laugh ....

CraigDouglas
Probably just a typo, but if your code says 'cancle' rather than cancel it won't help

Also, why put this code in the form after update event?

Minty · Jul 26, 2017

plog said:
Causes hair lose, constipation and vertigo?

This sounds like old age...

The_Doc_Man · Jul 26, 2017

Minty, could also be a liver problem. Sadly, I speak of experience - though after treatment it is better now. But the hair I lost didn't come back, darn it, and I've always been accused of being dizzy on a good day. As to the constipation, people always tell me what I'm full of, so who knows...

OK, Craig, down to business: There are two problems I see.

The first is that you spelled "Cancel" wrong, so whatever it was going to do, it wouldn't have done it. We can't see if you have Option Explicit - but you probably don't, because that code wouldn't compile correctly if you did. Spelling Cancel wrong means you had an "automatic" variable created for you of type Variant, so the code would have run anyway. But it had no way to pass back that value to Access because to Access, that is YOUR variable, not part of the Access environment.

The second is that the event you chose for this situation is the WRONG EVENT. There is no Cancel option for a Form_AfterUpdate. Perhaps you want to use the BeforeUpdate event, because you CAN cancel that.

https://msdn.microsoft.com/en-us/library/bb238523(v=office.12).aspx

If you figure out that you are about to do an update and you want to see if it is OK to proceed, you MUST do the procedure validation check BEFORE the update because if you don't, your update is a fait accompli. I.e. too late to care.

gemma-the-husky · Jul 27, 2017

Code:

Private Sub Form_AfterUpdate()
 On Error GoTo Form_AfterUpdate_Err

 If (IsNull(txtCustomerEmailAddress)) Then
    Beep
    MsgBox "Do you want to leave without " & vbCrLf & _
 "putting an email address in?", vbYesNo + vbExclamation, "Email Missing"
    If vbNo Then cancle = True
 End If

the thing is this code just isn't right even without the typo. Cancel isn't an argument to the sub and setting it to true or false will do nothing. (Well you might be able to use it if it's declared correctly elsewhere, but I doubt if that is the intention). Also the msgbox syntax isn't right to obtain a useable result. I doubt if form_afterupdate is the right event.

CraigDouglas · Jul 27, 2017

Thank you for trying to help me.
I have fixed the spelling mistake and used the before update event instead of the after update event. I think I can also use the code in the on unload event? What I have done is combine two different section of vba code to try and get the result I want. I can post those if anyone thinks that will help? The code is still not doing what I am hoping for.
The code that I have changed is now as follows:

'Yes no code to get the user to put an email address in before closing the form.

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ErrorHandler

If (IsNull(txtCustomerEmailAddress)) Then
Beep
MsgBox "Do you want to leave without " & vbCrLf & _
"putting an email address in?", vbYesNo + vbExclamation, "Email Missing"
If vbNo Then Cancel = True
End If

CleanUpAndExit:

Exit Sub

ErrorHandler:
Call MsgBox("An error was encountered" & vbCrLf & vbCrLf & _
"Description: " & Err.Description & vbCrLf & _
"Error Number: " & Err.Number, , "Error")

Resume CleanUpAndExit
End Sub

The Message that comes up now after closing the relevant form is: You can’t save this record this time etc.

isladogs · Jul 27, 2017

It could be that you have an empty string rather than a null value
Try this instead as it will work for either though may not solve your problem

Code:

 If Nz(Me.txtCustomerEmailAddress,"")="" Then

Just to clarify is that error on closing the form after you click Yes or click No

If that still doesn't work, you could also try replacing it with similar code in Form_Close event where Yes closes the form and No returns focus to the textbox. Omit the Cancel line if so.

CraigDouglas · Jul 28, 2017

Dear Ridders

The message comes up if you click yes or no. I will see if I can get the code to work with what you have said.

Craig

isladogs · Jul 28, 2017

Well you said it was a 'yesno problem'

Putting code in Form_Close instead may be a better bet for you

The_Doc_Man · Jul 28, 2017

Just be sure that where you put the code is an event that can be canceled. If you create the event by letting Access build the infrastructure for you, it WILL include the Cancel variable (as an integer) in what it builds. If it doesn't build a Cancel, you used the wrong event. Here's your hint... in a sequence of related events, it is USUALLY the first event in the sequence that can be canceled. So you take form events Open, Load, Current, Activate... the first one is Open and that can be canceled. Once you decide to not cancel, those other events WILL fire. For form closure, it is Unload, Deactivate, Close - so the Close event is the wrong choice. Again, the first event in the sequence, in this case Unload, is the right choice.

isladogs · Jul 28, 2017

hi doc man

You're right. Its too late in form close event. My mistake

I had meant this to be taken in conjunction with my comments in post 8 and meant to write that it should be done as part of cmdClose event code before closing the form

CraigDouglas · Aug 2, 2017

Thank you Ridders and Doc Man for continuing to help me. I have done what I believe you suggested, but now when I click the Yes or No in the message box nothing happens. It just stays on my Add New Customer form. The code I used was:

Private Sub Form_Unload(Cancel As Integer)
On Error GoTo ErrorHandler

If Nz(Me.txtCustomerEmailAddress, "") = "" Then
Beep
MsgBox "Do you want to leave without " & vbCrLf & _
"putting an email address in?", vbYesNo + vbExclamation, "Email Missing"
If vbNo Then Cancel = True
End If

CleanUpAndExit:

Exit Sub

ErrorHandler:
Call MsgBox("An error was encountered" & vbCrLf & vbCrLf & _
"Description: " & Err.Description & vbCrLf & _
"Error Number: " & Err.Number, , "Error")

Resume CleanUpAndExit

End Sub

Please I would appreciate some more help.
Craig

Minty · Aug 2, 2017

The code as shown has no effect if they press yes.

Code:

 Private Sub Form_Unload(Cancel As Integer)
    On Error GoTo ErrorHandler

    If Nz(Me.txtCustomerEmailAddress, "") = "" Then
        Beep
        MsgBox "Do you want to leave without " & vbCrLf & _
               "putting an email address in?", vbYesNo + vbExclamation, "Email Missing"
        If vbNo Then Cancel = True
        [COLOR="Red"]Exit Sub[/COLOR]   [COLOR="Green"]' They said No so leave the form open[/COLOR]
    End If
   [COLOR="Red"] Me.Dirty = False [/COLOR] [COLOR="Green"]' Save the record[/COLOR]
[COLOR="red"]    DoCmd.Close acForm, "YourFormName" [/COLOR] [COLOR="green"] 'Close the form[/COLOR]

CleanUpAndExit:

    Exit Sub

ErrorHandler:
    Call MsgBox("An error was encountered" & vbCrLf & vbCrLf & _
                "Description: " & Err.Description & vbCrLf & _
                "Error Number: " & Err.Number, , "Error")

    Resume CleanUpAndExit

End Sub

CraigDouglas · Aug 4, 2017

Dear Minty

Thank you for trying to help me.

I put the code you said in, I put my forms name in your code & I used the unload event. When you try to leave the form the message comes up and it has the yes no option but you can't leave the form. You get trapped on the form.

Minty · Aug 4, 2017

Personally I'd still do this on the Before Update event of the form, as that will fire if you try and close the form without saving the record.

In that case you don't get trapped on the form.

CraigDouglas · Aug 4, 2017

Dear Minty

I tried what you suggested and put it in before update evet. Now an error message, if you call it that, comes up and says you can't save this record at this time. Once you click the yes or no option.
Craig

YesNo Problem (1 Viewer)

CraigDouglas

Registered User.

plog

Banishment Pending

isladogs

MVP / VIP

Minty

AWF VIP

The_Doc_Man

Immoderate Moderator

gemma-the-husky

Super Moderator

CraigDouglas

Registered User.

isladogs

MVP / VIP

CraigDouglas

Registered User.

isladogs

MVP / VIP

The_Doc_Man

Immoderate Moderator

isladogs

MVP / VIP

CraigDouglas

Registered User.

Minty

AWF VIP

CraigDouglas

Registered User.

Minty

AWF VIP

CraigDouglas

Registered User.

Similar threads

Users who are viewing this thread