UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Looping Through 2 Datasets, Access 2010    
 
   
bryan fusilier
post Aug 17 2017, 10:09 PM
Post#1



Posts: 85
Joined: 8-July 10



I might be thinking about this all wrong, but I need to create a routine that allows me to print the attached results (excel file) in a specific order. My query returns two fields, "lngIDScope" and "hypBlindsPIDLink" . The first field represents a parent record and the second field are PDF files associated with the parent record. Each parent record can have multiple PDF's associated with it.

I'm trying to create a routine that sends this entire dataset to the printer in a specific order. What I need to do is to:
1) print a report that I've created that is filtered by the first column in the dataset "lngIDScope"
2) print the PDF's associated with that record.
3) Repeat (Except I only want the report from step 1 to print once, not once for each associated record, which is what is happening now)

I need this to loop through the entire dataset to create a package of documents where the Parent report prints, followed by the associated PDF's, then on the next Parent Report followed by those PDF's etc. The idea is that the main report becomes a cover sheet for the associated PDF's behind it so that a stack of papers can easily be sorted by hand by using the "coversheet" as a separator.

I know how to create a loop that does things with a dataset one record at a time, but I don't know how to prevent the duplicate records in column one from printing multiple times. If you look at the dataset you will see that the data in the first column "lngIDScope" gets repeated for each associated PDF file. I only want the main report to print once, followed by the associated PDF's, then to loop to the next unique value in column one, print that report and then the associated PDF’s and on and on until the loop works its way through the entire dataset.

Where I’m stuck is trying to prevent the main report from printing multiple times, one for each associated PDF. It seems like I should be able to create a loop where it goes through only the unique values of the first column to print a report and to then print the associated PDF's and then jump to the next uniqe value in column one, print the report and then print the PDF's, but for the life of me I have no idea how to approach that.


I hope I’m explaining this in a way that makes sense and apologize in advance if it doesn’t.
Attached File(s)
Attached File  Multiple_Dataset_Question.zip ( 17.89K )Number of downloads: 11
 
Go to the top of the page
 
MadPiet
post Aug 17 2017, 10:57 PM
Post#2



Posts: 2,257
Joined: 27-February 09



Two steps...
1. create a forward-only read-only recordset of unique lngIDScope values

SELECT DISTINCT lngIDScope
FROM [MyTable]
ORDER BY lngIDScope

then
for each of those,
1. print cover page.
2. open recordset (keep writing dataset... spending too much time in PowerBI!) of BlindsPIDLink files.

strsql = "SELECT FileName FROM ChildTable WHERE lngIDScope = " & rsScopeValues.Fields("lngIDScope").Value
rs.Open(strSQL,dbForwardOnly)
do until rs.EOF
...open file
... print
... close
rs.MoveNext
loop
rs.Close
Go to the top of the page
 
bryan fusilier
post Aug 18 2017, 04:28 PM
Post#3



Posts: 85
Joined: 8-July 10



Thanks MadPiet! I'm working my way through your example and am going to now show my ignorance on a couple of things. I'm running into an issue with the rs.open portions of your code. I believe it is because this is an ADO connection and I'm trying to reuse some existing DAO code in this old application. I'm afraid I'm really not up to speed enough on the difference between the two to understand what each one can and can't do. I'm now reading up on all that.

Here is the code as it stands now. "I had to change my original query so the original email referencing the feild "lngIDScope" has been changed to "lngID_EIL". The query that generates the data I need is fairly complex so I basically write the results of that into a simple table called "BryanEILTest" and call on that table to test this thing out." The editor does not like either of the two rs.Open statements, nor does it like the strSQL statement.

Not sure what I'm doing wrong, nor do I understand if this is the proper way to create the loop.


Private Sub cmdPrintEIL_Click()
On Error GoTo ErrorHandler
Dim DB As DAO.Database
Dim rsPID As DAO.Recordset
Dim rsScopeValues As DAO.Recordset
Dim strPIDHyperlink As String
Dim strSQL As String
Dim strSQLEIL As String
Dim strPIDActualAddress As String

Set DB = CurrentDb()
'step 1
strSQLEIL = "SELECT DISTINCT lngID_EIL From [BryanEILTest]ORDER BY lngID_EIL;"

rs.Open(strSQLEIL ,dbForwardOnly)

DoCmd.OpenReport "rptEIL", , , "[lngID_EIL]= " & rsScopeValues.Fields("lngID_EIL").Value, , "PrintAllDocuments"


'Step 2 iterate through the 2nd table to print related documents

strSQL= "SELECT [hypBlindsPIDLink]" & _
"FROM BryanEILTest" & _
"WHERE lngID_EIL = " & rsScopeValues.Fields("lngID_EIL").Value"

rs.Open(strSQL,dbForwardOnly)


With rsPID
If Not .BOF And Not .EOF Then
.MoveFirst
Do Until .EOF
strPIDHyperlink = !hypBlindsPIDLink
If Len(strPIDHyperlink & "") > 0 Then
strPIDActualAddress = Mid(strPIDHyperlink, (InStr(1, strPIDHyperlink, "#") + 1), (Len(strPIDHyperlink) - (InStr(1, strPIDHyperlink, "#") + 1)))
If FileExists(strPIDActualAddress) Then
If OpenOrPrintFile(strPIDActualAddress, "Print") = False Then
If OpenOrPrintFile(strPIDActualAddress, "Open") = False Then
'do nothing; we do not want any 'error' messages
End If
End If
End If
End If
.MoveNext
Loop
End If
.Close
End With

End Sub
Go to the top of the page
 
MadPiet
post Aug 18 2017, 06:21 PM
Post#4



Posts: 2,257
Joined: 27-February 09



I'd just upload the database, but I have 2016, and you wouldn't be able to read it... but here's the code I wrote... It loops through the outer recordset (of Scopes) and then prints all the names of the PDF files to the immediate window (that's what all that Debug.Print stuff is about.) Just remove/comment those out and substitute your code for printing out the PDFs instead.

CODE
Option Compare Database
Option Explicit

Public Sub ProcessPDFs()
    Dim rsScope As DAO.Recordset
    Dim rsPDFs As DAO.Recordset
    
    Set rsScope = DBEngine(0)(0).OpenRecordset("SELECT DISTINCT PDFFileList.lngIDScope FROM PDFFileList ORDER BY PDFFileList.lngIDScope;", dbOpenForwardOnly)
    Do Until rsScope.EOF
        'do something with values
        Debug.Print rsScope.Fields("lngIDScope").Value
        '  I'm opening a filtered recordset here... just the "children" of the current lngIDScope
        Set rsPDFs = DBEngine(0)(0).OpenRecordset("SELECT hypBlindsPIDLink FROM PDFFileList WHERE PDFFileList.lngIDScope = " & rsScope.Fields("lngIDScope").Value)
        
        Do Until rsPDFs.EOF
            Debug.Print vbTab & rsPDFs.Fields(0).Value
            rsPDFs.MoveNext
        Loop  ' -- for rsPDFs

        rsPDFs.Close
        rsScope.MoveNext
    Loop  ' -- for rsScope
    
    Set rsPDFs = Nothing
    rsScope.Close
    Set rsScope = Nothing
End Sub
Go to the top of the page
 
bryan fusilier
post Aug 19 2017, 07:36 PM
Post#5



Posts: 85
Joined: 8-July 10



Thank you so much MadPiet! I finally understand how this is supposed to work. It's not quite really working yet, but I don't really think the code is the issue. But at this point I'm not sure what the issue is.

You're code sends everything to the immediate window exactly in the correct order, but when I insert the actual code to print the documents they don't come out of the printer in the correct order. All off the coversheets print first and then all the PDF's print. I can watch these files hit the print spooler and it seems like once it gets there it's getting all jumbled up. I'm not really sure why. Maybe I need to pause the code somehow to give each cover sheet time to print before getting to the PDF's?? I'm really not sure. Any thoughts on what might be causing the code to print in the correct order to the immediate window, but to then get all out of synch as it goes to the printer?

Here is the your code modified to work with my parameters:

Private Sub cmdPrintEIL_Click()
Dim rsScope As DAO.Recordset
Dim rsPDFs As DAO.Recordset
Dim strPIDHyperlink As String
Dim strSQL As String
Dim strPIDActualAddress As String

Set rsScope = DBEngine(0)(0).OpenRecordset("SELECT DISTINCT lngID_EIL From [BryanEILTest]ORDER BY lngID_EIL;", dbOpenForwardOnly)
Do Until rsScope.EOF
'do something with values

'Debug.Print rsScope.Fields("lngID_EIL").Value


DoCmd.OpenReport "rptEIL", acViewNormal, , "[lngID_EIL]= " & rsScope.Fields("lngID_EIL").Value

'Step 2 iterate through the 2nd table to print related documents' I'm opening a filtered recordset here... just the "children" of the current lngIDScope
Set rsPDFs = DBEngine(0)(0).OpenRecordset("SELECT hypBlindsPIDLink FROM BryanEILTest WHERE BryanEILTest.lngID_EIL = " & rsScope.Fields("lngID_EIL").Value)


Do Until rsPDFs.EOF
'Debug.Print vbTab & rsPDFs.Fields(0).Value

strPIDHyperlink = rsPDFs.Fields("hypBlindsPIDLink").Value
If Len(strPIDHyperlink & "") > 0 Then
strPIDActualAddress = Mid(strPIDHyperlink, (InStr(1, strPIDHyperlink, "#") + 1), (Len(strPIDHyperlink) - (InStr(1, strPIDHyperlink, "#") + 1)))
If FileExists(strPIDActualAddress) Then
If OpenOrPrintFile(strPIDActualAddress, "Print") = False Then
If OpenOrPrintFile(strPIDActualAddress, "Open") = False Then
'do nothing; we do not want any 'error' messages
End If
End If
End If
End If

rsPDFs.MoveNext
Loop ' -- for rsPDFs

rsPDFs.Close
rsScope.MoveNext
Loop ' -- for rsScope

Set rsPDFs = Nothing
rsScope.Close
Set rsScope = Nothing


End Sub
Go to the top of the page
 
MadPiet
post Aug 19 2017, 07:50 PM
Post#6



Posts: 2,257
Joined: 27-February 09



Not entirely sure what's going on. (because my debug.print statements indicate that my code order is correct!). Maybe Access is doing the sync/asynchronous stuff wrong?

You may need to stick an

CODE
Application.DoEvents


after the first print statement (the real ones, not the debug.print junk... that's just for me to see what's going on... ) I think Access isn't waiting until after the print command is executed completely before going to the next record. Might need to check with the more knowledgeable folks around here, though. I haven't done a lot of serious programming in Access in a long time!
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    13th December 2017 - 09:46 AM