By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,327 Members | 1,791 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,327 IT Pros & Developers. It's quick & easy.

Linking database records to .doc .xls .pdf etc.

P: 6
Hi,

I'm currently working on an Access database that stores personal details.

I would like to be able to link the database to a network drive so a user can see all related .doc .pdf .xls files on the network that are associated with that record.

**************************
Here is the ideal situation:

User looks up record with unique ID 123456 in the database

Database then displays screen showing content of S:\Users\123456 (Where S:\ is a network drive)

User clicks database record showing "Personal Details.doc"

MS Word opens S:\Users\123456\Personal Details.doc

**************************************

Any help would be greatly appreciated.

Thanks

Simon Tindall
Dec 6 '07 #1
Share this Question
Share on Google+
5 Replies


Rabbit
Expert Mod 10K+
P: 12,392
Assuming that S:\ is mapped correctly on the user's computer, you can use the Application.FileSearch method to list the files and FollowHyperlink to open it.
Dec 6 '07 #2

P: 6
Hi,

thanks for your help on that one.

I managed to do this but ran in to a few event handling problems, see below for my slightly clumbsy but working code.

I'm not saying its the best way to do it but it works.

Thanks again.


Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. 'Declaring the variable that determines the location of the files
  4. Private DirectoryLocation As String
  5. Private SelectedFileName As String
  6. Private FileExtension As String
  7. Dim filecheck As Boolean
  8.  
  9.  
  10. 'Events for when the form loads
  11. Private Sub Form_Load()
  12.  
  13.     'Set the location of the files and make it a unique folder by using StudentRefNo
  14.     DirectoryLocation = "x:\" & Me.StudentRefNo
  15.  
  16.     'setting a flag to say the filename field is blank
  17.     filecheck = True
  18.  
  19.  
  20.     'Refreshing the file list box by calling the readDirectory class
  21.     readDirectory
  22.  
  23.  
  24. End Sub
  25.  
  26. 'Events for the when the browse button is clicked
  27. Private Sub BrowseButton_Click()
  28.  
  29.     'Declaring parameters for the FileDialog function
  30.     Dim fd As FileDialog
  31.     Set fd = Application.FileDialog(msoFileDialogFilePicker)
  32.     With fd
  33.  
  34.         'display the file dialog box
  35.         .Show
  36.  
  37.         'if an item is selected then pass the filename to the text field on the form
  38.         If .SelectedItems.Count <> 0 Then
  39.             Me.FileName = .SelectedItems(1)
  40.             filecheck = False
  41.         Else
  42.             filecheck = True
  43.         End If
  44.     End With
  45.  
  46. End Sub
  47. 'Events for when the attach button is clicked
  48. Private Sub AttachButton_Click()
  49.  
  50. 'if statement to display messagebox if user clicks attach when no file has been selected
  51. If filecheck = True Then
  52. MsgBox "Please select a file to attach", 16, "Select File"
  53. GoTo Reload
  54. End If
  55.  
  56.  
  57.  
  58.     'declaring constants to use later
  59.     SelectedFileName = Mid(FileName, InStrRev(FileName, "\") + 1)
  60.     FileExtension = Mid(FileName, InStrRev(FileName, "."))
  61.  
  62.     Dim fso As FileSystemObject
  63.     Set fso = New FileSystemObject
  64.  
  65.     'check to see if the alumni already has a folder associated with it
  66.     'if it does, go to next step
  67.     If fso.FolderExists(DirectoryLocation) Then
  68.         Else
  69.         'if it doesnt, then make the directory
  70.         MkDir (DirectoryLocation)
  71.     End If
  72.  
  73.  
  74.  
  75. 'Check to see if file already exists
  76. Dim x As String
  77. x = SelectedFileName
  78. Dim adext As Boolean
  79. Dim checkdirectory As Boolean
  80.  
  81. check:
  82. checkdirectory = True
  83. For i = 0 To FileList.ListCount
  84. If FileList.ItemData(i) = DirectoryLocation & "\" & x Then
  85.     checkdirectory = False
  86. End If
  87. Next i
  88.  
  89.  
  90. If Not checkdirectory Then
  91.         adext = True
  92.         x = InputBox("File Already Exists, Please Type a New Filename", "File Already Exists") & FileExtension
  93.         If x = FileExtension Then GoTo Reload
  94.         GoTo check
  95. End If
  96.  
  97.  
  98. FileCopy FileName, DirectoryLocation & "\" & x
  99.  
  100. 'code to remove all items from list
  101. Reload:
  102. Do While FileList.ListCount > 0
  103. FileList.RemoveItem (0)
  104. Loop
  105.  
  106. 'calling a class to refresh the items in the listbox
  107. readDirectory
  108. End Sub
  109.  
  110. 'code to open file when double clicked
  111. Private Sub FileList_DblClick(Cancel As Integer)
  112. Set openfile = FileList
  113. Application.FollowHyperlink (openfile)
  114. End Sub
  115.  
  116.  
  117. Private Sub readDirectory()
  118.  
  119. 'code to search for files in unique directory
  120. Dim fs
  121. Set fs = Application.FileSearch
  122. With fs
  123. .LookIn = "x:\" & Me.StudentRefNo
  124. .FileName = "*"
  125.  
  126. .Execute
  127.  
  128.     If Len(.FileName) > 0 Then
  129.         For i = 1 To .foundfiles.Count
  130.         FileList.AddItem (.foundfiles(i))
  131.         Next i
  132.     End If
  133.  
  134. End With
  135.  
  136. End Sub
  137.  
  138. 'opens directory when button clicked
  139. Private Sub OpenFolder_Click()
  140. Application.FollowHyperlink ("x:\" & Me.StudentRefNo)
  141. End Sub
  142.  
  143. 'refreshes list when button clicked
  144. Private Sub RefreshButton_Click()
  145.  
  146. 'removes all items
  147. Do While FileList.ListCount > 0
  148. FileList.RemoveItem (0)
  149. Loop
  150.  
  151. 'populates list again
  152. readDirectory
  153.  
  154. End Sub
  155.  
Dec 18 '07 #3

P: 6
Forgot to mention there is a section in there to browse for and attach a file too.
Dec 18 '07 #4

Rabbit
Expert Mod 10K+
P: 12,392
Not a problem. I look a quick glance but instead of setting a flag to see if there's a filename stored, you can just use IsNull(TextboxName) when you need to check instead of making sure you have the flag properly set.
Dec 18 '07 #5

P: 1
Can you please explain how this works and is set up. I am very green.
Mar 21 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.