Running Code as Program Closes

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
buzzcarterAsked:
Who is Participating?
 
harfangCommented:
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
 
Leigh PurvisDatabase DeveloperCommented:
Is UpdateNow a global variable?
Or just local to your form?
0
 
Rey Obrero (Capricorn1)Commented:

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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
buzzcarterAuthor Commented:
Public UpdateNow
in module
0
 
Rey Obrero (Capricorn1)Commented:
oops

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

    Set db = objAccess.DBEngine.OpenDatabase(strDB)
0
 
buzzcarterAuthor Commented:
Capricorn1

No Joy.  Same failure to excute.
0
 
Rey Obrero (Capricorn1)Commented:
buzzcarter, can you be more specific.
0
 
Leigh PurvisDatabase DeveloperCommented:
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
 
harfangCommented:
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
 
buzzcarterAuthor Commented:
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
 
Leigh PurvisDatabase DeveloperCommented:
Markus has nailed it.

(And my forehead is still red and throbbing).
0
 
Rey Obrero (Capricorn1)Commented:

place the sub before Application.quit
0
 
buzzcarterAuthor Commented:
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
 
thenelsonCommented:
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
 
Leigh PurvisDatabase DeveloperCommented:
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
 
buzzcarterAuthor Commented:
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
 
buzzcarterAuthor Commented:
Thanks to All! I learned from everyone.
0
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.

All Courses

From novice to tech pro — start learning today.