Type mismatch error

[melaniews]

This looks simple enough to me, but it gives me a type mismatch error.


Private Sub Form_Close()

Dim Msg, Style, Title, Response, MyString
Msg = "Do you want to export to a text file?"
Style = "vbYesNoCancel"
Title = "Export?"
Response = MsgBox(Msg, Style, Title)<=== Code stops here.

If Response = vbYes Then

DoCmd.TransferText acExportDelim, &quot;basic export specification&quot;, &quot;participatedinpromo&quot;, &quot;promo export.txt&quot;

ElseIf Response = vbNo Then

DoCmd.Close

Else

End If

End Sub

Any help is appreciatd.

TIA
Melanie

 

[TonyJollans]

Hi Melanie,

vbYesNoCancel is a numeric constant (value 3). You have created a string (value &quot;vbYesNoCancel&quot, hence the type mismatch. Just remove the quotes in your assignment of Style.

Enjoy,
Tony

 

[melaniews]

Thanks so much Tony. That worked and ----
brought me another problem.

I get an error message saying &quot;The Close Action was Canceled&quot; when I click the No button and it takes me here:

ElseIf Response = vbNo Then

DoCmd.Close <=== Code stops here


Can you help me here?

TIA
Melanie

 

[PaulF]

Your code is already in the Close event of the form, no need to resend the code to close.. just do nothing and it will continue to close on its own.

PaulF

 

[melaniews]

Thanks. I see that now. What about the cancel option. Should that be docmd.cancelevent to return to the form -- or is it too late and the form closes anyway.

TIA,
Melanie

 

[PaulF]

you can use the cancelevent... but you'll have to trap the error message (2501 I think it is)

PaulF

 

[TonyJollans]

Hi Melanie,

You cannot cancel the Close event, the Form has already been closed. If you want to trap it and stop the Form closing put your code in the Unload event.

Enjoy,
Tony