Invalid Call or Procedure - Error 5 (1 Viewer)

[bleakhouse]

I am trying to rename a file in my procedure, my code has stopped working recently and I cannot work out why.

I am running Windows 10 and Access 2019, I have posted my code below

Any help would be most appreciated.

RenameInput = Environ("userprofile") & "\documents\datafiles\BHFH_Search_NotOurArea\Search_Report.pdf" ': MsgBox fl.FileExists(RenameInput)
RenameOutput = Environ("userprofile") & "\documents\datafiles\BHFH_Search_NotOurArea\Search_Report_"
RenameOutput = RenameOutput + SearchAreaAndName
DoCmd.SetWarnings (Warningsoff)
RenameOutput = RenameOutput + CurrentDateandTime
MsgBox RenameInput
MsgBox RenameOutput

Name RenameInput As RenameOutput

I have checked that all the required filenames exist

Thanks in anticipation

Regards

Ian Clegg

 

[cheekybuddha]

You have only posted partial code.

Please post the rest of the procedure, or at least the declarations of your variables like fl, RenameInput, RenameOutput, SearchAreaAndName, CurrentDateandTime and also the code that assigns their values.

Also why are are you using the + operator for concatenation?

 

[bleakhouse]

Full Code Posting

Dim SearchName As String
Dim SearchArea As String
Dim Sql As String
Dim StrCommand As String
Dim Response As String

Dim Report1 As String
Dim Report2 As String
Dim Report3 As String
Dim Report4 As String
Dim Report5 As String
Dim Report6 As String
Dim Report7 As String
Dim Report8 As String
Dim Report9 As String
Dim Report10 As String
Dim Report11 As String
Dim Report12 As String
Dim Report13 As String
Dim Report13a As String
Dim ReportOutput As String
Dim Reportstring As String

Dim SearchAreaAndName As String

Dim RenameInput As String
Dim RenameOutput As String

Dim SearchName2 As String
Dim SearchArea2 As String

Dim CurrentDate As String
Dim CurrentTime As String
Dim CurrentDateandTime As String


DoCmd.SetWarnings Warningsoff

SearchName = InputBox("Enter Search Name", "Enter Search Name", "Surname")

SearchAreaAndName = SearchName
SearchName = StrConv(SearchName, 3) + "*"

SearchArea = InputBox("Enter Search Area", "Search Area", "Area")

SearchAreaAndName = SearchAreaAndName + " " + StrConv(SearchArea, 3) '+ ".pdf"
MsgBox SearchAreaAndName

SearchArea = StrConv(SearchArea, 3) + "*"

'MsgBox (SearchAreaAndName)

' Extract Data
Sql = "select * into Extracted_Baptisms from baptism where surname = '" & SearchName & "' and place like '" & SearchArea & "'"
DoCmd.RunSQL (Sql)
Sql = "select * into Extracted_Marriages_Bride from marriage where [bridesurname] = '" & SearchName & "' and place like '" & SearchArea & "'"
DoCmd.RunSQL (Sql)
Sql = "select * into Extracted_Marriages_Groom from marriage where [groomsurname] = '" & SearchName & "' and place like '" & SearchArea & "'"
DoCmd.RunSQL (Sql)
Sql = "select * into Extracted_Burials from burials where surname = '" & SearchName & "' and place like '" & SearchArea & "'"
DoCmd.RunSQL (Sql)
Sql = "select * into Extracted_41census from 1841census where surname = '" & SearchName & "' and township like '" & SearchArea & "'"
DoCmd.RunSQL (Sql)
Sql = "select * into Extracted_51census from 1851census where surname = '" & SearchName & "' and township like '" & SearchArea & "'"
DoCmd.RunSQL (Sql)
Sql = "select * into Extracted_BatleyBurials from BatleyBurials where name = '" & SearchName & "' and ParishorDistrict like '" & SearchArea & "'"
DoCmd.RunSQL (Sql)
Sql = "select * into Extracted_DewsburyBurials from DewsburyBurials where name = '" & SearchName & "' and ParishorDistrict like '" & SearchArea & "'"
DoCmd.RunSQL (Sql)
Sql = "select * into Extracted_HeckmondwikeBurials from HeckmondwikeBurials where name = '" & SearchName & "'and ParishorDistrict like '" & SearchArea & "'"
DoCmd.RunSQL (Sql)
Sql = "select * into Extracted_soothillwarregister from soothillwarregister where surname = '" & SearchName & "'"
DoCmd.RunSQL (Sql)
Sql = "select * into Extracted_newspapernotices from newspapernotices where sname = '" & SearchName & "'"
DoCmd.RunSQL (Sql)
'Sql = "select * into Extracted_dewsburyrollofhonour from dewsburyrollofhonour where surname = '" & SearchName & "'"
'DoCmd.RunSQL (Sql)
Sql = "select * into Extracted_stpaulinusbaptisms from tblstpaulinusbaptism where surname = '" & SearchName & "'"
DoCmd.RunSQL (Sql)

DoCmd.SetWarnings Warningsoff

StartsHere: 'Setup Files for Copying Output To UndeDim Attachment1 As Stringr Copy to Output Directory

BaseFilePath = Environ("userprofile") & "\Documents\Datafiles\BHFH_Search_NotOurArea\"
BaseFilePath2 = Environ("userprofile") & "\Documents\Datafiles\BHFH_Search_NotOurArea\"
BaseInputPath = Environ("userprofile") & "\Documents\Datafiles\BHFH_Search_NotOurArea\"

OutputPath = Environ("userprofile") & "\Documents\Datafiles\BHFH_Search_NotOurArea\" ' OutPath

CurrentDb.Execute ("Delete * from extractionheader")

Set recsetOp1 = CurrentDb.OpenRecordset("ExtractionHeader")
recsetOp1.AddNew
recsetOp1!SearchName = SearchName
recsetOp1!SearchArea = SearchArea
recsetOp1.Update

Set fl = New Scripting.FileSystemObject

Start:
DoCmd.OutputTo acOutputReport, "BHFH_Report_Header1", acFormatPDF, Environ("userprofile") & "\Documents\Datafiles\BHFH_Search_NotOurArea\1_ReportFrontPage.pdf"
'DoCmd.OutputTo acOutputReport, "BHFH_Report_Header2", acFormatPDF, Environ("userprofile") & "\Documents\Datafiles\BHFH_Search_notOurArea\2_ReportContentsPage.pdf"
DoCmd.OutputTo acOutputReport, "BHFH_Extracted_Baptisms", acFormatPDF, Environ("userprofile") & "\Documents\Datafiles\BHFH_Search_NotOurArea\3_Extracted_Baptisms.pdf"
DoCmd.OutputTo acOutputReport, "BHFH_Extracted_marriages_Bride", acFormatPDF, Environ("userprofile") & "\Documents\Datafiles\BHFH_Search_NotOurArea\4_Extracted_Marriages_Bride.pdf"
DoCmd.OutputTo acOutputReport, "BHFH_Extracted_marriages_Groom", acFormatPDF, Environ("userprofile") & "\Documents\Datafiles\BHFH_Search_NotOurArea\5_Extracted_Marriages_Groom.pdf"

DoCmd.OutputTo acOutputReport, "BHFH_Extracted_burials", acFormatPDF, Environ("userprofile") & "\Documents\Datafiles\BHFH_Search_NotOurArea\6_Extracted_Burials.pdf"
'DoCmd.OutputTo acOutputReport, "BHFH_Extracted_Batleyburials", acFormatPDF, Environ("userprofile") & "\Documents\Datafiles\BHFH_Search_notOurArea\6a_Extracted_BatleyBurials.pdf"
'DoCmd.OutputTo acOutputReport, "BHFH_Extracted_dewsburyburials", acFormatPDF, Environ("userprofile") & "\Documents\Datafiles\BHFH_Search_NotOurArea\6b_Extracted_DewsburyBurials.pdf"
'DoCmd.OutputTo acOutputReport, "BHFH_Extracted_Heckmondwikeburials", acFormatPDF, Environ("userprofile") & "\Documents\Datafiles\BHFH_Search_NotOurArea\6c_Extracted_HeckmondwikeBurials.pdf"

'DoCmd.OutputTo acOutputReport, "BHFH_Extracted_dewsburyrollofhonour", acFormatPDF, Environ("userprofile") & "\Documents\Datafiles\BHFH_Search_NotOurArea\7_Extracted_Dewsbury_RollofHonour.pdf"
'DoCmd.OutputTo acOutputReport, "BHFH_Extracted_newspapernotices", acFormatPDF, Environ("userprofile") & "\Documents\Datafiles\BHFH_Search_NotOurArea\8_Extracted_NewspaperNotices.pdf"
'DoCmd.OutputTo acOutputReport, "BHFH_Extracted_soothillwarregister", acFormatPDF, Environ("userprofile") & "\Documents\Datafiles\BHFH_Search_NotOurArea\9_Extracted_SoothillWarRegister.pdf"
'DoCmd.OutputTo acOutputReport, "BHFH_Extracted_StPaulinusBaptisms", acFormatPDF, Environ("userprofile") & "\Documents\Datafiles\BHFH_Search_NotOurArea\10_Extracted_StPaulinusBaptisms.pdf"
DoCmd.OutputTo acOutputReport, "BHFH_Extracted_41Census", acFormatPDF, Environ("userprofile") & "\Documents\Datafiles\BHFH_Search_NotOurArea\11_Extracted_41Census.pdf"
DoCmd.OutputTo acOutputReport, "BHFH_Extracted_51Census", acFormatPDF, Environ("userprofile") & "\Documents\Datafiles\BHFH_Search_NotOurArea\12_Extracted_51Census.pdf"
DoCmd.OutputTo acOutputReport, "Blank_Report_Landscape", acFormatPDF, Environ("userprofile") & "\Documents\Datafiles\BHFH_Search_NotOurArea\14_Blank_Report.pdf"

FirstFilePath = BaseFilePath2 & "1_ReportFrontPage.pdf" ': MsgBox fl.FileExists(FirstFilePath)
SecondFilePath = BaseFilePath2 & "2_ReportContentsPage.pdf" ': MsgBox fl.FileExists(SecondFilePath)
ThirdFilePath = BaseFilePath2 & "3_Extracted_Baptisms.pdf" ': MsgBox fl.FileExists(ThirdFilePath)
FourthFilePath = BaseFilePath2 & "4_Extracted_Marriages_Bride.pdf" ': MsgBox fl.FileExists(FourthFilePath)
FifthFilePath = BaseFilePath2 & "5_Extracted_Marriages_Groom.pdf" ': MsgBox fl.FileExists(FifthFilePath)
SixthFilePath = BaseFilePath2 & "6_Extracted_Burials.pdf" ': MsgBox fl.FileExists(SixthFilePath)
FilePath6a = BaseFilePath2 & "6a_Extracted_Batleyburials.pdf" ': MsgBox fl.FileExists(NinethFilePath)
FilePath6b = BaseFilePath2 & "6b_Extracted_Dewsburyburials.pdf" ': MsgBox fl.FileExists(NinethFilePath)
FilePath6c = BaseFilePath2 & "6c_Extracted_Heckmondwikeburials.pdf" ': MsgBox fl.FileExists(NinethFilePath)
SeventhFilePath = BaseFilePath2 & "7_Extracted_Dewsbury_RollofHonour.pdf" ': MsgBox fl.FileExists(SeventhFilePath)
EighthFilePath = BaseFilePath2 & "8_Extracted_NewspaperNotices.pdf" ': MsgBox fl.FileExists(EighthFilePath)
NinethFilePath = BaseFilePath2 & "9_Extracted_SoothillWarRegister.pdf" ': MsgBox fl.FileExists(NinethFilePath)
TenthFilePath = BaseFilePath2 & "10_Extracted_StPaulinusBaptisms.pdf" ': MsgBox fl.FileExists(NinethFilePath)
EleventhFilePath = BaseFilePath2 & "11_Extracted_41Census.pdf" ': MsgBox fl.FileExists(NinethFilePath)
TwelfthFilePath = BaseFilePath2 & "12_Extracted_51Census.pdf" ': MsgBox fl.FileExists(NinethFilePath)
FourteenthFilePath = BaseFilePath2 & "14_Blank_Report.pdf" ': MsgBox fl.FileExists(NinethFilePath)

CurrentDate = Date
CurrentTime = Time()
CurrentDateandTime = "_" + CurrentDate + "_" + CurrentTime + ".pdf"

Operations = "cat output"

OutputFilePath = OutputPath & "Search_Report.pdf" ' xxxxxxxxxxxx

ArgumentString = FirstFilePath & " " & SecondFilePath & " " & ThirdFilePath & " " & FourthFilePath & " " & FifthFilePath & " " & SixthFilePath & " " & EleventhFilePath & " " & TwelfthFilePath & " " & FourteenthFilePath & " " & Operations & " " & OutputFilePath

executablepath = Environ("ProgramFiles(x86)") & "\PDFtk Server\bin\PDFTK.exe"

Shell (executablepath & " " & ArgumentString), 3

For Counter = 1 To 40000

Next Counter

RenameInput = Environ("userprofile") & "\documents\datafiles\BHFH_Search_NotOurArea\Search_Report.pdf" ': MsgBox fl.FileExists(RenameInput) '.pdf"

RenameOutput = Environ("userprofile") & "\documents\datafiles\BHFH_Search_NotOurArea\Search_Report_"

RenameOutput = RenameOutput + SearchAreaAndName

DoCmd.SetWarnings (Warningsoff)

RenameOutput = RenameOutput + CurrentDateandTime

MsgBox RenameInput
MsgBox RenameOutput

Name RenameInput As RenameOutput

Using the + for concatenation has always worked for me, what else should be used?

Regards

 

[arnelgp]

Date Delimiter "/" is not allowed on Filename.
you need to Replace() it with "_" or "."

also ":" will cause your text to be truncated, so avoid also (Time portion).

 

[cheekybuddha]

Arnel is on to the probable cause here.

Try:

' ...
CurrentDateandTime = Format(Now, "\_yyyymmdd\-hhnnss\.\p\d\f")
' ...

 

[bleakhouse]

CurrentDate = Date
CurrentTime = Time()

CurrentDate = Replace(CurrentDate, "/", "_")

CurrentDateandTime = "_" & CurrentDate & " at " + CurrentTime & ".pdf"

Operations = "cat output"

OutputFilePath = OutputPath & "Search_Report.pdf" ' xxxxxxxxxxxx

ArgumentString = FirstFilePath & " " & SecondFilePath & " " & ThirdFilePath & " " & FourthFilePath & " " & FifthFilePath & " " & SixthFilePath & " " & EleventhFilePath & " " & TwelfthFilePath & " " & FourteenthFilePath & " " & Operations & " " & OutputFilePath

executablepath = Environ("ProgramFiles(x86)") & "\PDFtk Server\bin\PDFTK.exe"


RenameInput = Environ("userprofile") & "\documents\datafiles\BHFH_Search_NotOurArea\Search_Report.pdf" ': MsgBox fl.FileExists(RenameInput) '.pdf"

RenameOutput = Environ("userprofile") & "\documents\datafiles\BHFH_Search_NotOurArea\Search_Report_"

RenameOutput = RenameOutput + SearchAreaAndName

DoCmd.SetWarnings (Warningsoff)

RenameOutput = RenameOutput + CurrentDateandTime

MsgBox RenameInput
MsgBox RenameOutput

Name RenameInput As RenameOutput

Arnel is on to the probable cause here.

Try:

' ...
CurrentDateandTime = Format(Now, "\_yyyymmdd\-hhnnss\.\p\d\f")
' ...

I have tried lots of ways to fix this which have failed, this solution works. Thank you ever so much.

Regards and Thank you

Ian

 

[cheekybuddha]

You're welcome! I'm pleased we could help.

 

[Isaac]

Next time please remember to use code tags and indent your code to make it readable