Create Log

[Gerrit.B]

I am trying to add error detailt to table with following code.
When report can't be exported it should add details like reportname, database and path to table and quit database.

But when code starts I get runtime error "3134", Syntax errot in INSERT INTO statement.

Where did I go wrong in my code?

Function Test()
Dim strDBName As String
Dim strDBPath As String
Dim strRptName As String
Dim str2SQL As String

strDBName = Application.CurrentProject.Name
strDBPath = Application.CurrentProject.Path
strRptName = "ReportName"

    str2SQL = "INSERT INTO tblLog ([Log], [Database], [Path]) VALUES ('" & strRptName & "','" & strDBName & "','" & strDBPath & "');"       

DoCmd.RunSQL str2SQL    
DoCmd.QuitEnd Function

 

[Gerrit.B]

Closed database, and it's is working now.

 

[Gerrit.B]

Still not working as required.

Option Compare Database
Option Explicit


Const sDefaultPath As String = "C:\Temp\"
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strRptName As String
Dim intCounter As Integer
Dim rpt As Report
Dim str2SQL As String
Dim strDBName As String
Dim strDBPath As String
Dim strFunctionName As String
Dim strModuleName As String


Function ExportReportsPDF()


On Error GoTo ExportReportsPDF_Err


If Len(Dir(sDefaultPath, vbDirectory)) = 0 Then
MyMkDir sDefaultPath
End If


strDBName = Application.CurrentProject.Name
strDBPath = Application.CurrentProject.Path
strModuleName = Application.VBE.SelectedVBComponent.Name


strFunctionName = "ExportReportsPDF"


    strSQL = "SELECT [MSysObjects]![Name] AS Report " & _
             "FROM MSysObjects " & _
             "WHERE (((MSysObjects.Name) Like ""exp*"") And ((MSysObjects.Type) = -32764))"


    Set rst = DBEngine(0)(0).OpenRecordset(strSQL)


    Do While Not rst.EOF
        Debug.Print rst!Report
        strRptName = rst!Report
        
DoCmd.OpenReport strRptName, acViewDesign
Set rpt = Reports(strRptName)
rpt.Printer.PaperSize = acPRPSA4
DoCmd.Save
DoCmd.Close acReport, strRptName, acSaveNo
        
DoCmd.OutputTo acOutputReport, strRptName, "PDFFormat(*.pdf)", sDefaultPath & Mid(rst!Report, 4, 55) & ".pdf", False, "", 0, acExportQualityPrint


        rst.MoveNext
intCounter = intCounter + 1 ' increase value of intCounter by 1


    Loop


ExportReportsPDF_Exit:
Exit Function


ExportReportsPDF_Err:
    str2SQL = "INSERT INTO tblError ([Export], [Database],[Path],[ModuleName],[FunctionName]) VALUES ('" & strRptName & "','" & strDBName & "','" & strDBPath & "','" & strModuleName & "','" & strFunctionName & "');"
    DoCmd.RunSQL str2SQL
    
Resume ExportReportsPDF_Exit


End Function

I use code above.
When I export 10 reports It stops at report 5 and inserted this in tblError.
After that it quits, how to change above code that it will restart with next report.

Sample database: https://gerritbakker.stackstorage.com/s/2Sdx9y5aF73F3ZO

 

[Gerrit.B]

Almost there.

Option Compare Database
Option Explicit


Const sDefaultPath As String = "C:\Temp\Error\"
Dim rst As DAO.Recordset
Dim str1SQL As String
Dim str2SQL As String
Dim strRptName As String
Dim rpt As Report
Dim strDBName As String
Dim strDBPath As String
Dim strFunctionName As String
Dim strModuleName As String


Function ExportReportsPDF()


DoCmd.SetWarnings False


On Error GoTo ExportReportsPDF_Err


If Len(Dir(sDefaultPath, vbDirectory)) = 0 Then
MyMkDir sDefaultPath
End If


strDBName = Application.CurrentProject.Name
strDBPath = Application.CurrentProject.Path
strModuleName = Application.VBE.SelectedVBComponent.Name


strFunctionName = "ExportReportsPDF"


    str1SQL = "SELECT [MSysObjects]![Name] AS Report " & _
             "FROM MSysObjects " & _
             "WHERE (((MSysObjects.Name) Like ""exp*"") And ((MSysObjects.Type) = -32764))"
    Set rst = DBEngine(0)(0).OpenRecordset(str1SQL)


    Do While Not rst.EOF
        Debug.Print rst!Report
        strRptName = rst!Report
              
DoCmd.OpenReport strRptName, acViewDesign
Set rpt = Reports(strRptName)
rpt.Printer.PaperSize = acPRPSA4
DoCmd.Save
DoCmd.Close acReport, strRptName, acSaveNo
     
DoCmd.OutputTo acOutputReport, strRptName, "PDFFormat(*.pdf)", sDefaultPath & Mid(rst!Report, 4, 55) & ".pdf", False, "", 0, acExportQualityPrint


MsgBox "Report " & Mid(strRptName, 4, 55) & " Exported"
        
        rst.MoveNext
    
    Loop


Exit Function


ExportReportsPDF_Err:
    str2SQL = "INSERT INTO tblError ([Export],[Database],[Path],[ModuleName],[FunctionName]) VALUES ('" & strRptName & "','" & strDBName & "','" & strDBPath & "','" & strModuleName & "','" & strFunctionName & "');"
    DoCmd.RunSQL str2SQL
    
MsgBox "Report " & Mid(strRptName, 4, 55) & " Failed"


Resume Next
    
DoCmd.SetWarnings True


End Function

After receiving error ""Report 5 Failed"" with report 5 it will run str2SQL and will resume with same report and returns message "Report 5 Exported"

Why does it return to the same report again?

 

[Micron]

Because you're presenting the message box regardless of whether or not the export was successful, (Resume Next) followed by the rs.MoveNext. You need to skip whatever comes after the error causing part and move on to the next record. This should do it:

...
DoCmd.OutputTo acOutputReport, ....
MsgBox "Report " & Mid(strRptName, 4, 55) & " Exported"

ExportError:
        rst.MoveNext
...
...
MsgBox "Report " & Mid(strRptName, 4, 55) & " Failed"
...
...
Resume ExportError

BTW, you should always MoveFirst before beginning a loop as there is no guarantee you will start with what you think is the first record in any recordset. I'd also recommend at least testing that the rs is not empty before doing anything with it, including any Move statements. Like
IF rs.Recordcount = 0 Then..., (not > 0) or
IF rs.EOF OR rs.BOF Then...
In either case, the rs has no records and you should exit rather than try to work with the rs.