Solved Print Multiple file paths saved in table (2 Viewers)

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 17:14
Joined
Oct 29, 2018
Messages
21,358
That's true
But till this point i've reached i don't know how to continue or do what you telling here in this quote

so here is the equivalent of your simpleCSV() function based on the File Table

SQL:
SimpleCSV("SELECT Files.xLink FROM Files WHERE Files.fType In('xID','Passport')")

If i may ask you to make the edits on sample DB on your reply here and change print code to let it only extract PDFs as 1 merged file per user and file named by username and delete filter by used name !
i need to get all file for all users if i didn't choose a user
Hi. In the SimpleCSV() call, you would also add the ID of the current record in the loop. Something like:

SELECT xLink FROM Files WHERE fType In('ID','Passport') AND ID=" & rs!ID
 

alvingenius

IT Specialist
Local time
Today, 02:14
Joined
Jul 10, 2016
Messages
169
Hi. In the SimpleCSV() call, you would also add the ID of the current record in the loop. Something like:

SELECT xLink FROM Files WHERE fType In('ID','Passport') AND ID=" & rs!ID
then what's next?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 17:14
Joined
Oct 29, 2018
Messages
21,358
then what's next?
You pass the array created from the SimpleCSV() function and the name of the new merged file to the function you found for merging pdfs.
 

alvingenius

IT Specialist
Local time
Today, 02:14
Joined
Jul 10, 2016
Messages
169
You pass the array created from the SimpleCSV() function and the name of the new merged file to the function you found for merging pdfs.


Arrayname : LinksArray

SQL:
Sub Combine_PDFs_Demo()
Dim strPDFs(0 To 2) As String
Dim bSuccess As Boolean
strPDFs(0) = "C:\Users\Ryan\Desktop\Page1.pdf"
strPDFs(1) = "C:\Users\Ryan\Desktop\Page5.pdf"
strPDFs(2) = "C:\Users\Ryan\Desktop\Page10.pdf"
 
bSuccess = MergePDFs(strPDFs, "C:\Users\Ryan\Desktop\MyNewPDF.pdf")

If bSuccess = False Then MsgBox "Failed to combine all PDFs", vbCritical, "Failed to Merge PDFs"

End Sub

please edit the code
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 17:14
Joined
Oct 29, 2018
Messages
21,358
Arrayname : LinksArray

SQL:
Sub Combine_PDFs_Demo()
Dim strPDFs(0 To 2) As String
Dim bSuccess As Boolean
strPDFs(0) = "C:\Users\Ryan\Desktop\Page1.pdf"
strPDFs(1) = "C:\Users\Ryan\Desktop\Page5.pdf"
strPDFs(2) = "C:\Users\Ryan\Desktop\Page10.pdf"

bSuccess = MergePDFs(strPDFs, "C:\Users\Ryan\Desktop\MyNewPDF.pdf")

If bSuccess = False Then MsgBox "Failed to combine all PDFs", vbCritical, "Failed to Merge PDFs"

End Sub

please edit the code
Is this just a test? I don't see SimpleCSV() in it.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 17:14
Joined
Oct 29, 2018
Messages
21,358
no, i've already posted the simplecsv code before, and really i don't know how to go further

i'll apprecaite it if you edit the sample db attached in your reply

then i'll understand what u done from the final code !
Hi. If you created an array from the SimpleCSV() and called it LinkArray, then you should be able to simply go like this:

bSuccess = MergePDFs(LinkArray, "C:\FolderName\NewName.pdf")

What happens if you tried that? Also, what happened (what was the result) when you tried the code you posted earlier where the array was called strPDFs?
 

alvingenius

IT Specialist
Local time
Today, 02:14
Joined
Jul 10, 2016
Messages
169
Hi. If you created an array from the SimpleCSV() and called it LinkArray, then you should be able to simply go like this:

bSuccess = MergePDFs(LinkArray, "C:\FolderName\NewName.pdf")

What happens if you tried that? Also, what happened (what was the result) when you tried the code you posted earlier where the array was called strPDFs?

bSuccess = MergePDFs(LinkArray, "C:\FolderName\NewName.pdf")

this one give me the 1pdf file with 4 pages ( 2 per user ) ( valid file if i wanna combine all file paths )

the other code with strPDFs i must defind all arrays for it

now back to the point of how to passing file paths arrays for every user id and saves the output az ID username.pdf !!
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 17:14
Joined
Oct 29, 2018
Messages
21,358
bSuccess = MergePDFs(LinkArray, "C:\FolderName\NewName.pdf")

this one give me the 1pdf file with 4 pages ( 2 per user )
Probably because you didn't add the filter in the SimpleCSV() function to keep the files to merge within the same person.

the other code with strPDFs i must defind all arrays for it

now back to the point of how to passing file paths arrays for every user id and saves the output az ID username.pdf !!
You should be able to use a loop to do that. Have you tried using a loop to create the array per user?
 

alvingenius

IT Specialist
Local time
Today, 02:14
Joined
Jul 10, 2016
Messages
169
Probably because you didn't add the filter in the SimpleCSV() function to keep the files to merge within the same person.


You should be able to use a loop to do that. Have you tried using a loop to create the array per user?

That's what i've reached till now

SQL:
Private Sub cmdFiles_Click()
Dim i As Integer

Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Dim LinksArray() As String
Dim bSuccess As Boolean
Dim SavePath As String
LinksArray = Split(SimpleCSV("SELECT xLink FROM Files WHERE fType In('xID','Passport') AND User_ID  like '" & Me.txtID & "*'"), ",")


    Set db = CurrentDb
    strSQL = " SELECT Files.User_ID, UserProfile.UserName, Files.xLink " & _
    " FROM UserProfile INNER JOIN Files ON UserProfile.User_ID = Files.User_ID " & _
    " WHERE (((Files.fType)='xID' Or (Files.fType)='Passport'));"

    Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
    For i = 0 To rs.RecordCount

        bSuccess = MergePDFs(LinksArray, "D:\NewName.pdf")
        If bSuccess = False Then MsgBox "Failed to combine all PDFs", vbCritical, "Failed to Merge PDFs"
    rs.MoveNext
    Next i
    
rs.Close
Set rs = Nothing
db.Close

End Sub

My issue here all ids saved as same name NewName.pdf , so the last record will overwritten the file , saves only 2 Pdfs for the last ID

So for that i've
SQL:
Dim SavePath As String

i wanna say
SQL:
SavePath = "D:\"& "Username" &".pdf"

then i'll change this
SQL:
bSuccess = MergePDFs(LinksArray, "D:\NewName.pdf")

to

bSuccess = MergePDFs(LinksArray, SavePath)

This code here is not working
SQL:
SavePath = "D:\"& "Username" &".pdf"

Username here i get from STRSQL code, but it's not getting the value of the username .
 

alvingenius

IT Specialist
Local time
Today, 02:14
Joined
Jul 10, 2016
Messages
169
SQL:
SavePath = "D:\" & rs.Fields("UserName") & ".pdf"
i think i figured it
but it only get me
D:\Mike.pdf file

So that's the only thing missing now ,, getting file name saved as record id loop
how to make username changes with the ID loops

SQL:
SavePath = "D:\" & rs.Fields("UserName") & ".pdf"
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 17:14
Joined
Oct 29, 2018
Messages
21,358
SQL:
SavePath = "D:\" & rs.Fields("UserName") & ".pdf"
i think i figured it
but it only get me
D:\Mike.pdf file

So that's the only thing missing now ,, getting file name saved as record id loop
how to make username changes with the ID loops

SQL:
SavePath = "D:\" & rs.Fields("UserName") & ".pdf"
Maybe instead of username, it's UserID.

Sent from phone...
 

alvingenius

IT Specialist
Local time
Today, 02:14
Joined
Jul 10, 2016
Messages
169
Maybe instead of username, it's UserID.

Sent from phone...
Same issue
It returns
d:\1.pdf
d:\1.pdf
in debug print

and that's valid value
this one here Debug.Print rs.Fields("User_ID") go through records
and there are 2 records with user id = 1 in files table

so when looping, it get first record User id = 1 , and it's merged as pdf, next loop will get the second record user id = 1
for that the value returned is valid
1
1

For that, i think I'll need a way to loop through IDs, not through records
or what you think I did wrong here?


Edit 1:

I've edited the StrSQL recordset to only get USER ID , USERNAME grouped for that it will only get me 1 ID per record
and it's filterd with User_id too

so the final code is that
SQL:
Private Sub cmdFiles_Click()
Dim i As Integer

Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Dim LinksArray() As String
Dim bSuccess As Boolean

LinksArray = Split(SimpleCSV("SELECT xLink FROM Files WHERE fType In('xID','Passport') AND User_ID  like '" & Me.txtID & "*'"), ",")


    Set db = CurrentDb
    strSQL = " SELECT Files.User_ID, UserProfile.UserName " & _
    " FROM UserProfile INNER JOIN Files ON UserProfile.User_ID = Files.User_ID " & _
    " GROUP BY Files.User_ID, UserProfile.UserName " & _
    " HAVING (((Files.User_ID)like '" & Me.txtID & "*')) " & _
    "ORDER BY Files.User_ID;"
    Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

    For i = 0 To rs.RecordCount - 1
        Dim SavePath As String
        SavePath = "D:\" & rs.Fields("Username") & ".pdf"
        'Debug.Print rs.Fields("Username")
        'Debug.Print LinksArray(i)
        bSuccess = MergePDFs(LinksArray, SavePath)
        If bSuccess = False Then MsgBox "Failed to combine all PDFs", vbCritical, "Failed to Merge PDFs"
    rs.MoveNext
    Next i
 
rs.Close
Set rs = Nothing
db.Close

End sub

So if I chose Mike or Peter from combo, it send the username id to txtID then it create a pdf with the chosen username with its valid files (2pages)

The new problem now, if i didn't choose any users from combo, it creates
Mike.pdf ( 4 pages for mike and peter)
Peter.pdf ( 4 pages for mike and peter)

and that's valid since the

SQL:
LinksArray = Split(SimpleCSV("SELECT xLink FROM Files WHERE fType In('xID','Passport') AND User_ID  like '" & Me.txtID & "*'"), ",")

So the returned value if no user_id
is 4 file paths
it creates Mike.pdf with 4 pdfs then loop for peter.pdf with the same 4 file paths pdf

So now how to edit this LinksArray to only pass file paths per User_ID in the current loop if no user-chosen?
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 17:14
Joined
Oct 29, 2018
Messages
21,358
Same issue
It returns
d:\1.pdf
d:\1.pdf
in debug print

and that's valid value
this one here Debug.Print rs.Fields("User_ID") go through records
and there are 2 records with user id = 1 in files table

so when looping, it get first record User id = 1 , and it's merged as pdf, next loop will get the second record user id = 1
for that the value returned is valid
1
1

For that, i think I'll need a way to loop through IDs, not through records
or what you think I did wrong here?


Edit 1:

I've edited the StrSQL recordset to only get USER ID , USERNAME grouped for that it will only get me 1 ID per record
and it's filterd with User_id too

so the final code is that
SQL:
Private Sub cmdFiles_Click()
Dim i As Integer

Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Dim LinksArray() As String
Dim bSuccess As Boolean

LinksArray = Split(SimpleCSV("SELECT xLink FROM Files WHERE fType In('xID','Passport') AND User_ID  like '" & Me.txtID & "*'"), ",")


    Set db = CurrentDb
    strSQL = " SELECT Files.User_ID, UserProfile.UserName " & _
    " FROM UserProfile INNER JOIN Files ON UserProfile.User_ID = Files.User_ID " & _
    " GROUP BY Files.User_ID, UserProfile.UserName " & _
    " HAVING (((Files.User_ID)like '" & Me.txtID & "*')) " & _
    "ORDER BY Files.User_ID;"
    Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

    For i = 0 To rs.RecordCount - 1
        Dim SavePath As String
        SavePath = "D:\" & rs.Fields("Username") & ".pdf"
        'Debug.Print rs.Fields("Username")
        'Debug.Print LinksArray(i)
        bSuccess = MergePDFs(LinksArray, SavePath)
        If bSuccess = False Then MsgBox "Failed to combine all PDFs", vbCritical, "Failed to Merge PDFs"
    rs.MoveNext
    Next i

rs.Close
Set rs = Nothing
db.Close

End sub

So if I chose Mike or Peter from combo, it send the username id to txtID then it create a pdf with the chosen username with its valid files (2pages)

The new problem now, if i didn't choose any users from combo, it creates
Mike.pdf ( 4 pages for mike and peter)
Peter.pdf ( 4 pages for mike and peter)

and that's valid since the

SQL:
LinksArray = Split(SimpleCSV("SELECT xLink FROM Files WHERE fType In('xID','Passport') AND User_ID  like '" & Me.txtID & "*'"), ",")

So the returned value if no user_id
is 4 file paths
it creates Mike.pdf with 4 pdfs then loop for peter.pdf with the same 4 file paths pdf

So now how to edit this LinksArray to only pass file paths per User_ID in the current loop if no user-chosen?
Hi. Glad to hear you're making good progress. Try this... just before this line:

Code:
bSuccess = MergePDFs(LinksArray, SavePath)

Add the following:

Code:
LinksArray = Split(SimpleCSV("SELECT xLink FROM Files WHERE fType In('xID','Passport') AND User_ID='" & rs!User_ID & "'"))
 

alvingenius

IT Specialist
Local time
Today, 02:14
Joined
Jul 10, 2016
Messages
169
Hi. Glad to hear you're making good progress. Try this... just before this line:

Code:
bSuccess = MergePDFs(LinksArray, SavePath)

Add the following:

Code:
LinksArray = Split(SimpleCSV("SELECT xLink FROM Files WHERE fType In('xID','Passport') AND User_ID='" & rs!User_ID & "'"))
Hi
Thanks for not giving up on me 🌷

So I tried your code and put it where you said then i got this error
Capture.PNG


and that's what it shows after i click debug

Capture2.PNG


and what do u think about manipulating Username from StrSQL recordset as grouped?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 17:14
Joined
Oct 29, 2018
Messages
21,358
Hi
Thanks for not giving up on me 🌷

So I tried your code and put it where you said then i got this error
View attachment 81837

and that's what it shows after i click debug

View attachment 81838

and what do u think about manipulating Username from StrSQL recordset as grouped?
Hi. I'm about to step out, but try using this instead:

rs!Files.User_ID

Hope that helps...
 

alvingenius

IT Specialist
Local time
Today, 02:14
Joined
Jul 10, 2016
Messages
169
Hi. I'm about to step out, but try using this instead:

rs!Files.User_ID

Hope that helps...
So the new code will be
SQL:
 LinksArray = Split(SimpleCSV("SELECT xLink FROM Files WHERE fType In('xID','Passport') AND User_ID='" & rs!Files.User_ID & "'"))

But got a different error
Capture.PNG


Debugging Point to

Capture2.PNG
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 17:14
Joined
Oct 29, 2018
Messages
21,358
So the new code will be
SQL:
 LinksArray = Split(SimpleCSV("SELECT xLink FROM Files WHERE fType In('xID','Passport') AND User_ID='" & rs!Files.User_ID & "'"))

But got a different error
View attachment 81839

Debugging Point to

View attachment 81840
Hi. We need to find out what is the name of the User_ID field in your recordset. So, do a Debug.Print strSQL, then copy and paste it in the query designer. Run it, and then let me know what is the name of the column with the User_ID. Thanks.
 

alvingenius

IT Specialist
Local time
Today, 02:14
Joined
Jul 10, 2016
Messages
169
Hi. We need to find out what is the name of the User_ID field in your recordset. So, do a Debug.Print strSQL, then copy and paste it in the query designer. Run it, and then let me know what is the name of the column with the User_ID. Thanks.

You mean this ?

Code:
SimpleCSV("SELECT xLink FROM Files WHERE fType In('xID','Passport') AND User_ID='" & rs!files.User_ID & "'")

because of the whole table fields here
Capture.PNG
 

alvingenius

IT Specialist
Local time
Today, 02:14
Joined
Jul 10, 2016
Messages
169
Edit1 :


Even when i tried the array with your simplecsv() function alone here

SQL:
Private Sub Command27_Click()

Dim rs As Recordset
Dim i As Integer
Dim LinksArray() As String
    LinksArray = Split(SimpleCSV("SELECT xLink FROM Files WHERE fType In('xID','Passport') AND User_ID='" & rs!User_ID & "'"))

For i = LBound(LinksArray) To UBound(LinksArray)
    'looping through array here
    Debug.Print LinksArray(i)

Next i

End Sub

it gives me this
Capture1.PNG


and it points me to the Linksarray with yellow
 

Attachments

  • Capture1.PNG
    Capture1.PNG
    34.7 KB · Views: 108

Users who are viewing this thread

Top Bottom