msg box code

[Emma]

I have a piece of code which runs when a user leaves the last control on a form. The code displays a message box which asks the user whether they want to add a new record (select yes). Selecting no will display a second message box, which asks whether they want to exit database (yes) or close this form (no). Everything works fine except I cannot get the form to close!

The Code is:

Private Sub frame_Exit(Cancel As Integer)
Dim Msg, Style, Title, Response, MyString
Msg = "Do you want to add a new record? " ' Define message.
Style = vbYesNo + vbExclamation + vbDefaultButton1 ' Define buttons.
Title = "End of record" ' Define title.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes.
DoCmd.GoToControl "accession_no"
DoCmd.GoToRecord , , acNewRec
'MyString = "Yes" ' Perform some action.
Else
Msg = "Do you want to close the database? Select yes to close, no to return to the start of this record" ' Define message.
Style = vbYesNo + vbQuestion + vbDefaultButton1 ' Define buttons.
Title = "End of record" ' Define title.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes
DoCmd.Close acform "frm_objects" (this is the bit that doesnt work!"
Else
DoCmd.GoToControl "accession_no"
End If
End If
End Sub

Please if anyone can help I will be forever grateful - I do not understand why this code is not working!

Many many thanks
Emma

 

[Atomic Shrimp]

Hi Emma
There appears to be a comma missing in the line:
DoCmd.Close acform "frm_objects"

so it should read:
DoCmd.Close acform , "frm_objects"


Mike

 

[mhowell]

I believe that the return from the MsgBox is an integer (the value of vbYes). Response should be an integer and your if statement should be "If Response = 6 Then"

 

[Emma]

Thanks for both your comments, unfortunately neither seems to make any difference! When I put docmd.close , "frm_objects" I get a Run-time message 2585 "This action cant be carried out while processing a form or report event. A macro specified as the OnOpen, OnClose, Onformat, Onreatreat,Onpage or Onprint property setting contains an invalid action for the property. When you clicked ok, an Action failed dialog box will display the name of the macro that failed and its arguements"

THe form has two tabbed controls, but other than that is pretty straight forward. Many of the controls are combos, which drop down automatically when the user enters them, but other wise this is a relatively straightforward form.

Any further help really would be appreciated! I am at a loss at to why this doesnt work - I assumed that an action like Docmd.close was pretty uncomplicated!

Many thanks!
emma

 

[mhowell]

I tried your code and used the comma suggestion (after acFrom - DoCmd.Close acform , "frm_objects") and it worked.

 

[Emma]

Hi MHowell
Can I have your form!! Only joking!

Do you think this is either a problem with my form or my copy of access? I really am stumped - I have tried both suggestions and went back and tried again after you last post - but still no joy. I have compacted and repairded but still no close of form - just the runtime error message? I have a couple of other events (OnClose ... open the main form, OnOpen .. maximise form) The form as far as I am aware is a simple pop up form for data entry, nowt fancy!

ta for your help - but I am at a loss to see why my form doesnt work?

 

[Richie]

You didn't tell us what the action failed message was ,but assuming there's no other events taking place your form may just be corrupt. Have tried copying it to test the copy?

 

[Emma]

I didnt get the Action Failed message!
I either went to debug, or end and then the form stopped running the code.

 

[Richie]

What was highlighted in debug?

 

[Emma]

DoCmd.Close acForm, "frm_objects" was highlighted.

I have tried copying the database, and re-writing the code, but to no avail. I can create a button that runs this code (message boxes, then close form if user has finished data entry etc.) It just wont seem to work from once the text field has been completed.

I am completley stumped!

 

[Richie]

Have you tried moving the event to the lost focus of the control?

 

[Emma]

Just tried that, still the same runtime error, on debug its still highlighting the DoCmd.Close acForm, "frm_objects" line.

 

[Richie]

I should have spotted it before but since your trying to exit the control and to close the form Access also has to exit the control which it can't because it's still proccessing the control, since you have already tried it with a button I would suggest you create a custom form with two buttons one to close the form the other to go back to "accession_no"
Else DoCmd.OpenForm etc
Exit sub
End If
End Sub
should work

 

[Emma]

Many thanks for all your help Richie and everyone else who helped. I have created a custom message form which has the correct buttons, and this works really well.

Many thanks again!
Emma