Results 1 to 6 of 6
  1. #1
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130

    Variable Remains Empty

    Up until start up this morning, this was working correctly. I am using a series of SELECT CASE to create an SQL query in order to populate a listbox.

    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.



    Probably unrelated but I did notice one thing upon opening the db this morning. Some of my controls were bound that were previously (and should remain) unbound.

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Maybe because I haven't eaten yet, but that doesn't make sense in the change event of a combo. For starters, in the change event you'd have to use the .Text property of the combo, not the .Value property. Second, you're resetting the row source of another combo, then testing its value. Doesn't seem like it could have a value at that point. Have you set a breakpoint and stepped through the code?

    Edit: and in the change event of the combo you're changing that combo's row source?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    If it was working, then something has changed, or you have some type of corruption.

    So first port of call, have you got an older backup to try, and if so make a copy of it and try it on another machine if possible first.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  4. #4
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    did you put a stop on:
    Select Case Me.cbxRptCriteria.Value

    and determine the value?
    then step (F8) thru the code to see where it leads to?

  5. #5
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130
    Unfortunately no backup for several days and a lot of other forms and reports. However, thanks to ranman256 and pbaldy. Stepping through showed that it was indeed a problem with Value vs Text. Thanks guys. Sometimes cannot see the forest for the trees.

  6. #6
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Glad you got it sorted.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 01-24-2019, 12:58 AM
  2. User Login Form remains Opened at backend!
    By cap.zadi in forum Forms
    Replies: 9
    Last Post: 07-09-2017, 05:50 AM
  3. Access table deleted but memory remains same
    By shaikfazil in forum Access
    Replies: 1
    Last Post: 03-26-2014, 03:33 AM
  4. Replies: 9
    Last Post: 04-20-2012, 08:13 AM
  5. combi box remains unbound
    By zac123 in forum Access
    Replies: 1
    Last Post: 11-23-2011, 04:45 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums