#Type! error

[LDW]

Hello everyone!

I’m new to the Access Forum and was hoping someone could help me.

Although I’ve been using Access for many, many years, I am a beginner with VBA. I found a web forum that referenced how to print multiple .pdf files from only one report. My report has 102 AFO_NAMEs, so I’m trying to avoid creating/printing 102 reports separately.

I was able to convert most of the code I found to my report and field names, and got my report to print into 102 different .pdf files. However, each report is blank with a #Type! error.

Just for some background, my report name is Rpts_by_AFO, and it is setup to Group by AFO_NAME. My actual query it pulls from is ALL-STATES.

Here is my code now…
______________________________
Function PrintReports()

Dim sql As String
Dim db As Database
Dim rs As Recordset

sql = " SELECT [AFO_NAME] FROM [ALL-STATES] GROUP BY [AFO_NAME]"
Set db = CurrentDb
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Do Until rs.EOF
DoCmd.OpenReport "Rpts_by_AFO", acViewPreview, , sql = " & rs![AFO_NAME]"
DoCmd.OutputTo acOutputReport, "Rpts_by_AFO", "PDFFormat(*.pdf)", "C:\Desktop\Q1files\" & rs![AFO_NAME] & ".pdf", True
DoCmd.Close acReport, "Rpts_by_AFO"
DoEvents
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set db = Nothing

End Function
______________________________

Any help you can provide is much appreciated! Thank you!

 

[RuralGuy]

At the very least you need to change this line:
DoCmd.OpenReport "Rpts_by_AFO", acViewPreview, , sql = " & rs![AFO_NAME]"
...to...
DoCmd.OpenReport "Rpts_by_AFO", acViewPreview, , "sql = ' " & rs![AFO_NAME] & " ' "
...
assuming this was not a typo.

 

[LDW]

No, that was not a typo...so thanks for the help! I made that changed and now I get a popup box that says "Enter Parameter Value: sql".

Is something wrong with my SELECT statement because of that error?

 

[RuralGuy]

It is trying to apply a filter where a Field named sql has a value of whatever value in in the AFO_NAME field. You probably want it to read:
"[AFO_NAME] = ' " & rs![AFO_NAME] & " ' "

 

[pr2-eugin]

LDW, could you please explain plainly what is that you are doing here..

DoCmd.OpenReport "Rpts_by_AFO", acViewPreview, ,[COLOR=Red][COLOR=Black]"[/COLOR][B]sql = '" & rs![AFO_NAME] & "'[/B][/COLOR]"

 

[LDW]

Thanks, RuralGuy...that solved my sql error! However, I am back to running 102 reports with #Type! error.


To answer the other post on what am I trying to do, I have one report that is 197 pages. It is grouped by AFO_NAME and under each group is a list of team members. I wanted to print this to multiple PDF...one for each AFO_NAME with their team member info on that as well. Some are one page others will be two pages.


I have the code running without errors, but when I run it...it prints 102 PDF reports with #Type! error and no info. This is my current code now.
______________
Function PrintReports()

Dim sql As String
Dim db As Database
Dim rs As Recordset


sql = "SELECT [AFO_NAME] FROM [ALL-STATES] GROUP BY [AFO_NAME]"
Set db = CurrentDb
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)


Do Until rs.EOF
DoCmd.OpenReport "_by_AFO", acViewPreview, , "[AFO_NAME] = ' " & rs![AFO_NAME] & " ' "
DoCmd.OutputTo acOutputReport, "Rpts_by_AFO", "PDFFormat(*.pdf)", "C:\Desktop\Q1files\" & rs![AFO_NAME] & ".pdf", True
DoCmd.Close acReport, "Rpts_by_AFO"
DoEvents
rs.MoveNext

Loop


rs.Close

Set rs = Nothing
Set db = Nothing


End Function

 

[RuralGuy]

You may need to use the Nz() function to get around the Nulls.
sql = "SELECT Nz([AFO_NAME], "") FROM [ALL-STATES] GROUP BY [AFO_NAME]"

 

[LDW]

There are no nulls but tried it anyway, still getting the #type! error. Thanks anyway for the suggestion!

 

[pr2-eugin]

Okay in a separate Query window, try this and see if you get the desired result..

[FONT=Calibri]SELECT [AFO_NAME] FROM [ALL-STATES] GROUP BY [AFO_NAME]

[/FONT]

 

[RainLover]

I was able to convert most of the code I found to my report and field names, and got my report to print into 102 different .pdf files. However, each report is blank with a #Type! error.

Can you explain more about this error.

Where does it appear would be a good start plus anything else you may have that might be useful.

 

[LDW]

Thank you all for your help! I was able to troubleshoot with your help and some other posts. Have a great day!

 

[RainLover]

LDW

Could you please post the solution as we would love to know the answer too.

 

[LDW]

Function PrintReports()

Dim sql As String
Dim db As Database
Dim rs As Recordset

sql = "SELECT AFO_NAME FROM AFO_CODES"
Set db = CurrentDb
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Do Until rs.EOF
DoCmd.OpenReport "Rpts_by_AFO", acViewPreview, , "[AFO_Name]='" & rs![AFO_NAME] & "'"
DoCmd.OutputTo acOutputReport, "Rpts_by_AFO", "PDFFormat(*.pdf)", "C:\Desktop\Q1files\" & rs![AFO_NAME] & "_" & Format(Date, "mm""-""dd""-""yyyy") & ".pdf", False
DoCmd.Close acReport, "Rpts_by_AFO"
DoEvents
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set db = Nothing

End Function

 

[RainLover]

Thanks for that.

Most appreciated.

 

[RuralGuy]

Do you think the problem was in the GroupBy Clause?

 

[LDW]

Yes, I couldn't get that to work and then it ran fine without it.

 

[RuralGuy]

Thank you. I'll file that one away for use later.

 

[LDW]

Me too!!!!