?
Solved

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

Posted on 2009-04-21
7
Medium Priority
?
301 Views
Last Modified: 2012-08-14
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.
 






0
Comment
Question by:rberke
  • 4
  • 3
7 Comments
 
LVL 4

Expert Comment

by:newbieal
ID: 24200459
Have you tried, Application.DisplayAlerts = True?

Here is more info:
http://www.developerfusion.com/code/1130/turn-off-warning-messages-in-ms-office-applications/

Hope this helps.
0
 
LVL 5

Author Comment

by:rberke
ID: 24200822
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.

0
 
LVL 4

Expert Comment

by:newbieal
ID: 24200910
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?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 5

Author Comment

by:rberke
ID: 24201190
"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.

db1test.xls
db1.mdb
0
 
LVL 5

Author Comment

by:rberke
ID: 24201247
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
0
 
LVL 4

Expert Comment

by:newbieal
ID: 24204521
Thanks for attaching the db.  I will do some testing and see what I can come up with.
0
 
LVL 5

Accepted Solution

by:
rberke earned 0 total points
ID: 24244735
I found a solution with the help of another forum.

Private Sub CommandButton1_Click()

    lPath = "c:\db1.mdb"
again:
' 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
        accapp.Quit
        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
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question