Thanks Sir, this is indeed an awesome feature, I don't have much exposure to Active x control, you might as well dedicate a series of tutorials to guide in exploring some of the useful controls available via non-native controls to get the best out of Ms Access and implementing modern designs especially regarding UI. Not without highlighting any caveats of coarse. Thanks for your time and insights as always.
@@DanielPineault I used this to enable storing documents in my SQL database in base64 format. Using code to convert files into base64 format with the file names returned by your routine. This is followed by a simple recordset .addnew process to insert a record into the JobDocuments table. The user then sees a list of linked documents in a sub form for each Job (pathology jobs in this case).
@@DanielPineaultHello Daniel, works great! One question: it is possible drag&drom Email from Outlook? I get error, because Access don't see the file path..., thanks
Of course it is, I cover that in the last part of the video. You can't handle the same way you do for file, because they aren't files! Instead it all relies on Outlook automation, but you can drag and drop emails, contact, appointments, ...
No, not that I'm aware of, there's no BackStyle property, but ... You can simulate transparency by simply setting the background to the same color as the section behind it by doing something along the lines of: Me.YourListViewControlName.BackColor = Me.Section(details).BackColor It is one of those properties I wish was natively available to us. I had read a while back that using GDI APIs is was possible to make such alterations to controls, but I never dug into the subject.
This is great stuff! I'v heard that ActiveX is somtimes not very friendly when you are redistributing....is there any way to do the same without ActiveX control?
I hoping to create an article in which I will present another approach, but not sure when I'll have time to do so. That said, the ActiveX approach is by far the simplest approach to implement!
Thanks for the great video, I've been looking for something like this for a long time. But as a user without programming experience, I'm wondering how I can store the file path or file paths (for multiple files) in an Access table. Are there any instructions? Many thanks in advance
Here's the basic idea that you need to implement: Private Sub LV_DaD_OLEDragDrop(Data As Object, Effect As Long, Button As Integer, Shift As Integer, x As Single, y As Single) 'Don't forget to set the control's OLEDropMode to 1 - ccOLEDropManual (on the Other Property Sheet tab) Dim oDb As DAO.Database Dim oRs As DAO.Recordset Dim lFileCounter As Long Dim lTotalNumberFiles As Long Const sTable As String = "[YourTableName]" On Error GoTo Error_Handler Set oDb = CurrentDb Set oRs = oDb.OpenRecordset(sTable) lTotalNumberFiles = Data.Files.Count With oRs For lFileCounter = 1 To lTotalNumberFiles 'Debug.Print Data.Files(lFileCounter) .AddNew ![YourFieldName] = Data.Files(lFileCounter) .Update Next lFileCounter End With Error_Handler_Exit: On Error Resume Next oRs.Close Set oRs = Nothing Set oDb = Nothing Exit Sub Error_Handler: MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _ "Error Source: LV_DaD_OLEDragDrop" & vbCrLf & _ "Error Number: " & Err.Number & vbCrLf & _ "Error Description: " & Err.Description & _ Switch(Erl = 0, "", Erl 0, vbCrLf & "Line No: " & Erl) _ , vbOKOnly + vbCritical, "An Error has Occurred!" Resume Error_Handler_Exit End Sub Obviously, you need to replace YourTableName & YourFieldName with you values/names.
In such a case you'd need to copy and register the dll from a machine that has it or download a copy from www.microsoft.com/en-US/download/details.aspx?id=10019 (I believe that's the right one). That said, there is no 64-bit version of these ActiveX controls, so perhaps that's the issue you are facing. Just one more reason why I avoid 64-bit.
I'm not sure if I'll be able to help, but could you explain the context a bit, because typically we are dragging and dropping files for the desktop or Windows Explorer. What are you trying to do exactly?
for the moment i worked this solution out: Private Sub lstItem_OLEDragDrop(Data As Object, Effect As Long, Button As Integer, Shift As Integer, x As Single, y As Single) Const vbCFFiles As Integer = 15 'DataFormats.FileDrop If Data.GetFormat(vbCFFiles) Then Debug.Print "Explorer" Else If InStr(Data.GetData(-16370), "Outlook") > 0 Then Debug.Print "Outlook" else Debug.Print "from other programm" End If End If
Yes, but I would never expect any user to try both. Normally the drag and drop is setup for either files, or for some outlook item. If you really want to accommodate both then error testing would probably be your best option.
Here's an example of how it could be done: Private Sub LV_DaD_OLEDragDrop(Data As Object, Effect As Long, Button As Integer, Shift As Integer, X As Single, Y As Single) 'Don't forget to set the control's OLEDropMode to 1 - ccOLEDropManual (on the Other Property Sheet tab) Dim lFileCounter As Long Dim lTotalNumberFiles As Long Dim bFiles As Boolean On Error GoTo Error_Handler lTotalNumberFiles = Data.Files.Count 'This will err if D&D are Outlook Items, so we trap that error and redirect accordingly bFiles = True For lFileCounter = 1 To lTotalNumberFiles Call ProcessFile(Data.Files(lFileCounter)) 'Process each file Next lFileCounter ProcessOutlookItems: If Not bFiles Then Call ProcessMailItems Error_Handler_Exit: On Error Resume Next Exit Sub Error_Handler: If Err.Number = 461 Then GoTo ProcessOutlookItems Else MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _ "Error Source: LV_DaD_OLEDragDrop" & vbCrLf & _ "Error Number: " & Err.Number & vbCrLf & _ "Error Description: " & Err.Description & _ Switch(Erl = 0, "", Erl 0, vbCrLf & "Line No: " & Erl) _ , vbOKOnly + vbCritical, "An Error has Occurred!" Resume Error_Handler_Exit End If End Sub
Thank you for another informative video and also for using late binding. Looking forward to wht you come up with next on this subject.
Thank you for your support!
Thank you, Daniel, most useful! Good to see your work again.
My pleasure! If you implement it, let me know how things work out and what you use it for.
Thanks Sir, this is indeed an awesome feature, I don't have much exposure to Active x control, you might as well dedicate a series of tutorials to guide in exploring some of the useful controls available via non-native controls to get the best out of Ms Access and implementing modern designs especially regarding UI. Not without highlighting any caveats of coarse. Thanks for your time and insights as always.
Thank you for the feedback.
Very useful! I have some data entry spreadsheets coming from the Philippines that this will be very useful for 🎉
Great. Let me know how it works for you.
@@DanielPineault I used this to enable storing documents in my SQL database in base64 format.
Using code to convert files into base64 format with the file names returned by your routine. This is followed by a simple recordset .addnew process to insert a record into the JobDocuments table. The user then sees a list of linked documents in a sub form for each Job (pathology jobs in this case).
Thanks you for sharing your Use Case. I love knowing how people implement some of things I share here or on my blog.
Very interesting and useful!
Thank you Xavier.
Thanks, I have make solution with hyperlink, but your solution is great!🎉
Thank you.
@@DanielPineaultHello Daniel, works great! One question: it is possible drag&drom Email from Outlook? I get error, because Access don't see the file path..., thanks
Of course it is, I cover that in the last part of the video. You can't handle the same way you do for file, because they aren't files! Instead it all relies on Outlook automation, but you can drag and drop emails, contact, appointments, ...
Nice! I've been fiddling around with hyperlink to achieve such functionality.
Is there a way to set this control background to transparent?
No, not that I'm aware of, there's no BackStyle property, but ...
You can simulate transparency by simply setting the background to the same color as the section behind it by doing something along the lines of:
Me.YourListViewControlName.BackColor = Me.Section(details).BackColor
It is one of those properties I wish was natively available to us.
I had read a while back that using GDI APIs is was possible to make such alterations to controls, but I never dug into the subject.
Thank you
You are welcome.
excellent! thank
Glad you liked it!
Hope to incorporate DAD soon! Thank you.
Great. Let me know how it goes, what your of use you implement it for.
This is great stuff! I'v heard that ActiveX is somtimes not very friendly when you are redistributing....is there any way to do the same without ActiveX control?
I hoping to create an article in which I will present another approach, but not sure when I'll have time to do so. That said, the ActiveX approach is by far the simplest approach to implement!
Thanks for the great video, I've been looking for something like this for a long time.
But as a user without programming experience, I'm wondering how I can store the file path or file paths (for multiple files) in an Access table.
Are there any instructions?
Many thanks in advance
It's actually extremely easy to accomplish. Let me see if I can't put something together to demonstrate the process.
Here's the basic idea that you need to implement:
Private Sub LV_DaD_OLEDragDrop(Data As Object, Effect As Long, Button As Integer, Shift As Integer, x As Single, y As Single)
'Don't forget to set the control's OLEDropMode to 1 - ccOLEDropManual (on the Other Property Sheet tab)
Dim oDb As DAO.Database
Dim oRs As DAO.Recordset
Dim lFileCounter As Long
Dim lTotalNumberFiles As Long
Const sTable As String = "[YourTableName]"
On Error GoTo Error_Handler
Set oDb = CurrentDb
Set oRs = oDb.OpenRecordset(sTable)
lTotalNumberFiles = Data.Files.Count
With oRs
For lFileCounter = 1 To lTotalNumberFiles
'Debug.Print Data.Files(lFileCounter)
.AddNew
![YourFieldName] = Data.Files(lFileCounter)
.Update
Next lFileCounter
End With
Error_Handler_Exit:
On Error Resume Next
oRs.Close
Set oRs = Nothing
Set oDb = Nothing
Exit Sub
Error_Handler:
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Source: LV_DaD_OLEDragDrop" & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End Sub
Obviously, you need to replace YourTableName & YourFieldName with you values/names.
Hi again. Where can we obtain the ActiveX DAD control?
Look at the video, I demonstrate exactly how to get it and place it on a form.
@@DanielPineault Sorry, I meant, it does not show in my ActiveX list. What ocx file, how to make available in Access? I have O365 version.
In such a case you'd need to copy and register the dll from a machine that has it or download a copy from www.microsoft.com/en-US/download/details.aspx?id=10019 (I believe that's the right one). That said, there is no 64-bit version of these ActiveX controls, so perhaps that's the issue you are facing.
Just one more reason why I avoid 64-bit.
How can i reconize from witch program the data-drag comes? I look the variable Data & Effect, but that doesnt help.
I'm not sure if I'll be able to help, but could you explain the context a bit, because typically we are dragging and dropping files for the desktop or Windows Explorer. What are you trying to do exactly?
for the moment i worked this solution out:
Private Sub lstItem_OLEDragDrop(Data As Object, Effect As Long, Button As Integer, Shift As Integer, x As Single, y As Single)
Const vbCFFiles As Integer = 15 'DataFormats.FileDrop
If Data.GetFormat(vbCFFiles) Then
Debug.Print "Explorer"
Else
If InStr(Data.GetData(-16370), "Outlook") > 0 Then
Debug.Print "Outlook"
else
Debug.Print "from other programm"
End If
End If
@@DanielPineault in ur example, u take data from the explorer or from outlook. Each one has to b treated differently.
Yes, but I would never expect any user to try both. Normally the drag and drop is setup for either files, or for some outlook item.
If you really want to accommodate both then error testing would probably be your best option.
Here's an example of how it could be done:
Private Sub LV_DaD_OLEDragDrop(Data As Object, Effect As Long, Button As Integer, Shift As Integer, X As Single, Y As Single)
'Don't forget to set the control's OLEDropMode to 1 - ccOLEDropManual (on the Other Property Sheet tab)
Dim lFileCounter As Long
Dim lTotalNumberFiles As Long
Dim bFiles As Boolean
On Error GoTo Error_Handler
lTotalNumberFiles = Data.Files.Count 'This will err if D&D are Outlook Items, so we trap that error and redirect accordingly
bFiles = True
For lFileCounter = 1 To lTotalNumberFiles
Call ProcessFile(Data.Files(lFileCounter)) 'Process each file
Next lFileCounter
ProcessOutlookItems:
If Not bFiles Then Call ProcessMailItems
Error_Handler_Exit:
On Error Resume Next
Exit Sub
Error_Handler:
If Err.Number = 461 Then
GoTo ProcessOutlookItems
Else
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Source: LV_DaD_OLEDragDrop" & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End If
End Sub