Code:
Private Sub cbxRptCriteria_Change()
Dim strListSQL As String
strListSQL = ""
Me.lstRptSearchResult.RowSource = ""
Me.cbxReportType.RowSource = ""
dt1 = Me.tbxDate1.Value
dt2 = Me.tbxDate2.Value
With Me.cbxReportType
.AddItem "Defect Reports"
.AddItem "Inventory Reports"
.AddItem "Work Order Reports"
End With
Select Case Me.cbxReportType
Case "Work Order Reports"
With Me.cbxRptCriteria
.RowSource = ""
.AddItem "Date Range"
.AddItem "Employee"
.AddItem "SKU"
End With
Case "Defect Reports"
With Me.cbxRptCriteria
.RowSource = ""
.AddItem "Category"
.AddItem "Date Range"
.AddItem "Defect Type"
.AddItem "Employee"
.AddItem "Marketplace"
.AddItem "SKU"
.AddItem "Admin Summary"
End With
Case "Inventory Reports"
With Me.cbxRptCriteria
.RowSource = ""
.AddItem "All Inventory"
.AddItem "Company"
.AddItem "Discontinued"
.AddItem "SKU"
End With
End Select
'Creates SQL Query for results box
Select Case Me.cbxReportType.Value
Case "Defect Reports"
Select Case Me.cbxRptCriteria.Value
Case "Category"
If Me.tbxDate1 = "Optional" Or Me.tbxDate2 = "Optional" Then
MsgBox "Omitting a date range will list all available records.", vbInformation, "ATTENTION"
strListSQL = "SELECT DISTINCT Category FROM DefectEvents"
Else
strListSQL = "SELECT DISTINCT Category FROM DefectEvents WHERE Date_Time >= #" & Format(dt1, "yyyy-mm-dd") & "# AND Date_Time < #" & Format(dt2, "yyyy-mm-dd") & "#;"
End If
Case "Defect Type"
If Me.tbxDate1 = "Optional" Or Me.tbxDate2 = "Optional" Then
MsgBox "Omitting a date range will list all available records.", vbInformation, "ATTENTION"
strListSQL = "SELECT DISTINCT Defect FROM DefectEvents"
Else
strListSQL = "SELECT DISTINCT Defect FROM DefectEvents WHERE Date_Time >= #" & Format(dt1, "yyyy-mm-dd") & "# AND Date_Time < #" & Format(dt2, "yyyy-mm-dd") & "#;"
End If
Case "Marketplace"
If Me.tbxDate1 = "Optional" Or Me.tbxDate2 = "Optional" Then
MsgBox "Omitting a date range will list all available records.", vbInformation, "ATTENTION"
strListSQL = "SELECT DISTINCT Marketplace FROM DefectEvents"
Else
strListSQL = "SELECT DISTINCT Marketplace FROM DefectEvents WHERE Date_Time >= #" & Format(dt1, "yyyy-mm-dd") & "# AND Date_Time < #" & Format(dt2, "yyyy-mm-dd") & "#;"
End If
Case "Date Range"
If Me.tbxDate1 = "Optional" Or Me.tbxDate2 = "Optional" Then
MsgBox "Omitting a date range will list all available records.", vbInformation, "ATTENTION"
MsgBox "You must select a date range using the Begin Date and End Date fields above.", vbInformation, "ATTENTION"
Else
End If
Case "SKU"
If Me.tbxDate1 = "Optional" Or Me.tbxDate2 = "Optional" Then
MsgBox "Omitting a date range will list all available records.", vbInformation, "ATTENTION"
strListSQL = "SELECT DISTINCT SKU FROM DefectEvents"
Else
strListSQL = "SELECT DISTINCT SKU FROM DefectEvents WHERE Date_Time >= #" & Format(dt1, "yyyy-mm-dd") & "# AND Date_Time < #" & Format(dt2, "yyyy-mm-dd") & "#;"
End If
Case "Employee"
If Me.tbxDate1 = "Optional" Or Me.tbxDate2 = "Optional" Then
MsgBox "Omitting a date range will list all available records.", vbInformation, "ATTENTION"
strListSQL = "SELECT DISTINCT Employee FROM DefectEvents"
Else
strListSQL = "SELECT DISTINCT Employee FROM DefectEvents WHERE Date_Time >= #" & Format(dt1, "yyyy-mm-dd") & "# AND Date_Time < #" & Format(dt2, "yyyy-mm-dd") & "#;"
End If
Case "Admin Summary"
If Me.tbxDate1 = "Optional" Or Me.tbxDate2 = "Optional" Then
MsgBox "You must select the date range for the week desired.", vbInformation, "ATTENTION"
strListSQL = ""
Else
MsgBox "Generating this report will send a copy to the accounting office.", vbOKCancel, "ATTENTION"
End If
End Select
Case "Inventory Reports"
Me.lstRptSearchResult.ColumnCount = 1
Select Case Me.cbxRptCriteria.Value
Case "All Inventory"
MsgBox "This will result in all items in inventory being included.", vbOKCancel, "All Inventory"
Me.lstRptSearchResult.Enabled = False
Case "Company"
If Me.tbxDate1 = "Optional" Or Me.tbxDate2 = "Optional" Then
MsgBox "Omitting a date range will list all available records.", vbInformation, "ATTENTION"
strListSQL = "SELECT DISTINCT Company FROM Inventory"
Else
strListSQL = "SELECT DISTINCT Company FROM Inventory WHERE Date_Time >= #" & Format(dt1, "yyyy-mm-dd") & "# AND Date_Time < #" & Format(dt2, "yyyy-mm-dd") & "#;"
End If
Case "SKU"
If Me.tbxDate1 = "Optional" Or Me.tbxDate2 = "Optional" Then
MsgBox "Omitting a date range will list all available records.", vbInformation, "ATTENTION"
strListSQL = "SELECT DISTINCT SKU FROM Inventory"
Else
strListSQL = "SELECT DISTINCT SKU FROM Inventory WHERE Date_Time >= #" & Format(dt1, "yyyy-mm-dd") & "# AND Date_Time < #" & Format(dt2, "yyyy-mm-dd") & "#;"
End If
Case "Discontinued"
If Me.tbxDate1 = "Optional" Or Me.tbxDate2 = "Optional" Then
MsgBox "Omitting a date range will list all available records.", vbInformation, "ATTENTION"
strListSQL = "SELECT DISTINCT Discontinued FROM Inventory"
Else
strListSQL = "SELECT DISTINCT Discontinued FROM Inventory WHERE Date_Time >= #" & Format(dt1, "yyyy-mm-dd") & "# AND Date_Time < #" & Format(dt2, "yyyy-mm-dd") & "#;"
End If
End Select
Case "Work Order Reports"
Me.lstRptSearchResult.ColumnCount = 1
Select Case Me.cbxRptCriteria.Value
Case "Date Range"
If Me.tbxDate1 = "Optional" Or Me.tbxDate2 = "Optional" Then
MsgBox "Omitting a date range will list all available records.", vbInformation, "ATTENTION"
MsgBox "You must select a date range using the Begin Date and End Date fields above.", vbInformation, "ATTENTION"
Else
strListSQL = "SELECT * FROM WOTracking WHERE Date_Time >= #" & Format(dt1, "yyyy-mm-dd") & "# AND Date_Time < #" & Format(dt2, "yyyy-mm-dd") & "#;"
End If
Case "Employee"
If Me.tbxDate1 = "Optional" Or Me.tbxDate2 = "Optional" Then
MsgBox "Omitting a date range will list all available records.", vbInformation, "ATTENTION"
strListSQL = "SELECT DISTINCT Employee FROM WOTracking"
Else
strListSQL = "SELECT DISTINCT Employee FROM WOTracking WHERE Date_Time >= #" & Format(dt1, "yyyy-mm-dd") & "# AND Date_Time < #" & Format(dt2, "yyyy-mm-dd") & "#;"
End If
Case "SKU"
If Me.tbxDate1 = "Optional" Or Me.tbxDate2 = "Optional" Then
MsgBox "Omitting a date range will list all available records.", vbInformation, "ATTENTION"
strListSQL = "SELECT DISTINCT SKU FROM WOTracking"
Else
strListSQL = "SELECT DISTINCT SKU FROM WOTracking WHERE Date_Time >= #" & Format(dt1, "yyyy-mm-dd") & "# AND Date_Time < #" & Format(dt2, "yyyy-mm-dd") & "#;"
End If
End Select
End Select
'Populates Listbox
Debug.Print strListSQL
Me.lstRptSearchResult.RowSource = ""
Me.lstRptSearchResult.RowSource = strListSQL
Me.lstRptSearchResult.Requery
End Sub
Regardless of the selections made, the value of strListSQL remains blank. I just end up with a bunch of blank lines in the Immediate window from the debug.