Export an individual record in a form

[PaulaCM]

Thanks in advance!

I have created a beautiful form that pulls all the right data from my table. (Hurrah!).

Now, I would like to be able to export each record (from the form) into its own pdf so that I can create and send invoices. I can pdf every record in the form using the Acrobat commands on my ribbon (obviously Access 2007). However, I haven't figured out how to pdf just one record.

is that possible?

thanks again!

[ADezii]

is that possible?

As Homer Simpson would say, Yessssssssss!

I do not have Access 2007 at home, but here is tested code that will save each Employee Record in a Form to an *,rtf File. Each File has a Unique Filename based on Emp & [EmployeeID] & .rtf. The code is rather simple and straightforward , so I'll just post it and if you have any questions, please feel free to ask. Simply change the acFormatRTF Argument to Save in *.pdf Format.

Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Dim strSQL As String

'Used to create a Recordset of Employee IDs for Form Filtering for each
'Employee. [EmployeeID] is the Primary Key making the job easy
strSQL = "Select Employees.[EmployeeID] From Employees;"

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)

With MyRS
  Do While Not MyRS.EOF
    Me.RecordSource = "Select * From Employees Where [EmployeeID] = " & _
                       ![EmployeeID]
    'Form has a single Record only, Output it to a unique RTF File
    DoCmd.OutputTo acOutputForm, "Employees", acFormatRTF, "C:\Test\Emp" & _
                   ![EmployeeID] & ".rtf"
     .MoveNext
  Loop
End With


MyRS.Close
Set MyRS = Nothing

[PaulaCM]

thanks!

I founds a way simple way to do it by creating a button. I've set it up so that it prints only the individual record and defaults to pdf!

so, thanks, but yowza that code looks complicated. I am in no way that cool....

best,

Paula

[ADezii]

thanks!

I founds a way simple way to do it by creating a button. I've set it up so that it prints only the individual record and defaults to pdf!

so, thanks, but yowza that code looks complicated. I am in no way that cool....

best,

Paula

Glad you were able to solve it yourself, Paula, obviously in a much simpler fashion.

[AdBo]

Found this topic via google a few years later.
I want a similar functionality: exporting a form or report per record and save the exported file under a filename with the recordID in the filename.
However it is well known that subForms are not exported to RTF in Access, so i am working around that to export a single Report for every individual record instead of trying to export a Form including Subforms. This works well.

I am running into trouble in the above VB statement if i try to export a lot of reports individually. I do not understand if the Employees.[EmployeeID] From Employees is referring to a table, the form or ......
I also get errors using the _ & statement in Access 2003.
Also if i try to run Access asks me about a macro to start. So a lot goes wrong.

What am i doing wrong? Some directions would greatly be appreciated.

Below i pasted my VB statement. I am now reffering to the Report "RAP_F_02_00_Bi ol_per_pat_bep_ biol". Referring to the Query on which the report is based does not help either.

Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Dim strSQL As String

'Used to create a Recordset of PatientIDs for Form Filtering for each
‘patient. [Patnr_waarde] is the Primary Key making the job easy
strSQL = "Select RAP_F_02_00_Bio l_per_pat_bep_b iol.[Patnr_waarde] From RAP_F_02_00_Bio l_per_pat_bep_b iol;"

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecord set(strSQL, dbOpenForwardOn ly)

With MyRS
Do While Not MyRS.EOF
Me.RecordSource = "Select * From RAP_F_02_00_Bio l_per_pat_bep_b iol Where [Patnr_waarde] = " & _ ! [Patnr_waarde]
'Form has a single Record only, Output it to a unique RTF File
DoCmd.OutputTo acOutputReport, "RAP_F_02_00_Bi ol_per_pat_bep_ biol", acFormatSNP, "D:\" & _
! [Patnr_waarde]& ".snp"
.MoveNext
Loop
End With

MyRS.Close
Set MyRS = Nothing

[ADezii]

The following Code should do the trick:

Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Dim strSQL As String
Dim strRptName As String

strRptName = "RAP_F_02_00_Biol_per_pat_bep_biol"

'Used to create a Recordset of PatientIDs for Form Filtering for each
'patient. [Patnr_waarde] is the Primary Key making the job easy
strSQL = "Select RAP_F_02_00_Biol_per_pat_bep_biol.[Patnr_waarde] From RAP_F_02_00_Biol_per_pat_bep_biol;"


Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)

With MyRS
  Do While Not MyRS.EOF
    'Open the Report Filering by the WHERE Clause for each specific [Patnr_waarde] Value
    DoCmd.OpenReport strRptName, acViewPreview, , "[Patnr_waarde] = " & ![Patnr_waarde]
    
    'Output Reports for each PK ([Patnr_waarde])
    DoCmd.OutputTo acOutputReport, strRptName, acFormatSNP, "D:\" & ![Patnr_waarde] & ".snp"
    
    'Close each Report after Outputting
    DoCmd.Close acReport, strRptName, acSaveNo
      .MoveNext       'Move to the next Record in Recordset
  Loop
End With

MyRS.Close
Set MyRS = Nothing