February 2009 ~ LEARN MS-ACCESS TIPS AND TRICKS

Synchronized Floating popup Form

a.p.r. pillaiFebruary 28, 2009Forms, msaccess controls 1 comment

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 fields are placed on the second page of the Tab Control and stay 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 has been the most frequently viewed or updated information and it is kept in full view, and Personal Info is kept behind because it is not so often viewed or edited.

We will design them differently with an exciting 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 the 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 you don't open another Pop-up Form. If you don't want the EmployeeSub Form to stay with 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.

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.
Open the Employees Form in Design View.
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.
Select Copy from Edit Menu.
Click on the Employees Table, select Form from the Insert menu, and select Design-View, from the list of options displayed, to create a new Form.
Click on the Detail Section of the Form and select Paste from Edit Menu to place the copied fields of the Employees Form onto the new Form.
If the Form Header/Footer Sections of the Form are not visible then select Form Header/Footer from View Menu.
Copy and Paste the Text Control with the employee name expression from the Header Section of the Employees Form in the Header Section of your Form. Change the Font Color to Red or some other color you like.
Create a Command Button in the Detail Section below the data fields.
Display its Property Sheet (View- -> Properties).
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.
Create a Command Button at the Footer Section of the Form.
Change the Name Property value to cmdClose and the Caption Property Value to Close.
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.
Change the Caption Property Value to Company Info.

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()
   DoCmd.Close
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
Else
   DoCmd.OpenForm "EmployeeSub", acNormal, , "[Employeeid] = " & Me![EmployeeID], acFormReadOnly, acWindowNormal
End If

Forms("EmployeeMain").ActiveControl.SetFocus

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
   Forms("EmployeeMain").SetFocus
End If

End Sub

Private Sub Form_Open(Cancel As Integer)
DoCmd.Restore
End Sub

Save the Form with the name EmployeeMain.
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.
Display the Form Header/Footer Section of the Form (View - ->Form Header/Footer).
Create a Command Button and change the Name Property Value to cmdClose and the Caption Property Value to Close.
Display the Form's Property Sheet.
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.
Display the Code Module of the Form (Alt+F11).

Copy and paste the following code into the Code Module.

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

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
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
Next

IsLoaded_Exit:
Exit Function

IsLoaded_Err:
IsLoaded = False
Resume IsLoaded_Exit
End Function

The Demo Run

To try out your creation, open the EmployeeMain Form, and click on the Record Navigation Control to advance a few records forward.
Click on the Personal Info Command Button. The EmployeeSub Form will open up showing the Personal Information that pertains to the same employee on the main Form. Check the Names of the Employees appearing on top of both forms.
Now, try advancing records on the main form forward or back to 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.
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:

SynchronizedForm2k.zip

Forms and Custom Properties

a.p.r. pillaiFebruary 21, 2009Forms, msaccess controls No comments

Introduction

Searching and finding a record in a Form is easy with the 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 the 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.

Create a temporary table with the name temp_param with two text fields; City and Title.
Add a single record with City field value as London and Title field value as a Sales Representative.
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 of the City and Title fields from the Employees Table. Create two Select Queries; by grouping values of these fields and using them as the source for the Combo Boxes. Do that by following the steps given below.
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 the SQL string given below and save the Query with the name cboTitleQ.

Query Name: cboTitleQ

SELECT Employees.Title
FROM Employees
GROUP BY Employees.Title;

Create a Combo-Box in the Header Section of the Main Form using cboCityQ as source data and select City as Control Source.
Create another Combo-Box, use the cboTitleQ Query as source data, and select Title as Control Source.
Name the City field Combo-Box, as cboCity and Title field, Combo-Box name as a cboTitle.
Design a Datasheet Form on the Employees Table and save the Form with the name Employees_Sub.
Insert the Employees_Sub Form as Sub-Form in the Detail Section of the Main Form.
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
Now you can select the 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 instances of the Main Form are used by individual Users, on different Machines, they are sharing the same Table to set different search criteria at the same time. These are likely to clash with each other when the Main Form is refreshed and the temp_param table record is updated. This can end up with an unexpected result for both users.

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, not so serious, side effects. When the User opens the Main Form it will be empty till they select some values from the unbound Combo-Boxes. We can rectify this to a certain extent 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 a certain extent, because if several Users are sharing the Main Form then the Custom Property values are saved by each User in 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. The 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
doc.Properties.Refresh
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 in 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]

Form_Close_Exit:
Exit Sub

Form_Close_Err:
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

Form_Load_Exit:
Exit Sub

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

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

ControlTip Text and Time Delay

a.p.r. pillaiFebruary 13, 2009Forms, msaccess controls No comments

Introduction

Most of the Controls on a Form like Command Buttons, Labels, TextBoxes, 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 results from this action one can expect by using it.

Our own Method without Time Delay

Here, we will implement a new method for Controls on the Main Switchboard (Control Screen), like Command Buttons, and 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 of this Property will not affect the Control in any way and will not have any side effects on 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 a Tag value set and programmed.

An image of a sample Main Switch Board 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. It displays Open Report SwitchBoard in the LBLTIP label Caption text at the bottom of the form 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 to 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

ControlTip_Exit:
Exit Function

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

The above Function has three Parameters:

Name of the Form where the Function is called.
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 the Form level (in our example from Detail Section) to display the welcome message after removing the earlier contents.
The Optional xswitch parameter value is used for display choices. The 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

a.p.r. pillaiFebruary 07, 2009External Links, msaccess controls 8 comments

Introduction

Is it possible to display a listing of different file types from Disk as Hyperlinks on Access Form and open them in their parent Applications? Several queries of this kind have been received in my emails from readers of the earlier Articles. This is about using File Brower (the Common Dialog Control) and using Hyperlinks in Microsoft Access.

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 the Files List Form.

To answer the above queries we will create a Form with a Datasheet Sub-Form and with a few simple controls to take a listing of all frequently used files of your choice (Text Files, Word Files, Excel Files, or Files of all Types) from the Disk and display them in a list of Hyperlinks. When you would like to open and work with a file in their parent Application, simply click on the hyperlink to select and open the file. An Image of the sample Form is given below:

Finding Files on Folders

Click on the Create File Links Button to open the File Browser (the Common Dialog Control). Browse and find your file(s), select one or more files, and click the OK button to bring the file references into the above List control as Hyperlinks.

The Files Path Name is displayed to the right side control of the Hyperlink. This will help in finding the location of frequently used files of Excel, Word, PDFs, or whatever file files you create a link for. It takes only a few clicks of the mouse and we are doing it plenty of times a day.

You may bring in files in batches and all of them will be added to the directory-list Table.

The Data Sheet 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.

A Table: DirectoryList with two Fields: 1. FileLinks with data type Hyperlink. 2. Path with a Text data type to store the file’s complete path name.
The Datasheet Form was created in the above Table with the name FilesListing_Sub.
The Main Form FilesListing with the Datasheet Form inserted as a Sub-Form that occupies the major part of the design.
A Command Button (Name: cmdFileDialog) with the Caption Create File Links runs the Common Dialog Control to browse and select files from the Disk and insert them as Hyperlinks in the FileLinks Field of Table.
The Field Path will be updated with the location address of the selected files.
The Unbound Text Box below will show the Current Project Path as the default location when the File Dialog is open.
The Command Button with the name cmdDelAll and with the Caption Delete All Links when clicked deletes all file links from the tables.
The Command Button with the name cmdDelLink and with the Caption Delete One Link when clicked deletes the selected hyperlink item from the List. You can manually delete one or more Links, select them by holding the Shift key down and clicking on the left border of items next to each other, and press the DELETE Key.
Command Button with the Caption Delete File on Disk and the name cmdDelFile deletes the selected Link and the file on disk. Only one File can be deleted at one time. Use this option with caution, once the file is deleted from the disk it cannot be reversed. Click at the left border of a link to select it and click on the Delete File on Disk.

Managing the Files List

The Files' List is created as Hyperlinks in the target table: DirectoryList. Links can be added to the list and incoming links are appended to the existing records. You cannot add a record manually or edit a record on the Datasheet (you may do so directly to 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 segments) 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 page and try it out before you design one of your own to understand how it works.

You can easily implement this in your various Projects by simply importing the Forms and the Table into your Projects if required. The demo database is an Access2007 Version file.

The Main Form Class Module Code

The VBA Code, which runs behind the Main Form FilesListing is given below for info.

Option Compare Database
Option Explicit
Dim strpath As String

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

Private Sub cmdDelFile_Click()
On Error GoTo cmdDelFile_Click_Err
Dim db As DAO.Database, rst As DAO.Recordset
Dim strFile As String

strFile = Me.DirectoryList.Form!Path
Set db = CurrentDb
Set rst = db.OpenRecordset("DirectoryList", dbOpenDynaset)
rst.FindFirst "Path = '" & strFile & "'"
If Not rst.NoMatch Then
If MsgBox("File: " & strFile & vbCr & "DELETE from Disk?", _
vbQuestion + vbYesNo, "cmdDelFile_Click") = vbYes Then
   If MsgBox("Are you sure you want to Delete" & vbCr _
   & rst!Path & " File from DISK?", vbCritical + vbYesNo, "cmdDelFile_Click()") = vbNo Then
    GoTo cmdDelFile_Click_Exit
   End If
    rst.Delete
    rst.Requery
    Me.DirectoryList.Form.Requery
    If Len(Dir(strFile)) > 0 Then
    Kill strFile
    MsgBox "File: " & strFile & " Deleted."
    Else
      MsgBox "File: " & strFile & vbCr & "Not Found on Disk!"
    End If
End If
Else
    MsgBox "File: " & strFile & " Not Found!!"
End If

cmdDelFile_Click_Exit:
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

cmdDelFile_Click_Err:
MsgBox Err & " : " & Err.Description, , "cmdDelFile_Click()"
Resume cmdDelFile_Click_Exit
End Sub

Private Sub cmdHelp_Click()
DoCmd.OpenForm "Help", acNormal
End Sub

Private Sub Form_Load()
'strpath = CurrentProject.Path & "\*.*"
On Error GoTo Form_Load_Err
GetProperty
strpath = Me!PathName

Form_Load_Exit:
Exit Sub

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

Private Sub cmdDelLink_Click()
On Error GoTo cmdDelLink_Click_Err
Dim db As DAO.Database, rst As DAO.Recordset
Dim strFile As String

strFile = Me.DirectoryList.Form!Path
Set db = CurrentDb
Set rst = db.OpenRecordset("DirectoryList", dbOpenDynaset)
rst.FindFirst "Path = '" & strFile & "'"
If Not rst.NoMatch Then
If MsgBox("Link: " & strFile & vbCr & "DELETE from above List?", _
vbQuestion + vbYesNo, "cmddelLink_Click()") = vbYes Then
    rst.Delete
    rst.Requery
    Me.DirectoryList.Form.Requery
    MsgBox "File Link: " & strFile & " Deleted."
End If
Else
    MsgBox "Link: " & strFile & " Not Found!!"
End If
rst.Close
Set rst = Nothing
Set db = Nothing

cmdDelLink_Click_Exit:
Exit Sub

cmdDelLink_Click_Err:
MsgBox Err & " : " & Err.Description, , "cmdDelLink_Click()"
Resume cmdDelLink_Click_Exit

End Sub

Private Sub cmdFileDialog_Click()
On Error GoTo cmdFileDialog_Click_Err
'Requires reference to Microsoft Office 12.0 Object Library.
Dim db As DAO.Database, rst As DAO.Recordset
   Dim fDialog As Office.FileDialog
   Dim varFile As Variant
Dim strfiles As String
   'Clear listbox contents.
   'Me.FileList.RowSource = ""

   'Set up the File Dialog.
   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
   With fDialog
      'Allow user to make multiple selections in dialog box.
      .AllowMultiSelect = True
      .InitialFileName = strpath
            
      'Set the title of the dialog box.
      .Title = "Please select one or more files"

      'Clear out the current filters, and add our own.
      .Filters.Clear
      .Filters.Add "All Files", "*.*"
      .Filters.Add "Access Databases", "*.mdb; *.accdb"
      .Filters.Add "Access Projects", "*.adp"
      .Filters.Add "Excel WorkBooks", "*.xlsx; *.xls; *.xml"
      .Filters.Add "Word Documents", "*.docx; *.doc"

      'Show the dialog box. If the .Show method returns True, the
      'user picked at least one file. If the .Show method returns
      'False, the user clicked Cancel.
      If .Show = True 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
        For Each varFile In .SelectedItems
        rst.AddNew
        strfiles = Mid(varFile, InStrRev(varFile, "\") + 1)
        strfiles = strfiles & "#" & varFile & "##Click"
        rst![FileLinks] = strfiles
        rst![Path] = varFile
        rst.Update
    Next
Me.DirectoryList.Form.Requery
         'Loop through each file selected and add it to the list box.
         'For Each varFile In .SelectedItems
            'Me.FileList.AddItem varFile
         'Next
      Else
         MsgBox "You clicked Cancel in the file dialog box."
      End If
   End With

cmdFileDialog_Click_Exit:
Exit Sub

cmdFileDialog_Click_Err:
MsgBox Err & " : " & Err.Description, , "cmdFileDialog_Click()"
Resume cmdFileDialog_Click_Exit
End Sub

Private Sub Form_Unload(Cancel As Integer)
If Len(strpath) = 0 Then
  strpath = "C:\My Documents\*.*"
End If
SetProperty
End Sub

Private Sub PathName_AfterUpdate()
'On Error GoTo PathName_AfterUpdate_Err
Dim str_path As String, i As Long
Dim test As String

    Me.Refresh
    str_path = Me!PathName
    i = InStrRev(str_path, "\")
    str_path = Left(str_path, i) & "*.*"
    strpath = str_path
    
    test = Dir(strpath)
    If Len(test) = 0 Then
        MsgBox "Invalid PathName: " & strpath
        strpath = CurrentProject.Path & "\*.*"
        Me.PathName = str_path
        Me.Refresh
        Exit Sub
    End If
    Me.PathName = strpath
    Me.Refresh
    
PathName_AfterUpdate_Exit:
Exit Sub

PathName_AfterUpdate_Err:
MsgBox Err & " : " & Err.Description, , "PathName_AfterUpdate()"
Resume PathName_AfterUpdate_Exit
End Sub

Private Function GetProperty() As String
On Error GoTo GetProperty_Err
Dim doc As DAO.Document
Dim db As DAO.Database
Dim prp As DAO.Property
Dim strLoc As String

Set db = CurrentDb
Set doc = db.Containers("Forms").Documents("FilesListing")
strLoc = doc.Properties("defaultpath").Value
If Len(strLoc) = 0 Then
   strLoc = CurrentProject.Path & "\*.*"
End If

strpath = strLoc
Me!PathName = strpath
Me.Refresh

GetProperty_Exit:
Exit Function

GetProperty_Err:
MsgBox Err & " : " & Err.Description, , "GetProperty()"
Resume GetProperty_Exit
End Function


Private Function SetProperty() As String
On Error GoTo SetProperty_Err
Dim doc As DAO.Document
Dim db As DAO.Database
Dim prp As DAO.Property
Dim strLoc As String

Set db = CurrentDb
Set doc = db.Containers("Forms").Documents("FilesListing")
strLoc = Me!PathName
If Len(strLoc) = 0 Then
    strLoc = CurrentProject.Path & "\*.*"
End If
doc.Properties("defaultpath").Value = strLoc

SetProperty_Exit:
Exit Function

SetProperty_Err:
MsgBox Err & " : " & Err.Description, , "SetProperty()"
Resume SetProperty_Exit
End Function

Download Demo Database

Download Demo DirListing2K1.zip