Access Export with Formatting PDF Report using a list box

[pattyjean]

Access 2010
I am using a list box to export with formatting report to PDF but I would like to include the selected item from the list box into the final report path name. Is this possible?
Thank you for your review.

 

[dhookom]

pattyjean,
Do you have any code or control names or anything else to share?

Duane
Hook'D on Access
MS Access MVP

 

[pattyjean]

Yes this is the 1st form I created to go to the second form. I would like to put it all on one click.

Option Compare Database

Private Sub cmdPDF_Click()



On Error GoTo Err_Handler

Const MESSAGE_TEXT1 = "No current DATA."
Const MESSAGE_TEXT2 = "No folder set for storing PDF files."
Dim strFullPath As String
Dim varFolder As Variant
strTest = Me.LstTerm.Value
MsgBox (strTest)

If Not IsNull(Me.LstTerm) Then
' build path to save PDF file
varFolder = DLookup("Folderpath", "pdfFolder")
strFullPath = varFolder & "\" & Me.cmdPDF_.Column(1) & "FALL-COHORT" & ".pdf"
' ensure current record is saved before creating PDF file
Me.Dirty = False
DoCmd.OutputTo acOutputReport, "FALL-COHORT", acFormatPDF, strFullPath, True
End If
Else
MsgBox MESSAGE_TEXT1, vbExclamation, "Invalid Operation"
End If

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description
Resume Exit_Here



End Sub

Private Sub LstTerm_Click()
DoCmd.OpenForm "startup2", , , , acViewPreview
End Sub

Private Sub LstTerm_DblClick(Cancel As Integer)

Dim strTest As String

DoCmd.OpenForm "startup2", , , , acViewPreview

strTest = "Click Open Report to pdf the Fall Cohort Retention by Term selected"
MsgBox (strTest)


End Sub

this is for the second form

Private Sub cmdPDF_Click()

On Error GoTo Err_Handler

Const MESSAGE_TEXT1 = "No current Year."
Dim strFullPath As String
Dim varFolder As Variant


If Not IsNull(Me.Term) Then
' build path to save PDF file
varFolder = DLookup("Folderpath", "pdfFolder")
If IsNull(varFolder) Then
MsgBox MESSAGE_TEXT2, vbExclamation, "Invalid Operation"
Else
strFullPath = varFolder & "\" & Me.Term & " " & "FALL-COHORT" & ".pdf"
' ensure current record is saved before creating PDF file
Me.Dirty = False
DoCmd.OutputTo acOutputReport, "FALL-COHORT", acFormatPDF, strFullPath, True
End If
Else
MsgBox MESSAGE_TEXT1, vbExclamation, "Invalid Operation"
End If

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description
Resume Exit_Here


End Sub

 

[dhookom]

I'm still a bit confused about what you need and can't get past your error in code regarding "If End ... If". Consider using TGML to format your code after pasting it into a reply. It's much easier to read.

Private Sub cmdPDF_Click()
  On Error GoTo Err_Handler
  Const MESSAGE_TEXT1 = "No current DATA."
  Const MESSAGE_TEXT2 = "No folder set for storing PDF files."
  Dim strFullPath As String
  Dim varFolder As Variant
  strTest = Me.LstTerm.Value
  MsgBox (strTest)
  If Not IsNull(Me.LstTerm) Then
    [COLOR=#4E9A06]' build path to save PDF file[/color]
    varFolder = DLookup("Folderpath", "pdfFolder")
    strFullPath = varFolder & "\" & Me.cmdPDF_.Column(1) & "FALL-COHORT" & ".pdf"
    [COLOR=#4E9A06]' ensure current record is saved before creating PDF file[/color]
    Me.Dirty = False
    DoCmd.OutputTo acOutputReport, "FALL-COHORT", acFormatPDF, strFullPath, True
  End If[COLOR=#CC0000]
   Else       [COLOR=#4E9A06]'there is no If for this statement so it won't compile[/color]
    MsgBox MESSAGE_TEXT1, vbExclamation, "Invalid Operation"
  End If[/color] 
Exit_Here:
Exit Sub

Err_Handler:
  MsgBox Err.Description
  Resume Exit_Here
End Sub

Duane
Hook'D on Access
MS Access MVP