email PDF filtered form does not work

[Ruegen]

Why is it that a form that is filtered won't email a pdf (onclick) compared to a form that isn't



in the report query I have Forms!frmBookingEdit!TeacherID

If the form frmBookingEdit is unfiltered to TeacherID it shows all records (as it should) and not the individual one filtered by TeacherID and the command to email the pdf of the report works fine

however if I filter the form with "TeacherID=" & Teacher ID

then the report comes up with #type errors

I can see the record in frmBookingEdit and the command to email the pdf works fine however the report comes up blank/type

why is this so?

[ItsMe]

Maybe "TeacherID=" & Me.TeacherID

[Ruegen]

Maybe "TeacherID=" & Me.TeacherID

the search form opens the frmBookingEdit with the correct school/TeacherID however when I go to email the report I get a type error.

If I open the form without using the search form and thus not using the filter "TeacherID=" & Me.TeacherID - the report works.

[Ruegen]

Note the report query criteria is Forms!frmBookingEdit!TeacherID

in turn if the form is open on the record I expect that the query for the report will pull the id from that record using Forms!frmBookingEdit!TeacherID but I get a type error

this only happens if the form is filtered by ID using "TeacherID=" & Me.TeacherID (and the form is opened from the search form)

[ItsMe]

I don't know. The criteria being in more than one place should not matter if it is the same criteria. Maybe the form the Access query is looking for in its criteria is not available.

I try to avoid adding criteria in Access queries and use them for relationships and joins for the most part. Then I can add criteria later in forms or reports using VBA.

[Ruegen]

I don't know. The criteria being in more than one place should not matter if it is the same criteria. Maybe the form the Access query is looking for in its criteria is not available.

I try to avoid adding criteria in Access queries and use them for relationships and joins for the most part. Then I can add criteria later in forms or reports using VBA.

So do I but I couldn't figure out how to get criteria for sendobject since I don't think it is an option.

[ItsMe]

What is it you are trying to email, a report based on a form's recordset? If so...

Make a copy of your Access query and a copy of your form. Remove the criteria from your Access query. Open the form and use the filter tools to replicate what was in the Access query (the criteria you deleted).

Once your form is all filtered, then fire the following code. You can view the criteria in the imediate window in the VBA editor (Ctrl + G). Place the code in a click event of a new button or something

Dim strWhere As String
strWhere = Me.Filter
MsgBox strWhere
Debug.Print strWhere
DoCmd.OpenReport "rptToEmail", acViewPreview, , strWhere, acHidden
DoCmd.SelectObject acReport, "rptToEmail", False
DoCmd.OutputTo acOutputReport, "rptToEmail", acFormatPDF
DoCmd.Close acReport, "rptToEmail"

With that, you may be able to decide how to get your form's recordset the way you want it. You could at least update the form's filter to match the criteria. I prefer updating the recordset because then it takes it out of the User's control to change it by removing a filter.

[Ruegen]

What is it you are trying to email, a report based on a form's recordset? If so...

Make a copy of your Access query and a copy of your form. Remove the criteria from your Access query. Open the form and use the filter tools to replicate what was in the Access query (the criteria you deleted).

Once your form is all filtered, then fire the following code. You can view the criteria in the imediate window in the VBA editor (Ctrl + G). Place the code in a click event of a new button or something

Dim strWhere As String
strWhere = Me.Filter
MsgBox strWhere
Debug.Print strWhere
DoCmd.OpenReport "rptToEmail", acViewPreview, , strWhere, acHidden
DoCmd.SelectObject acReport, "rptToEmail", False
DoCmd.OutputTo acOutputReport, "rptToEmail", acFormatPDF
DoCmd.Close acReport, "rptToEmail"

With that, you may be able to decide how to get your form's recordset the way you want it. You could at least update the form's filter to match the criteria. I prefer updating the recordset because then it takes it out of the User's control to change it by removing a filter.

OK, that is for viewing the report I assume..?, will that then allow me to email that rptToEmail as a PDF?

Normally I open the form, click the button and have it email the current seen record's report. If I don't put in the criteria on the query of the report I get all the records in the PDF - I assume yours makes a pdf appear in a msgbox??

[ItsMe]

I am not used to using acFormatPDF. Nowadays Access can email directly without having adobe software installed on the client. I am not hip to the Docmd EMail thing yet. I believe there is a way you can get it all into one line. If not, you can automate saving to disc and then tell your Docmd email to add as an attachment.

Regardless, the file is going to a temp folder, whether it is in Outlook Program files via a one line Docmd or somwhere you dictate. And you still need to resolve the issue of your query not working.

The code I provided there is just to help you explore some ways of doing things. You were placing criteria into an Access query and dedicating this qury to a couple of forms. It seems you may be able to create a better approach. You just need to get a couple things out of the way first.

If you need help with that code, just ask me. If you need to try a different aproach, I may not be able to help. I will still try, but I don't yet see how you plan to email anything to anybody.

[Ruegen]

I am not used to using acFormatPDF. Nowadays Access can email directly without having adobe software installed on the client. I am not hip to the Docmd EMail thing yet. I believe there is a way you can get it all into one line. If not, you can automate saving to disc and then tell your Docmd email to add as an attachment.

Regardless, the file is going to a temp folder, whether it is in Outlook Program files via a one line Docmd or somwhere you dictate. And you still need to resolve the issue of your query not working.

The code I provided there is just to help you explore some ways of doing things. You were placing criteria into an Access query and dedicating this qury to a couple of forms. It seems you may be able to create a better approach. You just need to get a couple things out of the way first.

If you need help with that code, just ask me. If you need to try a different aproach, I may not be able to help. I will still try, but I don't yet see how you plan to email anything to anybody.

Thanks - I do need you help.

To give you an idea - to get emails sent when the staff make a new booking it is unfiltered - a tempvar collects the ID number of the school, makes a new teacherID and inserts the SchoolID in the table.

That way the booking and teacher are related to the school.

Once the fill out the booking form they click a button of save and email (or save and don't email)

I use this to get it done - note I have a different named report with the same query and info. Because the form is unfiltered this all works well.

The code I use is:

If IsNull(Me.TeacherEmail) ThenMsgBox "You require an email in the teacher email field to email the school"
Else


On Error GoTo ErrorHandler
    DoCmd.SendObject acReport, "rptNewBookingEmail", "PDFFormat(*.pdf)", TeacherEmail, SchoolEmail, , "School Performance Tours - Booking Confirmation", , True
    DoCmd.Close acForm, "frmBookingNew", acSaveYes
    
    


ErrorHandler:
Select Case Err
Case 2501
MsgBox "You have cancelled the email"


End Select


End If

Because I have in the criteria of the report forms!frmNewBooking!TeacherID

It only pulls the same record information in the pdf before it sends it off to the client.

When the staff go to open an already made booking just to edit/change details about it they can click a button on this form that does the same thing. This works if just going through the records on the form unfiltered.

I have a search form that searches through schools and their corresponding bookings. When they click on the booking it opens the frmBookingEdit form and they can edit the booking previously made. From here if they click the button that send the pdf as an email the pdf of that report has type instead of the record.

I've tested this. Since the form is pulling the one record - forms!frmBookingEdit!TeacherID will not pick up the record that I have brought up using the search form.

I've tried making a tempvar so that when you click the button it gets the TeacherID (visible on the form) and saves the value (or at least tries and I get an error)

I tried replacing the criteria in the report query with the tempvar (which would be TeacherID) but with no luck and the same result.

[ItsMe]

After just glossing over this, it does seem that you have a query dedicated to a specific form, if not a specific function, ie email. This criteria that is hardcoded seems to be limiting the functionality of the Access query.

Let me read the post again and see what I can see

[ItsMe]

Well, if you stay with .SendObject you won't be able to pass the where clause to that line of code. At least I don't see how. That means you will have to pass the where clause to the report when it opens.

If you don't want to pass the where clause to the open event of the report, then you will need to save the PDF and attach it to an Email Object. YOu can include the where clause directly in the OutputTo method. I showed you an example above.

If you stay with how you have things now. You will need to make multiple queries and possibly multiple forms. You will have to open the form that matches the hardcoded criteria for the specific purpose. In other words, The user will have many places to click and navigate to the correct form to accomplish the task at hand.

It was like I was mentioning earlier about using filters instead of changing the recordset programaticaly. Using the filter is cheap and easy in the short term. Then the user starts fiddling with things.

Do you have an idea of which aproach you want to explore?

[Ruegen]

Believe it or not I found the problem. Any date that is past the current date will not show. This isn't a problem because we won't be sending past shows as confirmations!

No where in the query did I state that it shouldn't show results before current date() so I can't figure out why but it doesn't worry me. I put a refresh form on the button just in case someone changes an old date to new date for a booking (which I believe was the original culprit).

It's still a mystery to me but frankly I'm over it.

Thanks for your help ItsMe.

Now I am going to go get drunk - It is friday here.

[Ruegen]

Believe it or not I found the problem. Any date that is past the current date will not show. This isn't a problem because we won't be sending past shows as confirmations!

No where in the query did I state that it shouldn't show results before current date() so I can't figure out why but it doesn't worry me. I put a refresh form on the button just in case someone changes an old date to new date for a booking (which I believe was the original culprit).

It's still a mystery to me but frankly I'm over it.

Thanks for your help ItsMe.

Now I am going to go get drunk - It is friday here.

Turns out some of the staff also put the school under an area that was not to show. Putting that one in the manual...