Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Listbox Sorting Problem

Status
Not open for further replies.
joel009

joel009

Programmer
Jul 7, 2000
272
US
Hi!!
I have a listbox on a single form that is not cooperating. I searched through the threads and found some info but can't make it work.
Using Access 2010 in 2007 mode. I am only working with the Case "lblPartNumber", the rest is waiting for this solution. I had it all set up and working with a continuous form but now I need multiselect so I am converting to a listbox.
Code: 
Private Sub SortDetail(SortField)

    'from the label click events of Part Number, Description, Route and Station
    'keep sort order from the Description and or the Part Number field
    Dim rstemp As Recordset
    Dim strNewSortOrder As String
    
    Select Case SortField
        Case "lblPartNumber"
            If InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "DESC", vbTextCompare) = 0 Or Me.lbxPartRouteAssignmentAdd.Recordset.Sort = "" Then
                Set rstemp = Me.lbxPartRouteAssignmentAdd.Recordset.Clone
                rstemp.Sort = "PartNumber DESC"
                
            Else
                Set rstemp = Me.lbxPartRouteAssignmentAdd.Recordset.Clone
                rstemp.Sort = "PartNumber"
            End If
            
        Case "lblRoute"
            If Me.lbxPartRouteAssignmentAdd.Recordset.Sort = "" Then
                Me.lbxPartRouteAssignmentAdd.Recordset.Sort = "Route"
'''                Me.lbxPartRouteAssignmentAdd.Recordset.sortByOn = True
                Exit Sub
            End If
            
            If InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "Description", vbTextCompare) <> 0 Then
                If InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "Route", vbTextCompare) <> 0 Then
                    If InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "Description DESC", vbTextCompare) <> 0 Or InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "[Description] DESC", vbTextCompare) <> 0 Then
                        If InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "Route DESC", vbTextCompare) <> 0 Or InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "[Route] DESC", vbTextCompare) <> 0 Then
                            Me.lbxPartRouteAssignmentAdd.Recordset.Sort = "[Route] ASC, [Description] DESC"
                        ElseIf InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "Route", vbTextCompare) <> 0 Or InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "[Route] ASC", vbTextCompare) <> 0 Then
                            Me.lbxPartRouteAssignmentAdd.Recordset.Sort = "[Route] DESC, [Description] DESC"
                        End If
                    ElseIf InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "Description", vbTextCompare) <> 0 Or InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "[Description] ASC", vbTextCompare) <> 0 Then
                        If InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "Route DESC", vbTextCompare) <> 0 Or InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "[Route] DESC", vbTextCompare) <> 0 Then
                            Me.lbxPartRouteAssignmentAdd.Recordset.Sort = "[Route] ASC, [Description] ASC"
                        ElseIf InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "Route", vbTextCompare) <> 0 Or InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "[Route] ASC", vbTextCompare) <> 0 Then
                            Me.lbxPartRouteAssignmentAdd.Recordset.Sort = "[Route] DESC, [Description] ASC"
                        End If
                    End If
                Else
                    If InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "Description DESC", vbTextCompare) <> 0 Or InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "[Description] DESC", vbTextCompare) <> 0 Then
                        Me.lbxPartRouteAssignmentAdd.Recordset.Sort = "[Route] ASC, [Description] DESC"
                    Else
                        Me.lbxPartRouteAssignmentAdd.Recordset.Sort = "[Route] ASC, [Description] ASC"
                    End If
                End If
            ElseIf InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "Route", vbTextCompare) <> 0 Or InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "[Route] ASC", vbTextCompare) <> 0 Then
                If InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "Route DESC", vbTextCompare) <> 0 Or InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "[Route] DESC", vbTextCompare) <> 0 Then
                    Me.lbxPartRouteAssignmentAdd.Recordset.Sort = "[Route] ASC"
                Else
                    Me.lbxPartRouteAssignmentAdd.Recordset.Sort = "[Route] DESC"
                End If
            End If
     
        Case "lblStation"
            If Me.lbxPartRouteAssignmentAdd.Recordset.Sort = "" Then
                Me.lbxPartRouteAssignmentAdd.Recordset.Sort = "Station"
'''                Me.lbxPartRouteAssignmentAdd.Recordset.SortOn = True
                Exit Sub
            End If
            
            If InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "Description", vbTextCompare) <> 0 Then
                If InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "Station", vbTextCompare) <> 0 Then
                    If InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "Description DESC", vbTextCompare) <> 0 Or InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "[Description] DESC", vbTextCompare) <> 0 Then
                        If InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "Station DESC", vbTextCompare) <> 0 Or InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "[Station] DESC", vbTextCompare) <> 0 Then
                            Me.lbxPartRouteAssignmentAdd.Recordset.Sort = "[Station] ASC, [Description] DESC"
                        ElseIf InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "Station", vbTextCompare) <> 0 Or InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "[Station] ASC", vbTextCompare) <> 0 Then
                            Me.lbxPartRouteAssignmentAdd.Recordset.Sort = "[Station] DESC, [Description] DESC"
                        End If
                    ElseIf InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "Description", vbTextCompare) <> 0 Or InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "[Description] ASC", vbTextCompare) <> 0 Then
                        If InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "Station DESC", vbTextCompare) <> 0 Or InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "[Station] DESC", vbTextCompare) <> 0 Then
                            Me.lbxPartRouteAssignmentAdd.Recordset.Sort = "[Station] ASC, [Description] ASC"
                        ElseIf InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "Station", vbTextCompare) <> 0 Or InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "[Station] ASC", vbTextCompare) <> 0 Then
                            Me.lbxPartRouteAssignmentAdd.Recordset.Sort = "[Station] DESC, [Description] ASC"
                        End If
                    End If
                Else
                    If InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "Description DESC", vbTextCompare) <> 0 Or InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "[Description] DESC", vbTextCompare) <> 0 Then
                        Me.lbxPartRouteAssignmentAdd.Recordset.Sort = "[Station] ASC, [Description] DESC"
                    Else
                        Me.lbxPartRouteAssignmentAdd.Recordset.Sort = "[Station] ASC, [Description] ASC"
                    End If
                End If
            ElseIf InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "Station", vbTextCompare) <> 0 Or InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "[Station] ASC", vbTextCompare) <> 0 Then
                If InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "Station DESC", vbTextCompare) <> 0 Or InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "[Station] DESC", vbTextCompare) <> 0 Then
                    Me.lbxPartRouteAssignmentAdd.Recordset.Sort = "[Station] ASC"
                Else
                    Me.lbxPartRouteAssignmentAdd.Recordset.Sort = "[Station] DESC"
                End If
            End If
        
        Case "lblDescription"
            'start Instr at position 5 because field name contains string we're seeking
            'Access is not case specific
            If InStr(5, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "DESC", vbTextCompare) = 0 Or Me.lbxPartRouteAssignmentAdd.Recordset.Sort = "" Then
                Me.lbxPartRouteAssignmentAdd.Recordset.Sort = "Description DESC"
            Else
                Me.lbxPartRouteAssignmentAdd.Recordset.Sort = "Description"
            End If

        Case Else
            Exit Sub
    End Select
    
'''    Me.lbxPartRouteAssignmentAdd.Requery
'''    Set rstemp = rstemp.OpenRecordset
    Set Me.lbxPartRouteAssignmentAdd.Recordset = rstemp
    Me.lbxPartRouteAssignmentAdd.Requery
    Me.Refresh
    
End Sub

It goes through the motions but the records in the listbox are not getting sorted. Can anybody see my mistake?

Thanks for looking.

Joel
 
Sort by date Sort by votes
I'm confused. A list box typically has a Row Source property. The Row Source is typically a SQL statement. Your long code doesn't seem to mention either the Row Source or a SQL statement.

Duane
Hook'D on Access
MS Access MVP
 
Upvote 0 Downvote
Hi dhookom, The Row Source is included in the design with no WHERE clause and is set at form load to be:
Code: 
strSQL = "SELECT tblPartInfo.PlantCode, tblPartInfo.ModelYear, tblPartInfo.PartNumber, tblPartInfo.Description," _
                    & " tblPartRouteAssignment.Route, tblPartStation.Station, tblPartStation.StationBld_Rate, tblPartInfo.Bld_Rate," _
                    & " tblPartInfo.DOCK, tblPartInfo.PackDensity, tblPartInfo.Container, tblPartInfo.Length, tblPartInfo.Width, tblPartInfo.Height" _
                    & " FROM (tblPartInfo LEFT JOIN tblPartStation ON (tblPartInfo.PlantCode = tblPartStation.PlantCode)" _
                    & " AND (tblPartInfo.PartNumber = tblPartStation.PartNumber) AND (tblPartInfo.ModelYear = tblPartStation.ModelYear))" _
                    & " LEFT JOIN tblPartRouteAssignment ON (tblPartStation.PlantCode = tblPartRouteAssignment.PlantCode)" _
                    & " AND (tblPartStation.PartNumber = tblPartRouteAssignment.PartNumber) AND (tblPartStation.Station = tblPartRouteAssignment.Station)" _
                    & " AND (tblPartStation.ModelYear = tblPartRouteAssignment.ModelYear)" _
                    & " WHERE (((tblPartInfo.PlantCode)= " & "'" & PubstrPlantCode & "')" _
                    & " AND ((tblPartInfo.ModelYear)= " & "'" & PubstrModelYear & "'))" _
                    & " ORDER BY tblPartInfo.Description, tblPartInfo.DOCK;"

I have been thinking of just rebuilding the whole SQL depending on the desired sorting or filtering, especially because I can't get this to work. It was much easier when the listbox wasn't involved.
This worked when it was on a continuous form:
Code: 
Private Sub SortDetail(SortField)

    'from the label click events of DOCK, Storage
    'keep sort order from the Description and or the Part Number field
'    dim
    Dim strNewSortOrder As String
    
    Select Case SortField
        Case "lblPartNumber"
            If InStr(1, Me.OrderBy, "DESC", vbTextCompare) = 0 Or Me.OrderBy = "" Then
                Me.OrderBy = "PartNumber DESC"
            Else
                Me.OrderBy = "PartNumber"
            End If
            
        Case "lblRoute"
            If Me.OrderBy = "" Then
                Me.OrderBy = "Route"
                Me.OrderByOn = True
                Exit Sub
            End If
            
            If InStr(1, Me.OrderBy, "Description", vbTextCompare) <> 0 Then
                If InStr(1, Me.OrderBy, "Route", vbTextCompare) <> 0 Then
                    If InStr(1, Me.OrderBy, "Description DESC", vbTextCompare) <> 0 Or InStr(1, Me.OrderBy, "[Description] DESC", vbTextCompare) <> 0 Then
                        If InStr(1, Me.OrderBy, "Route DESC", vbTextCompare) <> 0 Or InStr(1, Me.OrderBy, "[Route] DESC", vbTextCompare) <> 0 Then
                            Me.OrderBy = "[Route] ASC, [Description] DESC"
                        ElseIf InStr(1, Me.OrderBy, "Route", vbTextCompare) <> 0 Or InStr(1, Me.OrderBy, "[Route] ASC", vbTextCompare) <> 0 Then
                            Me.OrderBy = "[Route] DESC, [Description] DESC"
                        End If
                    ElseIf InStr(1, Me.OrderBy, "Description", vbTextCompare) <> 0 Or InStr(1, Me.OrderBy, "[Description] ASC", vbTextCompare) <> 0 Then
                        If InStr(1, Me.OrderBy, "Route DESC", vbTextCompare) <> 0 Or InStr(1, Me.OrderBy, "[Route] DESC", vbTextCompare) <> 0 Then
                            Me.OrderBy = "[Route] ASC, [Description] ASC"
                        ElseIf InStr(1, Me.OrderBy, "Route", vbTextCompare) <> 0 Or InStr(1, Me.OrderBy, "[Route] ASC", vbTextCompare) <> 0 Then
                            Me.OrderBy = "[Route] DESC, [Description] ASC"
                        End If
                    End If
                Else
                    If InStr(1, Me.OrderBy, "Description DESC", vbTextCompare) <> 0 Or InStr(1, Me.OrderBy, "[Description] DESC", vbTextCompare) <> 0 Then
                        Me.OrderBy = "[Route] ASC, [Description] DESC"
                    Else
                        Me.OrderBy = "[Route] ASC, [Description] ASC"
                    End If
                End If
            ElseIf InStr(1, Me.OrderBy, "Route", vbTextCompare) <> 0 Or InStr(1, Me.OrderBy, "[Route] ASC", vbTextCompare) <> 0 Then
                If InStr(1, Me.OrderBy, "Route DESC", vbTextCompare) <> 0 Or InStr(1, Me.OrderBy, "[Route] DESC", vbTextCompare) <> 0 Then
                    Me.OrderBy = "[Route] ASC"
                Else
                    Me.OrderBy = "[Route] DESC"
                End If
            End If
     
        Case "lblStation"
            If Me.OrderBy = "" Then
                Me.OrderBy = "Station"
                Me.OrderByOn = True
                Exit Sub
            End If
            
            If InStr(1, Me.OrderBy, "Description", vbTextCompare) <> 0 Then
                If InStr(1, Me.OrderBy, "Station", vbTextCompare) <> 0 Then
                    If InStr(1, Me.OrderBy, "Description DESC", vbTextCompare) <> 0 Or InStr(1, Me.OrderBy, "[Description] DESC", vbTextCompare) <> 0 Then
                        If InStr(1, Me.OrderBy, "Station DESC", vbTextCompare) <> 0 Or InStr(1, Me.OrderBy, "[Station] DESC", vbTextCompare) <> 0 Then
                            Me.OrderBy = "[Station] ASC, [Description] DESC"
                        ElseIf InStr(1, Me.OrderBy, "Station", vbTextCompare) <> 0 Or InStr(1, Me.OrderBy, "[Station] ASC", vbTextCompare) <> 0 Then
                            Me.OrderBy = "[Station] DESC, [Description] DESC"
                        End If
                    ElseIf InStr(1, Me.OrderBy, "Description", vbTextCompare) <> 0 Or InStr(1, Me.OrderBy, "[Description] ASC", vbTextCompare) <> 0 Then
                        If InStr(1, Me.OrderBy, "Station DESC", vbTextCompare) <> 0 Or InStr(1, Me.OrderBy, "[Station] DESC", vbTextCompare) <> 0 Then
                            Me.OrderBy = "[Station] ASC, [Description] ASC"
                        ElseIf InStr(1, Me.OrderBy, "Station", vbTextCompare) <> 0 Or InStr(1, Me.OrderBy, "[Station] ASC", vbTextCompare) <> 0 Then
                            Me.OrderBy = "[Station] DESC, [Description] ASC"
                        End If
                    End If
                Else
                    If InStr(1, Me.OrderBy, "Description DESC", vbTextCompare) <> 0 Or InStr(1, Me.OrderBy, "[Description] DESC", vbTextCompare) <> 0 Then
                        Me.OrderBy = "[Station] ASC, [Description] DESC"
                    Else
                        Me.OrderBy = "[Station] ASC, [Description] ASC"
                    End If
                End If
            ElseIf InStr(1, Me.OrderBy, "Station", vbTextCompare) <> 0 Or InStr(1, Me.OrderBy, "[Station] ASC", vbTextCompare) <> 0 Then
                If InStr(1, Me.OrderBy, "Station DESC", vbTextCompare) <> 0 Or InStr(1, Me.OrderBy, "[Station] DESC", vbTextCompare) <> 0 Then
                    Me.OrderBy = "[Station] ASC"
                Else
                    Me.OrderBy = "[Station] DESC"
                End If
            End If
        
        Case "lblDescription"
            'start Instr at position 5 because field name contains string we're seeking
            'Access is not case specific
            If InStr(5, Me.OrderBy, "DESC", vbTextCompare) = 0 Or Me.OrderBy = "" Then
                Me.OrderBy = "Description DESC"
            Else
                Me.OrderBy = "Description"
            End If

        Case Else
            Exit Sub
    End Select
    
    Me.OrderByOn = True
    
End Sub

But we want multi-select so I am trying to change it to a listbox.

I was hoping the .sort would be similar to the .orderby but....

Joel
 
Upvote 0 Downvote
Why not just adjust the code that is " set at form load " to modify the row source sql?

I think you are working way too hard when clearly you have the understanding and most of the code to modify the row source.

Duane
Hook'D on Access
MS Access MVP
 
Upvote 0 Downvote
I've been tossing that around. But then how do I tell what the current Sort or Where criteria is? I am setting it up to retain the sort on the Description field if other field sorting is requested (lbl click event). So say a user sorts Description DESC and then sorts on Route, then the sort would be Route Acs, Description Desc. Click Route again and it would be Route Desc and Description Desc. Unless the user goes back to sort on the Description the sort order for Description remains as it was. Only clicking on the Description lbl will change the Description order and clicking on the Part Number will ignore the Description field.

Joel
 
Upvote 0 Downvote
The current sort is always available in the Row Source property. If you want to save it some place else, you could use the tag property of the list box.

I'm not about to attempt to decipher your code to determine your specifications for the sorting.

Duane
Hook'D on Access
MS Access MVP
 
Upvote 0 Downvote
OK, I've made some changes now the listbox is blank after the requery but the rowsource is correct. Please look at only the first case, the rest are waiting for me to figure this out:

Code: 
Private Sub SortDetail(SortField)

    'from the label click events of Part Number, Description, Route and Station
    'keep sort order from the Description and or the Part Number field
    Dim strSQL As String
    Dim strSQLSort As String
    Dim intStart As Integer
    
    strSQL = Me.lbxPartRouteAssignmentAdd.RowSource
    
    intStart = InStr(1, strSQL, "ORDER BY") + 9
    
    Select Case SortField
        Case "lblPartNumber"
            If InStr(intStart, strSQL, "PartNumber DESC", vbTextCompare) = 0 Or intStart = 0 Then
                strSQLSort = "PartNumber DESC"
            Else
                strSQLSort = "PartNumber"
            End If
            
        Case "lblRoute"
            If Me.lbxPartRouteAssignmentAdd.Recordset.Sort = "" Then
                Me.lbxPartRouteAssignmentAdd.Recordset.Sort = "Route"
'''                Me.lbxPartRouteAssignmentAdd.Recordset.sortByOn = True
                Exit Sub
            End If
            
            If InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "Description", vbTextCompare) <> 0 Then
                If InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "Route", vbTextCompare) <> 0 Then
                    If InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "Description DESC", vbTextCompare) <> 0 Or InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "[Description] DESC", vbTextCompare) <> 0 Then
                        If InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "Route DESC", vbTextCompare) <> 0 Or InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "[Route] DESC", vbTextCompare) <> 0 Then
                            Me.lbxPartRouteAssignmentAdd.Recordset.Sort = "[Route] ASC, [Description] DESC"
                        ElseIf InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "Route", vbTextCompare) <> 0 Or InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "[Route] ASC", vbTextCompare) <> 0 Then
                            Me.lbxPartRouteAssignmentAdd.Recordset.Sort = "[Route] DESC, [Description] DESC"
                        End If
                    ElseIf InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "Description", vbTextCompare) <> 0 Or InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "[Description] ASC", vbTextCompare) <> 0 Then
                        If InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "Route DESC", vbTextCompare) <> 0 Or InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "[Route] DESC", vbTextCompare) <> 0 Then
                            Me.lbxPartRouteAssignmentAdd.Recordset.Sort = "[Route] ASC, [Description] ASC"
                        ElseIf InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "Route", vbTextCompare) <> 0 Or InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "[Route] ASC", vbTextCompare) <> 0 Then
                            Me.lbxPartRouteAssignmentAdd.Recordset.Sort = "[Route] DESC, [Description] ASC"
                        End If
                    End If
                Else
                    If InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "Description DESC", vbTextCompare) <> 0 Or InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "[Description] DESC", vbTextCompare) <> 0 Then
                        Me.lbxPartRouteAssignmentAdd.Recordset.Sort = "[Route] ASC, [Description] DESC"
                    Else
                        Me.lbxPartRouteAssignmentAdd.Recordset.Sort = "[Route] ASC, [Description] ASC"
                    End If
                End If
            ElseIf InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "Route", vbTextCompare) <> 0 Or InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "[Route] ASC", vbTextCompare) <> 0 Then
                If InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "Route DESC", vbTextCompare) <> 0 Or InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "[Route] DESC", vbTextCompare) <> 0 Then
                    Me.lbxPartRouteAssignmentAdd.Recordset.Sort = "[Route] ASC"
                Else
                    Me.lbxPartRouteAssignmentAdd.Recordset.Sort = "[Route] DESC"
                End If
            End If
     
        Case "lblStation"
            If Me.lbxPartRouteAssignmentAdd.Recordset.Sort = "" Then
                Me.lbxPartRouteAssignmentAdd.Recordset.Sort = "Station"
'''                Me.lbxPartRouteAssignmentAdd.Recordset.SortOn = True
                Exit Sub
            End If
            
            If InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "Description", vbTextCompare) <> 0 Then
                If InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "Station", vbTextCompare) <> 0 Then
                    If InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "Description DESC", vbTextCompare) <> 0 Or InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "[Description] DESC", vbTextCompare) <> 0 Then
                        If InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "Station DESC", vbTextCompare) <> 0 Or InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "[Station] DESC", vbTextCompare) <> 0 Then
                            Me.lbxPartRouteAssignmentAdd.Recordset.Sort = "[Station] ASC, [Description] DESC"
                        ElseIf InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "Station", vbTextCompare) <> 0 Or InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "[Station] ASC", vbTextCompare) <> 0 Then
                            Me.lbxPartRouteAssignmentAdd.Recordset.Sort = "[Station] DESC, [Description] DESC"
                        End If
                    ElseIf InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "Description", vbTextCompare) <> 0 Or InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "[Description] ASC", vbTextCompare) <> 0 Then
                        If InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "Station DESC", vbTextCompare) <> 0 Or InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "[Station] DESC", vbTextCompare) <> 0 Then
                            Me.lbxPartRouteAssignmentAdd.Recordset.Sort = "[Station] ASC, [Description] ASC"
                        ElseIf InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "Station", vbTextCompare) <> 0 Or InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "[Station] ASC", vbTextCompare) <> 0 Then
                            Me.lbxPartRouteAssignmentAdd.Recordset.Sort = "[Station] DESC, [Description] ASC"
                        End If
                    End If
                Else
                    If InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "Description DESC", vbTextCompare) <> 0 Or InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "[Description] DESC", vbTextCompare) <> 0 Then
                        Me.lbxPartRouteAssignmentAdd.Recordset.Sort = "[Station] ASC, [Description] DESC"
                    Else
                        Me.lbxPartRouteAssignmentAdd.Recordset.Sort = "[Station] ASC, [Description] ASC"
                    End If
                End If
            ElseIf InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "Station", vbTextCompare) <> 0 Or InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "[Station] ASC", vbTextCompare) <> 0 Then
                If InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "Station DESC", vbTextCompare) <> 0 Or InStr(1, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "[Station] DESC", vbTextCompare) <> 0 Then
                    Me.lbxPartRouteAssignmentAdd.Recordset.Sort = "[Station] ASC"
                Else
                    Me.lbxPartRouteAssignmentAdd.Recordset.Sort = "[Station] DESC"
                End If
            End If
        
        Case "lblDescription"
            'start Instr at position 5 because field name contains string we're seeking
            'Access is not case specific
            If InStr(5, Me.lbxPartRouteAssignmentAdd.Recordset.Sort, "DESC", vbTextCompare) = 0 Or Me.lbxPartRouteAssignmentAdd.Recordset.Sort = "" Then
                Me.lbxPartRouteAssignmentAdd.Recordset.Sort = "Description DESC"
            Else
                Me.lbxPartRouteAssignmentAdd.Recordset.Sort = "Description"
            End If

        Case Else
            Exit Sub
    End Select
    
    strSQL = "SELECT tblPartInfo.PlantCode, tblPartInfo.ModelYear, tblPartInfo.PartNumber, tblPartInfo.Description," _
                    & " tblPartRouteAssignment.Route, tblPartStation.Station, tblPartStation.StationBld_Rate, tblPartInfo.Bld_Rate," _
                    & " tblPartInfo.DOCK, tblPartInfo.PackDensity, tblPartInfo.Container, tblPartInfo.Length, tblPartInfo.Width, tblPartInfo.Height" _
                    & " FROM (tblPartInfo LEFT JOIN tblPartStation ON (tblPartInfo.PlantCode = tblPartStation.PlantCode)" _
                    & " AND (tblPartInfo.PartNumber = tblPartStation.PartNumber) AND (tblPartInfo.ModelYear = tblPartStation.ModelYear))" _
                    & " LEFT JOIN tblPartRouteAssignment ON (tblPartStation.PlantCode = tblPartRouteAssignment.PlantCode)" _
                    & " AND (tblPartStation.PartNumber = tblPartRouteAssignment.PartNumber) AND (tblPartStation.Station = tblPartRouteAssignment.Station)" _
                    & " AND (tblPartStation.ModelYear = tblPartRouteAssignment.ModelYear)" _
                    & " WHERE (((tblPartInfo.PlantCode)= " & "'" & PubstrPlantCode & "')" _
                    & " AND ((tblPartInfo.ModelYear)= " & "'" & PubstrModelYear & "'))" _
                    & " ORDER BY "
                    
    strSQL = strSQL & strSQLSort & ";"

'''    Set rstemp = rstemp.OpenRecordset
    Me.lbxPartRouteAssignmentAdd.RowSource = strSQL
    Me.lbxPartRouteAssignmentAdd.Requery
    Debug.Print Me.lbxPartRouteAssignmentAdd.RowSource
    Me.Refresh
    
End Sub

I am getting no errors but after the Sub the listbox is empty. Here is the RowSource's from the debug.print

Code: 
SELECT tblPartInfo.PlantCode, tblPartInfo.ModelYear, tblPartInfo.PartNumber, tblPartInfo.Description, tblPartRouteAssignment.Route, tblPartStation.Station, tblPartStation.StationBld_Rate, tblPartInfo.Bld_Rate, tblPartInfo.DOCK, tblPartInfo.PackDensity, tblPartInfo.Container, tblPartInfo.Length, tblPartInfo.Width, tblPartInfo.Height FROM (tblPartInfo LEFT JOIN tblPartStation ON (tblPartInfo.PlantCode = tblPartStation.PlantCode) AND (tblPartInfo.PartNumber = tblPartStation.PartNumber) AND (tblPartInfo.ModelYear = tblPartStation.ModelYear)) LEFT JOIN tblPartRouteAssignment ON (tblPartStation.PlantCode = tblPartRouteAssignment.PlantCode) AND (tblPartStation.PartNumber = tblPartRouteAssignment.PartNumber) AND (tblPartStation.Station = tblPartRouteAssignment.Station) AND (tblPartStation.ModelYear = tblPartRouteAssignment.ModelYear) WHERE (((tblPartInfo.PlantCode)= '04025') AND ((tblPartInfo.ModelYear)= '2011')) ORDER BY PartNumber DESC;
SELECT tblPartInfo.PlantCode, tblPartInfo.ModelYear, tblPartInfo.PartNumber, tblPartInfo.Description, tblPartRouteAssignment.Route, tblPartStation.Station, tblPartStation.StationBld_Rate, tblPartInfo.Bld_Rate, tblPartInfo.DOCK, tblPartInfo.PackDensity, tblPartInfo.Container, tblPartInfo.Length, tblPartInfo.Width, tblPartInfo.Height FROM (tblPartInfo LEFT JOIN tblPartStation ON (tblPartInfo.PlantCode = tblPartStation.PlantCode) AND (tblPartInfo.PartNumber = tblPartStation.PartNumber) AND (tblPartInfo.ModelYear = tblPartStation.ModelYear)) LEFT JOIN tblPartRouteAssignment ON (tblPartStation.PlantCode = tblPartRouteAssignment.PlantCode) AND (tblPartStation.PartNumber = tblPartRouteAssignment.PartNumber) AND (tblPartStation.Station = tblPartRouteAssignment.Station) AND (tblPartStation.ModelYear = tblPartRouteAssignment.ModelYear) WHERE (((tblPartInfo.PlantCode)= '04025') AND ((tblPartInfo.ModelYear)= '2011')) ORDER BY PartNumber;

Any ideas?

Joel
 
Upvote 0 Downvote
Figured it out. When I cut and pasted the rowsource from the debug.print and put it into a query it gave me an error. While it never gave me a problem in the Continuous form the listbox new query wants a table specified for the filter: I changed the first case statement to :
Code: 
Case "lblPartNumber"
            If InStr(intStart, strSQL, "PartNumber DESC", vbTextCompare) = 0 Or intStart = 0 Then
                strSQLSort = "tblPartInfo.PartNumber DESC"
            Else
                strSQLSort = "tblPartInfo.PartNumber"
            End If

And it works. Thanks for looking.

Joel
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Moss100
  • Locked
  • Question
Concatenation problem 3
Replies
5
Views
866
Moss100
Moss100
Moss100
  • Locked
  • Question
Filter vba problem
Replies
1
Views
486
Andrzejek
Andrzejek
Moss100
  • Locked
  • Question
Access send email attachments - problem when null or empty
Replies
7
Views
2K
Andrzejek
Andrzejek
Moss100
  • Locked
  • Question
Adodo problem
Replies
7
Views
258
dhookom
dhookom
Dan Stoica
  • Locked
  • Question
Problems with Foxpro dsnless linked tables
Replies
0
Views
329
Dan Stoica
Dan Stoica

Part and Inventory Search

Sponsor

Back
Top