Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

FILE BROWSER IN MSACCESS

Introduction


SEARCHING FOR OTHER FILES FROM MSACCESS

We can search for Microsoft Access database files on disk by selecting File - - > Open option from the main menu.  But, if we want to search, select and open some other file from disk then how do we do that?  If we are able to do that then what do we do with those File(s) in Access? Well, we can create a Hyperlink to the file and store it in a data field or use FileCopy() function to make a copy of it into a different location.



Demo Run Preview

Anyway let us get to work with the first part. But before that, a preview of the Run of our Project is shown below:


Designing a Demo Form

open a Database from your Computer or from the Network Drive.

Let us design a small Form with the Common Dialog Control, a Textbox and a Command Button with few lines of VBA Code for our project.  The design will look like the image given below. 

Tip: You can download a demo database from the bottom of this page.

The rectangular shaped object at the left side is the Common Dialog control inserted from the ActiveX control’s group. when the User clicks on the Browse. . . button the dialog box like the first image above opens up.

Now let us get to work.

  1. Open a new Form and Create a Text-Box Control, wide enough to hold the Path and File name selected from the disk.
    • Change the Caption of the child-label to File Path Name.
    • Select the Textbox control, display the property sheet (F4) and change the Name property value to lbldb.
  2. Create a Command Button as shown in the above Design, display its Property Sheet and change the following property values as shown below:
    • Name = cmdBrowse
    • Caption = Browse. . .
  3. Now it is time to bring in the real hero element of our design: the Microsoft Common Dialog Control, to do that follow the procedure given below:
    • Select ActiveX Control from Insert Menu
    • You will find a List of ActiveX Controls opens up, scroll down and select the Microsoft Common Dialog Control and Click OK. If you didn't met any trouble on the way out after clicking the OK button you will find a square shaped control sitting on your Form. If your MS-Office installation is not properly done it is likely that you may end with a message like 'this ActiveX DLL is not registered, re-install it' or something similar.

    Display the Property Sheet of the Common Dialog Control and change the Name property to cmDialog1. You can place it anywhere at your convenience, it will not be visible when you activate your Form.

  4. Click on the Command Button to select it.
  5. Display the Property Sheet (F4).
  6. Click on the On Click Event property and select [Event Procedure] from the drop-down control.
  7. Click on the build (. . .) button to open the VBA Module Window of the form.

    The VBA Code

  8. Copy the Following Visual Basic Code and paste it, over-writing the existing empty procedure lines, into the Module and save the Form.
Private Sub cmdBrowse_Click()
Dim VFile As String
On Error GoTo cmdBrowse_Click_Err
ChDrive ("C") 
ChDir ("C:\")
  cmDialog1.Filter = "All Files (*.*)|*.*| _ Text Files (*.txt)|*.txt|Excel WorkBooks (*.xls)|*.xls"  cmDialog1.FilterIndex = 1
  cmDialog1.Action = 1
  If cmDialog1.FileName =  "" Then
  VFile = cmDialog1.FileName 
  Me!lbldb = VFile
  End If  
  cmdBrowse_Click_Exit:
  Exit Sub
  cmdBrowse_Click_Err:
  MsgBox Err.Description, , "cmdBrowse_Click"
  Resume cmdBrowse_Click_Exit
End Sub 

Test Run

Open the Form in normal view and click on the Browse Button. The File Browsing Control, that we have seen on the top of this page, will open up. Select a file from any location you like and click Open. The selected file with its complete location address will be inserted into the Text Box control.



Download



Download Demo Database



Share:

12 comments:

  1. I know you posted this some 3 years ago but a client of mine needed maintenance in an Access application and this saved me a lot of time. Thank you for the tutorial and the SIMPLE way of making this work. Other tutorials were huge.

    Thanks again!

    ReplyDelete
  2. I like the form but it doesnt work, it gives me the error message" object doesnt support this property or method"
    thanks.

    ReplyDelete
  3. You may try the following Code, if you are using MS-Access 2003. This sample code you can find on the Code Module of Employees Form in NorthWind.mdb database. The Program is run from the Add/Remove Button click on the Form.

    Private Sub AddPicture_Click()
    ' Use the Office File Open dialog to get a file name to use
    ' as an employee picture.
    getFileName
    End Sub




    Sub getFileName()
    ' Displays the Office File Open dialog to choose a file name
    ' for the current employee record. If the user selects a file
    ' display it in the image control.
    Dim fileName As String
    Dim result As Integer
    With Application.FileDialog(msoFileDialogFilePicker)
    .Title = "Select Employee Picture"
    .Filters.Add "All Files", "*.*"
    .Filters.Add "JPEGs", "*.jpg"
    .Filters.Add "Bitmaps", "*.bmp"
    .FilterIndex = 3
    .AllowMultiSelect = False
    .InitialFileName = CurrentProject.path
    result = .Show
    If (result <> 0) Then
    fileName = Trim(.SelectedItems.Item(1))
    Me![ImagePath].Visible = True
    Me![ImagePath].SetFocus
    Me![ImagePath].Text = fileName
    Me![FirstName].SetFocus
    Me![ImagePath].Visible = False
    End If
    End With
    End Sub

    You may modify it to suit to your requirement.

    Regards,

    ReplyDelete
  4. Hi - could you add a demo database of your browser lesson to your site? Having a demo version to compare to my own would be really useful!! Thanks.

    ReplyDelete
  5. Hi,

    Thanks a lot!

    The MS-Access 2002 example database contains the same code as the MS-Access 2003's. To find said database: http://support.microsoft.com/?scid=kb%3Ben-us%3B276376&x;=20&y;=10

    Bye !

    ReplyDelete
  6. The Northwind example for Access 2003 is missing an operand. These lines:
    If (result
    0) Then
    should actually be
    If (result <> 0) Then
    It was missing the not equal. It works great after that. Thanks.

    ReplyDelete
  7. The Code is corrected. Thanks for pointing out the Error.

    ReplyDelete
  8. I really liked this post, this one is going straight into my stumble upon akun :)

    ReplyDelete
  9. I want to thank the blogger very much not only for this post but also for his all previous efforts. I found thesimpleyoga.com to be greatly interesting. I will be coming back to thesimpleyoga.com for more information.

    ReplyDelete
  10. Bookmarked your website. Thank you for sharing. Definitely worth the time away from my workload.

    ReplyDelete
  11. I just wanted to comment your blog and say that I really enjoyed reading your blog post here. It was very informative and I also digg the way you write! Keep it up and I’ll be back to read more in the future

    ReplyDelete

Comments are subject to moderation before publishing.

Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

http://clicky.com/101199826
Blog Directory Visit blogadda.com to discover Indian blogs

Search

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email

Labels

Forms How Tos Functions MS-Access Security Reports msaccess forms Animations msaccess animation Class Module Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Array Queries msaccess reports External Links msaccess tips Accesstips Downloads Objects Property Menus and Toolbars Controls Event MsaccessLinks Process Controls Art Work Collection Object WithEvents msaccess How Tos Combo Boxes Graph Charts List Boxes VBA msaccessQuery Command Buttons Command Button Data Dictionary Object Emails and Alerts Form Query RaiseEvent Report Calculation Custom Wizards DOS Commands Data Type ms-access functions msaccess functions msaccess graphs msaccess reporttricks Item Object Reference msaccessprocess security advanced Access Security Add Custom Functions Field Type Fields Form Instances Key Macros Menus SubForm Top Values Variables msaccess email msaccess menus progressmeter Access2007 Auto-Number Copy Expression Join Methods Microsoft Numbering System Records Recordset Security Split Table Time Difference Utility Workgroup Wrapper Classes database ms-access msaccess wizards reference text tutorial vba code Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Conditional Formatting Data Filtering Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Tables Union Query User Users Water-Mark Word automatically commands function hyperlinks iSeries Date iif msaccess msaccess alerts pdf files restore switch toolbar updating upload

Featured Post

WithEvents and All Form Control Types

Introduction So far we have worked extensively with only one type of Control: the TextBoxes on Form, enabled with Events to Raise and captu...

Labels

Blog Archive

Recent Posts