Page 1 of 1

Hyperlink buttons for files, emails and other URLs

PostPosted: Tue Feb 06, 2018 9:49 pm
by Villeroy
2018-02-14: Error handler for empty/invalid file paths. Macro sets the "Action" property so you don't have to care about it.
2018-02-17: Error handling is one of my weak points. Now it ignores any wrong row cursor position.

This forum's user Nocton inspired me with a small Basic snippet. A previous version of this macro was availlable in this topic but it was more difficult to understand and to set up. Nocton demonstrated how to change the URL of a push button and open the new URL with one click on that button.

The attached database demonstrates how to deal with files and paths in system notation and with missing protocol, e.g. mail addresses stored without mailto: prefix and www without http: prefix.
The Basic macro embedded in the attached database document is not the code which is triggered by the form buttons. It is a macro to install Python code to the global scope. Store the attachment in a "trusted directory" according to the macro security settings in Tools>Options>Security, button [Macro Security...] With high security level you can add trusted directories where you store trusted documents with embedded macros.

Install the Python macro
Open my database.
Open the form named "PythonCode". The form contains the Python code supposed to handle your URL buttons and a push button.
Push the button on that form. The installer asks you for permission to save the text in your user profile.
Simple use with a table column of fully qualified URLs starting with some protocol: prefix
such as file:///Path/
1) Open your own database form where you want to add some URL button.
2) Add a push button. Name does not matter.
3) Set the "additional info" property to the name of the column (not the name of any form control) where you stored the URLs. If the form's underlying table or query shows the URL in column "Web" then enter "Web" as additional info. It is not necessary to add any form control showing the URL.
4) Go to the Events tab of the properties dialog and assign a macro to the event "Approve Action".
Click the ellipsis [...] and naivgate to "My Macros" > pyDBA > URLButton > URLButton_Approve. This is the macro which will fill out the button's URL with the current value of the specified field before the button actually calls that URL.
More advanced use cases demonstrated in my database
If you have a column with file paths such as C:\path\picture.png, assign macro FileButton_Approve instead of URLButton_Approve. This variant will convert the system path to a file URL. My database stores file names in Data.Files and the path in Setup.Value where Setup.Name = 'FilePath'.
The query "qCompletion" in my demo database concatenates the dummy file names with a path specified in the setup table. You may enter your own path in Setup.Value. Just enter some folder name where you store pictures and/or PDFs with trailing \ or /. Then enter some actual file names into the "Data" table. Now the file button on my form should open the local files on your system.
If you have mail addresses, they are propably missing the mailto: prefix. Then you need a query with a calculated field which concatenates 'mailto:' with the naked mail address. Query "qCompletion" in my demo database does this conditionally where the mailto: is missing and it adds http:// to the www addresses where http: or https: is missing. Because the query combines 2 tables for the file path and the file names, this combined record set is not editable. Therefore I added a subform to the editable main form, bound this subform to the query, linked it to its parent form by the common ID column and added the push buttons to this subform. The form wizard can also create this combination of form and subform.
Every time you select another main form record from table "Data", the subform fetches from "qCompletion" the record with complete URLs and a full file path having the same ID. When you click a button, the macro assigns the URL from the column that has been specified by the "additional info".
The grey text boxes on top of my form show the URLs. They are not required for the button functionality. The buttons keep on working when you delete the text boxes.