Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Synchronized Floating popup Form


This Form is designed specifically for inquiry purposes (not for Data Entry). The Source Data Table has several fields and the information in them can be categorized into different groups for viewing.

For example, the Employees Form, in the Northwind.mdb sample database, has been divided into two parts, viz. Company Information and Personal Information. The Company Info part is designed on the First Page of a Tab Control and the second part Personal Info category of fields are placed on the second page of the Tab Control and stays hidden till it receives a click on the second page of the Tab Control, to bring the data into view. Let us assume that the Company Info is the most frequently viewed or updated information and it is kept in full view and Personal Info kept behind because it is not so often viewed or updated.

We will design them differently with an interesting trick involving two separate stand-alone Forms without linking them as Main Form and Sub-Form. Company Info on one Form and Personal Info on a separate Form but both will have source data from the Employees table.

Let us name them as EmployeeMain and EmployeeSub Forms. They will remain as two independent Forms. A sample image of both Forms in running mode is given below:

The Trick Designs Plan

The trick is that when we open the EmployeeMain Form it will show only the Company Info alone (no trick here). We will move a few records forward using the Record Navigation Button and at this point, we would like to see the Personal Info part of the current record. We will click on a Command Button to open the EmployeeSub Form with the Personal Info of the current record on the EmployeeMain Form. From this point onwards the records on both Forms move forward/back synchronized, when you move to Next or Previous records on the EmployeeMain Form, even though they are two separate Forms.

The EmployeeSub Form is defined as a Pop-up Form (or its Pop up Property value is set to Yes) to float it above the EmployeeMain Form or any other Form open in the Application Window, provided if you don't open another Pop-up Form. If you don't want the EmployeeSub Form to stay on you may close it and open it again when needed using the Personal Info Command Button on the EmployeeMain Form. When you close the EmployeeMain Form this action will close the EmployeeSub Form too if it is running.

  1. We can very easily design these two Forms by importing the Employees Table and Employees Form from C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb Database. If you have not used these Objects so far for any other examples given on this site you may import them into your database now.
  2. Open the Employees Form in Design View.
  3. Click on the Form and drag the mouse over all Controls on the Company Info Tab except the Photo (if you want it you may select that too) and the Command Button controls.
  4. Select Copy from Edit Menu.
  5. Click on the Employees Table and select Form from Insert menu and select Design View from the list of options displayed, to create a new Form.
  6. Click on the Detail Section of the Form and select Paste from Edit Menu to place the copied fields of Employees Form on to the new Form.
  7. If the Form Header/Footer Sections of the Form are not visible then select Form Header/Footer from View Menu.
  8. Copy and Paste the Text Control with the employee name expression from the Header Section of the Employees Form to the Header Section of your Form. Change the Font Color to Red or some other Color you like.
  9. Create a Command Button in the Detail Section below the data fields.
  10. Display its Property Sheet (View- -> Properties).
  11. Change the Name Property Value to cmdPersonalInfo and the Caption Property Value to Personal Info.

    NB: You must be careful with the names of Forms and Controls I suggest here because they are used in the Programs. If you give them differently the trick may not work as expected after completion.

  12. Create a Command Button at the Footer Section of the Form.
  13. Change the Name Property value to cmdClose and the Caption Property Value to Close.
  14. Display the Form's Property Sheet. Click on the left top corner of the Form where a black rectangle is shown, at the intersection of the horizontal and vertical design guide (scales) meet, to select the Form's Property Sheet, if it is not the current one.
  15. Change the Caption Property Value to Company Info.
  16. Display the VBA Code Module of the Form (Alt+F11). Copy and Paste the following code into the Code Module.

    The Form Module Code

    Option Compare Database
    Option Explicit
    Dim strSQL As String
    Private Sub cmdClose_Click()
    End Sub
    Private Sub cmdPersonalInfo_Click()
    If IsLoaded("EmployeesSub") Then
       strSQL = "SELECT Employees.* FROM Employees "
       strSQL = strSQL & "WHERE ([EmployeeID] = " & Me![EmployeeID] & ");"
       Forms("EmployeeSub").RecordSource = strSQL
       DoCmd.SelectObject acForm, "EmployeeSub", False
       DoCmd.OpenForm "EmployeeSub", acNormal, , "[Employeeid] = " & Me![EmployeeID], acFormReadOnly, acWindowNormal
    End If
    End Sub
    Private Sub Form_Close()
        DoCmd.Close acForm, "EmployeeSub"
    End Sub
    Private Sub Form_Current()
    If IsLoaded("EmployeeSub") Then
       strSQL = "SELECT Employees.* FROM Employees "
       strSQL = strSQL & "WHERE ([EmployeeID] = " & Me![EmployeeID] & ");"
       Forms("EmployeeSub").RecordSource = strSQL
       DoCmd.SelectObject acForm, "EmployeeSub", False
    End If
    End Sub
    Private Sub Form_Open(Cancel As Integer)
    End Sub
  17. Save the Form with the name EmployeeMain.
  18. Repeat the process from Step-3 to Step-8 for transferring information from the Personal Info Tab of the Employees Form to a new Form.
  19. Display the Form Header/Footer Section of the Form (View - ->Form Header/Footer).
  20. Create a Command Button and change the Name Property Value to cmdClose and the Caption Property Value to Close.
  21. Display the Form's Property Sheet.
  22. Change the following Property Values as given below:
    • Caption = Personal Info
    • Default View = Single Form
    • Allow Additions = No
    • Allow Deletions = No
    • Data Entry = No
    • Record Selectors = No
    • Navigation Buttons = No
    • Dividing Lines = No
    • Auto Resize = Yes
    • Pop Up = Yes
    • Border Style = Dialog
    • Allow Design Changes = Design View Only.
  23. Display the Code Module of the Form (Alt+F11).
  24. Copy and paste the following code into the Code Module.
    Private Sub cmdClose_Click()
            DoCmd.Close acForm, Me.Name
    End Sub
  25. Save the Form with the name EmployeeSub.

    We need a small program to check whether the EmployeeSub Form is in an Open state or not before attempting to refresh its source data and bring it into a visible state.

    The Standard Module Code

  26. Copy and Paste the following Code into a Global Module (Standard Module) and save the Module:
Public Function IsLoaded(ByVal strFormName As String) As Boolean
Dim j As Integer

On Error GoTo IsLoaded_Err

IsLoaded = False
For j = 0 To Forms.Count - 1
    If Forms(j).Name = strFormName Then
       IsLoaded = True
       Exit For
    End If

Exit Function

IsLoaded = False
Resume IsLoaded_Exit
End Function

The Demo Run

  1. To try out your creation, open the EmployeeMain Form, click on the Record Navigation Control to advance few records forward.
  2. Click on the Personal Info Command Button. The EmployeeSub Form will open up showing Personal Information pertains to the same employee on the main Form. Check the Names of the Employee appearing on top of both forms.
  3. Now, try advancing records on the main form forward or back with the record navigation control. You will see that the corresponding personal information on the EmployeeSub Form is also moving along with the records on the main form.
  4. If you close the EmployeeMain Form while the EmployeeSub Form is open both will be closed.

Download Demo Database

You may download the sample database from the Download Link given below and give it a try before you design one of your own to understand the trick:

Download Demo SynchronizedForm2k.zip


Forms and Custom Properties


Searching and finding a record in a Form is easy with Edit - -> Find (Ctrl + F) Option on a particular field value. But, this will fetch only the first record even if there are more records matching the same search text. Most of the time we need to find records that match values in more than one field, like records of Sales Representatives of Northwind Traders located in the City of London.

We will create a simple method to find all records of Employees Table that match both fields (City and Title) and display them. If you have not imported the Employees sample Table from C:\Program Files\Microsoft Office\Office\Samples\NorthWind.mdb before then you may do it now.

  1. Create a temporary table with the name temp_param with two text fields; City and Title.
  2. Add a single record with City field value as London and Title field value as Sales Representative.
  3. Design the Main Form with this Table and place both Fields on the Header Section of the Form.

    Even better, if you create two Combo Boxes (instead of placing the above fields directly) using values from City and Title fields from Employees Table. Create two Select Queries; by grouping values on these fields and use them as the source for the Combo Boxes. Do that by following the steps given below.

  4. Query Name: cboCityQ
    SELECT Employees.City
    FROM Employees
    GROUP BY Employees.City;

    Copy and paste the above SQL String into a new Query's SQL editing window and save it with the name cboCityQ. Create a second Query with SQL string given below and save the Query with the name cboTitleQ.

  5. Query Name: cboTitleQ
    SELECT Employees.Title
    FROM Employees
    GROUP BY Employees.Title;
  6. Create a Combo-Box in the Header Section of the Main Form using cboCityQ as source data and select City as Control Source.
  7. Create another Combo-Box using cboTitleQ as source data and select Title as Control Source.
  8. Name the City field Combo-Box as cboCity and Title field Combo-Box as cboTitle.
  9. Design a Datasheet Form on the Employees Table and save the Form with the name Employees_Sub.
  10. Insert the Employees_Sub Form as Sub-Form in the Detail Section of the Main Form.
  11. Click on the Sub-Form, display the Property Sheet (View- ->Properties), and set the following Property values as shown below:

    Link Child Fields = City; Title

    Link Master Fields = cboCity;cboTitle

  12. Now you can select City and Title values from the Combo-Boxes and all the matching records will immediately show up in the Datasheet Sub-Form.

Database Sharing Issues

If it is a single User database (or given Exclusive Access to a single User on Network) then the above method works fine and will have no issues. But, it is different when the database is shared on a Network. Even though different instance of the Main Form is used by individual User, on different Machines, they are sharing the same Table to set different search criteria at the same time and likely to clash each other when the Main Form is refreshed and the temp_param table record is updated and end up with the unexpected result.

A Workaround Method

A workaround to this problem is to use Unbound Text Boxes/Combo-Boxes on the Main Form and not to use the temp_param Table at all to store the values selected from cboCity and cboTitle Combo-Boxes.

Users can set the Values on the Unbound Text Boxes/Combo-Boxes on their own instance of the Form without conflicts.

This method also has some minor side effects. When the User opens the Main Form it will be empty till they Select some values from the unbound Combo-Boxes. This we can rectify to a certain extend by creating two Custom Properties on the Main Form. Save the last used value from the Combo-Box controls into these custom properties when the Form closes and restore them back when the Form opens again.

Why I said 'to certain extend' because if several Users are sharing the Main Form then the Custom Property values are saved by each User of the Main Form (when he/she closes their instance of the Form) and will retain only the value saved last. But all the Users who open the Main form next time will be presented with the records related to the Custom Property Values saved last. But this is not a big issue because most probably next time when they open the Form they may require records for different criteria and can change it too.

Creating Custom Properties on Form

To implement this method, first, we must create the Custom Properties: prpCity and prpTitle on the Main Form and save some initial values into them. We can do this only with VBA Code. Sample Code is given below:

Public Function CustomProperty()
Dim db As DAO.Database, doc As Document, prp As Property

Set db = CurrentDb
Set doc = db.Containers("Forms").Documents("Main")
'creates the Custom Property with Name, data type and initial value
Set prp = doc.CreateProperty("prpCity", dbText, "London")

'add the new Custom Property to the Properties collection of the Form Main
'NB: This will not appear in the Property Sheet of the Form

doc.Properties.Append prp
Set prp = doc.CreateProperty("prpTitle", dbText, "Manager")
doc.Properties.Append prp
End Function

Here, you can see that the reference to the Main Form is addressed differently than the usual method of Forms_Main or Forms![Main] or Forms("Main") etc.. The Forms group is addressed as Container; a member of the Containers (Tables, Forms, Reports, etc.) Group and the Main Form is addressed as a Document, a member of the Documents Collection. To learn more about Containers and Documents visit the page with the Title: Saving Data on Forms not in Table.

Since this is a one-time exercise you can Copy the above Code into a Global Module (Standard Module) and Run the Code directly by placing the cursor in the middle of the Code and pressing F5 (Run).

If you attempt to run the Code a second time it will show Errors indicating that the Custom Properties with the given name are already present on the Form.

The next step is to use these Properties on the Form_Close() and Form_Load() EventProcedures to save values from the Combo-Box Controls into the Custom Properties and restore them back into the Combo-Box Controls when the Form is open.

Saving Combo Box Value into Custom Property

The following Code saves the Combo-Box contents into prpCity and prpTitle custom properties on the Main Form when the Form is closed:

Private Sub Form_Close()
Dim db As Database, doc As Document, prp As Property

On Error GoTo Form_Close_Err

Set db = CurrentDb
Set doc = db.Containers("Forms").Documents("Main")

'Save the current values from the combo boxes into the custom properties
doc.Properties("prpCity").Value = Me![cboCity]
doc.Properties("prpTitle").Value = Me![cboTitle]

Exit Sub

MsgBox Err.Description, , "Form_Close()"
Resume Form_Close_Exit
End Sub

Restoring Value from Custom Property

The following Code restores the cboCity and cboTitle values when the Main Form opens by Users again:

Private Sub Form_Load()
Dim db As Database, doc As Document, prp As Property

On Error GoTo Form_Load_Err

Set db = CurrentDb
Set doc = db.Containers("Forms").Documents("Main")

'Set the Combobox values from the Custom Property values saved earlier

Me![cboCity] = doc.Properties("prpCity").Value
Me![cboTitle] = doc.Properties("prpTitle").Value

Exit Sub

MsgBox Err.Description, , "Form_Load()"
Resume Form_Load_Exit
End Sub

How about positioning a particular record on the Form, which you have worked on last time, as the current record when the Form opens? Click here to find out.


ControlTip Text and Time Delay


Most of the Controls on a Form like Command Buttons, Labels, Text-Boxes, and others have the Property ControlTip Text. This can be set with a value of up to a maximum length of 255 characters, either manually during design time or through Visual Basic. The text is displayed when the Mouse Pointer rests on the control for a few seconds. This is useful to inform the user to do a certain action, like click or Double-Click, on the control to run a Program or Macro attached to it.

The Toolbar Buttons above also have this feature programmed with the Screen Tip Property Value (another name for ControlTip Text) to give clues to the User as to what the control does or what to do to use it.

For example, when you point the Mouse pointer on the Copy Toolbar Button it will show Copy (Ctrl+C) indicating that either Click on the Button to Copy the selected Text/Control or use Ctrl+C to get the same result.

The time delay is programmed into this action assuming that the Mouse Pointer rests on the Control, because the user probably doesn't know what it does, and needs some help to indicate what he/she should do to use or what result one can expect by using it.

Our own Method without Time Delay

Here, we will implement a new method on Controls on the Main Switchboard (Control Screen), like Command Buttons, List-Boxes, and how to achieve the same result without the time delay that needs for the ControlTip Text Property.

There is another Property named Tag placed near to the ControlTip Property; its function is not clearly defined and free to use, as you wish. The value in this Property will not affect the Control in any way and will not have any side effects to other Properties either. You can write a short story into the Tag Property if you can limit your story to 2048 characters.

We are going to use this Property of different Controls on the Main Switchboard of a sample database to give clues to the User instantly what each control does or what to do (like Click or Double-Click) to run the Program or Macro attached to them.

You must select each control and display their Property Sheet(View - -> Properties) and type the Value, you would like to display, into the Tag Property. This text will appear instantly in the Label with dark background (you can use any color you like) placed at the bottom of the Form when the Mouse is moved over a Control with Tag value set and programmed.

An image of a sample Main SwitchBoard with the above trick is given below:

Using the Mouse Move Event

A Label with dark background (let us call it LBLTIP) is placed at the bottom of the design to display the clues when the Mouse moves over the controls above. The example text displayed on the image was saved in the Tag Property of the List-Box along with Forms' Names.

To display the clue and to remove it when the Mouse moves out of the Control we need to run a few lines of VBA Code at two places. On Mouse Move Event Procedure of the Control as well as at the Detail Section of the Form to remove the text and replace it with some general message text or a zero-length string to keep it empty.

The sample code in the On Mouse Move Event Procedure of the Reports Command Button is given below that displays Open Report SwitchBoard in the LBLTIP label Caption below, when the Mouse is moved over the Command Button.

Private Sub cmdRpt_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
If Me.LBLTIP.Caption = Me.cmdRpt.Tag Then
    Me.LBLTIP.Caption = Me.cmdRpt.Tag
End If
End Sub

When the Mouse moves out of the Command Button and touches the empty area of the Detail Section of the Form the LBLTIP Caption changes to Welcome User: Admin (the current MS-Access User Account name) appears and stays there till the mouse moves over another Control on the Form.

The VBA Code in the Detail Section On Mouse Move Event Procedure is given below:

Private Sub Detail_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
Dim strtag As String
strtag = "Welcom User: " & CurrentUser
If Me.LBLTIP.Caption <> strtag Then
    Me.LBLTIP.Caption = strtag
End If
End Sub

Limiting the Mouse Move Event

Even though the Detail Section Mouse Move Event Procedure Code has no change; repeating all three lines of code for each control is excess work. The IF. . .Then statement is used to test and prevent setting the LBLTIP Caption value repeatedly.

We can implement this with a single line of code if we write a common routine and place it in a Global Module (standard Module) and call it with a single line of code with necessary Parameters. Such a Function is given below

Copy the following VBA Code and save it in a Standard Module:

Public Function ControlTip(ByVal frmName As String, Optional strtext As String = "Welcome User: ", Optional xswitch As Integer = 0)
On Error GoTo ControlTip_Err
With Forms(frmName).Controls("lblTip"
    Select Case xswitch
        Case 1
            If .Caption <> strtext Then
                .Caption = strtext
            End If
        Case Else
            If .Caption <> strtext Then
                .Caption = strtext & CurrentUser
            End If
    End Select
End With

Exit Function

MsgBox Err.Description, , "ControlTip()"
Resume ControlTip_Exit
End Function

The above Function has three Parameters:

  1. Name of the Form from where the Function is called.
  2. The LBLTIP.Caption display text. This will be taken from the Tag Property Value of the Control. This parameter is defined as Optional so that this can be called without a value at Form-level (in our example from Detail Section) to display the welcome message after removing the earlier contents.
  3. The Optional xswitch parameter value is used for display choices. Value of 1 will display the Text value passed through the Second Parameter or else it will display a welcome message to the User replacing the earlier text in the LBLTIP Caption Property.

With the above Function we can simplify the first two Sub-Routines as given below:

 Private Sub cmdRpt_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)

     ControlTip Me.Name, Me.cmdRpt.Tag, 1
End Sub

Private Sub Detail_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
    ControlTip Me.Name
End Sub

 Demo Database Download

You can download the sample database (Access2000 Version) from the link given below and try it out.


Download Demo ControlTip2k.zip


External Files List in Hyperlinks


Is it possible to display a directory listing of External Files on a Form and open them in their parent-applications? These kinds of several queries have received through E-mails pointing towards this objective, from Readers of the earlier Article: File Brower in MS-Access (Common Dialog Control).

We have seen that we can open and work with external data sources like dBase Tables, Excel databases, and AS400(iSeries) Tables directly without linking them permanently with MS-Access and familiarized these procedures through the following Articles:

  • Opening External Data Sources
  • Opening dBase Files Directly
  • Display Excel Value Directly on Form
  • Opening Excel Database Directly
  • Database Connection String Properties
  • Access Live Data in Excel
  • Access Live Data in Excel-2
  • Source ConnectStr Property and ODBC
  • But, all of them fall into only one category, data files.

    Designing a Form

    To answer the above query we will create a Form with a Datasheet Sub-Form and with few simple controls to take a listing of a particular category of Files of your choice (Text Files, Word Files, Excel Files, or All Files) from a Folder (including sub-folders, if needed) and display them in a list. If you would like to open and work with them in their parent Applications, you are welcome to do so by clicking and opening it in their parent Application. An Image of a sample run is given below:

    All you have to do is to type the File Path like C:\Documents and Settings\UserName\My Documents\*.xls into a Text Box and click a Command Button and there it is; all excel files from the selected folder is presented on the Form as Hyperlinks on the Datasheet.

    I know you are not impressed because you could memorize and keep entering the above Path or some other Path without difficulty. Then what about other numerous locations and sub-folders on several Disk Drives? Opening Windows Explorer is out of the question; to find the location and enter it into the Text Box Control manually. Well, if there is no other way then that also can be done.

    Finding Files on Folders

    No, you will click on the Path Lookup Command Button to invoke something like the Common Dialog Control and browse to the location of the files you are interested in, select one of them and click the OK button. The File's location address with the File you have selected will be placed in the Text Control.

    That saves a lot of trouble in finding the location of the files. It takes only a few clicks of the mouse and we are doing it plenty of times a day.

    You may bring in the selected file into the list in Datasheet view by clicking the Get Files Command Button or modify the file name part with Wild Card characters (like *.xls, or Acc*.jpg) to bring in a group of files into the list.

    The DataSheet Form Design

    The Form has a simple design as you can see from the image given above. The following are the main elements of the design.

    1. A Table: DirectoryList with a single Field FileLinks with data type Hyperlink.
    2. A Datasheet Form created on the above Table with the name FilesListing_Sub.
    3. The Main Form FilesListing with the Datasheet Form inserted as Sub-Form that occupies the major part of the design.
    4. A Command Button (Name: cmdPathLookup) with the Caption Path Lookup runs the Sub-Routine and works like Common Dialog Control to browse and select a file from the required location and insert the Path Name in the Text Box below.
    5. A Text Box with the name PathName to hold the location address (or Path Name of the Files) and to modify as per your requirement before you Click the Get Files Command Button. If you know the Path Name correctly then you may type it in the Text Box directly, whichever is easier for you.
    6. The Command Button with the name cmdGo and with the Caption Get Files when clicked brings in all the files matching the Path Name specified and displays them in the Data Sheet View above.
    7. A Combo-Box with the name cboYN with Yes, No Values. If you set the value to Yes before clicking the Get Files Command Button; all files from the Sub-Folders of the selected folder, if any, also included in the output.
    8. The Command Button with the name cmdClose and with the Caption Close when clicked closes the FilesListing Main Form.

      The Property Sheet Values

    9. Display the Property Sheet(View- ->Properties) of the Main Form FilesListing and change the Property Values as given below to modify its behavior when opened:
      • Caption = External Files List
      • Default View = Single Form
      • Allow Edits = Yes
      • Allow Deletions = Yes
      • Allow Additions = No
      • Scroll Bars = Neither
      • Record Selectors = No
      • Navigation Buttons = No
      • Dividing Lines = No
      • Auto Re-Size = Yes
      • Auto Center = Yes
      • Pop up = Yes
      • Modal = No
      • Border Style = Dialog
      • Control Box = Yes
      • Min Max Buttons = None
      • Close Button = Yes
      • Allow Design Changes = Design View Only

    The Main Form Design

    The image of the Main Form in design view is given below:

    The heading Labels External Files List is created with the 3D Text Creation Program and modified to look like the above design. You can download this 3D Text Creation Wizard from any of the following links:

Managing the Files List

The Files' List is created as Hyperlinks in the target table: DirectoryList, every time, replacing the existing list of files, and appears in Datasheet View. You may click on the HyperLink to open that file in its parent Application to work with it.

You can delete unwanted files from the list. Select a record by clicking on the left border of the list and press Delete Key or press Ctrl+- (press and hold Ctrl Key and press - Key). You cannot add a record manually or edit a record on the Datasheet (you may do so directly on the Table if you like to mess around with it) so that the HyperLink Value Segments are not altered.

If you would like to know more about the Hyperlink value Segments (four of them) and what they do; go to the link Open Forms with Hyperlinks in ListBox.

You may download the sample database from the bottom of this post and try it out before you design one of your own to understand it better.

You can easily implement this in your various Projects by simply importing the Forms and the Table into them.

It is an Access2000 Version file and Access97 users cannot use it. But, you can design the above Forms and Controls with their specific names as explained above by following Step-1 to 9.

The Main Form Class Module Code

The Sub-Routines and Function which runs behind the Main Form FilesListing are given below. You may create the above design with the correct names explained under Steps-1 to 9, copy and paste the complete code in the Main Form Class Module and save the Form.

Option Compare Database
Option Explicit

Private Sub cmdClose_Click()
    DoCmd.Close acForm, Me.Name
End Sub

Private Sub cmdGo_Click()
Dim strtxt As String
On Error GoTo cmdGo_Click_Err
strtxt = Nz(Me![PathName], "")
New_Search strtxt, Me!cboYN

Exit Sub

MsgBox Err.Description, , "cmdGo_Click"
Resume cmdGo_Click_Exit
End Sub

Private Sub cmdPathLookup_Click()
'Source Code : Northwind.mdb sample database
'Displays the Office File Open dialog to choose a file name    
'If the user selects a file    
'display it in the Pathname control.    
Dim result As Integer    
On Error GoTo cmdPathLookup_Click_Err
    With Application.FileDialog(msoFileDialogFilePicker)
        .Title = "Select a File from Target Folder"
        .Filters.Add "All Files", "*.*"
        .FilterIndex = 1
        .AllowMultiSelect = False
        .InitialFileName = CurrentProject.path
        result = .Show
        If (result  0) Then
            Me!PathName = Trim(.SelectedItems.Item(1))
        End If
    End With

Exit Sub

MsgBox Err.Description, , "cmdPathLookup_Click"
Resume cmdPathLookup_Click_Exit
End Sub

Private Sub Form_Load()
  Me!PathName = CurrentProject.path & "\*.*"
End Sub

Private Function New_Search(ByVal strFilePathName As String, boolsubfolders As Boolean)
'Author : a.p.r. pillai
'Date   : Feb. 2009
'URL    : www.msaccesstips.com
'All Rights Reserved by www.msaccesstips.com
Dim i, strFolder As String, strFiles As String, locn As Long
Dim db As Database, rst As Recordset, xtn As Integer

'On Error GoTo New_Search_Err

If Len(Trim(strFilePathName)) = 0 Then
   MsgBox "File Path Name required."
   Exit Function
End If
xtn = InStr(1, strFilePathName, ".")
locn = InStrRev(strFilePathName, "\")

If xtn > 0 And locn > 0 Then
   strFiles = Trim(Mid(strFilePathName, locn + 1))
   strFolder = Trim(Left(strFilePathName, locn - 1))
ElseIf xtn > 0 And locn = 0 Then
   strFiles = Trim(strFilePathName)
   If Len(Left(strFiles, xtn - 1)) = 0 Then
      MsgBox "Invalid File specification."
      Exit Function
   End If
   strFolder = CurrentProject.path & "\*.*"
ElseIf xtn = 0 And locn > 0 Then
   strFiles = "*.*"
   strFolder = Trim(strFilePathName)
End If

With Application.FileSearch
'the .NewSearch resets earlier search property values to defaults
    .LookIn = strFolder
    .SearchSubFolders = boolsubfolders
    .fileName = strFiles
If .Execute() > 0 Then
    i = .FoundFiles.Count
    MsgBox "File found = " & .FoundFiles.Count
    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE DirectoryList.* FROM DirectoryList;"
    DoCmd.SetWarnings True
    Set db = CurrentDb
    Set rst = db.OpenRecordset("DirectoryList", dbOpenDynaset)
    For i = 1 To .FoundFiles.Count
        strFiles = Mid(.FoundFiles(i), InStrRev(.FoundFiles(i), "\") + 1)
        strFiles = strFiles & "#" & .FoundFiles(i) & "##Click"
        rst![FileLinks] = strFiles
    MsgBox "No matching File Names found!"
End If
End With

Exit Function

MsgBox Err.Description, , "New_Search"
Resume New_Search_Exit
End Function

If there is any difficulty in running the On Click Event Procedure of the Path Lookup Command Button then replace that with the File Browser method explained in the Article: File Browser in MS-Access (Common Dialog Control).

Download Demo Database

Download Demo DirListing2K.zip


MSA GURU : Access Tips & Tricks App

  • Download Android App 'MSA Guru' Version of LEARN MS-ACCESS TIPS AND TRICKS from Google Play Store.

Want to Post Free Ads on the Web


Subscribe in a reader
Your email address:

Delivered by FeedBurner


Popular Posts

Blog Archive

Powered by Blogger.

Follow by Email


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