If Then Statement

[Samantha]

I know this must be something so simple That I am missing. I have the following code attached to a button on a modal form. My thought is that when a user clicks okay a message box will open for the user to select yes or no if they select no I want the message box to close allowing the person to return to frmLookupServiceAddress, otherwise I want both the message box and form to close and save. I am not getting any error message the message box goes away in both responses.

Dim Response As VbMsgBoxResult
    MsgBox "Did you verify that this is a new address?", vbYesNo
        If Response = vbNo Then
            MsgBox "Please verify that this address is not currently in the database.", vbInformation
        Else
        If Response = vbYes Then DoCmd.Close acForm, "frmLookupServicesAddress", acSaveYes
    End If

Thank you for your time!

 

[BigHappyDaddy]

So you were only missing assigning the result of the MsgBox to your defined variable.

I also cleaned up the code slightly. Since the MsgBox will only have the choice of Yes or No, there is no need for the second if statement.
Also notice that now the MsgBox is a function, the open and closing pararentheses are required.

Dim Response As VbMsgBoxResult
     [COLOR=red]Response =[/COLOR] MsgBox [COLOR=red]([/COLOR]"Did you verify that this is a new address?", vbYesNo[COLOR=red])[/COLOR]
     If Response = vbNo Then
          MsgBox "Please verify that this address is not currently in the database.", vbInformation
     Else
          DoCmd.Close acForm, "frmLookupServicesAddress", acSaveYes
     End If

 

[CJ_London]

you are not assigned a value to Response you need to change your code to (note the use of the brackets)

Response=MsgBox("Did you verify that this is a new address?", vbYesNo)

An alternative is not to use the response at all

if (MsgBox "Did you verify that this is a new address?", vbYesNo)= vbNo Then
    MsgBox "Please verify that this address is not currently in the database.", vbInformation
Else
    DoCmd.Close acForm, "frmLookupServicesAddress", acSaveYes
End If

 

[ypma]

Samantha' try this I have used Rslt instead of Reponse


Dim Rslt As Integer
Rslt = MsgBox("verfy that this is a new address?", vbYesNo)
If Rslt = vbNo Then
MsgBox "Please verify that this address is not currently in the database.", vbInformation
Else
If Rslt = vbYes Then DoCmd.Close acForm, "
frmLookupServicesAddress", acSaveYes

Regards YPMA
Sorry CJ London and Big Happy Daddy did not see your replies, some sort of time delay.

 

[nanscombe]

The acSaveYes parameter of the DoCmd.Close method saves any changes to the layout of the form rather than the data.

A AcCloseSave constant that specifies whether or not to save changes to the object. The default value is acSavePrompt.

Any new record entered would automatically be saved when the form closed.

I think there is more to this than meets the eye.

If they answer no:
How would they verify the address?
How would they delete a newly entered address if it was a duplicate?

 

[Samantha]

Thank you for all of the responses,

If they answer no:
How would they verify the address?
How would they delete a newly entered address if it was a duplicate?

frmLookupServicesAddress has a search feature built into it so it can be searched when the user returns to the form.
I haven't even thought about how to delete from that same form. I always reverted to an additional continuous form to delete. This started as a database that only I was inputting information into. I am open for suggestions .

I have tried

Dim Response As VbMsgBoxResult
     Response = MsgBox("Did you verify that this is a new address?", vbYesNo)
     If Response = vbNo Then
          MsgBox "Please verify that this address is not currently in the database.", vbInformation
     Else
          DoCmd.Close acForm, "frmLookupServicesAddress", acSaveYes
     End If

This method is still not closing the form "frmLookupServicesAddress". You are correct it saves the data as soon as I tab away from the field.

Samantha

 

[nanscombe]

I see no obvious reason why your code above should not work, in fact I have attatched a demo showing that i does work.


The only change I would make personally is to the form closing line, I would use ...

DoCmd.Close acForm, Me.Name, acSaveNo

Me.Name is a shortcut to the name of the object, in this case the form. If you change the name of the form you don't have to change the code.

acSaveNo means that any change to the layout, or presumably the position, of the form is not saved when it is closed.



Actually, I just tried and managed to get the code to fail.

It was when the name of the form did not match the name in the Close command. I expected to see an error message, but there was none.

So using Me.Name instead of writing in the name might well fix the problem.

 

[Samantha]

nanscombe,
You are right the name of the form was spelled incorrectly rookie mistake. Thank you for all your help.

Samantha