Collate single record report and existing pdf

Hi all. Using Access 2003 I am trying to figure out how to print and collate a report and an existing pdf file that is opened based on a field in the record. I figure that it probably has to do with looping through the records but I am not very good at that.
So I have a form with a sub form that opens records based on a combo box. I then have a report (a checklist sheet) that prints 1 page per record. I would like to print a pdf file associated to that record before it prints the next record.

Here are the names of the objects being used:

Any help would be appreciated.
Thank you,

Post your answer or comment

comments powered by Disqus
I have created a report using a query. How can I print a single record report while I'm in the form? I would like to be able to print the current record I have open.

First, the background:

1. I'm a novice when it comes to Access, but figuring it out pretty rapidly.
2. I'm using Access 2007.
3. I'm trying to create a patient database (think medical records)

Second, the question:

Can I create a report for a single record (i.e., patient)? I would like to be able to create several single-record reports. For instance, one would be a kind of summary page with demographics for this one particular patient, one would be more diagnostic info, etc.

I have attached a file showing a screen shot of my order entry form along with details of my command button.

Upon hitting the Print Preview button I preview the invoice report for the selected invoice. I then hit print, select the appropriate printer and generally everything works as desired.

However ocassionally rather than just the single record report, it spews out copies of the form for hundreds of records.

Appreciate any words of wisdom! Attached Files Order Entry Form_Part1.pdf (433.6 KB, 17 views) Order Entry Form_Part2.pdf (285.9 KB, 11 views) Reply With Quote 07-28-2010, 05:03 PM #2 Access_Blaster User Windows XP Access 2000 Join Date May 2010 Posts 320 Print Single Record This will print the record you are currently looking at if that will help.

I've been trying to get my head round this one , but i'm just to thick to get it.

I have a continuous form that lists all items at a certain location.
The user selects a record by clicking on the record selector and then clicks on a command button with this code:

stDocName = "DivingInspectionCert"
stLinkCriteria = ("EquipmentID = " & Me!EquipmentID)
DoCmd.OpenForm stDocName, , , stLinkCriteria

The user then enters inspection details in to the "DivingInspectionCert" form which i want to store in a table (DivingCert) which will relate to the item. On completion of this form the user then clicks on a command button with this code:

DoCmd.RunCommand acCmdSaveRecord

If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "There are no items to Print", vbInformation, "EquiTrac"
DoCmd.OpenReport "DivingInspectionRpt", , , ("EquipmentID = " & Me!EquipmentID)

When i click on this button, the report doesn't print and i get a message "No current record".
The inspection details are not stored in the table.
I did have the forms RecordSource based on the table "DivingCert" but that didn't work and i have just tried a query but it is still not working.

Would be much appreciated if you can help me.
Thank you

Hi all,

I have some ongoing problems with getting a report to 'work' with a single record. So far I have 3 issues; displaying a single record, printing a single record, and outputing a single record to a PDF file.

The report itself is a report of customer and supplier contracts based on data in multiple tables, and I only want to print/view/output a single contract at a time. I have a query which pulls all the required data into one place. The report launches from a command button I have in a form which is displaying the record I currently want to work with. The name of the form is stored in a constant called 'const_rptContractReport', and the ID of the record I want is stored in a field called '[CONTRACT_ID]'.

Regarding viewing, I initially tried this:

DoCmd.OpenReport const_rptContractReport, acViewPreview, , "[CONTRACT_ID] = " & Me!CONTRACT_ID.value

It's my understanding that this should work, but it was inconsistent. Sometimes it would open the correct record, but sometimes it would open the first record in the query. There seemed to be no logical reason for it. I tried printing out the contract ID and indeed it was the case that sometimes the ID was correct, and sometimes it wasn't, even though the same record was always being displayed. Not getting very far, I switched to this:

DoCmd.OpenReport const_rptContractReport, acViewPreview, , "[CONTRACT_ID] = " & Forms(const_frmContractBuilderForm)! lue

This seems to work consistently (at least for now!), so not really understanding what was going on there. Even weirder, if I want to print the record I use this:

DoCmd.OpenReport const_rptContractReport, acViewNormal, , "[CONTRACT_ID] = " & Forms(const_frmContractBuilderForm)! lue

This prints the record, but all my unbound fields turn up empty when printed! I think I may be missing something obvious with the printing due to my lack of experience with 2010, as it is just the unbound fields that don't print.

Finally, is there anyway to output a single record to a PDF? I've checked out the OutputTo method, but as far as I can work out this dumps the entire report. It would be so convenient if this could be limited to a single record. Is there any way to do this? I'm thinking that I may have to create a query that will pull out only the single record that I want and use that as the report source. Would this be the best way, or is there a simple command that I'm missing.

Sorry for the long winded post, especially as it's my first technical post on this forum. Any help would be much appreciated.


Have exhausted all apparent avenues and would appreciate some assistance. I am looking to export (and also e-mail) a single record from a multi-record report. I have managed to print a single record but exporting or e-mailing such is beyond my skills.

Any suggestions gratefully appreciated



I am trying to Export a single record from my customer table and using below codes -

Private Sub lblPDF_Click()
Dim myPath As String
Dim stDocName As String
Dim theFileName As String
stDocName = "rptCustomerMaster" (is my Report File name)
>>>DoCmd.OpenReport stDocName, acPreview, , "CustId = " & Nz(Me.CustID, 0)
myPath = "C:..."
theFileName = "CustID " & CustID & ".pdf"
DoCmd.OutputTo acOutputReport, stDocName, acFormatPDF, theFileName, True
End Sub

Having errors on the highlighted code, could somebody guide me how to resolve it, or is there any support available to export record into PDF file?

Hi everyone.

I'm the HSE & HR Manager of an engineering company. I've built a data base with tables, forms and a modal dialogue as a switch.

All works swimmingly.

I've made tables for:

Job Tasks, which includes corresponding codes



I want to generate a report that singles out:

Employee:Position:Codes (job tasks that are assigned to that position):

And I only want to print that record.

Obviously the macro builder is limited - like me on code building.

I can do stuff with wizards, but code is very very basic.

Can any of you lovely people help me??



I am working on a database which will enable fellow employees to record drawing issues. The report will need to show the details of each drawing and each recipient involved in the issue.

My form (see first .jpg) allows the user to select the drawings and the recipients. However, I would like them to be able to access a report based on the single record which they have entered. So far, I have been able to create a report which shows all of the information, although some of the fields are repeated often (see second .jpg). I would like the report to show a single list of drawings selected, with all of their details located from their table (drawing table) and the same with the recipients.

For example, I would like one section as follows:
Drawing Nubmer, Drawing Title, Current Revision, Revision Description

And the other:
Recipient, Association

Any help would be appreciated.
Thanks in Advance.

Hi, I would like to run a single page report from a button situated on a form. The contents of the report would depend on the record currently displayed within the form.

I have an order record open within the form, and then the button would print off an order sign-off sheet report for that particular order, detailing the order details at the top with a signing area at the bottom.

Can anyone advise me how to go about this, as all the reports I create seem to include all the records in the table, etc.

Thanks in advance...

The way my database is setup is that each record holds all the information I wish to query / calculate / lookup 'only' for that record. I'm currently not interested in querying across records (which is too bad cuz that would certainly make this easier), so that's why I've posted this under the programming thread, figuring querying may not be my best solution.

The basic form setup for each record has about 20 combo boxes (all providing the same choices). So you have 4 groups and each group can choose any combination of 5 of these items. Let me Illustrate;

Group 1___Group 2____Group 3___Group 4
Sony______ Panasonic ___N/A _________N/A

So the above camera choices show up as combo boxes (looking up from another table) on the form. They save the choices by their lookup ID in the current form's corresponding fields. Also, the fields are 'not' relational between both tables, which is why querying won't work. I've done it this way for a very specific reason (discovering through much trial and error), so please don't worry about giving me advice on joining the tables and not needing to save the choices to fields.

I now have a bunch of fields in a single record which I need to do a search across to make summations for an executive report. So for the above project, the end result would be;

Group 1 has 2 x Sony & 1 Nikon
Group 2 has 2 x Sony & 1 Panasonic
Group 3 has 1 x Panasonic & 1 Nikon

All Groups 4 x Sony & 2 Panasonic & 2 Nikon

I apologize if this thread should have been started in the query / report section, but I'm not seeing how to do such activities with Access regular functions, so I'm thinking this will probably be some programming and the generation of a pop-up form as the eventual report.

Thanks to anyone pointing me in the right direction

Hi All,

I have access database with a form which has combo boxes which get populated with queries and they are interlinked with query for e.g. Combobox1 gets populated with region and combobox2 gets populated with the country in that region so if you select "Asia" Region in Combobox1 then Combobox2 will get populated with countries like "Singapore", "Japan", "Hongkong" etc. Now you will have to select one of the country and then print report and need to do the same exercise for all regions. Now this means that whenever the selection in combobox1 selection changes the combobox2 requeries it's data in order to populate the respective region countries in it.I want the code to print the files to pdf using "pdfcreator" as printer and then save the files on a specfic path like "C:".

Below is the code which I have worked on so far but it shows an error that is Run-time error '3061': Too few parameters. Expected 1:

Code: Private Sub Commandbutton_Click () Dim rst As DAO.Recordset Dim CustomerID As Long Set rst = CurrentDb.OpenRecordset("Query1", dbOpenSnapshot) Debug.Print rst With rst Do Until .EOF ProgramID = !ProgramID DoCmd.OpenReport "StrptReport", acViewPreview, , "CustomerID=" & CustomerID Reports![StrptReport].Caption = [FullReportName] Name "StrptReport" As [FullReportName] & ".pdf" .MoveNext Loop .Close End With Set rst = Nothing End sub When I click on the debug error It highlights the below line from code:
Code: Set rst = CurrentDb.OpenRecordset("Query1", dbOpenSnapshot)
Thanks a lot for your help in advance.

I am a newbie and am wanting to email SINGLE record from my Form's Command Button to a Report, but get all records. Can successfully print one record using Print Command button to the same Report, but not email. Any posts on this all have "bits & pieces" answers. Can anyone give me the complete answer? I am trying to use SendObject

I want to have a comand button on my form which will create a report for a single record. Is this possible or will the report only includ all the records in that table.

Have been browsing these boards for some time and they have been very useful.

I am now stuck with my latest project which is to email a single record with an image (photograph)

Emailing the report is fine but I am stuck on how to send the image. I notice that OLE objects don't like to travel via email so won't leave the database. As all my images are in one local folder and labelled the same as the user they relate to, is there some VB code I could use that I could add after sending the report which would pick out the image from this directory at the same time?


Hey, I've got a (most likely) simple question for anyone with the time to answer it. I have a report set up and I want to be able to export a single record to a word document. I know how to export a report with OutputTo, and I know how to open a single record with OpenReport, but I can't seem to figure out how to accomplish both tasks. Is there a way to do OpenReport, give it a temporary name and then output that report?

Help would be greatly appreciated. I am new to access and I've been doing okay, but this point has me fairly well stumped. So far reading previous posts on this board has been a Godsend for my project, so I was hoping someone might have the answer I seek.


I am currently working on a database to assist with the reporting of various incidents. Since all incidents tend to be different, I have had to create numerous different tables, some or all of which may be used to report a single incident.

Anyway, my problems start to crop up when trying to generate the report. And most of us know, there always has to be a report . I am looking for a query/expression/whatever that will search all the tables for records matching a certain criteria that will be entered. Then, take only those records that match the criteria and shove them into a report.

The problem I get is that regardless of whether a table has a record matching the criteria or not, the report has headers for the information. If there is not record mtaching the criteria, I don't want to see headers with blank spaces.

Does this make sense?

Any suggestions?

P.S. I am somewhat of a noob here, so you may need to use small words

Thanks everyone!!!!!!!!

Hi, I've seen bits and pieces of the answer to this in other threads but because i'm a total newb to Vb I'm not able to piece it all together to get what I want.

Basically so far I have two buttons on my form which allow me to preview the current record in a report I made in design view, and also to print the current record in the form on a report.
However I don't seem able to figure out how to make a button send the current record in the form to an email address in .rtf or html format in the report.

Currently my button for previewing looks like this and I've had a slight go at adapting it but I always get error messages.

Private Sub Command63_Click()

Dim strReportName As String
Dim strCriteria As String

If NewRecord Then
MsgBox "This record contains no data." _
, vbInformation, "Invalid Action"
Exit Sub
strReportName = "comreport1"
strCriteria = "[System Number]='" & Me![System Number] & "'"

DoCmd.OpenReport strReportName, acViewPreview, , strCriteria

End If
End Sub

Anyone who could lend a hand would be straight in to my good book!!

hi everybody

i ned to make a reports record source and the control sources for a few text boxs a bit more dynamic,

i have a report with 27 txt boxs going accross, the control source for these will change as they are named after dates, depending on a value set in a form.. they are named w1 to w27 .. w1 will ALWAYs be the value in the txtbox startgantt on the form, w2 will be the same value + 7 , and w3 will be the same value + 14 etc, so they each box is an increment of one week, so the control source for w1 would be for example 15/11/2010 and the control source for w2 would be 22/11/2010 and so on and so on in 7 day increments. the labels would just be a copy of the control source, the date

ive wrote some quick code but i never usualy use reports and im a bit lacking in experience in their use.. the code i wrote is quick and simple but i think should change the forms record source and should change the control sources and labels for the 27 txt boxs.. it obviously doesnt work

can somebody help ?

thanks in advance for any advice

	Private Sub Report_Open(Cancel As Integer)
Dim stSQL As String
Dim dCdate As Date
Dim count As Double
dCdate = [Forms]![Form1]![ganttstart]
stSQL = "Select [fldTrainee], [fldCourse], [" & dCdate & "]"
Me.w1_Label.Caption = dCdate
For i = 1 To 26
dCdate = dCdate + 7
stSQL = stSQL & ", [" & dCdate & "]"
Next i
stSQL = stSQL & " From qryGantt"
Me.RecordSource = stSQL
dCdate = [Forms]![Form1]![ganttstart] + 7
Me.w2_Label.Caption = dCdate
Me.w2.ControlSource = dCdate
dCdate = dCdate + 7
Me.w3_Label.Caption = dCdate
Me.w3.ControlSource = dCdate
dCdate = dCdate + 7
Me.w4_Label.Caption = dCdate
Me.w4.ControlSource = dCdate
dCdate = dCdate + 7
Me.w5_Label.Caption = dCdate
Me.w5.ControlSource = dCdate
dCdate = dCdate + 7
Me.w6_Label.Caption = dCdate
Me.w6.ControlSource = dCdate
dCdate = dCdate + 7
Me.w7_label.Caption = dCdate
Me.w7.ControlSource = dCdate
dCdate = dCdate + 7
Me.w8_Label.Caption = dCdate
Me.w8.ControlSource = dCdate
dCdate = dCdate + 7
Me.w9_Label.Caption = dCdate
Me.w9.ControlSource = dCdate
dCdate = dCdate + 7
Me.w10_Label.Caption = dCdate
Me.w10.ControlSource = dCdate
dCdate = dCdate + 7
Me.w11_Label.Caption = dCdate
Me.w11.ControlSource = dCdate
dCdate = dCdate + 7
Me.w12_Label.Caption = dCdate
Me.w12.ControlSource = dCdate
dCdate = dCdate + 7
Me.w13_Label.Caption = dCdate
Me.w13.ControlSource = dCdate
dCdate = dCdate + 7
Me.w14_Label.Caption = dCdate
Me.w14.ControlSource = dCdate
dCdate = dCdate + 7
Me.w15_Label.Caption = dCdate
Me.w15.ControlSource = dCdate
dCdate = dCdate + 7
Me.w16_Label.Caption = dCdate
Me.w16.ControlSource = dCdate
dCdate = dCdate + 7
Me.w17_Label.Caption = dCdate
Me.w17.ControlSource = dCdate
dCdate = dCdate + 7
Me.w18_Label.Caption = dCdate
Me.w18.ControlSource = dCdate
dCdate = dCdate + 7
Me.w19_Label.Caption = dCdate
Me.w19.ControlSource = dCdate
dCdate = dCdate + 7
Me.w20_Label.Caption = dCdate
Me.w20.ControlSource = dCdate
dCdate = dCdate + 7
Me.w21_Label.Caption = dCdate
Me.w21.ControlSource = dCdate
dCdate = dCdate + 7
Me.w22_Label.Caption = dCdate
Me.w22.ControlSource = dCdate
dCdate = dCdate + 7
Me.w23_Label.Caption = dCdate
Me.w23.ControlSource = dCdate
dCdate = dCdate + 7
Me.w24_Label.Caption = dCdate
Me.w24.ControlSource = dCdate
dCdate = dCdate + 7
Me.w25_Label.Caption = dCdate
Me.w25.ControlSource = dCdate
dCdate = dCdate + 7
Me.w26_Label.Caption = dCdate
Me.w26.ControlSource = dCdate
dCdate = dCdate + 7
End Sub


I have created quite a complex report built from over 20 sub reports (due to it having to be a document rather than a report). So I have some questions if someone could kindly advise or point me in the right direction....

1. How many sub reports can 1 main report handle?
2. Is there a way in the main report to make the sub reports miniscule but be able to highlight them when needed to move or change something? I have made them as small as possible because of restricted room but then have problems getting into them..
(I do need to add more pages in about another 6)
3. I can import word documents (certificates) but I cant find a way to import PDF documents. So I am creating the sub report, bringing in the word document as an image and then placing in the main report as a sub report.
4. I also need to redevelop all of this report including all sub forms another 3 times as we have potentially 4 kinds of reports with potentially slightly different data in all (text fields are driven from a table by reporttype id)
Is there an easy way to copy it all without having to re-create all sub reports and then redirect the main report to the other new sub reports?

5. And finally, eventually I need to create a report that needs to have hyperlinks from content page, also needs to have pictures in per record (record per page) and other information. I dont think access reporting can handle this, so could someone advise the best way to report in this way. This part of the project is some weeks away but I am trying to figure out the best way in advance.

many thanks


I have a form/subform combination on my database.

One record on the subform can relate to several records on the subform, i.e

Record 1, has five incidents on it so records in the subform are 1.1, 1.2, 1.3 etc.

I then need a print off of these in report format, I only need a single record for the report though. The issue i'm having is that all of the records are printing on the report! Is there any criteria I can state to print the current form? I know I can create a form button to print current form, but I need the report format.

Any advice!!?

Access 2007:
I have a form which our clients can to sign in when they come to visit... It contains basic information which is captured and printed on a worksheet for our service representatives to use to assist the client.

Table Name: Visits (current number of records: 10,000+)
Key Field: VisitID (Auto number field)

I have created a report: "rptDayByDayVisits" which is called within the form (frmSignIn-wPrint) upon pressing the save & print button after the form field data is entered.

Question: How do I capture the "VisitID" field data - which has been auto filled on the form - and send it as a parameter to the DoCmd.OpenReport?

The intent is to print only a single record ID'd by the "VisitID" field...

Currently I have coded:

X = 10327 ' This works but is a constant

strWhere = "[VisitID] = " & X
DoCmd.OpenReport "rptDayByDayVisits", acViewPreview, , strWhere

I'm not sure if this is the correct path to take... Again, how can I dynamicaly capture the "VisitID" value from the form and insert it as a paramater into the DoCmd.OpenReport function?

Hi all, hope you can help with this as I am a bit of a newbie when it comes to Access and VBA...

I am developing a database system at work that is used to record audits of work quality, and we want to be able to e-mail an Access report to workers where quality issues have been identified - nothing special, just a single record report.

The auditor selects an audit record from a listbox on the main form - the first column of which is the unique ID of the audit. Below the list box I have put three command buttons which allow the report to be viewed, printed or e-mailed, the first two of which are working very nicely thanks to the 'OpenArgs' attribute in DoCmd.OpenObject. The 'proforma' report is also called from two other forms which are used for creating the audit records in the back-end tables, and when a 'fail' record is added a module is called which saves a Snapshot Format copy of the report to a network drive. This too works fine.

I have a problem however when trying to generate the report when using DoCmd.SendObject - no matter what method I use to input it, the report simply will not pick up the unique audit record ID to complete the SQL command which populates the fields in the report. I have written a new function in a module for generating the e-mail, and the e-mail itself is generating fine, however the attachment generated by SendObject is just the report template.

On the main form the code behind the command button which attempts to send the e-mail is very simple:

	run sendproforma(Me.LstHistory.Column(0))

The argument for getting the record ID into this function has been coded as follows:

	Public Function sendproforma(auditID As String)

The first lines of the Reports 'On Activate' routine deal with picking up the audit record ID from the various inputs mentioned before. I am going from memory on this a bit, but is essentially as follows:

	Dim strvariable, SQL As String
If forms!frmresults1.IsLoaded Then
   strvariable = forms!frmresults1.UniqueID
ElseIf forms!frmresults1view.IsLoaded Then
   strvariable = forms!frmresults1view.NewUniqueID
ElseIf Not IsNull(Me.OpenArgs) Then
   strvariable = Me.OpenArgs
   strvariable = auditID
End If
SQL = "SELECT * FROM qryauditresults WHERE uniqueref = '" & strvariable & "'"

The last part of the group of IF's is the bit I'm trying to get to work when sending the report via e-mail. I have tried populating this from the listbox on the main form, a hidden text field which contains the same value as the selected record in the listbox in both a new form as well as the original main form, all to no avail thus far which is very frustrating to say the least!

We are running Access 2003 (SP3) on Windows XP currently. I will try and get online here tomorrow at work and post up all the relevant code tomorrow but in the meantime maybe what I have posted here will be sufficient for one of you boffins to be able to spot where I am running into trouble

Ok, not only a beginner, but after reading i do not know how many threads, internet columns, etc. I have to be stupid also. The main thing I need is to be able to have a user input information in a form and when they hit the "submit" command button on the form, have it automatically send and email of the single record to several recipients placed in the "TO" and "CC" fields and only if information is present, then close the database form.
Second macro request is to have (assume this is what i want) is to have the shortcut or macro on the desktop so it will open the form in the maximized mode, and not show the ribbons, sidebars, etc for the user to access, only the input form which would respond as requested in the main macro listed above.
As i said earlier, beginner and stupid, the built in macros in access 2010 seem to be easy but not able to do everything (probably me), and not grasping the vba code very fast in order to do this. I do have the database, form, report, and a query all set up with them tied or linked together by the PK autonumber field. Appreciate any and all help to make this work after 4 frustrating days.

Not finding an answer? Try a Google search.