Read DB, Add Name to File, Create Folder

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

'PDF

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