Results 1 to 11 of 11
  1. #1
    rhysgjones is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    6

    linking to a file on a mapped drive using combo boxes as variables for the file path

    Im pretty new to Access Databases and am trying to create a form where a user can "upload a file" into access, by upload i envisage a table with the below fields (i may need more) that actually store the path to the file, when the toggle button is pressed and the user browses to and selects a file and clicks ok then Access will move the file to a location on our network (mapped network Drive)



    DocumentID (Priimary key)
    ClientID
    DocumentPath

    the form contains the following combo boxes cboCLientName, cboFileType and cboVendor the values selected in these combo boxes are to be used to create the path of where i want the document to be uploaded i.e if

    cboClientName.value = Client1234
    cboFiletype.value = Licenses
    cboVendor.value = Microsoft

    this would transpose to J:\Client1234\Licenses\Microsoft\ the file name would be whatever is select when a toggle button labelled "upload and Link" is pressed

    As well as this table being populated the file will physically be COPIED from its source location to the above location determined by the combo boxes.

    Once i have this form working the next stage is to be able to click on these files paths from other forms as if they are hyperlinks as such


    I hope there is someone out there that can help me with this as unfortunately this is not within my skill set at this stage

    Thanks in advance

    Rhys

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    A lot going on here.

    1. Browse to and select file(s)

    2. Copy file(s) to folder

    3. Save path string to table

    For 1 see https://www.accessforums.net/showthr...ght=attachment

    For 2 look at http://forums.devarticles.com/micros...vba-30811.html

    For 3 depends on form setup. Can be a simple: Me!fieldname = variable path or maybe an SQL UPDATE or INSERT action.
    Last edited by June7; 03-02-2012 at 07:47 AM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    rhysgjones is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    6
    Hi June7

    Thanks so much for the pointers i have managed to get the document into the table and also copy it to a path defined by a combobox value however there is two problems;

    1. the path must ALREADY exist on the filesystem it doesn't create directories causing it to drop into debug, Do you know if its possible to create directory structures on the fly
    2. how can i retrieve the original filename so that i can use it in the NewName variable and retain the original document name and extension as you can see from my code below all docs are called example.xls and are obviously overwritten each time

    the code i have is:

    Private Sub btnAddDoc_Click()
    Dim f As Object
    Dim ImagePath As Variant

    DoCmd.GoToRecord , , acNewRec

    Set f = Application.FileDialog(3)
    f.AllowMultiSelect = True
    f.Show

    For Each ImagePath In f.SelectedItems

    DocumentPath = ImagePath
    DoCmd.Save
    DoCmd.GoToRecord acActiveDataObject, , acNext

    OldName = ImagePath
    NewName = "C:\NewFolder\" & cbovendor.Value & "\SomeExample.xls"
    retval = 0
    Dim objFSO As Object
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    retval = objFSO.CopyFile(OldName, NewName, True)
    Set objFSO = Nothing

    Next ImagePath
    End Sub



    the last part of my quest is to be able to click on the paths in the table somehow and be able to open the document

    Thanks again for your help

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    1. This what you want? http://www.techonthenet.com/access/f...file/mkdir.php

    2. Extract file name from the path/file string and use it instead of the hard-coded file name: Mid(ImagePath, InStrRev(ImagePath,"\")+1)
    You might want a more meaningful folder name than NewFolder.

    3. What kind of documents? Here is example for Word .doc: http://www.techonthenet.com/access/modules/word_doc.php
    or use intrinsic FollowHyperlink or Allen Browne's alternative http://allenbrowne.com/func-GoHyperlink.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    rhysgjones is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    6
    Thanks for all the help i am nearly there i just have once problem with the file path

    one of the variable sin the filepath come from the master form "frmMaster" and is a combo box containing the PK value in a clients database the combo box cboClientName lists the friendly names

    my end path is made up of variables the first parts comes from a systemsvariable table field DocumentBasePath the next subfolder i want to be the clients name returned and i am having issues with the following bit of code

    Client = DLookup("Client Name", "Clients", "ClientID = " & Forms![frmMaster].cboClientName)

    it returns a runtiem error "Syntax error (missing Operator) in query expression 'Client Name'

    my complete code is:



    Private Sub btnAddDoc_Click()


    Dim f As Object
    Dim ImagePath As Variant
    Dim Client As Variant
    DocBasePath = DLookup("DocumentBasePath", "SystemVariables")
    Client = DLookup("Client Name", "Clients", "ClientID = " & Forms![frmMaster].cboClientName)


    DoCmd.GoToRecord , , acNewRec


    Set f = Application.FileDialog(3)
    f.AllowMultiSelect = True
    f.Show


    For Each ImagePath In f.SelectedItems
    If Len(Dir(DocBasePath, vbDirectory)) = 0 Then
    MsgBox "Please check 'Document Base Path' is present in System Setup and is valid"
    Exit Sub
    End If

    If Len(Dir(DocBasePath & Client, vbDirectory)) = 0 Then
    MkDir DocBasePath & Client
    End If

    If Len(Dir(DocBasePath & Client & "\" & cboDocType.Value, vbDirectory)) = 0 Then
    MkDir DocBasePath & Client & "\" & cboDocType.Value
    End If


    If Len(Dir(DocBasePath & Client & "\" & cboDocType.Value & "\" & cbovendor.Value, vbDirectory)) = 0 Then
    MkDir DocBasePath & Client & "\" & cboDocType.Value & "\" & cbovendor.Value
    End If


    OldName = ImagePath
    Newname = DocBasePath & Client & "\" & cboDocType.Value & "\" & cbovendor.Value & "\" & Mid(ImagePath, InStrRev(ImagePath, "\") + 1)
    retval = 0
    Dim objFSO As Object
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    retval = objFSO.CopyFile(OldName, Newname, True)
    Set objFSO = Nothing


    DocumentPath = Newname
    DocumentType = cboDocType.Value
    DoCmd.Save
    DoCmd.GoToRecord acActiveDataObject, , acNext


    Next ImagePath
    End Sub


    the rest of my code was/is working perfectly up until i tried adding in the clientname into the path


    Thanks for all your help

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    That's why advise to avoid spaces, special characters, punctuation (underscore is exception) in names or reserved words as names. If used, must enclose in [].

    Client = DLookup("[Client Name]", "Clients", "ClientID = " & Forms![frmMaster].cboClientName)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    rhysgjones is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    6
    thats for hat didnt pick up on that field name in the table have fixed that and now get further its now has a syntas error with 'ClientID = ' from the below code


    Client = DLookup("ClientName", "Clients", "ClientID = " & Forms![frmMaster].cboClientName)

    if i test with the following code:

    Client = DLookup("ClientName", "Clients", ClientID = 31)

    it works perfectly and retrived the valid Client name for ClientID31 so just need to get the syntax right to retrive the value from the frmMaster form from cboClientName

    thanks so much for all your help

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    The expression must be within quotes:

    Client = DLookup("ClientName", "Clients", "ClientID = 31")

    If the criteria is provided by a variable - concatenate variables - reference to a control on form is a variable:

    Client = DLookup("ClientName", "Clients", "ClientID = " & Me.ClientID)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    rhysgjones is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    6
    Thanks so much for your help i had it all working until i tried to save two additional fields into the UploadedDocuments Table i would like to also save the Filename as a unique field and also the value from the combobox cboDocType

    i tried the following code but nothing happens

    DocumentPath = Newname
    DocumentType = Me.txttest.Value
    FileName = Mid(ImagePath, InStrRev(ImagePath, "\") + 1)
    DoCmd.Save
    DoCmd.GoToRecord acActiveDataObject, , acNext


    my complete code is


    Private Sub btnAddDoc_Click()

    Dim f As Object
    Dim ImagePath As Variant
    Dim Client As Variant
    DocBasePath = DLookup("DocumentBasePath", "SystemVariables")
    Client = DLookup("ClientName", "Clients", "ClientID = " & Me.ClientID)
    DoCmd.GoToRecord , , acNewRec

    Set f = Application.FileDialog(3)
    f.AllowMultiSelect = True
    f.Show

    For Each ImagePath In f.SelectedItems
    If Len(Dir(DocBasePath, vbDirectory)) = 0 Then
    MsgBox "Please check 'Document Base Path' is present in System Setup and is valid"
    Exit Sub
    End If

    If Len(Dir(DocBasePath & Client, vbDirectory)) = 0 Then
    MkDir DocBasePath & Client
    End If

    If Len(Dir(DocBasePath & Client & "\" & cboDocType.Value, vbDirectory)) = 0 Then
    MkDir DocBasePath & Client & "\" & cboDocType.Value
    End If

    If Len(Dir(DocBasePath & Client & "\" & cboDocType.Value & "\" & cbovendor.Value, vbDirectory)) = 0 Then
    MkDir DocBasePath & Client & "\" & cboDocType.Value & "\" & cbovendor.Value
    End If

    OldName = ImagePath
    Newname = DocBasePath & Client & "\" & cboDocType.Value & "\" & cbovendor.Value & "\" & Mid(ImagePath, InStrRev(ImagePath, "\") + 1)
    retval = 0
    Dim objFSO As Object
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    retval = objFSO.CopyFile(OldName, Newname, True)
    Set objFSO = Nothing

    DocumentPath = Newname
    DocumentType = Me.txttest.Value
    FileName = Mid(ImagePath, InStrRev(ImagePath, "\") + 1)
    DoCmd.Save
    DoCmd.GoToRecord acActiveDataObject, , acNext

    Next ImagePath
    End Sub


    I have a fair amount of data validation to code into it but i will be able to handle that just not sure what im missing in trying to same other fields into the table
    Thanks again for all your help

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    DocumentType and FileName are fields included in the form's RecordSource? If so, try
    Me!DocumentType = Me.txttest.Value
    Me!FileName = Mid(ImagePath, InStrRev(ImagePath, "\") + 1)


    Step debug, follow the code as it executes, see where it deviates from expectations, fix, repeat.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    rhysgjones is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    6
    Thank you so much

    have everything working perfectly now

    much appreciated

Please reply to this thread with any new information or opinions.

Similar Threads

  1. path\file access error 75
    By PRASANNA in forum Programming
    Replies: 2
    Last Post: 04-25-2011, 04:50 AM
  2. View .txt file on hard drive
    By nfaunt in forum Programming
    Replies: 0
    Last Post: 04-04-2011, 09:49 AM
  3. Output tables to an excel file on a network drive
    By GraemeG in forum Import/Export Data
    Replies: 1
    Last Post: 04-01-2011, 03:06 PM
  4. Replies: 1
    Last Post: 01-06-2011, 11:23 AM
  5. parametrize file path
    By ysrini in forum Access
    Replies: 3
    Last Post: 03-01-2010, 09:37 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums