use DoCmd.OutputTo to export a PDF of the current record on a report?

[Xenver]

Hello,

I want to create a button on a form that exports the current record on the form to a PDF of a report. I've taken the code i found here:

http://bytes.com/topic/access/answers/909411-can-i-use-docmd-outputto-export-current-record-only

and modified it to be this:

Private Sub Command481_Click()
 
Dim strOriginalRecordSource As String
Dim strNewRecordSource As String
 
strOriginalRecordSource = Me.RecordSource
strNewRecordSource = "SELECT * FROM [qry_Projects Credits] WHERE [Project Number] = " & Me![Project Number]
 
Me.RecordSource = strNewRecordSource
 
DoCmd.OutputTo acOutputReport, "rpt_Projects Credits", acFormatPDF, , True, "", 0
 
Me.RecordSource = strOriginalRecordSource
 
End Sub

The form is based on the query "qry_Projects Credits". The primary key is "Project Number". The report i want to export to is called rpt_Projects Credits. When I try to run this i get the error:

Run-Time Error '3075': Syntax error (missing operator) in a query expression '[Project Number]='

When I click "Debug" it points to this line:

Me.RecordSource = strNewRecordSource

I have some experience with programming in C++ from a couple of courses i took a while ago (one first year university programming course and a robotics course later). I think i understand the logic that the code is trying to carry out but im still very new to VBA and am unfamiliar with the syntax.

Thanks,

Xenver

[twinnyfo]

Is you rfirst block of code in your form or report? As it looks to me right now, it is taking the record source for the current Form and changing it, but you are doing nothing to change the record source of the Report.

You could modify the code and place it in the OnOpen event of the Report, but you would run into problems if this report were called independently of hte Form (and if the form had a null value for the Project Number).

Lots of questions about this post, but we're willing to look into it if we get a little more information.

[Xenver]

There are other blocks of code, but i don't think they'll interfere with this one, for the most part they just open/ close forms with the DoCmd.Open[object type] or DoCmd.Close commands. The only one more complicated than that runs an append query that updates the query that this form uses and sets warnings off/turns them on, it looks like this:

DoCmd.SetWarnings (WarningsOff)
DoCmd.OpenQuery "Project to project credits Project Number"
DoCmd.SetWarnings (WarningsOn)
DoCmd.OpenForm "frm_Projects Credits"

I thought by modifying the record source of "qry_projects Credits" I would in-turn modify the record source for the report because the report is based on that query.I'm more than willing to tell you anything you need to know, but I'm a little hesitant to post the whole database.

[twinnyfo]

1. I don't need you to post the whole db.

2. Is your first code block in Post #1 on your FOrm or on your report? Even if you change the record source for the Form, you do nothing to the record source of the report. You are not changing the query, just the record source. You must do the same for the report....

[Xenver]

The Button is on the form. I think i see what you're saying, I need to change the record source of the report, not the form. How do i go about doing that while keeping the button on the form?

I thought by using these lines:

strNewRecordSource = "SELECT * FROM [qry_Projects Credits] WHERE [Project Number] = " & Me![Project Number]
Me.RecordSource = strNewRecordSource

I would be changing the record source of the query, but i guess i just changed the record source of the form to the query containing only the current record, is that right?

[twinnyfo]

Let's start by playing with this code that you already have. No guarantees that it will work right away, but place this in your report's Module:

Private Sub Report_Open(Cancel As Integer)
    Dim strNewRecordSource As String 
 
    strNewRecordSource = "SELECT * FROM [qry_Projects Credits] " & _
        "WHERE [Project Number] = " & _
        Forms![Your Form Name].[Project Number] & ";"
 
    Me.RecordSource = strNewRecordSource
    Me.Requery
End Sub

I must admit that I've never tried to change the record source of a report that is already open, so I don't know what the results will be (and I don't know if it will output to PDF properly, either).

There are other ways of doing this, such as using the form controls in your actual query, but, again, if the Report ever opens independently of the Form, you will have problems.

This might point you in the right direction for now....

[Xenver]

I'm sorry, I must have mislead you, the report will not be open when the button is pressed. With the code you gave me in the report's module the button creates a shortcut to a pdf that doesn't exist in my recent items. The shortcut says it's target is a PDF in my documents folder but windows cannot find the document and it doesn't seem to exist.

also when i press the export button now it prompts me to input a value for whatever the project number is, for example, if the project number is 123456 the prompt would say "enter a value for 123456" I've just been inputting the project number.

Also this isn't new with the addition of the code you gave me, but i think is caused by some changes i made to the original code i found: When I open my form it prompts me to "enter a value for Me.Project_Number, again I've just been entering the project number I just created (all these forms are on a new project form progression). I'm not sure what caused this but before the project number displayed would just default to the latest one created, which is what i want.

[Xenver]

Hello, i realize I'm probably not making much sense, so i made a stripped down version of the db for you to look at, there are still some buttons that don't work and stuff, but it should get the point across. Hope this helps. yes i know the tbl_Projects_Credits is a nightmare in terms of normalization, but it's all i could figure out within the time i can devote to this.