Access VBA List not populating Data-CodePudding

I have an Access VBA list box with a rowsource that will not populate. It was working last week and after reopening the DB this week, for some reason it will not show the results of my string.

Private Sub PullData(strType As String)
Dim rst As DAO.Recordset
Dim sSQL As String

On Error GoTo Err_PullData

Set rst = CurrentDb.OpenRecordset("SELECT * FROM TBLactionstaken_ARCHIVE", dbOpenDynaset, dbReadOnly)

Select Case strType

Case "Actions"
    
    If Me.Frame388.Value = 1 Then
        Me.lstActionsTaken.RowSource = "SELECT TBLactionstaken_ARCHIVE.RecordNumber, TBLactionstaken_ARCHIVE.ActionTakenID, TBLparticipants.[PartSS#], TBLactionstaken_ARCHIVE.ActionDate, TBLactionstaken_ARCHIVE.ActionStatus, TBLactionstaken_ARCHIVE.ReasonID, " & _
                                       "TBLreasons.Description, TBLactionstaken_ARCHIVE.MANHType, TBLactionstaken_ARCHIVE.ProcessedDate, TBLactionstaken_ARCHIVE.PayOutStatus, " & _
                                       "TBLactionstaken_ARCHIVE.PayOutDate FROM (TBLactionstaken_ARCHIVE INNER JOIN TBLparticipants ON TBLactionstaken_ARCHIVE.RecordNumber = " & _
                                       "TBLparticipants.RecordNumber) INNER JOIN TBLreasons ON TBLactionstaken_ARCHIVE.ReasonID = TBLreasons.ReasonID WHERE " & _
                                       "(((TBLactionstaken_ARCHIVE.ActionDate)<= [Form]![txtEndDate] And (TBLactionstaken_ARCHIVE.ActionDate)>= [Form]![txtEndDate]));"


          Me.lstActionsTaken.Requery
          
    ElseIf Me.Frame388.Value = 2 Then
        Me.lstActionsTaken.RowSource = ""
    Else
        MsgBox "No Data Available", vbExclamation, "Archive Search"
        Exit Sub
    End If

Case "Transactions"
    lstActionsTaken.Visible = False
    lstTransactions.Visible = True
    
End Select

Exit_PullData:

Exit Sub

Err_PullData:
    MsgBox Err.Description
    Resume Exit_PullData
    
End Sub

The list box is set up with 11 columns. It's not throwing an error and I can use the immediate window to determine that my fields have values.

CodePudding user response：

Try with specificly formatted date expressions:

    If Me.Frame388.Value = 1 Then
        Me.lstActionsTaken.RowSource = _
            "SELECT TBLactionstaken_ARCHIVE.RecordNumber, TBLactionstaken_ARCHIVE.ActionTakenID, TBLparticipants.[PartSS#], TBLactionstaken_ARCHIVE.ActionDate, TBLactionstaken_ARCHIVE.ActionStatus, TBLactionstaken_ARCHIVE.ReasonID, " & _
            "TBLreasons.Description, TBLactionstaken_ARCHIVE.MANHType, TBLactionstaken_ARCHIVE.ProcessedDate, TBLactionstaken_ARCHIVE.PayOutStatus, " & _
            "TBLactionstaken_ARCHIVE.PayOutDate FROM (TBLactionstaken_ARCHIVE INNER JOIN TBLparticipants ON TBLactionstaken_ARCHIVE.RecordNumber = " & _
            "TBLparticipants.RecordNumber) INNER JOIN TBLreasons ON TBLactionstaken_ARCHIVE.ReasonID = TBLreasons.ReasonID WHERE " & _
            "(TBLactionstaken_ARCHIVE.ActionDate <= #" & Format(Me!txtEndDate.Value, "yyyy\/mm\/dd") & "# And TBLactionstaken_ARCHIVE.ActionDate >= #" & Format(Me!txtEndDate.Value, "yyyy\/mm\/dd") & "#);"

' Not needed:          Me.lstActionsTaken.Requery