Group Rowsource values for a query

[mafhobb]

Hi

I have been asked to modify a form that uses the following query to display results:

        Me.lstsearch.RowSource = "Select [ContactID], [maxofsubcallid], [CallID], [SubCallDate], [SKU], [WhoPickedUp], [IssueType], [StatusAfterCall], [ResolutionDetails], [CustomerID] " & _
                        "From [Search on last subcallID] " & _
                        "Where [StatusAfterCall] like '*" & Me.cmbSearch & "*' " & "Order By [StatusAfterCall], [SubCallDate]; "

The rowsource for cmbSearch is: SELECT DISTINCT SubCalls.StatusAfterCall

This has worked well until now but I have been asked to modify the code to do the following: "StatusAfterCall" can have about 20 different values but all of them can be grouped in two major categories: "Finished" and "In Process". What I need to do is to group the values into "Finished" and "In process" (through a table or VBA) and then change "cmbSearch" rowsource's so it only shows those two groups. The user will then only be able to select "Finished" or "In process" in the combobox and the results displayed will show all the items of that category.

What would be a good strategy to do this?

Thanks

mafhobb

 

[Cronk]

I'd likey use an option box with 2 options (In Progress and Finished) with the default being the more likely choice, with the option changing the row source of your combo.

 

[mafhobb]

Ok, so something like this:

If chkboxInProcess=1 then
.....
"Where [StatusAfterCall]="In Process 1" OR [StatusAfterCall]="In Process 2" OR [Status After Call]="In Process 3" OR.......& "Order By [StatusAfterCall], [SubCallDate]; "
.....
ElseIf chkboxFinished=1 then
.....
"Where [StatusAfterCall]="Finished 1" OR [StatusAfterCall]="Finished 2" OR [StatusAfterCall]="Finished 3" OR.......& "Order By [StatusAfterCall], [SubCallDate]; "
....
End If

I am not sure what you mean by "changing the Rowsource of your combo". What would you change? What I need is not the individual "Finished" or "In Process" distinct values in the combobox....what I need is something that allows me to chose "Finished" or "In process" which then returns all the [StatusAfterCall] that match that category. Of course, I would separate the [StatusAfterCall] values as needed to represent the "Finished" and "In process" categories.

Does this makes sense or am I totally missing it?

mafhobb

 

[mafhobb]

This is what did it:

    If Me.cmbSearch.Value <> "" Or Not IsNull(Me.cmbSearch) Then
        If fraResOption = 1 Then
            Me.lstsearch.RowSource = "Select [ContactID], [maxofsubcallid], [CallID], [SubCallDate], [SKU], [WhoPickedUp], [IssueType], [StatusAfterCall], [ResolutionDetails], [CustomerID] " & _
                        "From [Search on last subcallID] " & _
                        "Where [StatusAfterCall]=[cmbsearch] Order By [StatusAfterCall], [SubCallDate]; "
            cmbTimeFrame.Value = ""
            Me.lstsearch.Requery
            Me.txtcount = Me.lstsearch.ListCount
            Exit Sub
        ElseIf fraResOption = 2 Then
            Me.lstsearch.RowSource = "Select [ContactID], [maxofsubcallid], [CallID], [SubCallDate], [SKU], [WhoPickedUp], [IssueType], [StatusAfterCall], [ResolutionDetails], [CustomerID] " & _
                        "From [Search on last subcallID] " & _
                        "Where [StatusAfterCall]=[cmbsearch] And ([ResolutionDetails] = 'Allgemeine Reklamation in Bearbeitung' or [ResolutionDetails] = 'Beschädigter Artikel in Bearbeitung' or [ResolutionDetails] = 'Bestellung der Artikel in Bearbeitung' or [ResolutionDetails] = 'Empfehlung für Kunden erstellen' or [ResolutionDetails] = 'Fehlende Teile in Bearbeitung' or [ResolutionDetails] = 'Gutschrift/Rücksendung in Bearbeitung' or [ResolutionDetails] = 'Rücksednung/Reparatur in Bearbeitung' or [ResolutionDetails] = 'Rücksendung/Reparatur in Bearbeitung' or [ResolutionDetails] = 'Software-Problem in Bearbeitung') Order By [StatusAfterCall], [SubCallDate]; "
            cmbTimeFrame.Value = ""
            Me.lstsearch.Requery
            Me.txtcount = Me.lstsearch.ListCount
            Exit Sub
        ElseIf fraResOption = 3 Then
            Me.lstsearch.RowSource = "Select [ContactID], [maxofsubcallid], [CallID], [SubCallDate], [SKU], [WhoPickedUp], [IssueType], [StatusAfterCall], [ResolutionDetails], [CustomerID] " & _
                        "From [Search on last subcallID] " & _
                        "Where [StatusAfterCall]=[cmbSearch] And Not ([ResolutionDetails] = 'Allgemeine Reklamation in Bearbeitung' or [ResolutionDetails] = 'Beschädigter Artikel in Bearbeitung' or [ResolutionDetails] = 'Bestellung der Artikel in Bearbeitung' or [ResolutionDetails] = 'Empfehlung für Kunden erstellen' or [ResolutionDetails] = 'Fehlende Teile in Bearbeitung' or [ResolutionDetails] = 'Gutschrift/Rücksendung in Bearbeitung' or [ResolutionDetails] = 'Rücksednung/Reparatur in Bearbeitung' or [ResolutionDetails] = 'Rücksendung/Reparatur in Bearbeitung' or [ResolutionDetails] = 'Software-Problem in Bearbeitung') Order By [StatusAfterCall], [SubCallDate]; "
            cmbTimeFrame.Value = ""
            Me.lstsearch.Requery
            Me.txtcount = Me.lstsearch.ListCount
            Exit Sub
        End If
    End If

Option 1 is the case where no Resolution category is selected, so the results include all "finished" and "in process" categories.

Option 2 is the case for "in Process"

Option 3 is the case for "Finished"

mafhobb

 

[Cronk]

I'm glad you got the result you wanted and no doubt satisfying that you got there mainly yourself after my steer.

The code would ideally go in the after update event of the option box. There is probably a default or at least the most common option that you could set by setting the option value to that in the form's load event and make a call to the option after update sub to pre-load the combo.