Exporting a Report as a standalone PDF

[Throwaway54321]

I am pretty new to Microsoft Access so go easy on me.

I am attempting to create a system where a user completes a form, generates a report and uploads it to a LAN. Currently I have the form working fine and a formatted report with all of the tables records displayed over several pages.

I would like to have each record in the table have it’s own separate report/PDF if that makes sense. Preferably the user would press a button and a macro would run that would just export the most recent table entry as a PDF but I’m really not sure how to go about this.

I have C# knowledge but almost no knowledge of VBA. Where could I start with this problem?

Thanks

 

[theDBguy]

Hi. For starters, you could modify your report to only display one record, so when you export it to pdf, there will only one set of data. You can do this by adding a criteria to the query for your report.

 

[Throwaway54321]

Hi. For starters, you could modify your report to only display one record, so when you export it to pdf, there will only one set of data. You can do this by adding a criteria to the query for your report.

I appreciate the reply but I think I need something a bit more dynamic.

Access needs to know that at the end of the form when a ‘submit’ button is pressed it should display the information that has just been entered in a report and then export it as a PDF.

 

[Gasman]

I appreciate the reply but I think I need something a bit more dynamic.

Access needs to know that at the end of the form when a ‘submit’ button is pressed it should display the information that has just been entered in a report and then export it as a PDF.

That is what theDBGuy is suggesting?

 

[Throwaway54321]

That is what theDBGuy is suggesting?

Then I must be way out of my depths here because I don’t understand. This is so frustrating

I don’t have a query at the moment, I just have a table that gets populated once the form has been completed.

 

[Gasman]

It is not hard to create a query using the QBE window and the Builder.

As mentioned, put the criteria into the query for the report that looks at the controls on the form.
Then output the report to pdf.
EG

DoCmd.OutputTo acOutputReport, "rptServedWith", acFormatPDF, "c:\Temp\Served With\" & strFileName & " Served With Report.pdf"

 

[Throwaway54321]

Hi. For starters, you could modify your report to only display one record, so when you export it to pdf, there will only one set of data. You can do this by adding a criteria to the query for your report.

I’ve created a query and can see the criteria box. Should I be creating a criteria that handles the whole behaviour that I want or just displaying a single report at once? I’ve never used criteria/queries so this is quite confusing for me. Sorry :/

 

[Gasman]

Up to you. We do not know what you want.

For example, if you wanted all the details on your form for a particular record, but in a report format, the query would supply all those details. The report would be created in the format you prefer.
Then every time you open the report, it runs the query, which looks at the form for it's criteria and supplies it to the report and the report is output to pdf.

 

[Gasman]

If you are just going to base it on a table, then perhaps look at passing the criteria to the report.

https://docs.microsoft.com/en-us/office/vba/access/concepts/forms-design/apply-a-filter-when-opening-a-form-or-report

 

[Throwaway54321]

Up to you. We do not know what you want.

For example, if you wanted all the details on your form for a particular record, but in a report format, the query would supply all those details. The report would be created in the format you prefer.
Then every time you open the report, it runs the query, which looks at the form for it's criteria and supplies it to the report and the report is output to pdf.

I want all of the information that is entered on the form to be displayed on the report so I don’t think I need a query to filter things out (?) The report currently grabs info from a table and displays a report for each record in the table but in one long report/document.

I just want to automatically open a report (using a button) at the end of a form that displays all of the user inputted data and be able to save that somewhere. I appreciate this is probably incredibly frustrating for you but I really am trying to understand.

 

[theDBguy]

Hi. Are you able to post a sample copy of your database with test data, so we can help you better?

 

[Gasman]

When you say 'displays all of the user inputted data' are you talking about one record or mutliple records?

So if a user entered data for 3 cases, they would select each record in turn and press the button.?

If that is the case look at the link I posted

 

[Throwaway54321]

When you say 'displays all of the user inputted data' are you talking about one record or mutliple records?

So if a user entered data for 3 cases, they would select each record in turn and press the button.?

If that is the case look at the link I posted

No, just the data that they have entered in that single form’s ‘session’. When they press submit at the end it will add the information into a table and I would like a report of just that single records unfiltered data.

Currently it adds that data to the table but I have one report that shows all of the records from the table in a long document.

The idea is that a colleague can open the form, fill it out, press submit and save that document/report that has been generated somewhere on their computer. The documents/reports must be separate.

 

[sonic8]

[...]I would like a report of just that single records unfiltered data.

Maybe here is the root for the misunderstanding I sense here.

If you want to display a single record from a table that process is essentially filtering the data in the table. You would use the ID (unique attribute value to the record you want to display) from the form to filter the report.

You can either do that by creating a query using the data from the form to filter that table. That table would then be the Recordsource of your report, or you build a where-condition clause as text-string and pass that to DoCmd.OpenReport-Method's WhereCondition argument.

Once you grasped that concept, this text (with video) on how to output a filtered report to PDF might be of help.

 

[Throwaway54321]

Maybe here is the root for the misunderstanding I sense here.

If you want to display a single record from a table that process is essentially filtering the data in the table. You would use the ID (unique attribute value to the record you want to display) from the form to filter the report.

You can either do that by creating a query using the data from the form to filter that table. That table would then be the Recordsource of your report, or you build a where-condition clause as text-string and pass that to DoCmd.OpenReport-Method's WhereCondition argument.

Aha! That makes sense... So I need to find a way for the form to ‘know’ which ID it is in the table, filter a query by that ID and then generate a report of that query.

I’m not all of the way there yet but that has helped a lot, thanks

 

[sonic8]

So I need to find a way for the form to ‘know’ which ID it is in the table, filter a query by that ID and then generate a report of that query.

Yes, exactly.
And that should not be hard to solve. If your table contains an AutoNumber-ID, you can just put a TextBox control on the form bound to that table field and it will automatically contain the Id of that record from the table.

 

[June7]

So the code in Submit procedure can use ID of record that is just saved to open report and save as pdf.

Having a textbox bound to ID field is not needed. Don't need to do anything to query. Apply filter to report.

DoCmd.OpenReport "reportname", acViewPreview, , "ID=" & Me!ID
DoCmd.OutputTo acOutputReport, "reportname", acFormatPDF, "C:\some folder path\Test.pdf"
DoCmd.Close acReport, "reportname", acSaveNo

Now what do you want to name this PDF file?

 

[Throwaway54321]

Yes, exactly.
And that should not be hard to solve. If your table contains an AutoNumber-ID, you can just put a TextBox control on the form bound to that table field and it will automatically contain the Id of that record from the table.

I’ve got the text box setup to display the auto number field which for me is ‘ID’. Now I just need to get that ID value into the ‘Criteria’ box on the query right?

 

[Throwaway54321]

Yes, exactly.
And that should not be hard to solve. If your table contains an AutoNumber-ID, you can just put a TextBox control on the form bound to that table field and it will automatically contain the Id of that record from the table.

I did it! I love you!

 

[Uncle Gizmo]

So I need to find a way for the form to ‘know’ which ID it is in the table, filter a query by that ID and then generate a report of that query.

You might find my Blog on this very subject useful.

Multiple Reports - Slide Run Through

Essentially my blog is in answer to a particular question. In other words, the code is not generic. Hence it will need some work to mould it into your situation. I would be willing to help with this moulding, adjustment, with a view to making the code into a Stand-alone system.