Form Button (Wildcard) (1 Viewer)

[Harry Taylor]

I have a db with around 3000 records, numbered H1 to H3000.

I also have folders on the system where I keep photos relating to the customer, again numbered H1 to H3000 but with the customer names next to them;
Like H1402 - Jacks Bakery.

Is it possible to have a custom button that would look at the customer number on the database [Text198],
follow a windows path - \\SERVER\Works\Customers\
Open the folder relating to [Text198] with a wildcard at the end

Something like;
\\SERVER\Works\Customers\"[Text198]*"

Any help would be very much appreciated.

Thanks

 

[theDBguy]

I think you would have to use Dir() to search for the first matching folder before you can open it with something like the FollowHyperlink method.

 

[Pat Hartman]

Use the File System Object to open a folder. Then the user can pick the photo he wants. It would be helpful if you could tell us the purpose of opening a folder so we can recommend the code you need.

 

[Harry Taylor]

Hi Pat,
Thanks for your reply.

The Windows folders keep correspondence sent by the customer (H1402 - Jacks Bakery). It contains email correspondence, jpg's , safety sheets etc.

I have a hyperlink to open the 'Customer' folder - \\SERVER\Works\Customers - then scroll down to the one im looking at.

I wondered if it was possible to go straight to their folder using the [Text198] field on the db.

 

[Gasman]

Why not give your controls some meaningful names instead of Text198?

This appears to work, so you can concatenate your Text198 value and add an * to get the folder name

tt= dir("F:\temp\db1*",vbDirectory)
? tt
db1 Test

 

[Pat Hartman]

I'm confused. Do you want the user to scroll to the document or do you want the hyperlink to just open it?

Here's code I use that allows filters. It is run from the click event of a button and puts the name of the file in a control. You can then run the hyperlink on the next line of code. Look at the commented out options for filters and replace with your filter. Don't forget to add the reference to YOUR version of the MS Office xx.x Object Library first.

Me.txtYourFileName = fChooseFile()
Application.FollowHyperlink Me.txtYourFileName

Option Compare Database
Option Explicit

Public Function fChooseFile()
 
   ' Requires reference to Microsoft Office 11.0 Object Library.
 
   Dim fDialog As Office.FileDialog
   Dim varFile As Variant
 
 
   ' Set up the File Dialog.
   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
 
   With fDialog
 
      ' Allow user to make multiple selections in dialog box
      .AllowMultiSelect = False
            
      ' Set the title of the dialog box.
      .Title = "Please select one file"
 
      'starting location
      .InitialFileName = CurrentProject.path
      
      ' Clear out the current filters, and add our own.
      .Filters.Clear
''''      .Filters.Add "Excel ", "*.XLSX"
      .Filters.Add "Access Databases", "*.ACCDB, *.MDB"
''''      .Filters.Add "Access Projects", "*.ADP"
      .Filters.Add "All Files", "*.*"
 
      ' Show the dialog box. If the .Show method returns True, the
      ' user picked at least one file. If the .Show method returns
      ' False, the user clicked Cancel.
      If .Show = True Then
         'Loop through each file selected and add it to our list box.
         For Each varFile In .SelectedItems
            fChooseFile = varFile
''''            Me.FileList.AddItem varFile
         Next
        
      Else
         MsgBox "You clicked Cancel in the file dialog box."
      End If
   End With

End Function