filter report for different pdf files

[sonjuraec]

Bear with me folks- I am just an English teacher so a lot of this is beyond me. My code is creating a pdf file that includes everybody's reports instead of just that faculty member's reports.

Once I get it to generate the correct reports, I then have to figure out how to automatically email them.

Public Sub something3()
Dim db As Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT DISTINCT tblsection.Faculty, left(vtblfaculty.firstname,1)&vtblfaculty.lastname AS fn, vtblfaculty.email FROM vtblfaculty INNER JOIN tblsection ON tblsection.faculty=vtblfaculty.faculty WHERE term=" & Forms!frmimport!cbxTerm)

With rs
While Not rs.EOF
DoCmd.OpenReport "Backgrounds", acViewPreview, , , , "faculty =" & rs!Faculty
DoCmd.OutputTo acOutputReport, "Backgrounds", acFormatPDF, "C:\Users\Sonjurae\Desktop\" & rs!fn
rs.MoveNext

Wend
End With

One other thing is that I can't get the &".pdf" to work on the end of the filename, but that is no big deal right now.

 

[pbaldy]

You don't have the wherecondition in the right place. Get rid of 2 commas before it and try. Don't forget to close the report after outputting.

 

[sonjuraec]

First THANK YOU for responding! I tried that and it now tells me that it is typed incorrectly... However, when I move my mouse over the rs!faculty, I see a value!

this is where it fails
DoCmd.OpenReport "Backgrounds", acViewPreview, , "faculty =" & rs!Faculty

 

[pbaldy]

What's the code now?

 

[sonjuraec]

With rs
While Not rs.EOF
DoCmd.OpenReport "Backgrounds", acViewPreview, , "faculty =" & rs!Faculty
DoCmd.OutputTo acOutputReport, "Backgrounds", acFormatPDF, "C:\Users\Sonjurae\Desktop\" & rs!fn
rs.MoveNext

Wend
End With

I tried the if thing but that wasn't the problem because i forgot that my rs is also created by the same term that creates the Backgrounds report so the records have to match up

 

[pbaldy]

If faculty is text, see here for the syntax:

http://www.baldyweb.com/wherecondition.htm

 

[sonjuraec]

I did this using the link you provided because it is text

"rs!Faculty = ' "&Faculty&" ' "

now it creates an error for each faculty member

 

[sonjuraec]

Okay- it is now giving me the compile/expected end of statement error at the " ' " at the end. (I put those spaces in there just to post it)

 

[pbaldy]

You've reversed them. Try

"Faculty = '" & rs!Faculty & "'"

 

[sonjuraec]

we got it!!!
it was creating the same report for everyone, just the first faculty's in rs because i wasn't closing the report in between!! I added the Docmd.Close and VOILA!! Thank you so much! You guys are fantastic, and I have already signed up for Visual Basic and Database courses for next semester, so I can start contributing to some of this stuff! Again- thank you so much.

I reposted the code now that it is working in case anyone else needs it!

Public Sub something3()
Dim db As Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT DISTINCT tblsection.Faculty, left(vtblfaculty.firstname,1)&vtblfaculty.lastname AS fn, vtblfaculty.email FROM vtblfaculty INNER JOIN tblsection ON tblsection.faculty=vtblfaculty.faculty WHERE term=" & Forms!frmimport!cbxTerm)

With rs
While Not rs.EOF
DoCmd.OpenReport "Backgrounds", acViewPreview, , "Faculty = '" & rs!Faculty & "'"
DoCmd.OutputTo acOutputReport, "Backgrounds", acFormatPDF, "C:\Users\Sonjurae\Desktop\" & rs!fn
DoCmd.Close
rs.MoveNext
Wend
End With
End Sub

Next step- email them!

 

[pbaldy]

Happy to help!