Solved Print Multiple file paths saved in table

[alvingenius]

Hello Guys,

I've 2 tables
Table for User Profile ( Name, Date of birth, address, phone, email,...etc )
and table named " Files " that saves file paths of ID Photo, Passport image, Certificates images ( i know its local file paths !) - All Files in PDF extention

and report only showing user profile information and if I print this report it will print every user on a new page.
So, i want to print every user page following with ID and Passport only Photos linked to the user
( Without Certificate - Based on fType or Filename [file name renamed based on type] )

Is this possible?

Attached a sample of DB

Thanks.

Edit1 :

Solution at this post thanks to theDBguy

 

[theDBguy]

Hi. Can't look at your file right now, but have you tried using ShellExecute to print the PDF files? Just curious...

 

[alvingenius]

Hi. Can't look at your file right now, but have you tried using ShellExecute to print the PDF files? Just curious...

i didn't because i don't know how to point it to print multiple paths linked to every user automatically

 

[theDBguy]

i didn't because i don't know how to point it to print multiple paths linked to every user automatically

I imagine you would have to loop through the records and then call the "print" code for each one. For example,

1. Create a recordset of all UserID to print
2. Start the loop and get the first UserID
3. Create another recordset of all files for this UserID
4. Start the loop to get the first file
5. Print the file
6. Get the next file and print it
7. If no more file, get the next UserID and go back to Step 3
8. If no more UserID, close all recordsets

Hope that make sense...

 

[alvingenius]

if it will print every user profile + related ID and Passport with it then it will work,
but i'm not that good in vba to make it work using ur method!

 

[theDBguy]

if it will print every user profile + related ID and Passport with it then it will work,
but i'm not that good in vba to make it work using ur method!

Hi. I took a look at your file and made some changes. Unfortunately, though, I couldn't test it because I don't have those files to print. So, take a look and let us know how it goes.

 

[alvingenius]

Hi. I took a look at your file and made some changes. Unfortunately, though, I couldn't test it because I don't have those files to print. So, take a look and let us know how it goes.

Wow, you did the hard part and u make it look like Easy Peasy
it Prints all file paths linked to the user-chosen in form

However, this code needs some edits and i will tell you what i did too in italic

1. I only want ID photo and Passport Printed (fType) and exclude any other file paths.
   • for that i created a qry to filter ftype by ID or Passport
2. I want user profile printed as well before this files printed
3. I want to print all user profiles if I didn't choose a user from username combo
   • Mike + ID + Passport
   • Peter + ID + Passport
   • ... etc for 50 more users
   • i used the same qry mentioned above to filter users by USER_ID from combo with LIKE Statement to get all users in case no user selection so u may use this qry in code instead of writing it in sql

 

[theDBguy]

Wow, you did the hard part and u make it look like Easy Peasy
it Prints all file paths linked to the user-chosen in form

However, this code needs some edits and i will tell you what i did too in italic

1. I only want ID photo and Passport Printed (fType) and exclude any other file paths.
   • for that i created a qry to filter ftype by ID or Passport
2. I want user profile printed as well before this files printed
3. I want to print all user profiles if I didn't choose a user from username combo
   • Mike + ID + Passport
   • Peter + ID + Passport
   • ... etc for 50 more users
   • i used the same qry mentioned above to filter users by USER_ID from combo with LIKE Statement to get all users in case no user selection so u may use this qry in code instead of writing it in sql

Hi. Glad to hear it was helpful. Good job with your edits. Cheers!

 

[alvingenius]

Hi. Glad to hear it was helpful. Good job with your edits. Cheers!

thanks again
but I didn't finish it
this edits applied to this sample v2
but I made em for you, to help u edit the current code

edit1 :
I tried to edit this code with no luck
this code i tried

 strSQL = "SELECT Files.User_ID, Files.fType, Files.xLink"
strSQL = strSQL & " FROM Files"
strSQL = strSQL & " WHERE (((Files.User_ID) Like '*' & [Forms]![UserPorfilePrint]![txtID] & '*') AND ((Files.fType)='ID' Or (Files.fType)='Passport')); "

but i get
Run time error 3061
too few parameters expected 1

so what i want to edit on your code:

strSQL = "SELECT xLink FROM Files WHERE User_ID=" & Me.cboUserName.Column(1)

adding this
User ID Like "txtID" ' to get all users if no user selected
fType = "ID" or "Passport"

and I want to edit the code to print the main report for every user

 

[theDBguy]

thanks again
but I didn't finish it
this edits applied to this sample v2
but I made em for you, to help u edit the current code

edit1 :
I tried to edit this code with no luck
this code i tried

 strSQL = "SELECT Files.User_ID, Files.fType, Files.xLink"
strSQL = strSQL & " FROM Files"
strSQL = strSQL & " WHERE (((Files.User_ID) Like '*' & [Forms]![UserPorfilePrint]![txtID] & '*') AND ((Files.fType)='ID' Or (Files.fType)='Passport')); "

but i get
Run time error 3061
too few parameters expected 1

so what i want to edit on your code:

strSQL = "SELECT xLink FROM Files WHERE User_ID=" & Me.cboUserName.Column(1)

adding this
User ID Like "txtID" ' to get all users if no user selected
fType = "ID" or "Passport"

and I want to edit the code to print the main report for every user

Hi. To only print IDs and passports, try changing the SQL statement to this:

strSQL = "SELECT xLink FROM Files WHERE fType In('ID','Passport') AND User_ID=" & Me.cboUserName.Column(1)

Let us know how that goes...

 

[alvingenius]

Hi. To only print IDs and passports, try changing the SQL statement to this:

strSQL = "SELECT xLink FROM Files WHERE fType In('ID','Passport') AND User_ID=" & Me.cboUserName.Column(1)

Let us know how that goes...

it goes well,
now it only prints users ID and Passport selected

* what i miss now is
1- I wanna the main report named " UserProfiles " to be printed too with the user selected
2- If I didn't choose any user from the form then it should print all users main report + files related at this order :

• Mike + ID + Passport
• Peter + ID + Passport
• etc

for that i've added a txtbox to get user id named txtID
and changed ur code to

strSQL = "SELECT xLink FROM Files WHERE fType In('ID','Passport') AND User_ID  like '" & Me.txtID & "*'"

what i miss now is printing main report named " UserProfiles " with each user is the same order mentioned above

 

[theDBguy]

it goes well,
now it only prints users ID and Passport selected

* what i miss now is
1- I wanna the main report named " UserProfiles " to be printed too with the user selected
2- If I didn't choose any user from the form then it should print all users main report + files related at this order :

• Mike + ID + Passport
• Peter + ID + Passport
• etc

for that i've added a txtbox to get user id named txtID
and changed ur code to

strSQL = "SELECT xLink FROM Files WHERE fType In('ID','Passport') AND User_ID  like '" & Me.txtID & "*'"

what i miss now is printing main report named " UserProfiles " with each user is the same order mentioned above

Hi. Just a quick note...

With regards to #1 above, I decided to use a separate button just to show you how to print the PDFs, because you already know how to print the report. So, if you want to print the report and the PDFs at the same time, you should be able to combine the two buttons into one, or call the other button's Click event from the other one.

And with regards to #2 above, you will have to modify your report's query source to show all records. In other words, remove any filters. Then, you can adjust your code to use the WhereCondition argument of the OpenForm method to apply the filter. So, in your code, you can check if the user selected anything from the combo. If not, simply open the report, it should print all the records. If they selected something, then use it in your WhereCondition argument, and the report should open to a specific record - the one they selected.

 

[alvingenius]

Hi. Just a quick note...

With regards to #1 above, I decided to use a separate button just to show you how to print the PDFs, because you already know how to print the report. So, if you want to print the report and the PDFs at the same time, you should be able to combine the two buttons into one, or call the other button's Click event from the other one.

And with regards to #2 above, you will have to modify your report's query source to show all records. In other words, remove any filters. Then, you can adjust your code to use the WhereCondition argument of the OpenForm method to apply the filter. So, in your code, you can check if the user selected anything from the combo. If not, simply open the report, it should print all the records. If they selected something, then use it in your WhereCondition argument, and the report should open to a specific record - the one they selected.

Thanks for your reply

Regarding #1 , i tried it, if i called the button, it will print main report for all users in table then after that it will start to print files for every user
Regarding #2 , i'll choose not to choose any user and it will go for all users , i'll remove filter on users in Query

So i was thinking loudly and i shared that to you in Private Conversation , i'll share it here again for all

I just wanna give u a brief regarding what i'm doing in that DB i'm building

we have 50 employees in the company and every employee have requests every year to a specific department.
so i made an form that takes a copy from the request file to the Main server via local network
and saves the final file path at the table with username + request type + date of request
so we can view it anytime at the year

so, at the end of the year, we must print that for all users :
* a summarise for every request and request type of every user + a copy of the requests attached to it

I'm thinking loudly bro regarding our code you helping me with

if I didn't choose any user from the form

it will print 50 main reports - summarise ( for 50 users) + every user have about 20-30 files linked to him to be printed

so that will be 1200 pdfs to be printed on average.

I don't know if access can pass this much of files one time !!!!!

so I was thinking of saving the whole process in 1 PDF ( All users + all files )

so I made the default printer as Microsoft PDF and tried the same code. it giving me saving file dialog for every single file that exists on the table !!

so, is it possible to make a button generate all requests for every single user as PDF in Desktop or chosen location!
so 50 PDFs named as Users names?
or what do u think is good to handle it ???

 

[theDBguy]

Thanks for your reply

Regarding #1 , i tried it, if i called the button, it will print main report for all users in table then after that it will start to print files for every user
Regarding #2 , i'll choose not to choose any user and it will go for all users , i'll remove filter on users in Query

So i was thinking loudly and i shared that to you in Private Conversation , i'll share it here again for all


so, is it possible to make a button generate all requests for every single user as PDF in Desktop or chosen location!
so 50 PDFs named as Users names?
or what do u think is good to handle it ???

Hi. If you're talking about the report and not the ids and passports, then you can use the OutputTo method to create individual PDFs and automatically give them the file names and save location.

 

[alvingenius]

Hi. If you're talking about the report and not the ids and passports, then you can use the OutputTo method to create individual PDFs and automatically give them the file names and save location.

what about the rest of Files that i need to output them as 1 Pdf per user ( including only ID + Passport Photos ) Named as a user name in location i specified one time ( like desktop ) and it loops for all users exists

 

[theDBguy]

what about the rest of Files that i need to output them as 1 Pdf per user ( including only ID + Passport Photos ) Named as a user name in location i specified one time ( like desktop ) and it loops for all users exists

Hi. I don't understand the reasoning behind that. If you're going to print all the pages anyway, what's the difference between printing 200 1-page files and printing 1 file with 200 pages? In any case, if you still want to do it, the machine must have the full version of Adobe Acrobat to use the code you linked to earlier. So, if you're distributing your app, the users must have Acrobat too.

 

[alvingenius]

the machine that will do this operation has a full acrobat pro ( it only to my company not distributing it )
the reason behind what i want as i said it before:
At the end of every year, we are printing all requests by every user to a specific department
so , requests may reach 2000 pdf to be printed
so i don't think access or printer or PC at all will handle printing 2000 pdfs one time,
So instead i will save all requests by every user alone in 1 pdf only in desktop or any location so after that,i will print 1 PDF having all user requests
1 by 1 , or i might send the pdf to the department and they will handle the printing process

 

[theDBguy]

the machine that will do this operation has a full acrobat pro ( it only to my company not distributing it )
the reason behind what i want as i said it before:
At the end of every year, we are printing all requests by every user to a specific department
so , requests may reach 2000 pdf to be printed
so i don't think access or printer or PC at all will handle printing 2000 pdfs one time,
So instead i will save all requests by every user alone in 1 pdf only in desktop or any location so after that,i will print 1 PDF having all user requests
1 by 1 , or i might send the pdf to the department and they will handle the printing process

Hi. You're saying you don't "think" the machine can handle it. Are you thinking there is a memory size limit? Like I said, 2000 1-page files and a 2000-page file should have the same amount of space in memory. In any case, the only way to find out if you're correct, is to try it.

 

[alvingenius]

Hi. You're saying you don't "think" the machine can handle it. Are you thinking there is a memory size limit? Like I said, 2000 1-page files and a 2000-page file should have the same amount of space in memory. In any case, the only way to find out if you're correct, is to try it.

Hi,
i said that because its a big amount of papers i didn't try it before printing +2000 pages one time
so for that i asked you to save a 1 pdf for every user requests ,
and too we might keep em without printing and keeping em as pdf for archiving

 

[theDBguy]

Hi,
i said that because its a big amount of papers i didn't try it before printing +2000 pages one time
so for that i asked you to save a 1 pdf for every user requests ,
and too we might keep em without printing and keeping em as pdf for archiving

For archiving purposes, I would recommend keeping the files separate. That way, you can easily search through them. There would be a big difference in loading time between opening a 2000-page document and opening a 1-page document. Also, if you already have the code to combine pdf files, what else did you need me to do?