Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Open Forms with Hyperlinks in Listbox

We have seen in the previous example that all our Forms can be organized in one place with the use of a ListBox and can Open the Form by Double_Clicking an item in the list. As a result, based on the value in the Type field of the control table, a Form opens directly or runs a macro to perform a sequence of operations to prepare the output before opening a Form with the processed data linked to the Form.

The objective of this method that we are going to try out here is same, but we will do it differently. It is better to know more than one method, which we can use in our various Projects, rather than using the same thing everywhere.

For this example we need to design a small form (not a listbox) and make it look like a listbox.

First, create a table with two fields with the following field Types and sizes:

Field Name

Type

Size

Seq

Number

Integer

Desc

Hyperlink


Save the table with the Name: DataFiles2. Open the Table to add few records into it. In MS-Access 2003 we have a problem entering Hyperlink SubAddress of Objects from within the Database. But we can use more than one trick to beat this problem. First you must know how the Hyperlink data is stored within the Hyperlink field. The data is stored in a Four segments format, separated by # symbol.

Display Text # External File's Hyperlink Address # Internal Objects Hyperlink SubAddress # Control Tip Text

You can use the last segment to display a text when you point on the Hyperlink and that segment we will ignore here.

Right clicking on the Hyperlink Field displays the Hyperlink Shortcut Menu and pointing to it will display the Edit Hyperlink option, select it. This is the same menu you have opened from the Hyperlink SubAddress property of the command Button to Link a Macro in Closing Forms Example. But the Dialogue Control displayed here doesn't have the Object in this Database option in the Link To: list. You cannot browse to the database object to link its name directly into the Hyperlink field. But, you can use this dialogue box to enter data into the field.

  1. Type a Descriptive Text in the Text to Display box on the top of the dialog control.
  2. Type a hash symbol (#) followed by the type of object name (like Form, Report etc.) you are linking followed by the name of the Object itself after giving a space between the type and the Name in the Address: box at the bottom.

Example-1:

  1. Text to Display: Edit Employees' Data
  2. Address: #Form Employees

Click OK button. The 'Edit Employees Data' text only will be displayed in the Hyperlink field of the table. When you click on the field the Employees Form will open.

Example-2:

You can achieve the same result by typing a 3-segment Hyperlink value in the table Desc field directly:

Edit Employees' Data##Form Employees

You need to insert two hash (#) symbols between the Text to Display & the Hyperlink SubAddress segments to open an object from within the Database. The Object Type, like Form, Report, Macro and Table must be specified immediately after the 2nd hash symbol followed by the Object name itself after giving a space between them.

If you need to open an external file like a Word-File you can type the full path of the File like C:\My Documents\mywordfile.doc immediately after the first hash symbol. In that case the third segment is not required, if entered, it will be ignored.

Approaching to Edit a Hyperlink field is a tricky business. When you point the arrow key the field is ready to click and open the link not to facilitate editing. So selecting the field for editing is like catching a snake without getting a snakebite. You can approach the task in two ways.

  1. Point on the field and move the arrow towards the intersection of the left side field (if there is one) the pointing hand will change into a 3D + sign. At that point click the field to select the Hyperlink Field.
  2. Another method is to right-click on the Hyperlink field which will highlight the field and open the shortcut menu. Click outside somewhere to remove the shortcut menu displayed.

After selecting the Hyperlink field you can edit the contents by using the Example-2 method explained above.

Enter few more records in the DataFiles2 Table to open the Objects of your choice by entering their Object Type and Name correctly in the Hyperlink SubAddress segment and close the Table.

Now we need to design a small form. Click the Table and select Form from the Insert Menu and select AutoForm: Tabular from the New Form Wizard and click Ok. A tabular form is created and opened in data view. Close the Form and save it with the Name: DataFiles2. Open the form in design view to change its properties to look like a listbox.

  1. Click View menu and remove the check mark from the Form Header/Footer item. This action will display a warning message, click Yes to remove the Header & Footer Sections of the Form.
  2. Click on the left top corner of the form where the left-side and top scales meet, where a black square shows at the intersection, to select the Form rather than its controls.
  3. Press Alt+Enter keys or Click Properties from the View Menu to display the Form's property sheet.
  4. Change the following Form Properties:
    • Allow Datasheet View : No
    • Allow Additions : No
    • Allow Deletions: No
    • Data Entry: No
    • Scroll Bars: Neither
    • Record Selectors: No
    • Navigation Buttons: No
    • Dividing Lines: No
    • Border Style: None
    • ControlBox: No
    • Min Max Buttons: None
    • Close Button: No
  5. Click on the Detail Section of the form and display the Property Sheet.
  6. Change the Back Color Property Value to 128 (will change to dark red color)
  7. Select the Seq Field control and change its width property to 0.2'
  8. Select Seq & Desc text boxes together, select Align - - > Left from the Format Menu, which will bring the controls close together.
  9. Select Shortest from the Format - - > Size Option to change both controls height to the same value.
  10. Now change the Properties (still both controls selected) of the controls to the following values:
    • Special Effect: Flat
    • Top: 0
    • Back Color: 128
    • Fore Color: 16777215
  11. Resize the form's detail section close to the field sizes using the sizing handles at right and bottom edges of the form. Change both control's border Color & Font weight:
    • Border Color: 128
    • Font Weight: Bold
  12. Select the Seq field alone and change the following properties to:
    • a. Enabled: No
    • b. Locked: Yes

The completed form in design view will look like the sample given below:

When opened in Form View, it will look like the sample below:

If you opened it in Form View select Close from the File Menu to close the Form. Now the remaining task is to insert this form into our Main Control Form and change few properties of the control to finish the final design and you are ready to go.

Close all other objects and ensure that the Database window is not minimized, if so restore it. Now, open the Control Form in design View. Select Tile Vertically from the Windows Menu. The Database Window and the Form will arrange side by side. Click on the Forms Tab (if it is not visible), click, drag and drop the DataFiles2 form on the Control Screen where you want the Menu Options to appear. Resize the control to the desired size. Change its child label Caption to: Data Files. Size the child label to the same size of the control and its Special Effect Property to Etched. Save the Form. The completed design on the Control Screen will look like the sample given below:

Click an Item in the Listbox, the Hyperlinks opens the Object (Form, Table, Macro etc.) attached to it.

When more links are added to the DataFiles2 Table they will appear in the list as well.

Click Next for Control Screen Design...




Download Demo Database


Share:

Opening Access Forms

Using the same method that we have seen in the Closing Forms topic, we can create Command Buttons on the Control Screen and link the Forms to each Command Button by setting the Form Name to the Hyperlink SubAddress Property. This is the easiest method to open forms from a central point, when you have several forms in your Application. But the problem with this method is that you may end up with several command buttons on your Control Screen.

We will look into different methods to organize and open the Forms from the Control Screen. We will look into one of them here and we need little preparation for this method.

First of all, create a Table with the following Fields:

SRL Field Name Field Type Field Size
1. ID Number Integer
2. DESC Text 35 Characters
3. FORM Text 20 Characters
4. MACRO Text 20 Characters
5. TYPE Number Integer

and save it with the name: DataFiles.

An Image of a Table with sample data filled in is given below:

Fill the table with your data files' list for which you already have Forms designed for them with a Serial Number and Description in the ID & Desc fields and the Form Name for each Table in the Form Field. Fill the Type field with zero (0), for the time being and we will see the use of that information soon.

Now create a List Box on the Control Screen and select only ID & Desc fields from our DataFiles table as source data for the list box.

When you open your Control Screen you will see the serial number and description of your data files in the List Box. A Sample Screen with Listbox is given below for reference.

Click to Enlarge

Now, go back to the design view of the Control Screen (if you have turned on the Form View earlier) and click on the ListBox and display the Property Sheet (View- ->Properties). Change the Name Property to Dat1.

Display the VB Module (click on the Module Tab of your Database and click on the New option on the database window, which will open a new Global Module. Copy and Paste the following Code in that Module and save it.

Public Function FormOpen(ByVal FileName As String, ByVal ID As Integer) As Boolean 
Dim vartyp As Variant, varFormName As Variant 
Dim varMacName As Variant 
Dim Criteria As String  
On Error GoTo FormOpen_Err  
FormOpen = False  
Criteria = "ID = " & Format(ID)  
vartyp = DLookup("Type", FileName, Criteria)  
If vartyp = 0 Then
 varFormName = DLookup("Form", FileName, Criteria) 
   If IsNull(varFormName) = False Then
      DoCmd.OpenForm varFormName, acNormal
   End If
 ElseIf vartyp = 1 Then 
   varMacName = DLookup("Macro", FileName, Criteria) 
      If IsNull(varMacName) = False Then
         DoCmd.RunMacro varMacName
       End If 
End If
    FormOpen = True

FormOpen_Exit: 
Exit Function  

FormOpen_Err: 
MsgBox Err.Description, , "FormOpen" 
FormOpen = False 
Resume FormOpen_Exit 
End Function 

Now, we have to write a line of code for the On DblClick Event for the listbox. Go back to the listbox design view and display its Properties Sheet. Click on the On Dbl Click property and select [Event Procedure] from the combobox and click on the build ( ) button to open up the form's code module.

You will find an empty On Dbl Click Event Procedure with its starting line Private Sub Dat1_DblClick(Cancel As Integer) and End Sub lines. Complete it by inserting a line of code between these lines as shown below without making typing mistakes:

Private Sub Dat1_DblClick(Cancel As Integer)
   FormOpen "DataFiles", Me![Dat1]
End Sub

If you are not sure, highlight the middle line code above, copy and paste it correctly between the code lines appearing in your form module. It is important that you name the listbox as Dat1 and it is appearing in the code. The list box source data table name is in quotes and a comma is separating both the parameters to the FormOpen function. If you have used any of this objects name differently you have to correct them here.

Now, close the Form's Code Module and save the Form. It is time to test our project. Before testing, check the accuracy of the Form Names that you have filled in, in the Form field of the DataFiles Table.

Open the Control Form. Double-Click on any line in the listbox and you must see your Form corresponding to that item you have double-clicked opens immediately.

If you end up with some errors then double-check the Name of the listbox, the source data table name, the Form Name in the Form field of the DataFiles Table and ensure that zero is filled in the Type field as well. The serial numbers in the DataFiles Table must be continuous numbers. It is convenient if you give shorter names to your Forms so that you don't make mistakes while using their names.

How it works:

When you double-click on the listbox the sequence number corresponding to the item, and the Listbox Source Data Table's Name are passed to the function FormOpen(). The FormOpen() function picks the Type value of the item double-clicked and checks its status and executes.

If the Type Value is Empty - Ignores the Double-Click action (nothing happens).

0 - picks the Form's Name from the Form field of the DataFiles Table and opens it.

1 - the program looks for the Value in the Macro Field of the DataFiles Table and runs that macro.

This is useful if you want to open a table in Read only mode, or to open a form after running a series of Queries to create an output table/Query linked to a Form which expects different results every time etc. by writing a macro and putting it's name in the Macro Field.

This method can be used for Reports also. If you design more Forms/Reports later all you need to do is to add that item in the Listbox source table and the double-click action takes care of the rest.

Next we will see how to use the Hyperlink method in a listbox like design.

Click Next to see use of Hyperlinks in Listbox

Share:

Closing Forms

For closing an active Access Form we can use several methods and apply certain tricks to enhance the overall look of the action used for it.

The simplest method is to click the window close button X, which everybody knows. The On Close Event of the Form is also triggered on this action and the programmer can take care of any unfinished work like closing of other forms related to the main form etc. But, when more Forms are open at the same time then there is a chance that the user may close the wrong Form by clicking on the wrong X button on the top.

I prefer to create a Command Button on a convenient location on the Form, like at the Footer or Header of the Form and let the user click on it. Create a macro and link to the Command Button's Hyperlink SubAddress Property. My favourite name for the Macro is Hlink_Close and it will contain only the Close Action leaving the ObjectType and ObjectName parameters blank.

Click on the Hyperlink SubAddress Property of the Command Button and click on the build ... icon at the right side to open up the Edit HyperLink Dialog Control. Select the Object in this Database option from the Link To: Option List which will show the Database Objects: Tables, Forms etc., expand the Macro group and select the HLink_Close Macro and click OK.

This method has an attractive feature, when you point to the control with the Hyperlink SubAddress set with the Macro, the Cursor changes to a hand with the index finger pointing, as you see when you point the cursor on a Hyperlink in Internet.

The same HLink_Close Macro can be used this way for any number of Forms on it's Command Buttons, Labels, Images, or any other Object that has a Hyperlink SubAddress Property, to close a Form by clicking on the object.

This method will not work on a form with the Form's Popup Property set to Yes. In such case, if another form is present with the Form's Popup Property set to No, then that form will be closed leaving the original form untouched. If this is the only form open, then it will end up with an error message: can't follow the Hyperlink Macro Hlink_Close. Use alternative method for Forms with Popup Property set to Yes, like: write an On Click EventProcedure for the Command Button and write a Docmd.Close action in the VB Module without any parameters.

If you need to change only the style of the Mouse Pointer to a hand while pointing the cursor on the control then create a Macro with Action set to Beep or Echo and link this Macro to the controls Hyperlink SubAddress Property. I use the name HLink_Blank for such a Macro with the Action set to Beep. The Beep action will take place only when you click the object.

Tip: You can Open External Files Like Word, Excel etc. by setting the Hyperlink Address Property (not Hyperlink SubAddress) of the Command Button with an external File's location address. Select the Link to Option: Existing File or Webpage rather than Object in this Database from the Edit Hyperlink Dialog Control.

Click Next to see Opening Access Forms

Share:

Translate



PageRank
Your email address:

Delivered by FeedBurner

Search

Infolinks Text Ads


Blogs Directory

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

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

Featured Post

Function Parameter Array Passing

Last week we have explored the usage of ByVal (By Value) and ByRef (By Reference),  in the Function Parameter, to pass the value from  a Va...

Labels

Blog Archive

Recent Posts