Remou et al - Drag & Drop a pdf?

[jhaganjr]

Thanks again to Remou for the response to dragging and dropping an Outlook email into a memo field.

Now ... what about a pdf file? I've tried dropping it onto all field types (I think all). Nothing occurs that would indicate a codable event has taken place.

Why do this? We currently "attach" images to client records for our electronic filing. This process involves saving the image file with normal file management tools outside of Access, then clicking a form button in Access to execute code where we navigate to the file to be attached and select it. After selection the code does its thing to link everything up.

I was hoping we could speed that up with a drag and drop method.

Any insight?

Thanks!
Joe

 

[Remou]

Why not browse for the file within Access?

Here is code for a CommonDialog, it is not mine, but I forgot to note the creator - it may be Microsoft.

Option Compare Database
Option Explicit

Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
"GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long

Private Type OPENFILENAME
    lStructSize As Long
    hwndOwner As Long
    hInstance As Long
    lpstrFilter As String
    lpstrCustomFilter As String
    nMaxCustFilter As Long
    nFilterIndex As Long
    lpstrFile As String
    nMaxFile As Long
    lpstrFileTitle As String
    nMaxFileTitle As Long
    lpstrInitialDir As String
    lpstrTitle As String
    flags As Long
    nFileOffset As Integer
    nFileExtension As Integer
    lpstrDefExt As String
    lCustData As Long
    lpfnHook As Long
    lpTemplateName As String
End Type

Function LaunchCD(frmForm As Form, strStartIn As String, strFilter As String) As String
    Dim OpenFile As OPENFILENAME
    Dim lReturn As Long
    'Dim strFilter As String
    

On Error GoTo TrapError

    OpenFile.lStructSize = Len(OpenFile)
    OpenFile.hwndOwner = frmForm.hWnd
    'sFilter = strFilter
    OpenFile.lpstrFilter = strFilter
    OpenFile.nFilterIndex = 1
    OpenFile.lpstrFile = String(257, 0)
    OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
    OpenFile.lpstrFileTitle = OpenFile.lpstrFile
    OpenFile.nMaxFileTitle = OpenFile.nMaxFile
    OpenFile.lpstrInitialDir = strStartIn
    OpenFile.lpstrTitle = "Select a file"
    OpenFile.flags = 0
    lReturn = GetOpenFileName(OpenFile)
        If lReturn = 0 Then
            'MsgBox "A file was not selected!", vbInformation, _
              "Select a file using the Common Dialog DLL"
            LaunchCD = ""
         Else
            LaunchCD = Trim(Left(OpenFile.lpstrFile, InStr(1, OpenFile.lpstrFile, vbNullChar) - 1))
         End If
Exit_Sub:
    Exit Function
    
TrapError:
Dim strErrMessage As String
    Select Case Err.Number
        'Case 2474
        '    CurrentForm(pstrFirstControl).SetFocus
        '    Err.Clear
        '    Resume Next
        Case Else
            strErrMessage = "Please report error number UFC1212." & vbCr & Err.Description
            MsgBox strErrMessage, vbOKOnly + vbInformation, "System Error: Unhandled Error"
    End Select
End Function

 

[jhaganjr]

That code's a little over my head, but if it opens a file selection dialogue box - which is what it looks like - that's what we do now. We click a button that launches code that opens a file selection dialogue box (which I did not write).

I was just hoping a simple drag and drop method was out there - as with Outlook e-mail. We use a document management program that is always open on each user's PC. So the user could just drag and drop a pdf from that program window.

I guess not.

Thanks anyway!
Joe

 

[Hap007]

Dear Joe,

Just place an OLEBound object on a form
You can drag and drop pdf files onto that control.

Hope This Helps,
Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Add-on Solutions
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[Remou]

Hap007
Won't that lead to considerable database bloat?

 

[Remou]

It would be possible to use a bound object control, without binding it to a field. It could be used to obtain the name of the dropped file using the code found here:

http://support.microsoft.com/kb/199066

Private Sub oleOLEBound_AfterUpdate()
Me.LinkedFileTextbox = GetLinkedPath(Me.oleOLEBound)
End Sub

Function GetLinkedPath(objOLE As Variant) As Variant
   Dim strChunk As String
   Dim pathStart As Long
   Dim pathEnd As Long
   Dim path As String
   If Not IsNull(objOLE) Then
      ' Convert string to Unicode.
      strChunk = StrConv(objOLE, vbUnicode)
      pathStart = InStr(1, strChunk, ":\", 1) - 1

      ' If mapped drive path not found, try UNC path.
      If pathStart <= 0 Then pathStart = _
                       InStr(1, strChunk, "\\", 1)

      ' If either drive letter path or UNC path found, determine
      ' the length of the path by searching for the first null
      ' character Chr(0) after the path was found.
      If pathStart > 0 Then
         pathEnd = InStr(pathStart, strChunk, Chr(0), 1)
         path = Mid(strChunk, pathStart, pathEnd - pathStart)
         GetLinkedPath = path
         Exit Function
      End If
   Else
      GetLinkedPath = Null
   End If
End Function

 

[jhaganjr]

Hap007 & Remou,

This is awesome. It looks like we're on the right path.

The code you posted Remou returns what I would call machine language jibberish, or I get errors about the field being too small. And when I follow it in debug the str variables have the machine language jibberish in them.

I clicked through to the MS article and will try it as they describe it - which does not involve an AfterUpdate event on a form like I'm trying currently. I think I can learn a lot from that and see where it goes.

Any other suggestions? Any other geniuses out there with some input on this?

I'll get back with what I figure out.

Thanks!
Joe

 

[jhaganjr]

I think I'm at a dead end with this.

The article itself says "this procedure may not work in some circumstances". And it also talks about conditions under which "garbage characters" will be returned - which is what I get in my database.

I went through the procedure with the Northwind db as described in the article. The code only functioned correctly when I used the Insert > Object menu command as described. Trying to trigger the same code from an event after a drag and drop just wouldn't happen.

And even when it functioned it shortened all path segments to 8 characters, e.g. C:\MYDOCU~1\LONGNA~1.bmp. So, even if I could reliably capture it in a text field I couldn't trigger automated file activity with it.

And there's no gain in user processing time employing the article's method.

With the email drag and drop Remou gave me in the other post, we used the following:

Set olApp = GetObject("", "Outlook.Application")

Set olExp = olApp.ActiveExplorer
Set olSel = olExp.Selection
For i = 1 To olSel.Count
MsgBox olSel.item(i).EntryID
olSel.item(i).SaveAs CurrentProject.Path & "\mail.txt", olTXT
Next

As I interpret this, it essentially grabs Outlook and says whatever the current email is must be the one that got dragged and dropped, so let's save the current email to another place and format.

Is there a similar interface with My Computer that could use that same logic; i.e. whatever file is currently selected must be the file that got dropped, so let's do something with it?

Thanks.
Joe