Create hyperlink to open Access form

[mlai08]

I am investigating on the possibility of setting up an approval process for our employee intake procedure by using MS Access and Outlook.

The idea is for the requester to open an intake form in Access and fill in the new employee's information and send an Outlook email to the manager for approval. I know how to trigger off an event on the form to send an email to the manager but my challenge is how to create a hyperlink in the email for the recipient to directly open the intake form with the specific record, without the need to open the database and find the specific intake form.

The intake form is built in a frontend .mde file on a shared drive where all users have access to.

Any guidance/comments on how to achieve this would be much appreciated.

 

[spikepl]

I'm not sure you can do this with a hyperlink, but take this step by step. You can open an Access database file with a command line parameter, so it could open on some specific item. Google command line parameters for Access.

See if you can invoke that using hyperlink - i have my doubts, but perhaps you are lucky and I'm wrong.

In Outlook you can for sure add a menu button that could grab text from an email and open an Access database. Installing it for everybody is a little bit of a pain though, but doable.

 

[spikepl]

This thread has a similar aim: http://www.access-programmers.co.uk/forums/showthread.php?t=279013

 

[vbaInet]

A hyperlink would be very unsafe if it can do that. I've just had a quick look and there doesn't seem to be a way of doing it via a hyperlink.

What you can consider however is to create a db on the server that has access to the other person's machine. From this db you'll create a shortcut on the other person's machine which will use the command parameter spikepl mentioned. The hyperlink in the email, when clicked, would open that shortcut.

 

[mlai08]

A hyperlink would be very unsafe if it can do that. I've just had a quick look and there doesn't seem to be a way of doing it via a hyperlink.

What you can consider however is to create a db on the server that has access to the other person's machine. From this db you'll create a shortcut on the other person's machine which will use the command parameter spikepl mentioned. The hyperlink in the email, when clicked, would open that shortcut.

Yes, I can create a shortcut to pass the command line parameter to open a specfic form but it does not work in my case. The idea is for the user to receive an email with a hyperlink containing a variable, i.e. an ID to open a form with a specific record. As such setting the hyperlinik to the shortcut with a static parameter wouldn't work.

 

[speakers_86]

I think you could make this happen if you know how to write a Windows service. Something that watches for a click of a particular hyperlink. I've never done it, but maybe.

 

[spikepl]

Now try to again read carefully and this time understand vbaInet's answer, because you are just repeating your original requirement, which was perfectly understood by both me and vbaInet.

 

[vbaInet]

Now try to again read carefully and this time understand vbaInet's answer, because you are just repeating your original requirement, which was perfectly understood by both me and vbaInet.

Looks like mlai08 wasn't thinking out of the box when s/he read my post so I'll expand a little.

* User1 on FE1 wishes to send an e-mail with a hyperlink to User2
* FE1 sends a "hyperlink" creation request to delegate db (DbDelegate). The request contains the form name and the unique ID
* DbDelegate creates a shortcut on User2's PC which contains the path to User2's front end, the form name and the unique ID - with the last two contained within the command parameter. The parameter is handled when the db/form is opened.
* DbDelegate reports back to FE1 that the shortcut was successfully created
* FE1 proceeds to send the email with a hyperlink of the shortcut that DbDelegate created.
* User2 proceeds to open the e-mail. S/he clicks the link and is re-directed to the db > the form > and the record.

Here's another idea:

Instead of sending emails why not send messages to the other user's FE (front end). That way it will be much easier to redirect the user to the form and move to a specific record. You would be mimicking a messaging/queuing system. I would advise that you also use a delegate db for this too, which will handle all message requests between dbs.

And another idea:

Create an Outlook add-in or function that will interpret hyperlinks in emails that contain a specific Subject.

 

[mlai08]

Thank you for everyone's comments and suggestions. I might not have made my requirements clear enough.

Under vbaInet's example, is the shortcut creation on User2's PC an automated process? There are numerous User2 who are managers in our organization. We would try to avoid their involvement in any manual process to install FE and shortcut on their PC. All we would want is for them to just click a link in an email to open a specific form record and click an approve button on the form to approve the request. I am unsure if this is possible by using the shortcut solution unless somehow we could make the process automatic behind the scene.

The option of interpreting email hyperlink with Outlook Add-in and fucntion sounds interesting. vbaInet, can you please elaborate on it and provide VBA coding samples on how to get this done?

 

[vbaInet]

I might not have made my requirements clear enough.

... you are just repeating your original requirement, which was perfectly understood by both me and vbaInet.

It was clear as mud!

All of what has been suggested is meant to be automated. It's now down to you to research the ideas, start developing the process in code, ask specific questions and we can only quide you.

The Outlook option would be best served in an appropriate Outlook VBA forum.

 

[mlai08]

I have applied codes found in the internet to create a shortcut from the Intake request form as below, using a click event for testing purposes. However, I got an error "invalid procedure call or argument". Can you please advise what I have done wrong? The ClientDirectory path points to a dedicated database in the server for the manager to open the specific form for approval.

Public Sub test_Click()
Dim WSHShell As Object
Set WSHShell = CreateObject("WScript.Shell")
Dim AccessPath As String
Dim MyShortcut As Object
Dim DesktopPath As String
Dim ClientDirectory As String
Dim Workfield As String
Dim FormID As Integer
 
On Error GoTo Err_test_Click
 
' Read desktop path using WshSpecialFolders object
DesktopPath = WSHShell.SpecialFolders("Desktop")
AccessPath = """C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE"""
ClientDirectory = """\\officeblrgrp2.office.adroot.bmogc.net\Int_Tech\Technology Resource Repository BE\Intake Test.mdb"""
FormID = Me.IntakeID
Workfield = AccessPath & " " & ClientDirectory & "/cmd " & FormID
 
' Create a shortcut object on the desktop
Set MyShortcut = WSHShell.CreateShortcut(DesktopPath & "\IntakeForm.lnk")
 
' Set shortcut object properties and save it
MyShortcut.TargetPath = WSHShell.ExpandEnvironmentStrings(Workfield)
MyShortcut.WorkingDirectory = WSHShell.ExpandEnvironmentStrings("C:\Program Files (x86)\Microsoft Office\Office14\")
MyShortcut.WindowStyle = 4
MyShortcut.IconLocation = WSHShell.ExpandEnvironmentStrings("C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE, 0")
 
MyShortcut.Save
Set MyShortcut = Nothing
Set WSHShell = Nothing
Exit_test_Click:
    Exit Sub
Err_test_Click:
    MsgBox Err.Description
    Resume Exit_test_Click
End Sub

Also, once the shortcut is generated, how can I programmatically put it in a hyperlink to be sent to the manager for approval. Would it work if I put <a Href=MyShortcut>?

Thank you for your help.

 

[vbaInet]

To debug your code, comment out the On Error GoTo line.

But before you start writing code have you done the basic manual tests? I.e. creating a shortcut on the manager's machine that opens the form to the specific record?

 

[mlai08]

The error applies to

MyShortcut.TargetPath = WSHShell.ExpandEnvironmentStrings(Workfield)

I manually ceated a shortcut on my desktop and it can open the form with specific record # 2

Shortcut properties:
Target = "C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE" "\\officeblrgrp2.office.adroot.bmogc.net\Int_Tech\Technology Resource Repository BE\Intake Test.mdb" /cmd 2

Start in = "\\officeblrgrp2.office.adroot.bmogc.net\Int_Tech\Technology Resource Repository BE\"

 

[vbaInet]

Good! Doing the manual process makes you appreciate how everything will eventually come together. I have some other advice related to managing the shortcuts which I'll mention much later.

WorkField is not a valid environment string. An environment string would be something like TEMP. For example, if you want to create the shortcut in the TEMP folder on the target machine, the ExpandEnvironmentStrings() method will return the full path to that directory. Consider creating the shortcut in the sub-folder where the Front End is located.

 

[mlai08]

I changed the codes and replace workfield with the actual path but it still does not work. No error but no shortcut is created on my Desktop. I could direct the shortcut to another folder once it works on my Desktop.

Set MyShortcut = WSHShell.CreateShortcut(DesktopPath & "\IntakeForm.lnk")
 
' Set shortcut object properties and save it
MyShortcut.TargetPath = ("C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE" & " " & "\\officeblrgrp2.office.adroot.bmogc.net\Int_Tech\Technology Resource Repository BE\Intake Test.mdb" & "/cmd 2")
 
MyShortcut.WorkingDirectory = ("C:\Program Files (x86)\Microsoft Office\Office14\")
 
MyShortcut.WindowStyle = 4
 
MyShortcut.IconLocation = ("C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE, 0")
 
MyShortcut.Save
Set MyShortcut = Nothing
Set WSHShell = Nothing

 

[vbaInet]

I'm not on a pc at the moment but check the DesktopPath variable to ensure that it's returning a value, then step through each line as it runs.

 

[mlai08]

I got this shortcut target path under the Target properties "C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE \officeblrgrp2.office.adroot.bmogc.net\Int_Tech\Technology Resource Repository BE\Intake Test.mdb\cmd 2" when using the following code:

MyShortcut.TargetPath = ("C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE" & " " & "\\officeblrgrp2.office.adroot.bmogc.net\Int_Tech\Technology Resource Repository BE\Intake Test.mdb" & "/cmd 2")

However, the shortcut will only work if the Target properties is set to:
"C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE" "\\officeblrgrp2.office.adroot.bmogc.net\Int_Tech\Technology Resource Repository BE\Intake Test.mdb"/cmd 2 on my manually created shortcut.

How can I properly use quotation marks in the MyShortcut.TargetPath to generate the same Target path in the manually created shortcut? I tried putting quotation marks in different section of the path without success.

 

[speakers_86]

chr(34) is the ASCII code for double quotes. You can also use """ to do the same thing.

 

[mlai08]

Yes, I know that. My challenge is how to get rid of the last " on the Target path under the shortcut properties. The coding path has to always in the format of ("......") and must end with " to make the code works.

 

[speakers_86]

MyShortcut.TargetPath = chr(34) & "C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE [URL="file://\\officeblrgrp2.office.adroot.bmogc.net\Int_Tech\Technology"]\\officeblrgrp2.office.adroot.bmogc.net\Int_Tech\Technology[/URL] Resource Repository BE\Intake Test.mdb /cmd 2" & chr(34)

I would think it would be like this.