do loop with outputto rtf (1 Viewer)

[Mile-O]

Okay, post the complete code of that routine:

 

[dan-cat]

I refuse to die by a thousand cuts

 

[ddmcn]

Okay...here goes...

Thanks, Mile-O. A couple of things you should know.

First, the query results contain records for all the countries in our database. Secondly, the recordset contains multiple records for each country. For instance, there is a record for popluation and another record for GDP and another record for number of airports, etc. What I need the code to do is create a report for a the first country...then go on to the next country and create a new report and so on, using the country name (Entity) as part of the file name.

What I am getting right now is several large reports...for each country that contain data for all the countries. In other words it is not distinguishing between the countries...just dumping all the data into each report created.

Private Sub Command0_Click()

Dim db as DAO.Database
Dim rec as DAO.Recordset
Dim geoID As String
Dim strPathCountry As String

Set db = CurrentDb()
Set rec = db.OpenRecordset("QUERYcr")
Do While Not rec.EOF

geoID = rec.Fields("GEOCODE")
strPathCountry = "U:\COUNTRY_REPORTS\" & rec.Fields("Entity") & ".rtf"

DoCmd.OuptputTo acOutputReport, "COUNTRY_REPORT", acFormatRTF, strPathCountry
rec.MoveNext

Loop
rec.Close
set db = Nothing
set rec = Nothing

End Sub

 

[Mile-O]

What does this do for you?

Private Sub Command0_Click()

    On Error GoTo Err_Command0_Click

    Dim db As DAO.Database
    Dim rec As DAO.Recordset
    Dim strID As String
    Dim strPathCountry As String

    Set db = CurrentDb()
    Set rec = db.OpenRecordset("QUERYcr")
    
    With rec
        .MoveLast
        .MoveFirst
        Do While Not .EOF
            strID = .Fields("GEOCODE")
            strPathCountry = "U:\COUNTRY_REPORTS\" & .Fields("Entity") & ".rtf"
            DoCmd.OuptputTo acOutputReport, "COUNTRY_REPORT", acFormatRTF, strPathCountry
            .MoveNext
        Loop
        .Close
    End With
    
Exit_Command0_Click:
    Set strID = vbNullString
    Set strPathCountry = vbNullString
    Set rec = Nothing
    Set db = Nothing
    Exit Sub
    
Err_Command0_Click:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_Command0_Click

End Sub

 

[dan-cat]

Flippin 'eck my db has been downloaded 13 times and yet no-one has seen how I've done this

You're missing the cool piece of the code

intId = rec.Fields("Id")
strSQL = " SELECT tblCountry.Id, tblCountry.Country, tblCountry.Description, tblCountry.Population" & _
" FROM tblCountry" & _
" WHERE (((tblCountry.Id)=" & intId & "));"
qdf.SQL = strSQL

What I did was rebuild the structure of the query each time the report was outputted. This made sure the correct country id was detailed in each report.

 

[ddmcn]

Still trying to write 1215 page rtf file

I'm new at this VB stuff...but, would a For...Each...Next statement fix this problem?

 

[Mile-O]

Flippin 'eck my db has been downloaded 13 times and yet no-one has seen how I've done this

I've not downloaded it.

 

[ddmcn]

So....

does that mean I need to put the SQL text of my query in the code? I think that's what you're saying, Dan.

 

[dan-cat]

My moderator won't download my samples - when will I ever achieve recognition for the genius that I am?

dd,

Yah, in the original code I selected a table called countries as the recordset. This listed all the countries and their respective id's.

Then I altered the structure of the query that your report was based on so the report only detailed the country detailed in the current record of the recordset. The report was then outputted to rtf. After that we moved on to the next record within the record set and repeated the procedure until rec.EOF (end of file)

If you want you can post/email your db and I'll show you want I mean.

 

[Mile-O]

My moderator won't download my samples - when will I ever achieve recognition for the genius that I am?

How very...orange!

 

[ddmcn]

How very Orange?

Sounds like an Ulster joke to me.

Anyway...I'm getting the same result..a 1215 page report. It doesn't seem to want to group the records by Entity ... country name.

To see the kind of data that I am dealing with go to:

http://www.cia.gov/cia/publications/factbook/index.html

and select a country from the drop down list.

Dennis

 

[dan-cat]

Dennis,

There is not much more I can do for you without looking at your db. My example shows you how to navigate through a record set then rebuild the structure of your query depending on the current record within that recordset.

You can post a db with no data in if you're worried about confidential data. Other than that its difficult to help you.