File Picker error when user double clicks instead of pressing "OPEN"

Posted on 2006-06-29
Last Modified: 2008-01-09
I have code (shown below) which allows user to select a data file which is then established as the "be" file for the main application.  Works fine if user chooses file name and then clicks "OPEN".  However, if user double clicks file name, the function completes but leaves the "be" file in an unreadable state.  I then have to open the "be" file directly and let Access correct it.  I can't seem to trap the error.
Option Compare Database
Option Explicit
Public Function RetrieveArchive()
    ' Comments  :This function allows the user to select a 'specific' "_be.mdb" (data) file
                'and then copies that file to "C:\Program Files\SuperAudits"
    ' Parameters:
    ' Returns   :  -
    ' Created   : 01/06/06 09:07 RRS
    ' Modified  :
    ' --------------------------------------------------

On Error GoTo PROC_ERR
Dim strFilter As String
Dim strInputFileName As String
Dim strDestFileName As String
Dim vrtSelectedItem As Variant
Dim strFileName As Variant
Dim fs As FileSystemObject
Dim strSTATUS As String
Dim varSTATUS As Boolean
Dim vtext As String
Dim stdocname As String

DoCmd.Hourglass True
strSTATUS = "Retrieving Data, Please be patient"
varSTATUS = SysCmd(acSysCmdSetStatus, strSTATUS)
'initiate file dialog and allow user to choose
strFilter = ahtAddFilterItem(strFilter, "MDB Files (*.MDB)", "*.MDB")
strInputFileName = ahtCommonFileOpenSave( _
                InitialDir:="C:\SuperAudits", _
                Filter:=strFilter, OpenFile:=True, _
                DialogTitle:="Please select an input file...", _
'If user cancelled or closed the file dialog without picking a file,
'need to get out gracefully
If strInputFileName = "" Then
  MsgBox "You chose to cancel the file selection." & vbCr & _
        "If you wish to retrieve an archived data file, choose the file name and click OPEN."
  stdocname = "frm Selection Criteria"
  DoCmd.OpenForm stdocname, acNormal
End If

'define where to put the chosen file
strDestFileName = "c:\Program Files\SuperAudits\superaudits_be.mdb"

'copy the file to the chosen location
Set fs = CreateObject("Scripting.FileSystemObject")
 fs.CopyFile strInputFileName, "c:\Program Files\SuperAudits\", True
'tell the user it worked
varSTATUS = SysCmd(acSysCmdClearStatus)
DoCmd.OpenForm "frmOpenMain", acNormal

    DoCmd.Hourglass False
    Exit Function
    MsgBox Err.Description
    Resume PROC_EXIT
End Function
Question by:Shelnutt
  • 2
  • 2
LVL 84
ID: 17013236
I would suspect that the code is attempting to open the file in question, instead of merely returning the filename .... this is a function of the filepicker, and you'll be hard pressed to trap the error (since there really is no error from your code, but instead a system error from MS Access).

You could instead try training the user - add a string to the DialogTitle: "SINGLE CLICK ONLY" ... not sure this would work, of course, since most users ignore the title ...

Perhaps a combination of various flags would help - the ahtCommonFile routine has many OFN flags which you can OR together:

.Flags=ahtOFN_HIDEREADONLY OR ahtOFN_PATHMUSTEXIST for example ... those wouldn't work, of course, but perhaps there is a combination which would tell the dialog to only return the filename and to NOT attempt to open/run the selected file ...

Author Comment

ID: 17017293
Thank you for your quick response.  I will take a look at flags.  You are right, training users is really difficult.  That's why I am trying to trap all the ID10T errors.

Author Comment

ID: 17018180
I believe I have solved the problem.  I am posting this for others (you never know who else might hit a snag).  I set the "OpenFile:" flag to False and the code seems to work with either a double click or using the "SAVE" button.  The only drawback (and I'm not sure it is) is that the dialog now says SAVE instead of OPEN.

Points should be awarded to LSMConsulting since they started me on the path to a resolution.
LVL 84

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 125 total points
ID: 17019675
Hi Shelnutt,

Glad you got your problem solved. I wouldn't be too worried about what the file dialog button says, most people don't really pay much attention to it ... you can always hook the dialog and have the button say whatever you want, but this is quite troublesome and can lead to memory leaks and such.

You have to award the points. See here for information:

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

932 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now