VBA: best way to close Access & possible Logic issues

[AlanKW]

I'm at wits end here. Commands that seem to work in forms do not appear to work in Functions, and the logic doesnt appear to be working. Current version is ACC97, but going to ACC-XP shortly, so I'd like code that works in both.

I have a function called ValidUser that determines if the user should or should not be in the database. If not, the user has the option to send an email to the admin (me) to add the user. There lies the problem. Code below. I'll skip what brings us to this point as that part works.
[tt]
NotAValidUser:
Notify = MsgBox("You are currently not listed as a user in the " & TheDB & " database" & vbCrLf & _
"Do you wish to notify the Database Admin?", vbYesNo)

If Notify = vbYes Then
DoCmd.SendObject , , , "admin1", "admin2", , "Please add me as a user to the " & TheDB & " Database", , True
MsgBox "Thank you. You will be notified when you are added as a user", vbOKOnly
DoCmd.Quit
Else
DoCmd.Quit
End If
Exit Function [/tt]

It asks me if I want to notify the admin, I click on No and it goes back to the form continuing like it was a valid user. If I click on yes, it shoots off the email, gives the thank you and continues along.
instead of DoCmd.Quit I've tried RunCommand acCmdExit which seems to work just as well and application.quit which works just as well and application.exit closes the DB, but leaves Access open and you cant do anything with it.

What is the way to close a database from a function?

 

[cghoga]

You need parentheses around the Thank you message box.

MsgBox ("Thank you. You will be notified when you are added as a user", vbOKOnly)

Also, if the intent is to close the app regardless of the user clicking Yes or No, move the Quit outside, under the if statement.

Also, you can just say Quit. You don't have to say DoCmd.Quit.

Hope this helps.

 

[TonyJollans]

Hi AlanKW,

I just copied your code into a Form and it worked just fine and I can't think of any reason why it shouldn't.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[AlanKW]

I've used it in a form as well and worked, but this is a function thats being called from a form. The bigger picture is that this function (when working) is being used in several databases.

cghoga; I will try just a quit and see what happens; and Ive been able to use msbox without parens most of the time.

thanks

 

[bboffin]

The Help for DoCmd.Quit in Access97 indicates that this is a deprecated method and that you should use Application.Quit.

This doesn't explain your problem but does indicate which way you should code it for forwards compatibility.

The only thing I can think of is that DoCmd remembers the last object it operated on and uses this when no specific object is identified in the arguments. This can cause all sorts of problems especially with the Close method. Maybe this is what is what is messing things up since your DoCmd.SendObject didn't reference an object at all and presumably therefore implicitly references the form that called the function as long as this was the last object referenced by DoCmd.

You could try being specific by passing into the function a reference to the form that is calling it and making the first two arguments of the SendObject method acForm and MyForm.Name where MyForm is the name of the Form argumant to the function. This is in any case better programming practice and makes the code clearer.

 

[Golom]

You may want to try

   DoCmd.Quit acQuitSaveNone

The default is

acQuitSaveAll

and that may be causing other objects to be reloaded and those in turn implicitly cancel the Quit.

 

[AlanKW]

Thank you for your comments & suggestions. I've consolidated the above suggestions into the following code:
[tt]
NotAValidUser:
Notify = MsgBox("You are currently not listed as a user in the " & TheDB & " database" & vbCrLf & _
"Do you wish to notify the Database Admin?", vbYesNo)

If Notify = vbYes Then
DoCmd.SendObject acSendNoObject, , acFormatTXT, "admin1", "admin2", , "Please add me as a user to the " & TheDB & " Database", , True
MsgBox "Thank you. You will be notified when you are added as a user", vbOKOnly
End If
DoCmd.Close acForm, , acSaveNo
Application.Quit acQuitSaveNone
Exit Function
[/tt]

Different attempts to exit the Database were
[tt]
DoCmd.Quit acQuitSaveNone - goes back to form

DoCmd.Close acForm, "frm_MainMenu", acSaveNo
DoCmd.Quit acQuitSaveNone - returns to database window
[/tt]
But what I discovered this morning is that I lack patience. The code below does completely close the database, but it takes ~15 seconds.
[tt]
DoCmd.Close acForm, "frm_MainMenu", acSaveNo
Application.Quit acQuitSaveNone [/tt]

Not the perfect solution, but it'll have to do for now. I would be interested in other thoughts or code suggestions that you have for closing the database within a function.

-Alan
(Go Pats!)