Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Running Code as Program Closes

Posted on 2006-03-22
17
Medium Priority
?
366 Views
Last Modified: 2011-10-03
My objective is to close the MDE so it can be renamed.  Here is what I've got.

------------
Private Sub butUpdate_Click()
Dim DirPath As String, RunFS
UpdateNow = 1
DoCmd.Close acForm, "Options"      'Form the button is on
FltStaUpdate
Application.Quit
End Sub

'Download Update file
Sub FSUpdate()

   Dim dldata As FileRegistryDownloadData
   With dldata
      .DownloadDlgTitle = "Flight Status Manager System Update"
      .DownloadRemoteFileUrl = "http://www.FltStatus.com/FltStaUpdate.exe"
      .DownloadTempRegKey = "C:\Program Files\FltStatus"
      .DownloadLocalFileName = .DownloadTempRegKey & "\" & "FltStaUpdate.zip"
      If DownloadRemoteFile(dldata) = True Then
          MsgBox "Download success!"
      Else
          MsgBox "Download failed or user pressed Cancel"
      End If
   
   End With
End Sub

'Here is the trouble.  Work fine in a Sub
Private Sub Form_Close()
If UpdateNow = 1 Then
    Dim DirPath As String, strDB
    Dim objAccess As Access.Application
    Set objAccess = New Access.Application
    DirPath = CurrentProject.Path
    strDB = DirPath & "\FSUpdate.mde"
    'objAccess.Visible = True
    objAccess.OpenCurrentDatabase strDB
End If
End Sub

---------------------------------------
The FSUpate.mde that is opened, rename the current MDE.  But I can not get it to work using the OnClose method in my main form.  My code in OnClose does not run on application.quit
0
Comment
Question by:buzzcarter
  • 6
  • 4
  • 4
  • +2
17 Comments
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16263193
Is UpdateNow a global variable?
Or just local to your form?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 16263228

try this revision

Private Sub Form_Close()
If UpdateNow = 1 Then
    Dim DirPath As String, strDB

    Dim db As DAO.Database

    Dim objAccess As  New Access.Application

    DirPath = CurrentProject.Path
    strDB = DirPath & "\FSUpdate.mde"

    objAccess.Visible = True

    Set db = objAcess.DBEngine.OpenDatabase(strDB)
                 objAccess.OpenCurrentDatabase(strDB)


End If
End Sub
0
 

Author Comment

by:buzzcarter
ID: 16263255
Public UpdateNow
in module
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 16263264
oops

    Set db = objAcess.DBEngine.OpenDatabase(strDB)
should  be

    Set db = objAccess.DBEngine.OpenDatabase(strDB)
0
 

Author Comment

by:buzzcarter
ID: 16263409
Capricorn1

No Joy.  Same failure to excute.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 16263415
buzzcarter, can you be more specific.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16263432
Have you stepped through the code?  Is the code in that event running?
You say the same code works in a different event - but not on the form close?
0
 
LVL 58

Expert Comment

by:harfang
ID: 16263508
Hello,

Apparently, you want to open a new instance of Access while closing the current one. Your code will not work, because when you exit your application, all variables are reset, which will also close the automated version of Access that you are opening.
To avoid this default (and very reasonable) behavior, you will need to add this line:

    objAccess.UserControl = True

This tells the new copy that is is no longer controlled programatically through a VB variable but by the user.

Good Luck
(°v°)
0
 

Author Comment

by:buzzcarter
ID: 16263514
LPurvis
 
On Stepping through the code runs until application.quit and the form close does not run.

I have tested the same code in a Sub Test() module adn it excutes correctly.

0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16263541
Markus has nailed it.

(And my forehead is still red and throbbing).
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 16263543

place the sub before Application.quit
0
 

Author Comment

by:buzzcarter
ID: 16263666
harfang

You are correct in I want to open a new instance of Access while closing the current one.

I though the the Form Close ran just before the access application ended.  Where should I place the code and the addition objAccess.UserControl = True?

Or maybe another solution, is there a way to check to see if the first.mde is running?  I could open the second.mde and not let the code excute in the second.mde until the first.mde had closed.
0
 
LVL 39

Expert Comment

by:thenelson
ID: 16263669
Does it work when you close the form?  If so, then the form needs to be open when the database closes.  Best to create a form that opens hidden when the database opens and remains open until the database closes.  

BTW: you can create a batch file to wait then rename the file and call the batch file when the database closes:
Private Sub Form_Close()
Shell WaitRename.Bat
End Sub

WaitRename.Bat:

REM Wait 30 seconds for the database to close (60 seconds = 60000, 120 seconds = 120000, etc.)
PING 1.1.1.1 -n 1 -w 30000 >NUL
Rem Rename
Ren OldPathName NewName
0
 
LVL 44

Assisted Solution

by:Leigh Purvis
Leigh Purvis earned 800 total points
ID: 16263717
I think you've missed the point slightly buzzcarter.

Markus is saying that when your application closes the objects you've created are destroyed.
Since it is the only pointer to it (i.e. there's been no user intervention in the created instance) the new application instance will be destroyed too.
(It is indeed very reasonable behaviour - and more importantly, consistent).

The code he suggested allows the new instance to become under user control (i.e. won't be destroyed when your code pointer to it is removed).

Your app then closes - the other mde does what it needs to do.
0
 

Author Comment

by:buzzcarter
ID: 16263740
OK, I'm catching on.  

I removed the varible and the If statement as harfang point out and the Form Close now excutes.  But I still need a conditional statement.  I will try thenelson's suggestion and create a hidden form and open it on the condition, using the form close there.
0
 
LVL 58

Accepted Solution

by:
harfang earned 1200 total points
ID: 16263850
This is my test module which I created before answering:

Private Sub Form_Close()

    If chkRestart Then
        'MsgBox "closing"
        With New Access.Application
            .Visible = True
            .OpenCurrentDatabase CurrentDb.Name
            .UserControl = True
            .DoCmd.OpenForm Me.Name
        End With
    End If
   
End Sub

chkRestart is a check box, but it could be a boolean variable (your "UpdateNow"). To get your behaviour, I guess you would replace CurrentDb.Name with:

    ... CurrentProject.Path & "\FSUpdate.mde"

I don't think I mentioned anything about the if statement...

Good Luck!
(°v°)
0
 

Author Comment

by:buzzcarter
ID: 16264694
Thanks to All! I learned from everyone.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

810 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