Hyperlink/Coding Question (1 Viewer)

[BDW Jr]

I'm trying to help my department with a form. I have a field that we enter the document name. I would like to make this a hyperlink to the document. I would like the code look at another field. Depending on what is entered in that field, it will let you know what folder name we are going to be using. Example: if it is DCR it will go the DCR folder, if it is CR it will go to the CR folder. I've figured out a way to do it but it's not exactly correct. I would like the other specialist to be able to enter the name of their doc and the coding add the rest.

So the specialist would enter:
NC ABC 02-22-2013.pdf

Once they tab off that field, it will display as:
NC ABC 02-22-2013

Behind the scenes (pressing F2) it would be:
NC ABC 02-22-2013#S:\Support Services\DCR\NC ABC 02-22-2013.pdf#

Below is the coding that I'm using but it is assuming that all the doc names will be 17 characters in length. Also, the Hold field in the code is not necessary. I was the only way I could figure out how to keep all the info.

Private Sub FileName_AfterUpdate()
If Me.FileName.Value <> "" And Me.DCRCR.Value = "DCR" Then
Me.Hold.Value = Me.FileName.Value
Me.FileName.Value = Null
Me.FileName.Value = Left(Me.Hold.Value, 17) & "#S:\Support Services\DCR\" & Me.Hold.Value
Me.Hold.Value = Null
ElseIf Me.FileName.Value <> "" And Me.DCRCR.Value = "CR" Then
Me.Hold.Value = Me.FileName.Value
Me.FileName.Value = Null
Me.FileName.Value = Left(Me.Hold.Value, 17) & "#S:\Support Services\CRs\" & Me.Hold.Value
Me.Hold.Value = Null
ElseIf Me.FileName.Value = "" And Me.DCRCR.Value = "DCR" Then
Me.Hold.Value = Null
Me.FileName.Value = Null
ElseIf Me.FileName.Value = "" And Me.DCRCR.Value = "CR" Then
Me.Hold.Value = Null
Me.FileName.Value = Null

End If
End Sub

Any and all help is greatly appreciated.

 

[spikepl]

I'd suggest you back up a little and explain, in plain English, what all this is about. Among other things I do not quite understand why anyone has to "enter" anything into a computer-based system that reflects information already stored on the computer.

 

[BDW Jr]

My office runs data requests. In order to track it we are entering it into Access. Which company requested it, date of request, date sent to IT, date sent to requestor, data type and file name. The file name is the document name. Document name is the request we receive from the requestor and the signed approval from the supervisor to run the job. All that info is scanned. I'm trying to have the File Name (in Access) open the scanned document.

Let me know if you need more clarification.

 

[TJPoorman]

See if this code works for what you're looking for.

With this you don't need your Hold field. Also it doesn't matter the length

Private Sub FileName_AfterUpdate()
Dim strTemp As String

If Nz(Me.FileName, "") = "" Then Exit Sub

If Me.DCRCR = "DCR" Then
    strTemp = Me.FileName
    Me.FileName = Left(strTemp, InStrRev(strTemp, ".") - 1) & "#S:\Support Services\DCR\" & strTemp
ElseIf Me.DCRCR = "CR" Then
    strTemp = Me.FileName
    Me.FileName = Left(strTemp, InStrRev(strTemp, ".") - 1) & "#S:\Support Services\CRs\" & strTemp
End If
End Sub

 

[BDW Jr]

Thank you for your reply.

I seem to be getting an error message of it can't follow the link. When I look at the link, it is:

NC ABC 02-22-2013.pdf#http://NC ABC 02-22-2013#S:\Support Services\DCR\NC ABC 02-22-2013.pdf#http://NC ABC 02-22-2013.pdf#

The code works without the first "#http://NC ABC 02-22-2013#". Is there a way coding can get rid of it so it appears as:

NC ABC 02-22-2013.pdf#S:\Support Services\DCR\NC ABC 02-22-2013.pdf#

 

[BDW Jr]

TJPoorman, after playing around with the code I figured out what I needed to do. Thanks for your help. Here's the code:

Private Sub FileName_AfterUpdate()
Dim strTemp As String

If Nz(Me.FileName, "") = "" Then Exit Sub
If Me.DCRCR = "DCR" Then
strTemp = Me.FileName
Me.FileName = Null
Me.FileName = Left(strTemp, InStrRev(strTemp, "#http") - 1) & "#S:\Support Services\DCR\" & Left(strTemp, InStrRev(strTemp, "#http") - 1)
ElseIf Me.DCRCR = "CR" Then
strTemp = Me.FileName
Me.FileName = Left(strTemp, InStrRev(strTemp, "#http") - 1) & "#S:\Support Services\CRs\" & Left(strTemp, InStrRev(strTemp, "#http") - 1)

End If
End Sub