Save Report to PDF file and set filename to value on continuous form where check box = true

The following code works but is setting the file name to the first record showing on the continuous form. There is a checkbox "SelectInvoice" for each record showing on the form and I am trying to get it to set the filename to the record where the checkbox is True.

Private Sub cmdSaveToPDF_Click()

Dim FileName As String
Dim FilePath As String

FileName = Me.Company & " " & "Invoice " & Me.InvoiceNumber
FilePath = "C:\Users\John\Documents\Invoices\" & FileName & ".pdf"
DoCmd.OutputTo acOutputReport, "rptInvoice", acFormatPDF, FilePath
MsgBox "Invoice PDF has been saved.", vbInformation, "Save Confirmed"

End Sub

Do I need to use an SQL statement to refer to the underling table where the value of checkbox is true?
ExpressMan1Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
try selecting the record first before clicking the button cmdSaveToPDF

the reason why it is getting the first record is, the first record is the one on focus or selected.
0
 
gplanaCommented:
I don't fully understand your quen. Is it company a textbox? If so, change me.conpamy by me.company.text and also me.invoicenumber to me.invoiceNumber.text

You don't need to use a SQL statement, but the way for getting values for a control depends on what kind of control do you have.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
As Rey said, the code you're showing would work on the "focused" or "selected" record only. If you want to run this action for each of the selected records in your form, then you'd have to use a query to do that:

Dim rst As DAO.Recordset
Set rst = Currentdb.OpenRecordset("SELECT * FROM YourTable WHERE Selected=True")
Dim FileName As String
Dim FilePath As String

Do Until rst.EOF
  FileName = Me.Company & " " & "Invoice " & Me.InvoiceNumber
  FilePath = "C:\Users\John\Documents\Invoices\" & FileName & ".pdf"
  DoCmd.OutputTo acOutputReport, "rptInvoice", acFormatPDF, FilePath
Loop

MsgBox "Invoice PDF has been saved.", vbInformation, "Save Confirmed"

Open in new window

Of course, you'd have to change the Table and Field names to fit your project.
0
 
ExpressMan1Author Commented:
Thank You All. I can't believe I missed that!!
0
All Courses

From novice to tech pro — start learning today.