Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Open Forms with Hyperlinks in Listbox

Introduction

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. Based on the value in the Type field of the control table, a Form/Report 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 the same, but we can do it very easily and differently.  It is better to know more than one method for a task which we can use in our various projects, rather than using the same thing everywhere.

Continuous Form to look like a List.

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 a few records to 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 segment format, separated by the # symbol.

Hyperlink Address in 4 Segments

Syntax:

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

You can use the last segment to display some helpful text when you point to the Hyperlink. We will ignore this segment 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 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 the 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 hashes (#) 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 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 Edit a Hyperlink field is a tricky business. When you point to 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 the mouse pointer on the field and move 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 a 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 the data view. Close the form and save it with the Name: DataFiles2. Open the form in the design view to change its properties to look like a Listbox.

  1. Click the View menu and remove the checkmark 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 the 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, and 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
    • ForeColor: 16777215
  11. Resize the form's detail section close to the field sizes using the sizing handles at the 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 too:
    • a. Enabled: No
    • b. Locked: Yes

The completed form in the 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 a 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 as 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, and the Hyperlinks open 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 Hyperlinks2k.zip


Share:

14 comments:

  1. many thanks to you
    but can you up to us an examole to see that

    or send it to me at : ahmedtharwat19@yahoo.com

    ReplyDelete
  2. Yes, I will forward a sample database to you shortly.

    regards,
    a.p.r. pillai

    ReplyDelete
  3. Do you know how to connect a hyperlink to a command button that sends you to the hyperlink based on selections picked from combo boxes? Example to clarify:

    You want to set up a car database so you make tables with certain information regarding the car, but for the purpose of this, lets just look at the model table. The model table (tblModel) consists of the following fields: ModelID, ModelName, MakeID, Hyperlink. The MakeID would be a link if you had cascading combo boxes in the form. So from a form the user would select a Make (cboMake), which would then break down the Models (cboModel). Based on the selection made by the user for the model the code in the command button would compare the model selected in cboModel to the model in tblModel and return the hyperlink (field 4) so that when the user clicked the command button he would see a pdf for that model.

    Thanks in advance
    Cory Hall - cory_r_hall@yahoo.com

    ReplyDelete
  4. Yes, this is possible. Sample Code is given below:

    Private Sub cboModel_Click()
    Dim myhype, xy As String, xcboModel
    Dim db As Database, rst As Recordset

    xcboModel = Me![cboModel]

    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblModel", dbOpenDynaset)

    rst.FindFirst "ModelID = " & Chr$(34) & xcboModel & Chr$(34)

    If Not rst.NoMatch Then
    Set myhype = rst![Hlink]
    xy = HyperlinkPart(myhype, acAddress)
    Me.cmdPDF.HyperlinkAddress = xy
    End If

    rst.Close
    End Sub

    The above Code Runs when you Click on the cboModel ComboBox. Searches the Table for the Model Value selected in the Combo Box and when found retrieves the HyperLink Value into myhype Variable. The HyperLinkPart() Function extracts the .pdf file pathname from the Hyperlink Field. The extracted Path is inserted into the HyperLink Address Property of the cmdPDF Command Button. When you Click the Command Button the PDF File will be opened.

    The Hyperlink Field Value in the tblModel must be set in the following format:

    Display Text#pdf File PathName##Tooltip Text

    Example:

    Model Document#C:\My Documents\Lexus.pdf##Click

    You may visit the following link to learn how to refresh a ComboBox contents that depends on another ComboBox value selection:

    http://www.msaccesstips.com/2008/03/refresh-dependant-combo-box-contents/


    Regards,
    a.p.r. pillai

    ReplyDelete
  5. The code works great, thanks a lot. Was just going to ask about two lines:

    Set rst = db.OpenRecordset("tblModel", dbOpenDynaset)
    rst.FindFirst "ModelID = " & Chr$(34) & xcboModel & Chr$(34)

    I know if you have spaces in your titles such as Model ID instead of ModelID, you usually put the name in brackets such as [Model ID], but it seems for both tblModel and ModelID above it gives debug errors when you bracket them.

    ReplyDelete
  6. You don't need to put [] in the first statement, even if there is space in the Tahle Name.

    But, if there is space in the ModelID Field Name then you must insert the square brackets. When you do see that you are not adding extra spaces immediately after or before the brackets.

    Regards,

    ReplyDelete
  7. With the following code, I'm trying to get a bit fancier and embed the pdf within the form. I'm using a microsoft web browser object to do this, but when i click the cmdPDF button it just shows a page that can't find the hyperlink. acxWebBrowser is the name of the web browser object.

    Private Sub cboModel_Click()
    Dim myhype, xy As String, xcboModel
    Dim db As Database, rst As Recordset

    xcboModel = Me![cboModel]

    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblModel", dbOpenDynaset)

    rst.FindFirst "ModelID = " & Chr$(34) & xcboModel & Chr$(34)

    If Not rst.NoMatch Then
    Set myhype = rst![Hlink]
    xy = HyperlinkPart(myhype, acAddress)
    Me.acxWebBrowser.Navigate "xy"
    End If

    rst.Close
    End Sub

    ReplyDelete
  8. Remove the Quotes from Me.acxWebBrowser.Navigate "xy"

    Me.acxWebBrowser.Navigate xy

    and try again.

    ReplyDelete
  9. Thanks for the idea, can you send me to nurulmarea@gmail.com the example of the database so that I can get the clear idea. Thansk and regards

    ReplyDelete
  10. I will forward a Demo Database to you shortly.

    Regards

    ReplyDelete
  11. Hello,

    Can I pass argument inside hyperlink?
    For Example:
    When I open hyperlink in edit mode looks like
    "Address: #Form Employees"
    Can I open form Employees to display specific Employee?
    Something like:
    Docmd.OpenForm Employees,WhereCondition:="[ID]="&51

    ReplyDelete
  12. Parameter Values cannot be passed through HyperlinkSubAddress. You have already shown the example through the Docmd.OpenForm method.

    ReplyDelete
  13. Hello again,
    Is there way to call Public Function or Public Sub with Hyperlink?

    ReplyDelete

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

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 ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button 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