Option Compare Database
Function Export()
Call ExportPdf
End Function
Sub ExportPdf()
'Reading from Database cell
Dim db As Database
Dim TableSet As DAO.Recordset
Dim VariableLook As String
Dim ItemSelected As String
'Open connection to current Access database
Set db = CurrentDb()
'Create SQL statement to retrieve value from GST table
VariableLook = "select [Contract ID] from ContractID"
Set TableSet = db.OpenRecordset(VariableLook)
'Retrieve value if data is found
If TableSet.EOF = False Then
ItemSelected = TableSet("[Contract ID]")
Else
ItemSelected = "Not found"
End If
TableSet.Close
Set TableSet = Nothing
ContractVar = ItemSelected
'Reading from Database cell
Dim db2 As Database
Dim TableSet2 As DAO.Recordset
Dim VariableLook2 As String
Dim ItemSelected2 As String
'Open connection to current Access database
Set db2 = CurrentDb()
'Create SQL statement to retrieve value from GST table
VariableLook2 = "select [Ticket Number] from ContractID"
Set TableSet2 = db2.OpenRecordset(VariableLook2)
'Retrieve value if data is found
If TableSet2.EOF = False Then
ItemSelected2 = TableSet2("[Ticket Number]")
Else
ItemSelected2 = "Not found"
End If
TableSet2.Close
Set TableSet2 = Nothing
TicketVar = ItemSelected2
Dim myPath As String
Dim MyDate As String
MyDate = Format(Date, "YYYYMMDD")
MyYear = Format(Date, "YYYY")
MyMonth = Format(Date, "MM")
MyDay = Format(Date, "DD")
'Folder Creation
Set fso = CreateObject("Scripting.FileSystemObject")
folderFilePath = "S:\FinAdj\Tax Review Changes\Credit Memos\" & MyYear & ""
If fso.FolderExists("S:\FinAdj\Tax Review Changes\Credit Memos\" & MyYear & "") Then
'Create PDF
DoCmd.OpenReport "RPTCreditMemo", acViewPreview
myPath = "S:\FinAdj\Tax Review Changes\Credit Memos\" & MyYear & "\" & ContractVar & "_TIC" & TicketVar & "_" & MyDate & ".pdf"
DoCmd.OutputTo acOutputReport, "", acFormatPDF, myPath, False
DoCmd.Close acReport, "RPTCreditMemo"
Else
Dim fs2, f2, ts2
Set fs2 = CreateObject("Scripting.FileSystemObject")
CheckMyFile:
If fso.FolderExists("S:\FinAdj\Tax Review Changes\Credit Memos\" & MyYear & "") Then
'Create PDF
DoCmd.OpenReport "RPTCreditMemo", acViewPreview
myPath = "S:\FinAdj\Tax Review Changes\Credit Memos\" & MyYear & "\" & ContractVar & "_TIC" & TicketVar & "_" & MyDate & ".pdf"
DoCmd.OutputTo acOutputReport, "", acFormatPDF, myPath, False
DoCmd.Close acReport, "RPTCreditMemo"
GoTo EndProcess
ElseIf fso.FolderExists("S:\FinAdj\Tax Review Changes\Credit Memos\" & MyYear & "") Then
Set f2 = fs2.CreateFolder("S:\FinAdj\Tax Review Changes\Credit Memos\" & MyYear & "")
GoTo CheckMyFile
ElseIf fso.FolderExists("S:\FinAdj\Tax Review Changes\Credit Memos") Then
Set f2 = fs2.CreateFolder("S:\FinAdj\Tax Review Changes\Credit Memos\" & MyYear & "")
GoTo CheckMyFile
ElseIf fso.FolderExists("S:\FinAdj\Tax Review Changes") Then
Set f2 = fs2.CreateFolder("S:\FinAdj\Tax Review Changes\Credit Memos")
GoTo CheckMyFile
ElseIf fso.FolderExists("S:\FinAdj") Then
Set f2 = fs2.CreateFolder("S:\FinAdj\Tax Review Changes")
GoTo CheckMyFile
End If
EndProcess:
Close
End If
End Sub