Drag and drop PDF file to MS Access Form (1 Viewer)

npa3000

Member
Local time
Today, 17:37
Joined
Apr 16, 2021
Messages
36
Hello,

On our company we receive some invoices and at the same time we do 2 things:

1) Pass new record to access ( Id, Amount, supplier name, etc)
2) Save the invoice PDF at a network folder

Is there a way to save the file to the network folder from a drag n drop field in ms access form?

Thanks
 

June7

AWF VIP
Local time
Today, 07:37
Joined
Mar 9, 2014
Messages
5,423
Drag and drop in Access is a frequent topic. Did you do a search? The answer to your question is probably "no". You can use code in Click event.

Where does invoice PDF come from?
 

npa3000

Member
Local time
Today, 17:37
Joined
Apr 16, 2021
Messages
36
Thanks for your answer!

Yes and i couldn't find something.

We receive them via email.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:37
Joined
Oct 29, 2018
Messages
21,358
Thanks for your answer!

Yes and i couldn't find something.

We receive them via email.
Have you investigated doing this from the email side? If you're using Outlook, for example.
 

npa3000

Member
Local time
Today, 17:37
Joined
Apr 16, 2021
Messages
36
Have you investigated doing this from the email side? If you're using Outlook, for example.
Yes, we are using Outlook.

I don't think so that i can do it from there...

Do you have something on your mind that could help?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:37
Joined
Oct 29, 2018
Messages
21,358
Yes, we are using Outlook.

I don't think so that i can do it from there...

Do you have something on your mind that could help?
I don't have any example handy. See if this one helps...

 

Gasman

Enthusiastic Amateur
Local time
Today, 15:37
Joined
Sep 21, 2011
Messages
14,044
Yes, we are using Outlook.

I don't think so that i can do it from there...

Do you have something on your mind that could help?
I drag and drop outlook attachments all the time?

I have code somewhere that saves all attachments and replaces them with links in the selected emails.

You should be able to adapt that?
 
Last edited:

Nixversteher

New member
Local time
Today, 08:37
Joined
Feb 25, 2018
Messages
16
Hello
Yes you could evaluate dd events in Access, the question where the data comes from (outlook, excel, thunderbird,...) is not the first question, but how deep you want to deal with the problem.
Access itself does not provide any functions for a fully functional dd. But over some control elements a simple dd is possible, like e.g. the web control element, treeview or a listview.
The easiest way is the webcontrol for your requirement, outlook->attach->dd->save in path. Just use the webcontrol to automatically access the desired path, dd from outlook, done. Simple and easy, very little vba-code. Disadvantage is, the webcontrol triggers no usable event at the drop, i.e. an e.g. automated capture into your database or renaming of the file is not possible. Also I don't know now if this webcontrol still exists in the newer access-versions.
 

KitaYama

Well-known member
Local time
Tomorrow, 00:37
Joined
Jan 6, 2022
Messages
1,489
We receive a lot of drawings and documents. We also have to be able to open any drawing or concerning document from Access.
We have set a rule in saving documents.
For example the drawing AF15687BL101A01 is saved in Y:\\Drawings\A\F\1\ where Y is a network drive.

When entering data in Access, before update of the form checks to see if the Drawing and its relating documents are saved in their correct folder. If not, it gives an error message to correct the saving documents.

After saving the data, any user at any time can click the drawing by double clicking the record in search form.

I'd be glad to see if there's a solution for drag & drop a document into a form.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:37
Joined
Feb 19, 2002
Messages
42,970
Is there a way to save the file to the network folder from a drag n drop field in ms access form?
I really don't understand the requirement to drag and drop FROM a form. Are you talking about just the text path because storing the .pdf in a table will cause severe bloating problems in a very short time? How did the file get into a control on the form in the first place? Let's go back to the beginning. Where is the pdf? Did it come in an email? Are you opening the email and saving the pdf? Then what? You want to capture the location? If so, at that point, you have opened a file explorer window. To capture the path, you can click into the field at the top and that gives you the full path which you can copy and paste. You can then copy the file name separately. It is a two part process. But no typing is required.
Pass new record to access ( Id, Amount, supplier name, etc)
I also don't know what this means. Are you opening an Access form and typing in the data? Are you importing a file?

And finally, the best way to view documents such as pdf's and drawings is by using the FollowHyperlink method. That tells Windows to open whatever program the file extension is linked to. Trying to use a control on a form is pretty unsatisfactory, even the web control.

Application.Followhyperlink Me.SomeControlNameThatContainsThePath
 

bastanu

AWF VIP
Local time
Today, 08:37
Joined
Apr 13, 2010
Messages
1,401
@ npa3000: you might want to have a look at my free utility (http://forestbyte.com/ms-access-utilities/fba-outlook-companion/) that you should be able to easily modify for your needs. If you setup your a shared mailbox for your invoicing emails you can then attach the actual Outlook message to the database record and with a double-click you can open it (in Outlook) with the original attachment in place.

Cheers,
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:37
Joined
Feb 19, 2002
Messages
42,970
if the Drawing and its relating documents are saved in their correct folder.
How does Access know what the "correct" folder is and what the file names are?

You have decided that drag and drop is THE solution but what you are describing has nothing to do with drag and drop.

Drag and drop on an access form moves an item from one list to a different list. This is not a native feature. You have to code it.
Drag and drop in Windows relates to an object that is moved from one location to a different location.

In neither case would Drag and Drop transform something from a text string to an object or from an object to a text string.

I didn't look at @bastanu 's sample but it sounds like it might help with at least part of the process.
 

Isaac

Lifelong Learner
Local time
Today, 08:37
Joined
Mar 14, 2017
Messages
8,738
UA had some drag and drop stuff years ago, haven't looked at it in a long time though. Can't remember if/what drawbacks it came with.

Anyway, just code your Access function to let the user browse for the item and then handle it from there.

Drag and drop is over-rated. I've been seeing it for 20 years, and yet, even today, when faced with a drag and drop site I never use it, I click Browse anyway, just faster.
 

KitaYama

Well-known member
Local time
Tomorrow, 00:37
Joined
Jan 6, 2022
Messages
1,489
How does Access know what the "correct" folder is and what the file names are?
As I said there's a rule in place for saving documents and drawing. I think you missed it. Here's a better explanation:

Every saved order in tblOrders has a OrderPK and ProductFK.
If a user is trying to save an order that DrawingNo in tblProducts is AR36598FF203B, before update event of the form checks to see if Y:\Drawings\A\R\3\AR36598FF203B.pdf exists. If that file doesn't exist, then Cancel=True, give the user a message to save the drawing first, and don't save the record.

You have decided that drag and drop is THE solution but what you are describing has nothing to do with drag and drop.
I'm not sure if it's addressed to me or not. If it's to me, I haven't decided anything. I just said if a drag and drop is possible it MAY makes our workflow easier and I'd be glad to hear it, and I didn't offer any solution. I simply explained how we manage our documents.

Maybe you've mistaken me with the OP.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:37
Joined
Feb 19, 2002
Messages
42,970
My comments were directed to the OP. I though you were the OP giving some more information. Your post didn't offer a solution. It stated a process so I thought you were clarifying the process I asked about.

It is the OP who decided that drag and drop would solve some problem. I'm trying to figure out what is being dragged and from where to where? Drag and drop does not "convert" an object. It just moves it from one place to another.
 

npa3000

Member
Local time
Today, 17:37
Joined
Apr 16, 2021
Messages
36
Hello again,
most of you are right that I do not explain in detail exactly what I want to do.

Let's start from the beginning.

The company receives via email some PDFs which contain many invoices. Whenever we download and break them so that each PDF is an invoice (For example a PDF can have 4 invoices)

The data of the invoices (Number, Amount, supplier, date, etc.) were then passed to the Access application through a form.

At the same time we save the specific invoice, ie the PDF, in a web folder and the path has to do depending on the year and the week we receive them for example //Server/2022/W1/1234.pdf


So I wonder if there is a way after I have saved the invoice data in Access to create a drag n drop field so that the user drags the PDF from his desktop and based on the invoice data the path is created and stored there with correct name (I say the name because usually when we split a large pdf into many small ones they have random names).

What do you think ?
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:37
Joined
Sep 21, 2011
Messages
14,044
When I split my incoming pdf into separate invoices I use an app from the MS store.
When I save the original pdf, let's say LBS 150222 Invoice.pdf, the app splits the pages into
LBS 150222 Invoice Page 1.pdf
LBS 150222 Invoice Page 2.pdf


You could just let them enter the path of the original files in the DB, then move them to your relevant folder renaming and changing the path in the db as you do so?

So I select c:\temp\LBS 1502 Invoice Page 1.pdf for a record
When finished I press a button, and the file is moved from c:\temp to //Server/2022/W1/WhateverTheinvoiceNumberIs.pdf and then changes the filepath in the db to reflect the new location and name?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:37
Joined
Feb 19, 2002
Messages
42,970
were then passed to the Access application through a form
What passes them to the form? Are you using some software that does OLE automation? Looks like Gassman has made a guess that you are using software to split the original PDF. Are you? or are you doing this manually? Is the software automating Access to enter the data?

Using the word "passes" actually means some type of automation. If you type the data yourself, that is not "passing".
Using the phrase "drag and drop" means something. Drag and drop does NOT do conversions and you want to convert the the .pdf file to a name as well as move it to a different folder.

The easiest solution will be to decompose the original pdf, however you do it, to a specific folder. You said you name the files in a meaningful way. Then the code in Access will read the specific, local folder and for each file it finds, it will examine the name and given the key data in the name, it will create a record in some table to log the file and then it will use FSO (File System Object) to copy the file to the permanent folder and delete it from the temp folder.

So, if the file names have the necessary information to identify the FK you need in the log record and the FK can point to some record in a table that identifies the parent folder you want the file to end up in, the code is not hard. The FK is the "key". You can have the parent folder name in a table associated with the company/account/whatever. The app can make a subfolder with the date field.

If that is an acceptable method - it is NOT drag and drop, we can proceed with code.
 

Users who are viewing this thread

Top Bottom