Constructing Hyperlink from the Database Record fields

  • Thread starter Thread starter Umar
  • Start date Start date
U

Umar

I am working on a Windows XP environment using MS Office 2007 including
Access 2007. I want to open a document from Access 2007 which I can easily
do with Hyperlink type field. However since all the necessary information is
already in the Database Record I try to avoid creating additional field which
would be a Hyperlink type on the Form unless it is absolutely necessary.

Below is the code that I have to construct the FullFileName which consisted
of ServerName, Division, Unit, RequirementDirectory, FolderName and the
FileName itself. As you can see the Database records has all the necessary
information to construct the FullFileName.

The User is unsophisticated so I cannot expect them to open the document by
doing more than a click or a double-click of the mouse button on the FileName
field on the Form. If this cannot be done then I have to create another
field of Hyperlink type namely DocLink in the Form, as the code and the
comment indicated at the bottom of the code with that the User could click
Me.DocLink and that will open the document that the User need.

' Here is the code
__________________________________________________________________
Option Compare Database
Option Explicit

Private Sub FileName_DblClick(Cancel As Integer)

Dim stServerName, stDivision, stUnit As String
Dim stFullFileName, stReqDir, stFolderName, stFileName As String

Select Case Me.RequirementID
Case Is < 10
stReqDir = "\R000" & Me.RequirementID
Case Is < 100
stReqDir = "\R00" & Me.RequirementID
Case Is < 1000
stReqDir = "\R0" & Me.RequirementID
Case Else
stReqDir = "\R" & Me.RequirementID
End Select

stServerName = "\\MAINSERVER\USERSHARE"
stDivision = "\Division_Requirements"
stUnit = "\Branch_Unit"
stFolderName = "\" & Me.FolderName
stFileName = "\" & Me.FileName
stFullFileName = stServerName & stDivision & stUnit & stReqDir & _
stFolderName & stFileName
'
' After getting the Full File Name, the code to open the document should
go here
' The document or file could be .doc, .xls, .pdf, .txt, etc.
'
' OR
'
' I could create another field named DocLink with type Hyperlink in the Form
' and add the following code, but then the User has to click field DocLink to
' open the file, I try to avoid this additional step.

Me.DocLink = Me.FileName & “#†& stFullFileName & “#â€

End Sub

____________________________________________________________________

Any reply is appreciated.

Thank you,

Umar.
 
Hi Umar,

First, you should know that several of your variables are not declared as
you may assume:

Dim stServerName, stDivision, stUnit As String
Dim stFullFileName, stReqDir, stFolderName, stFileName As String

In this case, stUnit and stFileName "begin life" as string variables. The
others start as variants. You can prove this to yourself by running a little
experiment, using stServerName:

Private Sub Filename_DblClick(Cancel As Integer)
Dim stServerName, stDivision, stUnit As String
Dim stFullFileName, stReqDir, stFolderName, stFileName As String

Debug.Print "stServerName is: " & TypeName(stServerName)
stServerName = "\\MAINSERVER\USERSHARE"
Debug.Print "stServerName is: " & TypeName(stServerName)

End Sub

Check out the results in the Immediate Window. You need to explicitely
declare each variable. For example:

Dim stServerName As String, stDivision As String, stUnit As String

or, by having each declaration on a separate line:

Dim stServerName As String
Dim stDivision As String
Dim stUnit As String

~~~~~~~~~~~~~~~~~~~

The Select Case statement is not really necessary, as you can use the Format
function here:

Select Case Me.RequirementID
Case Is < 10
stReqDir = "\R000" & Me.RequirementID
Case Is < 100
stReqDir = "\R00" & Me.RequirementID
Case Is < 1000
stReqDir = "\R0" & Me.RequirementID
Case Else
stReqDir = "\R" & Me.RequirementID
End Select

Debug.Print "stReqDir via Select Case: " & stReqDir

stReqDir = "\R" & Format(Me.RequirementID, "0000")
Debug.Print "stReqDir via Format statement: " & stReqDir

~~~~~~~~~~~~~~~~~~~

I recommend adding a command button, with appropriate caption, such as
&Open File (the ampersand will cause the letter "O" to be an underlined hot
key, such that the user can use <Alt><O> instead of clicking their mouse. You
should be able to use the Application.FollowHyperlink method with a valid
filename. Here is a procedure that should get you started. I used the
lowercase "txt" prefix for the names of the text boxes. I have also added
some minimal data checking, and error handling. Something like this:

Option Compare Database
Option Explicit

Private Sub cmdOpenFile_Click()
On Error GoTo ProcError

Dim stServerName As String
Dim stDivision As String
Dim stUnit As String
Dim stFullFileName As String
Dim stReqDir As String
Dim stFolderName As String
Dim stFileName As String

'Check for required values
If Not IsNumeric(Me.txtRequirementID) Then
MsgBox "You need to enter a Requirement ID Number.", _
vbCritical, "Missing Required Data..."
Me.txtRequirementID.SetFocus
Exit Sub
End If

Debug.Print InStr(Me.txtFilename, ".")

If InStr(Me.txtFilename, ".") = 0 Then
MsgBox "You need to enter a Filename with Extension.", _
vbCritical, "Missing Required Data..."
Me.txtFilename.SetFocus
Exit Sub
End If

If Len(Me.txtFolderName & "") = 0 Then
MsgBox "You need to enter a Folder Name.", _
vbCritical, "Missing Required Data..."
Me.txtFolderName.SetFocus
Exit Sub
End If

stReqDir = "\R" & Format(Me.txtRequirementID, "0000")

stServerName = "\\MAINSERVER\USERSHARE"
stDivision = "\Division_Requirements"
stUnit = "\Branch_Unit"

stFolderName = "\" & Me.txtFolderName
stFileName = "\" & Me.txtFilename

stFullFileName = stServerName & stDivision & stUnit _
& stReqDir & stFolderName & stFileName

Application.FollowHyperlink stFullFileName

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error Opening File..."
Resume ExitProc
End Sub



Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________
 
Hello,
Thanks to you all, it work like a charm



Tom Wickerath wrote:

Hi Umar,First, you should know that several of your variables are not declared
02-Jan-10

Hi Umar

First, you should know that several of your variables are not declared a
you may assume

Dim stServerName, stDivision, stUnit As Strin
Dim stFullFileName, stReqDir, stFolderName, stFileName As Strin

In this case, stUnit and stFileName "begin life" as string variables. Th
others start as variants. You can prove this to yourself by running a littl
experiment, using stServerName

Private Sub Filename_DblClick(Cancel As Integer
Dim stServerName, stDivision, stUnit As Strin
Dim stFullFileName, stReqDir, stFolderName, stFileName As Strin

Debug.Print "stServerName is: " & TypeName(stServerName
stServerName = "\\MAINSERVER\USERSHARE
Debug.Print "stServerName is: " & TypeName(stServerName

End Su

Check out the results in the Immediate Window. You need to explicitel
declare each variable. For example

Dim stServerName As String, stDivision As String, stUnit As Strin

or, by having each declaration on a separate line

Dim stServerName As Strin
Dim stDivision As Strin
Dim stUnit As Strin

~~~~~~~~~~~~~~~~~~

The Select Case statement is not really necessary, as you can use the Forma
function here

Select Case Me.RequirementI
Case Is < 1
stReqDir = "\R000" & Me.RequirementI
Case Is < 10
stReqDir = "\R00" & Me.RequirementI
Case Is < 100
stReqDir = "\R0" & Me.RequirementI
Case Els
stReqDir = "\R" & Me.RequirementI
End Selec

Debug.Print "stReqDir via Select Case: " & stReqDi

stReqDir = "\R" & Format(Me.RequirementID, "0000"
Debug.Print "stReqDir via Format statement: " & stReqDi

~~~~~~~~~~~~~~~~~~

I recommend adding a command button, with appropriate caption, such a
&Open File (the ampersand will cause the letter "O" to be an underlined ho
key, such that the user can use <Alt><O> instead of clicking their mouse. Yo
should be able to use the Application.FollowHyperlink method with a vali
filename. Here is a procedure that should get you started. I used th
lowercase "txt" prefix for the names of the text boxes. I have also adde
some minimal data checking, and error handling. Something like this

Option Compare Databas
Option Explici

Private Sub cmdOpenFile_Click(
On Error GoTo ProcErro

Dim stServerName As Strin
Dim stDivision As Strin
Dim stUnit As Strin
Dim stFullFileName As Strin
Dim stReqDir As Strin
Dim stFolderName As Strin
Dim stFileName As Strin

'Check for required value
If Not IsNumeric(Me.txtRequirementID) The
MsgBox "You need to enter a Requirement ID Number.",
vbCritical, "Missing Required Data...
Me.txtRequirementID.SetFocu
Exit Su
End I

Debug.Print InStr(Me.txtFilename, "."

If InStr(Me.txtFilename, ".") = 0 The
MsgBox "You need to enter a Filename with Extension.",
vbCritical, "Missing Required Data...
Me.txtFilename.SetFocu
Exit Su
End I

If Len(Me.txtFolderName & "") = 0 The
MsgBox "You need to enter a Folder Name.",
vbCritical, "Missing Required Data...
Me.txtFolderName.SetFocu
Exit Sub

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
JavaScript - IFRAME Forms IE/NS
http://www.eggheadcafe.com/tutorial...98-55b6bfed4408/javascript--iframe-forms.aspx
 
Thank you,
it work like a charm.

Umar.

Tom Wickerath said:
Hi Umar,

First, you should know that several of your variables are not declared as
you may assume:

Dim stServerName, stDivision, stUnit As String
Dim stFullFileName, stReqDir, stFolderName, stFileName As String

In this case, stUnit and stFileName "begin life" as string variables. The
others start as variants. You can prove this to yourself by running a little
experiment, using stServerName:

Private Sub Filename_DblClick(Cancel As Integer)
Dim stServerName, stDivision, stUnit As String
Dim stFullFileName, stReqDir, stFolderName, stFileName As String

Debug.Print "stServerName is: " & TypeName(stServerName)
stServerName = "\\MAINSERVER\USERSHARE"
Debug.Print "stServerName is: " & TypeName(stServerName)

End Sub

Check out the results in the Immediate Window. You need to explicitely
declare each variable. For example:

Dim stServerName As String, stDivision As String, stUnit As String

or, by having each declaration on a separate line:

Dim stServerName As String
Dim stDivision As String
Dim stUnit As String

~~~~~~~~~~~~~~~~~~~

The Select Case statement is not really necessary, as you can use the Format
function here:

Select Case Me.RequirementID
Case Is < 10
stReqDir = "\R000" & Me.RequirementID
Case Is < 100
stReqDir = "\R00" & Me.RequirementID
Case Is < 1000
stReqDir = "\R0" & Me.RequirementID
Case Else
stReqDir = "\R" & Me.RequirementID
End Select

Debug.Print "stReqDir via Select Case: " & stReqDir

stReqDir = "\R" & Format(Me.RequirementID, "0000")
Debug.Print "stReqDir via Format statement: " & stReqDir

~~~~~~~~~~~~~~~~~~~

I recommend adding a command button, with appropriate caption, such as
&Open File (the ampersand will cause the letter "O" to be an underlined hot
key, such that the user can use <Alt><O> instead of clicking their mouse. You
should be able to use the Application.FollowHyperlink method with a valid
filename. Here is a procedure that should get you started. I used the
lowercase "txt" prefix for the names of the text boxes. I have also added
some minimal data checking, and error handling. Something like this:

Option Compare Database
Option Explicit

Private Sub cmdOpenFile_Click()
On Error GoTo ProcError

Dim stServerName As String
Dim stDivision As String
Dim stUnit As String
Dim stFullFileName As String
Dim stReqDir As String
Dim stFolderName As String
Dim stFileName As String

'Check for required values
If Not IsNumeric(Me.txtRequirementID) Then
MsgBox "You need to enter a Requirement ID Number.", _
vbCritical, "Missing Required Data..."
Me.txtRequirementID.SetFocus
Exit Sub
End If

Debug.Print InStr(Me.txtFilename, ".")

If InStr(Me.txtFilename, ".") = 0 Then
MsgBox "You need to enter a Filename with Extension.", _
vbCritical, "Missing Required Data..."
Me.txtFilename.SetFocus
Exit Sub
End If

If Len(Me.txtFolderName & "") = 0 Then
MsgBox "You need to enter a Folder Name.", _
vbCritical, "Missing Required Data..."
Me.txtFolderName.SetFocus
Exit Sub
End If

stReqDir = "\R" & Format(Me.txtRequirementID, "0000")

stServerName = "\\MAINSERVER\USERSHARE"
stDivision = "\Division_Requirements"
stUnit = "\Branch_Unit"

stFolderName = "\" & Me.txtFolderName
stFileName = "\" & Me.txtFilename

stFullFileName = stServerName & stDivision & stUnit _
& stReqDir & stFolderName & stFileName

Application.FollowHyperlink stFullFileName

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error Opening File..."
Resume ExitProc
End Sub



Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________
Click to expand...
 
Back
Top