none
Close database on cancel Ken Getz "fileopensave" module restarts with blank db RRS feed

  • Question

  • Hi Gurus of Access,

    I've written a routine that checks for an updated FE & then restarts the database after replacing with new version. That's all working swell with the testing I've done so far. I'm using the "FileOpenSave" module originally written by Ken Getz to select the FullName path to the new file. However I'm having an issue trying to close the database if the user clicks cancel instead of selecting the new file. This is only required on setup of this new method of FE update (Previously used FEupdater.exe by Peter Hibbs but new Office versions cause havoc).

    If the user clicks cancel I check for some string else try quit. I get error 2046 so I tried forcing it using docmd.closedatabase then quit but what that does is close the database then open access ready to select a db or create a new db.

    I'm clearly missing something or not clearing something. I'm sure all the gurus know Ken's module so will know what I'm referring to. So who knows what I need to do here? Any help much appreciated

    Tuesday, September 1, 2015 1:50 PM

Answers

  • Okay so I seem to have resolved the issue. I moved the whole routine from On Load event to On Open event which fires first & voila! It now behaves as I wanted it to.

    #RM won't you let me know which issues you noticed in the code so I can prevent problems down the line please.

    Wednesday, September 2, 2015 8:40 AM

All replies

  • Hi. Just curious... Do you get the same problem if you use a FileDialog object instead of the FileOpenSave module?
    Tuesday, September 1, 2015 2:53 PM
  • We can't tell what you're actually running into without seeing what your procedure(s) look like.
      It would also be helpful if you told us what version of Access you are working with.
    Tuesday, September 1, 2015 2:56 PM
  • To be honest I've never used the FileDialog. I happened upon the FileOpenSave at some point & have used it ever since. I'll make a copy of the db & test
    Tuesday, September 1, 2015 2:59 PM
  • To be honest I've never used the FileDialog. I happened upon the FileOpenSave at some point & have used it ever since. I'll make a copy of the db & test

    I don't know exactly what error you are getting just by the error code.  What I do know is that it is often related with DoCmd methods.  That said and given what you've explained so far, your problem is not with your common file dialog procedure.  Your problem is happening afterward.  Again, please post your procedure(s) so that we can take a closer look.

    Thanks,

    Tuesday, September 1, 2015 3:06 PM
  • OS is Win7 Pro with O2010 pro

    This is the call to the module I use for any file open / save actions

        strSrcFEpath = ahtCommonFileOpenSave( _
            Filter:=strFilter, FilterIndex:=3, OpenFile:=True, Flags:=lngFlags, _
            DialogTitle:="Please select Front-End Source file...")
    
    where strSrcFEpath is a string. If in the window to select a file the user clicks cancel (for whatever reason) I need to be able to close Access. It seems to shell back to access

    Tuesday, September 1, 2015 3:11 PM
  • OS is Win7 Pro with O2010 pro

    This is the call to the module I use for any file open / save actions

        strSrcFEpath = ahtCommonFileOpenSave( _
            Filter:=strFilter, FilterIndex:=3, OpenFile:=True, Flags:=lngFlags, _
            DialogTitle:="Please select Front-End Source file...")
    where strSrcFEpath is a string. If in the window to select a file the user clicks cancel (for whatever reason) I need to be able to close Access. It seems to shell back to access

    Hugh,

    We can't tell what your problem is with a snipet of code.  Once again, please post your procedures in their entirety to include all variables, local and global.  If it is too much to post, then consider placing your database on OneDrive and providing a link here.

    Another thing, tell us what your error message says for error 2046. 

    I will assume that you have attempted to debug this yourself and have stepped through the code to the point of the error.  Tell us what conclusions you have discovered in your debugging process.




    Tuesday, September 1, 2015 3:17 PM
  • To be honest I've never used the FileDialog. I happened upon the FileOpenSave at some point & have used it ever since. I'll make a copy of the db & test

    Hi. The difference between the module and the FileDialog object is that the former uses API calls, which can sometimes cause issues when used in a 64-bit Office environment. Whereas, the latter uses the same library as the current Office version. Remember, Ken wrote that a while back for old systems. Although it still works today, there are other alternatives now. And you said it yourself, you have been using it for a long time without a problem. So, that tells me there's probably nothing that needs to be changed in it. So, if it's giving you problems now, either modify how you use it, or use one of the alternatives, such as the FileDialog object. Just my 2 cents...
    Tuesday, September 1, 2015 3:22 PM
  • Actually Ken's module is a wrapper for the standard windows file open/save dialog box

    http://access.mvps.org/access/api/api0001.htm
    Tuesday, September 1, 2015 3:25 PM
  • Hi DB Guy,

    As I expressed to Hugh, the problem is not likely to be anything to do with his method for opening the common file dialog.  Note what he says here:

    "If the user clicks cancel I check for some string else try quit. I get error 2046 so I tried forcing it using docmd.closedatabase then quit but what that does is close the database then open access ready to select a db or create a new db."

    Notice his first sentence, after this happens: "If the user clicks cancel..." he then does the following: "...I check for some string else try quit."

    The problem is occurring after he is done with the common dialog.

    Tuesday, September 1, 2015 3:30 PM
  • Hi RM. Thanks for the clarification. You may be right... Cheers!
    Tuesday, September 1, 2015 3:51 PM
  • When the db starts it loads a login form with this code which opens the API file open/save

    Private Sub Form_Load()
    On Error GoTo Form_Load_Err
    
    Dim strFEpath As String, strSrcFEpath As String, strFilter As String, lngFlags As Long
    Dim FEstamp, FEstampSrc
    
    strFEpath = Application.CurrentProject.FullName
        strSrcFEpath = Nz(DLookup("FE_Source_path", "tblVersion"), "")
            FEstamp = Nz(DLookup("NewTimeStamp", "tblVersion"), "")
    
    If Len(Trim(strSrcFEpath) + vbNullString) = 0 Or Len(Trim(FEstamp) + vbNullString) = 0 Then      ' If Source path or FE time stamp field is empty
        If Dir(Application.CurrentProject.FullName & ".txt", vbNormal) <> "" Then
            Open Application.CurrentProject.FullName & ".txt" For Input As #1
                    Input #1, strSrcFEpath
                    Input #1, FEstamp
            Close #1
        
                CurrentDb.Execute "UPDATE tblVersion SET tblVersion.FE_Source_Path = '" & strSrcFEpath & "'", dbFailOnError
                    CurrentDb.Execute "UPDATE tblVersion SET tblVersion.NewTimeStamp = '" & FEstamp & "'", dbFailOnError
                        Kill Application.CurrentProject.FullName & ".txt"
                            Exit Sub
        Else
          strFilter = ahtAddFilterItem(strFilter, "Database Files (*.accdb,*.accdr)", "*.accdb; *.accdr")
            lngFlags = ahtOFN_HIDEREADONLY
      
            strSrcFEpath = ahtCommonFileOpenSave( _
                Filter:=strFilter, FilterIndex:=3, OpenFile:=True, Flags:=lngFlags, _
                    DialogTitle:="Please select Front-End Source file...")
            
            FEstampSrc = FileDateTime(strSrcFEpath)             ' Get time stamp of source file for later comparison
           
                CurrentDb.Execute "UPDATE tblVersion SET tblVersion.FE_Source_Path = '" & strSrcFEpath & "'", dbFailOnError
                    CurrentDb.Execute "UPDATE tblVersion SET tblVersion.NewTimeStamp = '" & FEstampSrc & "'", dbFailOnError
                        dbRestart strSrcFEpath
                            Exit Sub
        End If
    Else

    So if cancel was clicked err53 file not found. This is the error trap routines

    Form_Load_Quit:
        MsgBox "Update Cancelled!!" & vbCrLf & vbCrLf & "Program will now close."
        
    '        Do While Forms.Count > 0
    '            DoCmd.Close acForm, Forms(0).Name
    '        Loop
        
    '    DoCmd.CloseDatabase
    '    DoCmd.Quit acQuitSaveAll
    '    Application.CloseCurrentDatabase
    '    DoCmd.Quit acQuitSaveNone
            Exit Sub
    Form_Load_Err:
        If Err = 53 Then
                    Resume Form_Load_Quit
        Else

    You can see the different attempts to get it to close/quit properly. The error 2046 The command or action Quit isn't available now.

    I've just tried FileDialog method with the same result



    Tuesday, September 1, 2015 4:27 PM
  • I've just tried FileDialog method with the same result

    I guess that confirms what RM was saying about there's nothing wrong with Ken's API code.
    Tuesday, September 1, 2015 5:21 PM
  • You can see the different attempts to get it to close/quit properly. The error 2046 The command or action Quit isn't available now.

    Hi Hugh. I didn't see you try:

    Application.Quit

    Just a thought...

    Tuesday, September 1, 2015 5:24 PM
  • Hugh,

    We seem to be having a communication breakdown here.  Numerous times now, I have asked that you provide your procedure(s) in there entirety.  Furthermore, I requested that you tell us about any debugging steps you have already taken and what conclusions you were able to come up with.  Your post cannot be answered if you don't do what is asked of you and if you don't tell us correctly and precisely what is happening.  As I said before, your problem has nothing to do with Ken's procedure for calling the common dialog.

    Have you even tried to debug this yourself? 

    I already see a number of issues in the code you have provided, but can't assess the problem without seeing everything that is involved.

    What I want to see from you is your entire procedure that is failing and any related procedures.  I also want to know exactly what line your code is failing on.  These are simple requests, please respond accordingly.

    Tuesday, September 1, 2015 7:18 PM
  • Hi there, I did try Application.Quit When that didn't work I just renamed it to DoCmd.Quit I also tried them with the saveall / savenone in both formats with the same issue.
    Tuesday, September 1, 2015 8:27 PM
  • We do at that.

    When the database opens the startup form is the login form whose code is listed above. The code does not go beyond this as the 2 variables being tested in the beginning are null. So that causes the call to the fileopensave common dialog (I've tested with the Office FileDialog & that has the same result) at this line

    strSrcFEpath = ahtCommonFileOpenSave(

    If the user cancels the fileopensave then code returns at this line

    FEstampSrc = FileDateTime(strSrcFEpath) 

    and error 53 occurs hence my trapping for it. I then try quit for obvious reasons. Whether I use DoCmd.Quit or Application.Quit with any of the options (as you can see rem'd out) it gives error 2046 unless I use the DoCmd.CloseDatabase first but that in turn causes Access to restart with what appears to be a blank/new database. The only code that runs not shown here is the actual API

    Tuesday, September 1, 2015 8:41 PM
  • Okay so I seem to have resolved the issue. I moved the whole routine from On Load event to On Open event which fires first & voila! It now behaves as I wanted it to.

    #RM won't you let me know which issues you noticed in the code so I can prevent problems down the line please.

    Wednesday, September 2, 2015 8:40 AM
  • Hi. Glad to hear you got it sorted out. Good luck with your project.
    Wednesday, September 2, 2015 12:25 PM
  • Okay so I seem to have resolved the issue. I moved the whole routine from On Load event to On Open event which fires first & voila! It now behaves as I wanted it to.

    #RM won't you let me know which issues you noticed in the code so I can prevent problems down the line please.

    Hugh,

    I don't know why we have/had such a disconnect.  The code you provided above doesn't appear to be complete.  Your procedure is broken into two halves.  There seems to something missing between and after the second half.

    You also have a procedure called dbRestart that you did not provide any information on.

    Lastly, you never did answer any of my questions to you.

    At your request, I am providing your code back to you with comments regarding noticeable issues.  I have tweaked this code for your benefit.

    Private Sub Form_Open(Cancel As Integer)
        
        On Error GoTo Err_Process
        
        Dim strFilePath1 As String
        Dim strFilePath2 As String
        Dim varTimeStamp As Variant
        'You don't need two variables for timestamp!
        'Dim varTimeStamp2 As Variant
        Dim strFilter As String
        Dim lngFlags As Long
        Dim strSQL As String
        
        'You define and update the following variable but you don't use it at all throughout your procedure, instead you
        'use "Application.CurrentProject.FullName."
        strFilePath1 = Application.CurrentProject.FullName
        
        'Why are you trimming the field value at the time of lookup?  Why not trim values upon insert into the DB?
        strFilePath2 = Nz(Trim(DLookup("FE_Source_path", "tblVersion")), "")
        
        'The following variable is a variant, you don't need to use Nz as it will accept a null value.
        varTimeStamp = DLookup("NewTimeStamp", "tblVersion")
    
        'It is not necessary to check variables with vbNullString.  vbNullString is used for calling external procedures.
        If (Not (strFilePath2 <> "" And IsDate(varTimeStamp))) Then
            'Source path or FE time stamp field are empty
            If (Dir(strFilePath1 & ".txt", vbNormal) <> "") Then
                'This entire block makes no sense and should probably be done away with.  See the following comments.
    
                'Why are you writing to a text file when you only delete it in the next few lines of code?
                Open strFilePath1 & ".txt" For Input As #1
                Input #1, strFilePath2
                Input #1, varTimeStamp
                Close #1
            
                '---------------------------------------------------------------
                'Why are you doing two updates to the table when one will suffice?
                'Why are you updating the table with the same data you just looked up?
    
                'CurrentDb.Execute "UPDATE tblVersion SET tblVersion.FE_Source_Path = '" & strFilePath2 & "'", dbFailOnError
                
                'Why is NewTimeStamp defined as a string and not a date/time data type?
                'CurrentDb.Execute "UPDATE tblVersion SET tblVersion.NewTimeStamp = '" & varTimeStamp & "'", dbFailOnError
                '---------------------------------------------------------------
                
                strSQL = "UPDATE tblVersion " & _
                    "SET tblVersion.FE_Source_Path = '" & strFilePath2 & "', tblVersion.NewTimeStamp = " & varTimeStamp
                
                CurrentDb.Execute strSQL
                
                'Why are you deleting the file you just wrote to?  According to your code, there is nothing being done
                'with this file.
                Kill strFilePath1 & ".txt"
                
                'Exit Sub - Not necessary
            Else
                strFilter = ahtAddFilterItem(strFilter, "Database Files (*.accdb,*.accdr)", "*.accdb; *.accdr")
                lngFlags = ahtOFN_HIDEREADONLY
            
                strFilePath2 = ahtCommonFileOpenSave(Filter:=strFilter, FilterIndex:=3, OpenFile:=True, _
                    Flags:=lngFlags, DialogTitle:="Please select Front-End Source file...")
                  
                'You should check for a valid string instead of allowing an error to occur?
                If (strFilePath2 = "") Then
                    'Do whatever is necessary
                    MsgBox "Update cancelled by user!" & vbCrLf & vbCrLf & "Program will now close.", vbExclamation, "Update Cancelled"
                    DoCmd.Quit
                Else
                    '---------------------------------------------------------------
                    'You should have some type of check to ensure that the user actually selected a valid database
                    'file here.  Just because you set the filter for the common dialog does not prevent them from
                    'selecting something different.
                    '---------------------------------------------------------------
                    
                    
                    ' Get time stamp of source file for later comparison
                    varTimeStamp = FileDateTime(strFilePath2)
                     
                    '---------------------------------------------------------------
                    'Why are you doing two updates to the table when one will suffice?
                    'CurrentDb.Execute "UPDATE tblVersion SET tblVersion.FE_Source_Path = '" & strFilePath2 & "'", dbFailOnError
                    
                    'Same question as above. Why is NewTimeStamp defined as a string and not a date/time data type?
                    'CurrentDb.Execute "UPDATE tblVersion SET tblVersion.NewTimeStamp = '" & varTimeStamp & "'", dbFailOnError
                    '---------------------------------------------------------------
                    
                    strSQL = "UPDATE tblVersion " & _
                        "SET tblVersion.FE_Source_Path = '" & strFilePath2 & "', tblVersion.NewTimeStamp = " & varTimeStamp
                    
                    CurrentDb.Execute strSQL
                    
                    'What does the following procedure do?  Where's the code?
                    'dbRestart strFilePath2
                    
                    'Exit Sub - Not necessary
                End If
            End If
        Else
            'Else what???
        End If
    
    Exit_Process:
        Exit Sub
    
    Err_Process:
        Select Case Err
        Case 53
            'File not found
            'This error should not occur with appropriate checks and management
        Case Else
            'Else what???
            MsgBox "An unexpected error occurred while attempting to update the application." & vbCrLf & Err.Number & " " & Err.Description, vbExclamation, "Error"
        End Select
        Resume Exit_Process
        
    End Sub
    

    • Edited by RunningManHD Wednesday, September 2, 2015 7:36 PM
    Wednesday, September 2, 2015 4:21 PM
  • Hi RM, Thanks for the reply

    dbRestart is a routine which creates a batch file courtesy of Renaud Bompuis which copies the existing FE to .old copies the new FE to local folder with .new ext deletes the local & renames the .new

    The FE has a version table which stores the source path & timestamp of the .new file since the new local copy has no values for path & timestamp only version number. So if the dbRestart function is called then I write the txt file with path & timestamp for comparison. If it exists when the db starts it updates the table with those values (still need to change 2 updates into one) else the dbRetstart is called again since those values are null on form open of the new db. Hence my deleting the file.

    Since the dbRestart function is not called if user cancels the source file selection if wasn't related to the issue I was having.

    The rest of the routine was not shown because it only came into play if there was values in the table & they matched the existing source file or the timestamp was different. My testing was only at the point of both values being null so the rest of the routine wasn't run. Only the call to the API & the error on return.

    Since it's working now I need to clean up the code & remove unnecessary rem'd lines etc

    I can't recall who exactly it is that has a slogan "build a little test a little" & I find it easier to debug that way than to have a bunch of code completed then try debugging.

    Thanks for looking at my issue. Much appreciated

    Thursday, September 3, 2015 6:33 AM