Multiy Select Form to update Report VB

[Blitznb]

Can someone please help review this code. I have been working on this for months now and at a loss. Have a multi list box/combo box form that should open a form on preview command. I have run into multiple issues which I had in other topics. After reviewing help files and chnaging code, form and reports I am still getting errors. Its time to move on our quit. Please help.

Currently I am getting Run Time Type Mismatch 13. on this line
If IsNull(TempVars![Display1]) Or (TempVars![Display]) Or IsNull(TempVars![Year]) Or IsNull(TempVars![Month]) Then

Report

Private Sub Report_Open(Cancel As Integer)
    On Error GoTo ErrorHandler
    Dim strSQL As String
    If IsNull(TempVars![Display1]) Or (TempVars![Display]) Or IsNull(TempVars![Year]) Or IsNull(TempVars![Month]) Then
        DoCmd.OpenForm "Sales Reports Dialog2"
        Cancel = True
        Exit Sub
    End If
    
    strSQL = "SELECT [Year]"
    strSQL = strSQL & ", First([" & TempVars![Display1] & "],) AS SalesGroupingField1"
    strSQL = strSQL & ", First([" & TempVars![Display] & "],) AS SalesGroupingField"
    strSQL = strSQL & ", Sum([Sales]) AS [Total Sales]"
    strSQL = strSQL & ", Sum([Commission]) AS [Total Commission]"
    strSQL = strSQL & ", Sum([Employee]) AS [Total Employee]"
    strSQL = strSQL & ", First([Sales Analysis].[Month Name]) AS [Month Name]"
    strSQL = strSQL & " FROM [Sales Analysis] "
    strSQL = strSQL & " Where [Year]=" & TempVars![Year]
    strSQL = strSQL & " GROUP BY [Year], [" & TempVars![Group By1] & "], [" & TempVars![Group By] & "]"
    
    
     Debug.Print strSQL
    Me.RecordSource = strSQL
    Me.SalesGroupingField1_Label.Caption = TempVars![Display1]
    Me.SalesGroupingField_Label.Caption = TempVars![Display]
Done:
    Exit Sub
ErrorHandler:
    ' Resume statement will be hit when debugging
    If eh.LogError("Yearly Sales Report1_Open", "strSQL = " & strSQL) Then
        Resume
    Else
        Cancel = True
    End If
End Sub

Form Code

Sub PrintReports(ReportView As AcView)
    ' This procedure used in Preview_Click and Print_Click Sub procedures.
    ' Preview or print report selected in the ReportToPrint option group.
    ' Then close the Print Sales Reports Dialog form.
    Dim strReportName As String
    Dim strGroupingFilter As String
    Dim strReportFilter As String
    Dim lOrderCount As Long
    ' Determine report filtering
    If Nz(Me.lstReportFilter) <> "" Then
        strReportFilter = "([SalesGroupingField] = """ & Me.lstReportFilter & """)"
    End If
    
    ' Determine reporting time frame
    Select Case Me.lstSalesPeriod1
    Case ByYear
        strReportName = "Yearly Sales Report1"
        lOrderCount = DCountWrapper("*", "Sales Analysis", "[Year]=" & Me.cbYear)
    Case ByQuarter
        strReportName = "Quarterly Sales Report"
        lOrderCount = DCountWrapper("*", "Sales Analysis", "[Year]=" & Me.cbYear & " AND [Quarter]=" & Me.cbQuarter)
    Case ByMonth
        strReportName = "Yearly Sales Report1"
        lOrderCount = DCountWrapper("*", "Sales Analysis", "[Year]=" & Me.cbYear & " AND [Month]=" & Me.cbMonth)
    End Select
        
    If lOrderCount > 0 Then
        TempVars.Add "Group By1", Me.lstSalesGrouping.Value
        TempVars.Add "Group By", Me.lstSalesReports.Value
        TempVars.Add "Order By", Me.lstSalesReports.Value
        TempVars.Add "Display1", DLookupStringWrapper("[Display]", "Sales Reports2", "[Group By]='" & Nz(Me.lstSalesGrouping) & "'")
        TempVars.Add "Display", DLookupStringWrapper("[Display]", "Sales Reports", "[Group By]='" & Nz(Me.lstSalesReports) & "'")
        TempVars.Add "Year", Me.cbYear.Value
        TempVars.Add "Quarter", Me.cbQuarter.Value
        TempVars.Add "Month", Me.cbMonth.Value
   
        eh.TryToCloseObject
        DoCmd.OpenReport strReportName, ReportView, , strReportFilter, acWindowNormal
    Else
        MsgBoxOKOnly NoSalesInPeriod
    End If
    
End Sub

Pointers? Help? Direction?
It really should not be this hard, maybe I am overthinking or underthinking.......I am at a loss.

 

[VilaRestal]

At first glance
If IsNull(TempVars![Display1]) Or (TempVars![Display]) Or IsNull(TempVars![Year]) Or IsNull(TempVars![Month]) Then

perhaps should be

If IsNull(TempVars![Display1]) Or IsNull(TempVars![Display]) Or IsNull(TempVars![Year]) Or IsNull(TempVars![Month]) Then

Note the missing IsNull

 

[VilaRestal]

And second glance , in the second bit of code the line:
If Nz(Me.lstReportFilter) <> "" Then
should be
If Nz(Me.lstReportFilter,"") <> "" Then

 

[VilaRestal]

And 3rd glance
In the Form's code you're adding TempVars without checking whether they exist already.

I would prefer to use public variables (in the form or a module). You'll know they always exist so all you'll ever be doing is setting and getting their values. Less !s and []s too

Maybe even do

Public Type ReportCriteria
    strGroupBy1 As String
    strGroupBy2 As String
    strOrderBy As String
    strDisplay1 As String
    strDisplay2 As String
    intYear As Integer
    intQuarter As Integer
    intMonth As Integer
End Type

 

[Blitznb]

Geezzzzzzzzzz believe my brain is null. Thank you for that. Also thank you for the second one. I actually found that I was Summing Employee which killed the code.......GOT THAT FIXED AND YEA it works@@@@!!!!!!!!! WHOO HOO!!!!!!!!!!!! drinking heavy tonight@@!!!!!!!!!!!!!!!

Not sure if I can do the Public for strings when I have multiple forms like the example above to run different forms. Also dont believe it will let me do pub in form or report will it? Maybe I just dont understand public, private etc..... Please correct me if I am wrong. I tried in form and it Errored.

 

[VilaRestal]

Public variables in forms appear as properties of the form. You can access them in the same way you would a control on the form.

But, if it's working then don't 'fix' it. You just might want to keep an eye out for that if ever bugs occur when people run a report twice.