Execute Hyperlink

Theresag

New Member
Joined
Mar 6, 2007
Messages
29
I am trying to execute a hyperlink without making the user acutually do the work.
I have a simple table with two columns. One is a part #, the other is a network path to the documentation for that part. I would like the user to be able to input the part number, and upon entry, have the PDF file containing the documentation open.

It sounds simple, but I haven't been able to figure out how to go about it, and I have never written any VBA with Access, only Excel, so my feeble brain isn't helping me much today.

Thanks for any insights!
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Try this setup:

Turn the PartNo text box into a combo box (right-click in Design view, Change to > Combo Box)
Set the following properties:
Column Count -- 2
Column Widths -- 4;0
Row Source -- A query with the 2 fields from your lookup table, PartNo first.

After Update event --
Code:
   Application.FollowHyperlink PartNo.Column(1),,True

This assumes that the combo's name is PartNo. Change if not.
To create the ocde for an event, double-click the blank line so you see [Event Procedure], then click the ... button at the end of the line

Denis
 
Upvote 0

Theresag

New Member
Joined
Mar 6, 2007
Messages
29
Sorry, I'm missing something in the process:

Table1
PartNo, DocumentPath

Now, Query1 to pull both columns, with a Criteria Filter to allow entry of PartNo.
In the query:
"Turn the PartNo text box into a combo box (right-click in Design view, Change to > Combo Box)
Set the following properties:
Column Count -- 2
Column Widths -- 4;0"

Then enter the code below in the After Update field.

When I run the Query, nothing happens, except the part number I entered shows up in a datasheet view. Obviously I did not understand your instructions properly. Could you be so kind as to clarify? I apologize for not seeing my error!
 
Upvote 0

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Hi Theresa, if you haven't worked with combo boxes before they can be a bit confusing. Do this:
In Design view on the form, right-click the PartNo text box. Select Change To > Combo box, which will give you a drop-down box.
Now click the Properties, and go to the All tab. You will see the properties I referred to in that list. To populate the combo box with data, select the Row Source row and click the ... button. That brings up a query grid. Build the 2-column query as I mentioned; close the grid and save when prompted.
For the code, select the Events tab. Now, double-click the blank line next to After Update so you see [Event Procedure], then click the ... button at the end of that row. Paste in the code I provided, in the blank line between Private Sub ... and End Sub.

Now, when you pick a part number from the drop-down, the corresponding file will launch.

Denis
 
Upvote 0

Theresag

New Member
Joined
Mar 6, 2007
Messages
29
Brilliant! Thank you so much. Now I will impose upon you generosity with one more question. Once the user closes the PDF that launched, it returns to the form view, which is now in Edit mode. Trying to teach each user to hit Escape is daunting (and I'm sure they will forget!). Is there a way to, upon closing the linked document, have the form return to View mode?

Thanks so very much for all of your help!
 
Upvote 0

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Maybe one option is to move out of that combo box to another control. For example:

Code:
Private Sub PartNo_AfterUpdate()
    Me.FilePath = Me.PartNo.Column(1)
    DoCmd.GoToControl "FilePath"
    Application.FollowHyperlink FilePath, , True
End Sub

Denis
 
Upvote 0

Theresag

New Member
Joined
Mar 6, 2007
Messages
29
Denis,

Thank you for your help. I was not able to make sense of your suggestion, however. I'm in a bit over my head on this one. I've been asked to help create an elegant little interface whereby a user can input a part number using a barcoded sheet with a scanning wand, and the related documents in PDF format would open, with the user doing nothing else. I'm afraid nothing in my meager toolbag is going to be sufficient!

If you have any inclination to suggest something, I'll be most grateful, but I understand time constraints.

Thank you again!
 
Upvote 0

Forum statistics

Threads
1,190,558
Messages
5,981,688
Members
439,730
Latest member
gjvv

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top