Hyperlink Button - Need Help

[Guest]

Hi, i currently have the following code in the on click event of my button,

Private Sub Open_Rate_Program_Click()
Dim varURL As Variant
varURL = DLookup("[Program Location]", "Program Location", "[PROGRAM
LOCATION].[ID]=" & Me.ID)
If IsNull(varURL) Then
MsgBox "Sorry, this document does not exist!", vbOKOnly + vbInformation
Else
Application.FollowHyperlink varURL
End If
End Sub

I have a query that returns a list of: Record IDs, Company names, Program
Used, and Program Location. the query is supposed to reference 2 tables,
customers and base rates. I have a form that i input my customer info, and
have a combo box were i choose the base rate. I want the command button to
use the query, find the base rate, then the location of the file and open it.
The base rate table has the location field. any ideas?

 

[OldPro]

Hi, i currently have the following code in the on click event of my button,

Private Sub Open_Rate_Program_Click()
Dim varURL As Variant
varURL = DLookup("[Program Location]", "Program Location", "[PROGRAM
LOCATION].[ID]=" & Me.ID)
If IsNull(varURL) Then
MsgBox "Sorry, this document does not exist!", vbOKOnly + vbInformation
Else
Application.FollowHyperlink varURL
End If
End Sub

I have a query that returns a list of: Record IDs, Company names, Program
Used, and Program Location. the query is supposed to reference 2 tables,
customers and base rates. I have a form that i input my customer info, and
have a combo box were i choose the base rate. I want the command button to
use the query, find the base rate, then the location of the file and open it.
The base rate table has the location field. any ideas?

Okay, so you have a data input screen, and you want to look up the
proper base rate. Can't you just use a combobox?
What kind of file are you trying to open? It looks like you are
trying to open a website...

 

[Guest]

Thanks for such a quick response OldPro. I do have a combo box to select
which base rate to use when the customer file is first created, but i have a
details form that can't be edited, so that no changes are made by accident,
and i'm wanting a button other employees can just click and open the program
tied to the base rate that the record uses.

Hi, i currently have the following code in the on click event of my button,

Private Sub Open_Rate_Program_Click()
Dim varURL As Variant
varURL = DLookup("[Program Location]", "Program Location", "[PROGRAM
LOCATION].[ID]=" & Me.ID)
If IsNull(varURL) Then
MsgBox "Sorry, this document does not exist!", vbOKOnly + vbInformation
Else
Application.FollowHyperlink varURL
End If
End Sub

I have a query that returns a list of: Record IDs, Company names, Program
Used, and Program Location. the query is supposed to reference 2 tables,
customers and base rates. I have a form that i input my customer info, and
have a combo box were i choose the base rate. I want the command button to
use the query, find the base rate, then the location of the file and open it.
The base rate table has the location field. any ideas?

Okay, so you have a data input screen, and you want to look up the
proper base rate. Can't you just use a combobox?
What kind of file are you trying to open? It looks like you are
trying to open a website...

 

[OldPro]

Thanks for such a quick response OldPro. I do have a combo box to select
which base rate to use when the customer file is first created, but i have a
details form that can't be edited, so that no changes are made by accident,
and i'm wanting a button other employees can just click and open the program
tied to the base rate that the record uses.

Okay, I still need more to go on... when you say "open the program
tied to the base rate", what do you mean? What kind of program? Is
it a form? What does the form do? Is it an edit screen? Is it some
sort of Windows program that you are shelling to? Or is it a
webpage? What exactly is stopping you from doing what you want to do?

 

[Guest]

Sorry, it is a windows program. Right now I have 2 main tables,
Customers, which holds all my customer data, and then Base Rates, which has a
list of the different base rates that our company uses, and the location of
the program on the network.
I have 2 forms tied to the customers, an add/edit form, and a details
form (which is where i want my button). When the customer is added in the
add/edit form i use a combo box tied to my base rates table to choose the
certain program we use(the combo box also shows up on the details form, but
just can't be edited).
What i don't konw how to do is have the command button on the details
form, use the value in the combo box to look up the location of the program
and open it for each customer record.
I've tested to see if the programs open from the locations i have in the
base rates table, so i know they work. Thanks again for the help.

Customers Table Base Rate Table
ID Base Rate Name
Company Name Program Location
Base Rate Name
Etc...
(Base Rate Name is tied together under DataBase
Relationships)

 

[OldPro]

Sorry, it is a windows program. Right now I have 2 main tables,
Customers, which holds all my customer data, and then Base Rates, which has a
list of the different base rates that our company uses, and the location of
the program on the network.
I have 2 forms tied to the customers, an add/edit form, and a details
form (which is where i want my button). When the customer is added in the
add/edit form i use a combo box tied to my base rates table to choose the
certain program we use(the combo box also shows up on the details form, but
just can't be edited).
What i don't konw how to do is have the command button on the details
form, use the value in the combo box to look up the location of the program
and open it for each customer record.
I've tested to see if the programs open from the locations i have in the
base rates table, so i know they work. Thanks again for the help.

Customers Table Base Rate Table
ID Base Rate Name
Company Name Program Location
Base Rate Name
Etc...
(Base Rate Name is tied together under DataBase
Relationships)

What type of form is it? Normal, Continuous, or Datasheet? If it is
a normal form, why can't you add a command button? It if is a
Continuous form or a Datasheet, then you will have to make it a
subform, and place it on a normal unbound form.
Is the field with the path and filename displayed on the screen? It
doesn't have to be to reference it. Here is an example of the code to
put in the command button. I'll use my own fieldnames, so you will
have to change them to match. I don't allow spaces in my table names
or field names... less headache.

Dim db as dao.database
dim sTableName as string
dim sFileName as string
dim sWhere as string
dim vRetVal as Variant
set db=currentdb( )
sTableName="BaseRate"
if Not isnull(cmbBaseRate) then
sWhere = "[BaseRateName]=' " & cmbBaseRate & " ' "
sFilename=dlookup("ProgramLocation",sTableName,sWhere)
vRetVal=Shell(sFilename,1)
endif
db.close

 

[Guest]

OldPro thanks again, but i've yet to get that to work. I've tried multiple
variations, but to no avail. This morning i went back to the code i was
origionally working with and tweaked it a bit. Here is where i'm at right
now.

Private Sub Open_Rate_Program_Click()
Dim sFileName As String
sFileName = DLookup("[ProgramLocation]", "BaseRatesE", "BaseRatesE.[ID]="
& [ID])
If IsNull(sFileName) Then
MsgBox "Sorry, this document does not exist!", vbOKOnly + vbInformation
Else
Application.FollowHyperlink "sFileName"
End If
End Sub

I went away from the combo box because it was posing too many problems. Now
i just created a query to do the work, and i'm pulling the location from it
with the customer ID's. It acts like it wants to work, because when i press
my button I get the microsoft office access security notice about opening
hyperlink, but it doesn't do anything after i hit yes. If i replace
Application.FollowHyperlink "sFileName" with Application.FollowHyperlink
"\\fileserver\bh\......." i can open a specific exe, do you notice anything
wrong with my DLookUp or have any other ideas? Thanks again.

 

[OldPro]

OldPro thanks again, but i've yet to get that to work. I've tried multiple
variations, but to no avail. This morning i went back to the code i was
origionally working with and tweaked it a bit. Here is where i'm at right
now.

Private Sub Open_Rate_Program_Click()
Dim sFileName As String
sFileName = DLookup("[ProgramLocation]", "BaseRatesE", "BaseRatesE.[ID]="
& [ID])
If IsNull(sFileName) Then
MsgBox "Sorry, this document does not exist!", vbOKOnly + vbInformation
Else
Application.FollowHyperlink "sFileName"
End If
End Sub

I went away from the combo box because it was posing too many problems. Now
i just created a query to do the work, and i'm pulling the location from it
with the customer ID's. It acts like it wants to work, because when i press
my button I get the microsoft office access security notice about opening
hyperlink, but it doesn't do anything after i hit yes. If i replace
Application.FollowHyperlink "sFileName" with Application.FollowHyperlink
"\\fileserver\bh\......." i can open a specific exe, do you notice anything
wrong with my DLookUp or have any other ideas? Thanks again.

Yes, a string variable can't hold a NULL value. Redefine it as a
Variant, or use NZ( ) to force an empty string when there is a NULL.
Also, is ID a numeric field? If so, then this should work. If not,
you will need to surround it with single quotes as in my previous
example. One more thing - for this code to work, there has to be data
showing on the screen in the ID textbox.
If you get an error, do a debug and see which line is giving the
error. You can also trace through the lines one at a time and
position the cursor over variables to check their value. To do this,
position the cursor over one of the lines in edit mode and click F9.
Then run the form. When it gets to that line, it will stop. Then,
using F8, you can move one line at a time and see what is happening as
it happens.