Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2007
    Posts
    4

    Unanswered: How do I attach a file to a record?

    I've been looking all over for how to do this, and haven't been able to find out.
    I want the user to be able to attach files to a database record (stored in the file system, not in the database), and then be able to access the files from a listbox on the form, and also be able to clear any files attached to the record.

    So, it should look like this:
    1. User clicks "attach file"
    2. open/save dialog box comes up
    3. user chooses file and hits save/attach/open/whatever
    4. path to file is stored in database
    5. file shows up on listbox in the form for that record.
    6. user chooses file from listbox
    7. user clicks "open file"
    8. file opens in outside program

    I found code (http://www.mvps.org/access/api/api0001.htm) to get the command button to bring up the open/save box, but when I click open, I get "runtime error: you can't assign a value to this object." I don't know how to get the file path to save in the record. I got the listbox to show the files, but I don't know how to then allow the user to run the files shown.

    Any help would be APPRECIATED!

    John

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The following code opens a file picker dialog box with the appropriate jiggery...
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub cmdSelectFile_Click()
    
    On Error GoTo Err_cmdSelectFile_Click
    
    Dim fDialog As Office.FileDialog
    Dim varFile As Variant
          
        Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
            
            With fDialog
                'Set dialog box title
                .Title = "This is the dialog box's title"
                'Set to false to only allow user to select one item
                .AllowMultiSelect = True
                'Set the button caption
                .ButtonName = "Attach File"
                'If user hits ok then update default folder path
                If .Show = True Then
                    For varFile = 1 To .SelectedItems.Count
                        <INSERT YOUR UPDATE SQL HERE>
                    Next varFile
                End If
            End With
    
    Exit_cmdSelectFile_Click:
        Exit Sub
        
    Err_cmdSelectFile_Click:
        MsgBox "Error Number:  " & Err.Number & Chr(13) & _
               "Error Description:  " & Err.Description, vbCritical, "Unknown Error"
        Resume Exit_cmdSelectFile_Click:
        
    End Sub
    Now for point 4 you need to change the line that reads <INSERT YOUR UPDATE SQL HERE> to something along the lines of

    "UPDATE <yourtable> SET <thefieldname> = '" & .Selected Items "'"
    Note the single quotes in the above line.

    This should give you a good boost of a start!
    George
    Home | Blog

  3. #3
    Join Date
    Mar 2007
    Posts
    4

    hrm...

    When I do this, I get the following error:

    Compile error:
    User-defined type not defined.

    And then it shoots me over to
    Dim fdialog As Office.FileDialog

    I'm using Access 2003 and the 11.0 Object Library

    I'm new at VBA, thoughts?

  4. #4
    Join Date
    Mar 2007
    Posts
    4
    hokay,

    so I used the API from here (http://www.mvps.org/access/api/api0001.htm), along with the following code:

    Dim strFilter As String
    Dim strInputFileName As String
    Dim strSQL As String

    strFilter = ahtAddFilterItem(strFilter, _
    "All Files (*.*)", _
    "*.*")
    strInputFileName = ahtCommonFileOpenSave(Filter:=strFilter, _
    OpenFile:=True, _
    DialogTitle:="Please select an input file...", _
    Flags:=ahtOFN_HIDEREADONLY)
    strSQL = "UPDATE [Sales] SET [Filename] = " & strInputFileName '
    DoCmd.RunSQL strSQL
    End Sub
    This successfully brings up the open/save dialog, and allows me to choose a file, say "example.pdf"

    Now a new problem:
    When I choose example.pdf and hit "open," I get this error:
    "syntax error (missing operator) in query expression 'C:\Documents and Settings\example.pdf'"

    What'd I do wrong?

  5. #5
    Join Date
    Mar 2007
    Posts
    4
    VICTORIOUS:

    Private Sub cmdFileDialog_Click()
    Dim strFilter As String
    Dim strInputFileName As String
    Dim strSQL As String

    strFilter = ahtAddFilterItem(strFilter, _
    "All Files (*.*)", _
    "*.*")
    strInputFileName = ahtCommonFileOpenSave(Filter:=strFilter, _
    OpenFile:=True, _
    DialogTitle:="Please select an input file...", _
    Flags:=ahtOFN_HIDEREADONLY)
    strSQL = "UPDATE [Sales] SET [Filename] = '" & strInputFileName & "'"
    DoCmd.RunSQL strSQL
    End Sub

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by bigrabidfrog
    When I do this, I get the following error:

    Compile error:
    User-defined type not defined.

    And then it shoots me over to
    Dim fdialog As Office.FileDialog

    I'm using Access 2003 and the 11.0 Object Library

    I'm new at VBA, thoughts?
    Microsoft Office Object Library 10.0 or above is needed.

    EDIT: well done on getting the single and double quotiness correct
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •