On Event Click Error 400

[CedarTree]

Hi - getting the above error but only in the accde version (so I don't know where VBA is broken). In full accdb file, no problem. I confirmed all References are good.

Suggestions please?

 

[MajP]

Before making the accde ensure all modules have "Option Explicit" at the top and do a Debug Compile and ensure the code compiles. If that still fails post the entire module here, not just the click event code.

 

[CedarTree]

Thanks. Option Explicit I always add. Compiles fine too. I recompiled accde file and the code ran fine one time. I click on another record and go back to original record, click the button, and it fails again.

On event...

Private Sub btnCreateReport_Click()

    Dim sReportName As String
    
    Me.Dirty = False
    sReportName = fnCheckNullString(Me.zReportName)
    If sReportName = "" Then Exit Sub
    
    gvTemp = fnMessage(True, "Please confirm you want to run this report: " + sReportName)
    If gvTemp <> 1 Then Exit Sub
    
    Call subCreateReport(sReportName)
    
End Sub

Called subroutine... which creates a formatted Excel report

Sub subCreateReport(pReportName As String)

    Dim sql As String, rst As DAO.Recordset
    Dim sQueryToRun As String, sQueryToExport As String
    Dim iWorksheet As Integer, iWorksheets As Integer, iColumn As Integer, iColumns As Integer, sFreezeCell As String
    Dim sRange As String, sValue As String
    Dim sColumn As String, sColumnRevised As String, sColumnFormat As String
    Dim sReportPath As String, sReportFileName As String, sReportFileNameFull As String
    Dim sTeamMemberName As String, dtNow As Date
    
    Call subShowMessage("Running report: " + pReportName, "Report")
    dtNow = Now()
    
    sTeamMemberName = Replace(Replace(Replace(gsTeamMemberName, ",", ""), " ", ""), "'", "")
    If sTeamMemberName = "" Then Exit Sub
    sReportPath = gsReportPath + sTeamMemberName
    If fnDirExists(sReportPath) = False Then
        MkDir sReportPath
    End If
    
    'Run queries as needed
        sql = "SELECT * FROM usysLOCtblReportsQueries"
        sql = sql + " WHERE ReportName = '" + pReportName + "'"
        sql = sql + " ORDER BY QueryToRun"
        Set rst = CurrentDb.OpenRecordset(sql)
        If Not rst.EOF Then
            rst.MoveFirst
            While Not rst.EOF
                sQueryToRun = rst("QueryToRun")
                CurrentDb.Execute (sQueryToRun)
                rst.MoveNext
            Wend
        End If
        rst.Close
        Set rst = Nothing

    Select Case pReportName
        Case "Summary of Active Projects"
            sReportFileName = "SummaryActiveProjects_" + Format(dtNow, "YYYY_MM_DD_HH_MM") + ".xlsx"
            sReportFileNameFull = sReportPath + "\" + sReportFileName
            If fnFileExists(sReportFileNameFull) Then Stop
            sQueryToExport = CStr(fnLookupReportItem(pReportName, "QueryToExport"))
            iWorksheets = CInt(fnLookupReportItem(pReportName, "Worksheets"))
            iColumns = CInt(fnLookupReportItem(pReportName, "Columns"))
            sFreezeCell = "G3"
            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, sQueryToExport, sReportFileNameFull, True
        Case Else
            Exit Sub
    End Select

    Set gobjExcel = CreateObject("Excel.Application")
    Set gobjBook = gobjExcel.Workbooks.Open(sReportFileNameFull, False, False)
    If gsTeamMemberUsername = gsRoy Then
        gobjExcel.Visible = False
        'Stop
    Else
        gobjExcel.Visible = False
    End If
        
    For iWorksheet = 1 To iWorksheets
        gobjBook.Sheets(iWorksheet).Activate
        Set gobjSheet = gobjBook.ActiveSheet
        gobjExcel.ActiveWindow.zoom = 85
        gobjSheet.Range("1:1").WrapText = True
        gobjSheet.Range("1:1").Font.Bold = True
        gobjSheet.UsedRange.NumberFormat = "General"
        gobjSheet.UsedRange.Value = gobjSheet.UsedRange.Value
        gobjSheet.UsedRange.AutoFilter
        gobjSheet.UsedRange.Columns.AutoFit
        gobjSheet.Range("1:1").EntireRow.INSERT
        gobjSheet.Range("A1") = pReportName
        gobjSheet.Range("A1").Font.Bold = True
        gobjSheet.Name = pReportName
        gobjSheet.Range(sFreezeCell).select
        gobjExcel.ActiveWindow.FreezePanes = True
        For iColumn = iColumns To 1 Step -1 'have to work backwards since deleting some columns
            If gobjSheet.Columns(iColumn).ColumnWidth > 100 Then
                gobjSheet.Columns(iColumn).ColumnWidth = 100
            ElseIf gobjSheet.Columns(iColumn).ColumnWidth < 10 Then
                gobjSheet.Columns(iColumn).ColumnWidth = 10
            End If
            sColumn = CStr(gobjSheet.cells(2, iColumn)) 'First row has client name
            'If sColumn = "ProjectPhaseNum" Then Stop
            sColumnRevised = fnLookupReportColumn(pReportName, sColumn, "ColumnRevised")
            If sColumnRevised = "DELETE" Then
                gobjSheet.Columns(iColumn).Delete
            Else
                gobjSheet.cells(2, iColumn) = sColumnRevised
            End If
            sColumnFormat = fnLookupReportColumn(pReportName, sColumn, "ColumnFormat")
            If sColumnFormat <> "" Then gobjSheet.Columns(iColumn).NumberFormat = sColumnFormat
            'If sValue <> "PID" And Left(sValue, 3) = "PID" Then gobjSheet.Columns(iColumn).EntireColumn.Delete
        Next iColumn
    Next iWorksheet
    
    gobjSheet.Range("A1").select
    gobjBook.Save
    Set gobjSheet = Nothing
    Set gobjBook = Nothing
    gobjExcel.Visible = True
    Set gobjExcel = Nothing
            
    Call subShowMessage

End Sub

 

[CedarTree]

Things get as far as "Call subShowMessage("Running report: " + pReportName, "Report")" at least I know that - because that calls a pop-up that does show up just fine.

 

[CedarTree]

Can't walk the code in accde file unfortunately. Just accdb where I can't (yet) re-create the error.

 

[MajP]

That error is normally not a run time error, but a compile time error. So if it compiles you would not expect this. But to be safe verify you are passing a viable report name.
You seem to check that it is not null, or "", but you could still be passing a bad name.

 

[CedarTree]

Hold on... it might be the STOP in the code that I meant to add more logic to...

 

[MajP]

Not that I think this is an issue, but do not concatenate in access using an + use an &. Only use the + if you want null propagation.
Null + String = Null
Null & string = String

 

[CedarTree]

That seems to be it - it was stopping and ACCDE didn't know what to do. Sorry!!!

 

[CedarTree]

Not that I think this is an issue, but do not concatenate in access using an + use an &. Only use the + if you want null propagation.
Null + String = Null
Null & string = String

Good to know!!!

 

[MajP]

Good to know!!!

This can work to your advantage, but if not aware it can cause some BIG problems. Since you did not deliberately want to propagate a null, do not use it

Here is an example where using it is to your advantage and what can happen if not. Imagine you have First, Last, and MI for a name field. Some people may have a MI
You can then have a concatenated field that handles both cases without a complex iif.
FullName: = [first] & " " & [MI] + ". " & [Last]

If the name was John, Smith, and MI = L
John L. Smith
but if MI is null then
"John" & " " & Null + ". " & Smith
= "John" & " " & Null & "Smith"
= John Smith


If you instead used all "&" then you would get
John . Smith
If you used all + then you get
Null

To demo what can happen

Public Sub Test()
  Dim first As Variant
  Dim MI As Variant
  Dim Last As Variant
  first = "John"
  Last = "Smith"
  MI = "l"
  Debug.Print first & " " & MI + ". " & Last
  'now make it null 
  MI = Null
  Debug.Print first & " " & MI + ". " & Last
  Debug.Print "all & "
  Debug.Print first & " " & MI & ". " & Last
  Debug.Print "all +"
  Debug.Print first + " " + MI + ". " + Last
End Sub

John l. Smith
John Smith
- all &
John . Smith
- all +
Null

 

[Pat Hartman]

1. Add error traps to all significant code modules at a minimum. If you don't have specific errors you want to check for, just use an Else:

Select Case Err.Number
    Case Else
        Msgbox Err.Number & " -- " & Err.Description
End Case

You can add resume code if you want but I don't in this case because I don't know without knowing what the error is whether or not it is safe to resume. This Case will show you the error number as well as the description. That will help you if you need to trap for the error. Some people go much further and have a procedure that logs the error and even generates an email to the developer so he knows the error is happening even if the user forgets to report it. In that case, you need more info such as the procedure name and module where the failure is occurring.
2. Understanding the difference between + and & in concatenation operations is very useful.