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
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
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
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?
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?
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 .
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?
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. 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.
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.
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