If you are interested, you can find my earlier posts on finding the median, the mode, the geometric and harmonic means, ranking every row in a query, selecting random rows out of a table, calculating running sums and averages, calculating running differences, creating histograms, calculating probability masses out of given data, calculating cumulative distributions out of given data, finding percentile scores, percentile values, calculating distinct counts, full outer joins, parameter queries, crosstab queries, working with system objects, listing table fields, finding unmatched rows, calculating statistics with grouping, job-candidate matching, job-candidate matching with skill levels, great circle distances, great circle initial headings, and using Excel functions in Access.
One of the uses for the file-picker is a database of things like songs on your computer. I have an Access database of eBooks on my computer. You store the file name of each song or eBook along with details about that file (in the case of eBooks, I store the title, the author, the publisher, the publication date, the number of pages, etc.) in the database. All the fields are searchable, so I can quickly find any eBook I am looking for in my collection of over 2000 eBooks. Towards the end of the post, I will also show you how you can get Access to open up a given eBook based on the file name stored in the database once you have searched for and located it (so that you don't have to then go to the directory where it is stored and double-click on the file or anything so laborious).
Your situation can and will probably be totally different. But here is how I use the file-picker in my eBook database. To store the file name (along with its path) in the database, I created a text field for it of sufficient length. I then created a form with a control bound to this text field, and added a button next to it that I would click to invoke the file-picker. The file I picked in the file-picker would then be populated in the control (and because it is a bound control, in the table). The structure of my form is presented in the figure to the left.
The actual code behind the button is as below:
Private Sub CmdBookLocationPicker_Click()The important lines to note in this snippet of code are explained below:
Dim fd As FileDialog
Dim strFolderPath As String
Dim DefaultPath As String
If IsNull(Me.TxtBookLocation) Then
DefaultPath = CurrentProject.Path
DefaultPath = ExtractPathname(Replace(Me.TxtBookLocation, ".\", CurrentProject.Path & "\"))
'Display the file dialog box and allow the user to select the location to save the file.
Set fd = Application.FileDialog(msoFileDialogFilePicker)
.InitialView = msoFileDialogViewDetails
.AllowMultiSelect = False
.InitialFileName = DefaultPath
.Title = "Pick An Ebook"
If .Show Then
Me.TxtBookLocation = Replace(fd.selecteditems(1), CurrentProject.Path, ".")
If IsNull(Me.TxtBookLocation) Then
Me.CmdRead.Enabled = False
Me.CmdRead.Enabled = True
Declaring a variable of type fileDialog. In the above code it is a variable named fd. Note that you can select fileDialog as a valid type only if you have set a reference to the Microsoft Office 11.0 Object Library in your references (if you don't have access to the Microsoft Office 11.0 Object Library, check for higher versions of the Microsoft Office Object Library). If you are not sure how to set references, please refer to the post on using Excel functions in Access for details.
Setting the default path for the file-picker to start at. If the control already has a file name in it (which it would have if I was editing a current record), the default path would point to it. Otherwise, the default path would be the current location of the database (which is obtained by the use of "CurrentProject.Path").
Note that I store the final path to the eBook file as a relative path with respect to the location of the database itself, with the folder where the database is present being represented by ".\". This is very useful for portability since I can put this database along with the eBooks it contains in any directory structure on any computer and it would still work. If I stored absolute paths, then the system would not work even if I moved my entire database from C: on my computer to D: on another computer, for instance. So, if I already have a file name in the control, I replace the ".\" with the folder in which the database is located using the replace function.
Open the file-picker. This is obviously the most important step, and is accomplished by the line "Set fd = Application.FileDialog(msoFileDialogFilePicker)". This actually opens the windows file-picker. I then set various options for it in the next few lines. The important options are the view (details, tiles, large icons, small icons, thumbnails, etc.), whether or not multi-select is allowed (in this case, since I am storing a single file name in my database, I do not allow multi-select), the initial path where it should open up (you don't want the file-picker to constantly open up at My Documents or C:\, forcing you to navigate to where you want to go. Instead set the default path in advance so that the file-picker opens up near where you want to navigate to), and a title for the window (in this case, since I am picking the name of an eBook, the title I have chosen is "Pick an Ebook", but you should choose something appropriate depending on your application).
Note that the file-picker that pops up has the same properties you would expect out of any windows file-picker dialog. You can pick a file and hit OK, or double-click on a file name to pick it, and so on. You can navigate the directory structure on your computer freely from the default location and pick any file that is visible to the file-picker. You will also notice that the file-picker has several default locations in the left tab such as "My Computer", "Desktop", etc. So, this is a full-featured file-picker that will make your program look as professional as any other high-priced program like Microsoft Office!
Once I pick a file in the file-picker, I put that file (along with its path, converted to a relative path by replacing the current directory with ".\") in the control that holds the file name. This is accomplished by the line right after "if .show". I use fd.selecteditems(1) to pick out the first (and in my case, only, since I don't allow multi-select) selection and put it in the control on the form. If you do allow multi-select, you have to loop over the selecteditems list of fd and figure out how to deal with all the items it contains.
The if condition (if .show) is very important to include in the code. The file dialog's .show property is set to true when the user has picked a file and is set to false when the user hits cancel on the file-picker. So, you can access the selecteditems list only when .show is true, hence the if condition there.
Now, before I conclude this post, I will give you a snippet of code that will allow you to open a file from within Access. The code uses a function called FollowHyperlink(). It has several options, as the help file indicates, but it can be used simply as below to open the file passed as an argument to it:
application.followhyperlink(string containing file name)If your computer already knows how to handle files with the given extension, then the appropriate application will be launched and the file will be opened in that application. That is all there is to it! You don't have to know which application to open the file in. As long as the file type is registered on your computer, and your computer knows which application to open the file in, followhyperlink() will work flawlessly.
The code above has been checked in Access 2003. It will probably work in any version of Access from Access 97 on up, but please do let me know through the comments if you have problems using the code.
Hope this post has been helpful in solving any problems you might have had with using a file-picker in Access. If you have any problems or concerns with the VBA code in this lesson, please feel free to let me know by posting a comment. If you have other questions on Access that you would like me to address in future lessons, please feel free to let me know through your comments too. Good luck!