• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 306
  • Last Modified:

After launching access from excel, I stop getting normal warning messages "Do you want to save changes"

I use the following code to launch Access from Excel.

    Lpath = "c:\myDB.mdb"
    Set AccApp = CreateObject("Access.Application")
    AccApp.OpenCurrentDatabase lpath
    AccApp.docmd.setwarnings True  '<==== THIS DOES NOT WORK EITHER

    AccApp.Visible = True
    AccApp.Application.Run "startupFromExcel"   ' call a procedure named "startupFromExcel".
    Accapp.DoCmd.SetWarnings True   '<===THIS DOES NOT WORK  EITHER

In most respects the resulting Access instance works just as if it was launched from a normal desktop icon.

I can make typical development modifications, like changing a form or a query.

The only problem is that the Access instance acts as if I am running under docmd.setwarnings False.

Every time I close a query, it is saved without the "do you want to save changes" warning.

Does anybody have any suggestions of how to rectify this automatically.

For instance, I tried putting the following code into my Excel and Access modules, BUT THEY DO NOT WORK I still do not get warning messages.

    sub startupFromExcel()
    docmd.setwarnings true  'DOES NOT WORK
    end sub

Even if I close Access, then reopen it manually, the docmd.setwarnings False   persists.

The only way to rectify things is to close Access, then use task manager to kill all instances of Access, then reopen Access.

This is very annoying, because it causes me to accidentally make changes to things without realizing it.

I considered having my excel macro automatically kill every existing Access process, but that will not help much, and it strikes me as a little dangerous.

Hope someone can come up with an answer.

  • 4
  • 3
1 Solution
Have you tried, Application.DisplayAlerts = True?

Here is more info:

Hope this helps.
rberkeConsultantAuthor Commented:
That doesn't work in Access, it gives a compile error.

And, running it in excel only affects the excel environment.  Access continues to malfunction.

In doing some reading on this it is suggested to put:
DoCmd.SetWarnings False

prior to calling anything else, and then add DoCmd.SetWarnings True where and when you need it.

Have you tried that approach?
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

rberkeConsultantAuthor Commented:
"put it where I need it" seems a little vague since I have already put it various places that don't work.

But I tried a few other places with no success.

If you have any more ideas, you might use the attached demo to see if your ideas work.

Simply put db1test.xls and db1.mdb into c:\. Then open the excel file and push the button.

rberkeConsultantAuthor Commented:
i also played with application.getoption and application.setoption.

But all the confirm action options were already on, so those are not the source of the problem
Thanks for attaching the db.  I will do some testing and see what I can come up with.
rberkeConsultantAuthor Commented:
I found a solution with the help of another forum.

Private Sub CommandButton1_Click()

    lPath = "c:\db1.mdb"
' Eventually, we can delete this code
' Depending on how many things go wrong, there can be left over
' processes even when all databases have been closed.
' just to keep things clean while testing, we kill these leftover msaccess instances

    On Error Resume Next
    Set accapp = GetObject(, "access.application")
    If Err = 0 Then
        Set accapp = Nothing
        GoTo again
    End If
    On Error GoTo 0
'  the above code can be deleted.

    Set accapp = CreateObject("Access.Application")
    accapp.OpenCurrentDatabase lPath

    accapp.Visible = True      ' to avoid run time error 2455
    accapp.usercontrol = True  ' setting usercontrol must come after setting visible

    accapp.Application.Run "startupFromExcel"   ' call a procedure named "startupFromExcel".
    MsgBox "access now works"
End Sub
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now