edit code to store in field and not msgbox

  • Thread starter Thread starter deb
  • Start date Start date
D

deb

What I am trying to do is to click a button and have the insert hyperlink
dialog box to display. I then select the doc to insert as a hyperlink and it
is saved in a field called AttachLink on a form called f020Attachments.
This works great using the below code...
Private Sub btnHyperlink_Click()
On Error GoTo Err_btnHyperlink_Click

Me.[AttachLink].SetFocus
RunCommand acCmdInsertHyperlink


Exit_btnHyperlink_Click:
Exit Sub

Err_btnHyperlink_Click:
If Err.Number <> 2501 Then
MsgBox Err.Number & ": " & Err.Description
End If
Resume Exit_btnHyperlink_Click

End Sub

now I need to also get the UNC Path of the document and put it into the
field called UNCAttachment. Then I need to create hyperlink with a
concantinated field of the UNC and the document path minus the drive letter
for all users to be able to open.

I found this code in the MS Support web site.

It said to copy the below code into a module and then type ?GetUNCPath("I:")
in the immediate window and the UNC will display in a text box.

I did and it worked... however,
I need the UNC to go into a field named UNCAttachment on a form called
f020Attachments.

How can the code be edited to put the UNC in the field when a doc is
selected via the button called btnHyperlink.


Option Compare Database

Option Explicit

' These represent the possible returns errors from API.
Public Const ERROR_BAD_DEVICE = 1200&
Public Const ERROR_CONNECTION_UNAVAIL = 1201&
Public Const ERROR_EXTENDED_ERROR = 1208&
Public Const ERROR_MORE_DATA = 234
Public Const ERROR_NOT_SUPPORTED = 50&
Public Const ERROR_NO_NET_OR_BAD_PATH = 1203&
Public Const ERROR_NO_NETWORK = 1222&
Public Const ERROR_NOT_CONNECTED = 2250&
Public Const NO_ERROR = 0

' This API declaration is used to return the
' UNC path from a drive letter.

Declare Function WNetGetConnection Lib "mpr.dll" Alias _
"WNetGetConnectionA" _
(ByVal lpszLocalName As String, _
ByVal lpszRemoteName As String, _
cbRemoteName As Long) As Long

Function GetUNCPath(strDriveLetter As String) As String
On Local Error GoTo GetUNCPath_Err
Dim Msg As String, lngReturn As Long
Dim lpszLocalName As String
Dim lpszRemoteName As String
Dim cbRemoteName As Long
lpszLocalName = strDriveLetter
lpszRemoteName = String$(255, Chr$(32))
cbRemoteName = Len(lpszRemoteName)
lngReturn = WNetGetConnection(lpszLocalName, _
lpszRemoteName, _
cbRemoteName)
Select Case lngReturn
Case ERROR_BAD_DEVICE
Msg = "Error: Bad Device"
Case ERROR_CONNECTION_UNAVAIL
Msg = "Error: Connection Un-Available"
Case ERROR_EXTENDED_ERROR
Msg = "Error: Extended Error"
Case ERROR_MORE_DATA
Msg = "Error: More Data"
Case ERROR_NOT_SUPPORTED
Msg = "Error: Feature not Supported"
Case ERROR_NO_NET_OR_BAD_PATH
Msg = "Error: No Network Available or Bad Path"
Case ERROR_NO_NETWORK
Msg = "Error: No Network Available"
Case ERROR_NOT_CONNECTED
Msg = "Error: Not Connected"
Case NO_ERROR
' all is successful...
End Select
If Len(Msg) Then
MsgBox Msg, vbInformation
Else
' Display the path in a Message box or return
' the UNC through the function.
MsgBox Left$(lpszRemoteName, cbRemoteName)
GetUNCPath = Left$(lpszRemoteName, cbRemoteName)
End If
GetUNCPath_End:
Exit Function
GetUNCPath_Err:
MsgBox Err.Description, vbInformation
Resume GetUNCPath_End
End Function


I know it is alot of stuff but I really need this badly!!!
 
Hi Deb,

I have an Image Demo database that is still a work-in-progress (not finished
yet). It includes API code to open a common dialog, to allow a person to
select an image. If the image is located on a file server, the mapped drive
letter is converted to the UNC equivalent.

You can easily change the default file extension that the common dialog is
set to use. I have it set to default to JPEG and GIF images, but this can be
changed to default to .doc (and/or .docx for Word 2007 files). I'm not sure
you really need to create a hyperlink--have you tried using
Application.FollowHyperlink in VBA code, passing the full path to the file
that you want to open?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

deb said:
What I am trying to do is to click a button and have the insert hyperlink
dialog box to display. I then select the doc to insert as a hyperlink and it
is saved in a field called AttachLink on a form called f020Attachments.
This works great using the below code...
Private Sub btnHyperlink_Click()
On Error GoTo Err_btnHyperlink_Click

Me.[AttachLink].SetFocus
RunCommand acCmdInsertHyperlink


Exit_btnHyperlink_Click:
Exit Sub

Err_btnHyperlink_Click:
If Err.Number <> 2501 Then
MsgBox Err.Number & ": " & Err.Description
End If
Resume Exit_btnHyperlink_Click

End Sub

now I need to also get the UNC Path of the document and put it into the
field called UNCAttachment. Then I need to create hyperlink with a
concantinated field of the UNC and the document path minus the drive letter
for all users to be able to open.

I found this code in the MS Support web site.

It said to copy the below code into a module and then type ?GetUNCPath("I:")
in the immediate window and the UNC will display in a text box.

I did and it worked... however,
I need the UNC to go into a field named UNCAttachment on a form called
f020Attachments.

How can the code be edited to put the UNC in the field when a doc is
selected via the button called btnHyperlink.


Option Compare Database

Option Explicit

' These represent the possible returns errors from API.
Public Const ERROR_BAD_DEVICE = 1200&
Public Const ERROR_CONNECTION_UNAVAIL = 1201&
Public Const ERROR_EXTENDED_ERROR = 1208&
Public Const ERROR_MORE_DATA = 234
Public Const ERROR_NOT_SUPPORTED = 50&
Public Const ERROR_NO_NET_OR_BAD_PATH = 1203&
Public Const ERROR_NO_NETWORK = 1222&
Public Const ERROR_NOT_CONNECTED = 2250&
Public Const NO_ERROR = 0

' This API declaration is used to return the
' UNC path from a drive letter.

Declare Function WNetGetConnection Lib "mpr.dll" Alias _
"WNetGetConnectionA" _
(ByVal lpszLocalName As String, _
ByVal lpszRemoteName As String, _
cbRemoteName As Long) As Long

Function GetUNCPath(strDriveLetter As String) As String
On Local Error GoTo GetUNCPath_Err
Dim Msg As String, lngReturn As Long
Dim lpszLocalName As String
Dim lpszRemoteName As String
Dim cbRemoteName As Long
lpszLocalName = strDriveLetter
lpszRemoteName = String$(255, Chr$(32))
cbRemoteName = Len(lpszRemoteName)
lngReturn = WNetGetConnection(lpszLocalName, _
lpszRemoteName, _
cbRemoteName)
Select Case lngReturn
Case ERROR_BAD_DEVICE
Msg = "Error: Bad Device"
Case ERROR_CONNECTION_UNAVAIL
Msg = "Error: Connection Un-Available"
Case ERROR_EXTENDED_ERROR
Msg = "Error: Extended Error"
Case ERROR_MORE_DATA
Msg = "Error: More Data"
Case ERROR_NOT_SUPPORTED
Msg = "Error: Feature not Supported"
Case ERROR_NO_NET_OR_BAD_PATH
Msg = "Error: No Network Available or Bad Path"
Case ERROR_NO_NETWORK
Msg = "Error: No Network Available"
Case ERROR_NOT_CONNECTED
Msg = "Error: Not Connected"
Case NO_ERROR
' all is successful...
End Select
If Len(Msg) Then
MsgBox Msg, vbInformation
Else
' Display the path in a Message box or return
' the UNC through the function.
MsgBox Left$(lpszRemoteName, cbRemoteName)
GetUNCPath = Left$(lpszRemoteName, cbRemoteName)
End If
GetUNCPath_End:
Exit Function
GetUNCPath_Err:
MsgBox Err.Description, vbInformation
Resume GetUNCPath_End
End Function


I know it is alot of stuff but I really need this badly!!!
Click to expand...
 
I would be greatly honored if you would share your code on how to have the
mapped drive letter converted to the UNC equivalent. I sould like I could
learn a gread deal from your Image Demo DB. How can I find your DB?

Thank you very much for your resonse.

--
deb


Tom Wickerath said:
Hi Deb,

I have an Image Demo database that is still a work-in-progress (not finished
yet). It includes API code to open a common dialog, to allow a person to
select an image. If the image is located on a file server, the mapped drive
letter is converted to the UNC equivalent.

You can easily change the default file extension that the common dialog is
set to use. I have it set to default to JPEG and GIF images, but this can be
changed to default to .doc (and/or .docx for Word 2007 files). I'm not sure
you really need to create a hyperlink--have you tried using
Application.FollowHyperlink in VBA code, passing the full path to the file
that you want to open?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

deb said:
What I am trying to do is to click a button and have the insert hyperlink
dialog box to display. I then select the doc to insert as a hyperlink and it
is saved in a field called AttachLink on a form called f020Attachments.
This works great using the below code...
Private Sub btnHyperlink_Click()
On Error GoTo Err_btnHyperlink_Click

Me.[AttachLink].SetFocus
RunCommand acCmdInsertHyperlink


Exit_btnHyperlink_Click:
Exit Sub

Err_btnHyperlink_Click:
If Err.Number <> 2501 Then
MsgBox Err.Number & ": " & Err.Description
End If
Resume Exit_btnHyperlink_Click

End Sub

now I need to also get the UNC Path of the document and put it into the
field called UNCAttachment. Then I need to create hyperlink with a
concantinated field of the UNC and the document path minus the drive letter
for all users to be able to open.

I found this code in the MS Support web site.

It said to copy the below code into a module and then type ?GetUNCPath("I:")
in the immediate window and the UNC will display in a text box.

I did and it worked... however,
I need the UNC to go into a field named UNCAttachment on a form called
f020Attachments.

How can the code be edited to put the UNC in the field when a doc is
selected via the button called btnHyperlink.


Option Compare Database

Option Explicit

' These represent the possible returns errors from API.
Public Const ERROR_BAD_DEVICE = 1200&
Public Const ERROR_CONNECTION_UNAVAIL = 1201&
Public Const ERROR_EXTENDED_ERROR = 1208&
Public Const ERROR_MORE_DATA = 234
Public Const ERROR_NOT_SUPPORTED = 50&
Public Const ERROR_NO_NET_OR_BAD_PATH = 1203&
Public Const ERROR_NO_NETWORK = 1222&
Public Const ERROR_NOT_CONNECTED = 2250&
Public Const NO_ERROR = 0

' This API declaration is used to return the
' UNC path from a drive letter.

Declare Function WNetGetConnection Lib "mpr.dll" Alias _
"WNetGetConnectionA" _
(ByVal lpszLocalName As String, _
ByVal lpszRemoteName As String, _
cbRemoteName As Long) As Long

Function GetUNCPath(strDriveLetter As String) As String
On Local Error GoTo GetUNCPath_Err
Dim Msg As String, lngReturn As Long
Dim lpszLocalName As String
Dim lpszRemoteName As String
Dim cbRemoteName As Long
lpszLocalName = strDriveLetter
lpszRemoteName = String$(255, Chr$(32))
cbRemoteName = Len(lpszRemoteName)
lngReturn = WNetGetConnection(lpszLocalName, _
lpszRemoteName, _
cbRemoteName)
Select Case lngReturn
Case ERROR_BAD_DEVICE
Msg = "Error: Bad Device"
Case ERROR_CONNECTION_UNAVAIL
Msg = "Error: Connection Un-Available"
Case ERROR_EXTENDED_ERROR
Msg = "Error: Extended Error"
Case ERROR_MORE_DATA
Msg = "Error: More Data"
Case ERROR_NOT_SUPPORTED
Msg = "Error: Feature not Supported"
Case ERROR_NO_NET_OR_BAD_PATH
Msg = "Error: No Network Available or Bad Path"
Case ERROR_NO_NETWORK
Msg = "Error: No Network Available"
Case ERROR_NOT_CONNECTED
Msg = "Error: Not Connected"
Case NO_ERROR
' all is successful...
End Select
If Len(Msg) Then
MsgBox Msg, vbInformation
Else
' Display the path in a Message box or return
' the UNC through the function.
MsgBox Left$(lpszRemoteName, cbRemoteName)
GetUNCPath = Left$(lpszRemoteName, cbRemoteName)
End If
GetUNCPath_End:
Exit Function
GetUNCPath_Err:
MsgBox Err.Description, vbInformation
Resume GetUNCPath_End
End Function


I know it is alot of stuff but I really need this badly!!!
Click to expand...
Click to expand...
 
Back
Top