Lebans Rpt to pdf file question

[rfuscjr via AccessMonster.com]

I am no super techie guy but was able to open his db, insert my report name
in his function
and get the thing convert my report to pdf BUT.....when I stick the function
call into ***my*** vba code
(behind my report button) somethng VERY strange happens.

I have code(below) that runs thru a file of ids: tblAllProviderIds and moves
the first id in
that file into a second file: tblReportProviderId. This second table is
linked in the Report's control source query pull data from my:
MasterReportDataTable for just that one particular id. It then opens the
Access Report, Refreshes it to the share as an .RTF file (with the id as part
of
the file name) and continues to loop in this fashion until a report has been
generated for all ids...hope this makes sense. In other words we sort of
burst the Access report by id.

Anyhoo,
I stuck the convert function at the appropriate spot and it
failed at the point where it was called with a: Variable not defined error on:
blRet. I forgot
my: DIM blRet As Boolean statement!!!!

I added it and ran again and now it fails sooner with a type mismatch on:

Set rstTemp = db.OpenRecordset("MasterReportId", dbOpenTable)
(Note that rstTemp was indeed earlier set as: Dim rstTemp As Recordset)

I see no relation between the two yet when I remove: Dim blRet As Boolean it
gets past the Set rstTemp statement. As soon as I put the Dim blRet in, I
get the type mismatch.

Any ideas?

Some sample code is provided so get the idea...

Dim db As Database
Set db = CurrentDb
Dim strSqlStmnt As String
Dim qdf As QueryDef
Dim stDocName As String
Dim xcelapp As Object
Dim rstCCs As Recordset
Dim RptName As String
Dim rstTemp As Recordset
Set xcelapp = CreateObject("Excel.Application")
Set qdf = db.CreateQueryDef("")
Dim passNbr As Integer
'Dim blRet As Boolean
DoCmd.SetWarnings False

DoCmd.OpenQuery "qryLOAD_ActiveProviderIds", acViewNormal
Set rstTemp = db.OpenRecordset("tblReportProviderId", dbOpenTable)
Set rstCCs = db.OpenRecordset("tblAllProviderIds", dbOpenTable)

With rstCCs
.MoveFirst
Do Until rstCCs.EOF
RptName = "X:\Reports\" & rstCCs!LVPGID & "_Capacity_Sheet"
strSqlStmnt = "INSERT INTO tblReportProviderId ( LVPGID )SELECT Min
(tblAllProviderIds.LVPGID) AS MinOfLVPGID FROM tblAllProviderIds"
qdf.SQL = strSqlStmnt
qdf.ODBCTimeout = 0
qdf.Execute


blRet = ConvertReportToPDF("RptMaster", vbNullString, _
RptName & ".pdf", False, True, 150, "", "", 0, 0, 0)

 

[Douglas J. Steele]

Try changing the declaration from

Dim rstTemp As Recordset

to

Dim rstTemp As DAO.Recordset

Odds are your application has references to both ADO and DAO. Both of those
models have an object called "Recordset" in them, and usually the reference
to ADO is higher in the list of references than the one for DAO, so an
ambiguous declaration like what you've got results in an ADO recordset. (If
you want to ensure it's an ADO recordset, you'd use Dim rstTemp As
ADODB.Recordset. However, the code definitely needs a DAO recordset.)

 

[rfuscjr via AccessMonster.com]

Well it is somewhat greek to me but that worked....the only thing is that
Adobe opens up...for each report I create in the loop.....can I stop that
from happening?

THANKS FOR SAVING THE DAY!!!!!

Try changing the declaration from

Dim rstTemp As Recordset

to

Dim rstTemp As DAO.Recordset

Odds are your application has references to both ADO and DAO. Both of those
models have an object called "Recordset" in them, and usually the reference
to ADO is higher in the list of references than the one for DAO, so an
ambiguous declaration like what you've got results in an ADO recordset. (If
you want to ensure it's an ADO recordset, you'd use Dim rstTemp As
ADODB.Recordset. However, the code definitely needs a DAO recordset.)

I am no super techie guy but was able to open his db, insert my report name
in his function

[quoted text clipped - 73 lines]

.
.

 

[Dirk Goldgar]

Well it is somewhat greek to me but that worked....the only thing is that
Adobe opens up...for each report I create in the loop.....can I stop that
from happening?

See my answer to your separate question about this.