Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Editing Hyperlink Address Manually

Introduction.

Hyperlinks are everywhere in Internet. We have them in Microsoft Access too. It can be used to open Objects (Tables, Forms, Reports or Queries) by clicking on a  hyperlink, without writing a single line of VBA Code or Macro. Hyperlinks can be created in Tables or Forms. You can create menus to open Forms, Reports etc. You can open external files, like Word, Worksheets, .PDF, text and others with the use of Hyperlinks in Microsoft Access.

Hyperlinks in Access can be created or edited manually by entering it's link segments in proper order, in table field or on Form controls. Normally, editing can be done through a dialog box, displayed when right-clicked on the hyperlink control.


4 Segments of Hyperlink-Address in MS-Access.

Hyperlink address line is divided into four segments:

  1. Display Text : the text that appears in a field or control, indicating the hidden link's purpose
  2. Address : external file's path-name
  3. Sub-Address : Internal Object Name, to open Form, Report etc.
  4. Screen-tip : the text displays as a tool-tip.


Hyperlink Syntax is as follows:

Display Text#Address#Sub-Address#Tool-tip text

Hyperlink Data type field can store a maximum of 2048 characters.

You can find more details and usage of Hyperlinks in the earlier Post: Open Forms with Hyperlinks in Listbox.



Editing Hyperlink Manually.

Let us get into the manual editing of Hyperlink topic:

We will create a sample table with an Hyperlink field to try out the manual editing exercise.

  1. create a Table with a single field and data type Hyperlink.
  2. Save the table with a name.
  3. Open the table in datasheet view.

    If you know how to enter Hyperlink data into the field manually, you may do so, by following the hyperlink syntax shown below.

    Example: My Excel File#C:\\\My Documents\Workbook1.xls##Click

  4. After entering the sample hyperlink in the field, open the table in datasheet view.

    You will see that the hyperlink field is active and the only text showing is the Display Text (My Excel File) part of the hyperlink information.

  5. Move the mouse-pointer over the field, it changes the Mouse-Pointer to a hyperlink pointer (a Hand with index finger).

    If you click on the field to edit the hyperlink information, it will only open the document specified in the second segment.

    If you right-click on the field the shortcut menu, which carries the hyperlink editing options in a dialog box is displayed.  You may key-in values at appropriate controls to change the hyperlink values.  You cannot key-in values into the hyperlink field directly.



    Simple Trick to Edit Hyperlink Manually.

    But, we can manually edit the field with a simple trick.

  6. Close the Table for now.
    • Click on Office Button.
    • Select Access Options.
    • Select Advanced.
    • Find the Behavior Entering Field options.
    • Select Go to end of field radio button.
    • Click OK to close the Access Options control.
  7. Open the Table after completing the above steps.

You can see that the insertion point is positioned at the end of the field value, exposing the hyperlink information. Move the insertion point back to the location where you need editing the field.

If you have more than one record to edit then change them and reset Behavior Entering Field option to Select Entire Field.

TIP:

If you would like to show the full Pathname of the File as display text then copy the second segment hyperlink value and paste it in the display text position also.



Stand-alone Label Controls Have Hyperlink Properties.

The Label control on Form can be used as a hyperlink control.  The label control have Hyperlink Address & Hyperlink Sub-Address properties, use Caption property to set the Display Text value of hyperlink. Set the Special Effect property value to Raised to make it look like a Command Button.

Share:

Alphabetized Customer List

Introduction.

If you are new to Microsoft Access Report designing, then this simple Alphabetized Customer List preparation Tutorial will give you enough insight into what it takes to design a simple report.

We will need the following few steps to complete our task:

  1. Prepare Customers' source data in a SELECT Query for the report.
  2. Open a new report in Design View.
  3. Insert the SELECT Query name into the Record Source Property of the Report.
  4. Use Data Grouping and Sorting option of the Report to organize and display A, B, C  etc. in the Group Header.
  5. Design the Report.
  6. Preview the Report.

A Sample Report.

Sample alphabetized list of customers Report Preview is given below:


Designing A Report.

Get Some Sample Data.

But, first we need some ready-made sample data for our Report

Let us start with importing Customers Table from Northwind.accdb sample database.

  1. Click on External Data Menu.
  2. Click on Access Tool button to display the Import control dialog box, to specify the Source and destination of data.
  3. Click on Browse... button to locate the Northwind.accdb sample database, Select the file and click Open.

    The selected file pathname is inserted into the File Name control on the dialog box.

    The first option is already selected as default, to import one or more required Access Objects from the selected Access database.

  4. Click OK to open the selected Access Database and expose it's Tables, Queries, Reports etc.
  5. Click on the Tables Tab, select the Customers table and click OK to import the selected table.

    Next step is to create a SELECT Query using Customers table as Source.

  6. Click on the Create menu and select Query Design from the Other group.
  7. Click Close button to close the Show Table dialog box, without selecting any object from the displayed list.
  8. You will find the SQL View option at the extreme left of the Tool bar and select it to display the Query's SQL editing window.

    You will find the SQL statement SELECT; in the window.

  9. Copy the SELECT Query Code given below and paste it into the SQL window, over-writing the existing SELECT; statement.
    SELECT Left([First Name],1) AS Alpha, [First Name] & " " & [Last Name] AS CName
    FROM Customers
    ORDER BY Left([First Name],1), [First Name] & " " & [Last Name];
    

    As you can see in the above SQL string we have only two columns of data. First column have only one character in all rows, the left-most character taken from the First Name of the customer. The first column name on the query is given as Alpha.

    We have used MS-Access built-in string function Left() for this purpose. There are other useful functions in this category, like Right(), Mid() and others.

    Second Column of data is the name of the Customer, with First Name, Last Name joined together, separated with a space. The second column's name is given as CName. When you use an expression to create the query column data it is better to give a simple name to the column so that it is easy to memorize and use it on the Report or Form.  If you don’t give a suitable name then MS-Access will give names like Expr1, Expr2 (Expression 1, Expression 2 and so on) by default.

    In the ORDER BY clause of the query both the columns are sorted in Ascending (A-Z) Order, first on Alpha column and then on CName column.

  10. Save the Query with the name Customer ListQ.
  11. Open the Customer ListQ in data sheet view and check the data.

    Sample image of what we are going to create is given below for reference:


The Design Task.

Let us design the Report

  1. Select Report Design from the Create menu.

    An empty Report is open in Design View with it’s Property Sheet. The first priority is to define our Customer ListQ Query as the Record Source of our report. If the property sheet is not displayed then click on the Property Sheet tool bar button to display it.

  2. Select the Data Tab on the Property Sheet.
  3. Click on the Record Source property and click on the drop-down list at the right-end of the property.
  4. Find Customer ListQ Query (use the slider, if necessary) and select it from the drop-down list, to insert it into the Record Source property.
  5. Click on Group & Sort Toolbar button from Group & Totals Group under Design Menu, if it is not already in selected state, to display the Group and Sort controls under the Footer of the Report.
  6. Click on the Add a Group control displayed in the Group, Sort and Total shown below the empty report.
  7. Click on the Alpha column name displayed from the Query columns list.

    You can see the Alpha Group Header is now appearing between Page Header and Detail Sections of the empty report.

    We must sort the customer names appearing under a particular character group (say A,B,C... group) so that they will appear in proper order as well. 

    Note: We have sorted the data in the Query by defining ORDER BY clause, but we must define Grouping and Sorting on the Report also to make them appear in proper order on the Report as well.

  8. Click on Add a Sort control and select CName from the list.

    Now, let us create the Heading of the Report, Group Heading (A, B, C... etc.) and customer names list to appear under each group.

  9. Click on Label control to select it and draw a rectangle, wide enough to write the heading text "CUSTOMER LIST", select Bold and Italic formatting styles and change the font size to 16.
  10. Select the Text Box control and draw a text box on the Alpha Header Section of the report.
  11. Click on the Data Tab on the Property Sheet and select Alpha from the Control Source drop-down list. Change the font-style to Bold and character size to 16.
  12. Select the child lable of the text box and delete it.
  13. Create another text box in the Detail Section of the Report, below the Alpha Header control.
  14. Select CName column name from the drop-down list in the Control Source property under Data Tab on the property sheet of the text control.
  15. Reduce the Detail Sectoin height by dragging the Page-Footer section bar up to touch the text box's edge.
  16. Save the report with the name: Customer List.

    Print Preview the Report.

  17. Open the Customer List report in print preview and see how it looks.

If the Heading, Group heading and customer list are not properly aligned to the left in your report, as shown in the first image on the top, try to align all the controls to the left.

Share:

Archiving Master Data

Introduction.

Over a period of time the Master Table of our database can grow into large volume, having thousands or millions of records. Maximum logical size of an Ms-Access Database is  only 2GB.  Everyday Repairing and Compacting may not improve the performance of the database. Processing time of reports or running time of Queries will  keep on increasing.

Majority of old records in the main table may not involve in daily or monthly querying or report preparing processes.  But, old records may involve in year-end processes, like history analysis, next year business target setting and so on.

Normally, old records of master table are set with a flag indicating as archived and retained in the master table itself.  Active records are filtered from the master table for reports or querying to monitor the current activities of business functions.  The filtering and sorting process may take more time, every time, due to large volume of data in the main table.


Maintaining Data of Earlier Period.

Removing the old data from the main table and keeping them in a separate database will improve the active database’s performance.  The old data in archive.accdb (a name for reference) can be easily made available to the Front-End database for year-end processes.

You don't need to link and keep the archived table to the Front-End database permanently. You can combine the archived data with the active master table in a Union Query, by using direct references to the archived table, rather than keeping it linked to the FE database.  The Union Query having combined data of both the tables can be used as source for year-end processes.  You can find more details on Union Queries here.

But first, let us see, how we can safely transfer the old data from the master table (tblMaster)  to a new archive database: archive.accdb. Here, we assume that we have a Front-End, Back-End configuration of databases.


The Prelude of our Action Plan.

We need the following steps to complete the process:

  1. Create a new Access Database: archive.accdb in location C:\mdbs or in a location of your preference.

    Note:If you are connected to a common Network Server then create a folder there/use an existing folder, where you have access-rights, and save archive.accdb there. The database will be backed-up regularly by network administrators and will be safe for recovery, if need arises. Click on the link to learn more about Database Daily Backup.

  2. Close archive.accdb database.
  3. Open the Back-End Database.
  4. Transfer only the Structure of tblMaster into archive.accdb
  5. Create a SELECT Query on tblMaster with appropriate criterion to select the old data.
  6. Open the Select Query in Datasheet View, take the total record count and note it down.
  7. Change the SELECT query into an Append Query.
  8. Save and run Append Query to transfer the selected data directly to the archive.accdb database into table tblMaster.
  9. Close BE database and open archive.accdb database.
  10. Open tblMaster in archive database and check the count of records matches with the count taken earlier. If not, investigate the cause and redo from step 5 onwards, after deleting the wrong data in tblMaster of archive.accdb.
  11. Close the archive.accdb and open BE database.
  12. Create a Delete Query using tblMaster with the same Criteria you have used in the Append Query.
  13. Open the Delete Query in Datasheet View and take the count of records and ensure that it matches with the count you have taken earlier.
  14. Run the Delete Query to remove the records from tblMaster table from BE database.
  15. Run Repair and Compact option to reduce the database size.
  16. Close BE database and open FE database.

    Linking Old Data to Front-End (FE) Database.

  17. Create a Union Query to combine data from tblMaster in BE and from tblMaster in Archive.accdb.

Let us Execute the above Defined Plan.

Step-1 to step-3 are self explanatory.

In step 4: Right-click on tblMaster to display a shortcut menu.

  • High-light Export option and select Access Database from the displayed menu.
  • Click on Browse button and select the archive.accdb database and click Save to come back to the Export dialog box.
  • Click OK to open up the Export Options dialog box.
  • Select Definition Only option to transfer the tblMaster Table Structure into Archive.accdb.

Step-5: sample select Query Code:

SELECT tblMaster.*
FROM tblMaster
WHERE (((tblMaster.mstDate)<Dateserial(1981,1,1)));

The above criteria will select all the records of 1980 and earlier period.

Step-6 is self explanatory.

Step-7: Open the Query, created in step-5, in design view.

  • Click on the Append Query button on the Tool bar.
  • Select tblMaster from the Table Name drop-down control on the dialog box.
  • Select Another Database Radio Button.
  • Click on Browse… Command Button to find the archive.accdb database, select it and click OK to come back to the dialog box in Query Design View.
  • Click OK on the dialog box to change the Select Query to Append Query. Sample append query SQL is given below for reference.
    INSERT INTO tblMaster IN 'C:\mdbs\archive.accdb'
    SELECT tblMaster.*
    FROM tblMaster
    WHERE (((tblMaster.mstDate)<DateSerial(1981,1,1)));
    
  • Open Append Query in Datasheet View and check the record count with the count you have taken earlier.
  • If both count matches then save the Append Query.

Step-8: Right-Click on the Append Query and select Open to run the Query to extract selected data from tblMaster table and to append them in the archive.accdb tblMaster table.

  • Click Yes Command Button, on the warning message control, to reconfirm the action.

Step-9 to Step-11: Self explanatory.

Step-12:  Sample Delete Query SQL is given below:

DELETE tblMaster.*, tblMaster.mstDate
FROM tblMaster
WHERE (((tblMaster.mstDate)<DateSerial(1981,1,1)));

Step-13 to Step-16: Self explanatory.

Step-17: Sample Union Query SQL is given below:

SELECT tblMaster.* 
FROM tblMaster
UNION ALL SELECT tblMaster.*
FROM tblMaster in 'C:\mdbs\archive.accdb';

Save the Union Query with the name tblMasterQ. Use tblMasterQ as Source Data for all year-end processes or wherever you need all the data together.  For other purposes your database will run faster.

You can continue to transfer data, when they become old, into the archive.accdb and delete them from the BE database.  No other change required anywhere.

Share:

Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

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

Search

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email

Labels

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

Featured Post

Opening Access Objects from Desktop

Frequently Used Methods. Set the Form Name in Display Form Option of Current Database in Access Options . BIG DEAL!, this is the first t...

Labels

Blog Archive

Recent Posts