vba issues with requery in subform with filters

[Susy]

Hi again,



after andy49 helped me with the date format and sql issue, now I have been fighting with another problem....

when I set one filter from the combo boxes (see image), I am not able to requery the other filters so they show in their lists the only options available.



For example, if I were to select the Status "confirmed", then only name available under the filter "Sales Organizer" should be Mtendere Chakuamba. Right now I get all 9 different names saved in that field.




This is what my form, subform (from the table report) looks like...


and the code...

Private Sub cboStatus_AfterUpdate()

    Call SearchCriteria("[ReportID]")

End Sub


Private Sub cboType_AfterUpdate()
    
    Call SearchCriteria("[ReportID]")

End Sub


Function SearchCriteria(myString As String)

Dim strSOReportNo As String, strStaName As String, strTypeID As String, strSalesOrg As String
Dim strActDate As String, strActfDate As String, strActtDate As String, strActftDate As String
Dim task As String, strCriteria As String


    If IsNull(Me.cboSOReportNo) Then
        strSOReportNo = "[SOReportNo] like '*'"
    Else
        strSOReportNo = "[SOReportNo] = '" & Me.cboSOReportNo & "'"
    End If

    If IsNull(Me.cboStatus) Then
        strStaName = "[StatusName] like '*'"
    Else
        strStaName = "[StatusName] = '" & Me.cboStatus & "'"
    End If

    If IsNull(Me.cboType) Then
        strTypeID = "[TypeID] like '*'"
    Else
        strTypeID = "[TypeID] = " & Me.cboType
    End If
 
    If IsNull(Me.cboActionDate) Then
        strActDate = "[ActionDate] like '*'"
    Else
        strActDate = "[ActionDate] = #" & Format(Me.cboActionDate, "mm\/dd\/yyyy") & "#"
    End If
    
    If IsNull(Me.cboActionfDate) Or IsNull(Me.cboActiontDate) Then
        'strActfDate = "[ActionDate] like '*'"
        strActfDate = "[ActionDate] = #" & Format(Me.cboActiontDate, "mm\/dd\/yyyy") & "#"
        strActtDate = "[ActionDate] like '*'"
        strActftDate = "[ActionDate] like '*'"
    Else
        strActftDate = "[ActionDate] BETWEEN #" & Format(Me.cboActionfDate, "mm\/dd\/yyyy") & "# AND #" & Format(Me.cboActiontDate, "mm\/dd\/yyyy") & "#"
    End If
    
    
    If IsNull(Me.cboSalesOrg) Then
        strSalesOrg = "[SOrgID] like '*'"
    Else
        strSalesOrg = "[SOrgID] = " & Me.cboSalesOrg
    End If
    
    'myString = "[ReportID]"
      
    'strActDate & " AND " &
    
    strCriteria = strSOReportNo & " AND " & strStaName & " AND " & strTypeID & " AND " & _
                 strActDate & " AND " & strSalesOrg & " AND " & strActftDate
        task = "SELECT * FROM tblReport WHERE " & strCriteria & " ORDER BY " & myString
        Me.subfrmReport.Form.RecordSource = task
        Me.subfrmReport.Form.Requery

End Function

Do I need to create a query after each afterupdate from each filter?

I really have no idea what to do next...

If someone can help me, that will be great.

P.S.: The code was taken from austin72406, https://www.youtube.com/watch?v=choPri7y_o4

Unfortunately the filters are not updated in his video...


Ideas?

[ssanfu]

For example, if I were to select the Status "confirmed", then only name available under the filter "Sales Organizer" should be Mtendere Chakuamba. Right now I get all 9 different names saved in that field.

This sounds like you want to use what is termed "Cascading combo boxes".


Can you post the dB?

[Susy]

Have you seen the code?

Do I need to put the SQL statement in each rowsource of the filters after update?

[June7]

The posted code is building an SQL statement string that is applied to a form RecordSource property which I am sure you will find useful but the requirement you describe has nothing to do with the posted code and vice versa. As ssanfu said, you need to set up dependent (cascading) comboboxes.

Cascading comboboxes is a common topic. Search the forum or Google.

[Susy]

I see, but I have been trying to do it with multiple filters but it is not working...

I just attached a sample database with the same logic.

The name of the form is "Customer Search by Combobox Part 2"

Again, code was taken from https://www.youtube.com/watch?v=choPri7y_o4.

Just let me know if you can help.


Regards

[June7]

What do you mean by 'trying to do it with multiple filters' and 'it is not working'? What is 'it' - filtering the form recordset? What happens - error message, wrong results, nothing?

The requirement for filtering the form and requirement for cascading comboboxes are separate issues.

So exactly what is the issue? Is the form not filtering to the desired record(s)?

If you want a combobox list to be restricted by selection in another control, that is cascading (dependent) comboboxes and the posted code has no relationship to that requirement. Have you done any research on the topic?


Don't see any attached sample database.

My slow internet often doesn't like YouTube videos.

[Susy]

I already uploaded the sample database.

The subform is updating perfectly.

I just want the comboboxes to update their content depending on the other comboboxes...

[Susy]

No Idea, I have been doing queries, modifying the me.ctrlname.rowsource properties, but it is not working.

[Homegrownandy]

you need to attach the sample DB to a post in advanced options.

[Susy]

Here the file again. It was included in Post # 5.

[June7]

As already stated, you need to build cascading comboboxes. Disregard the sample code posted as it has no bearing on this requirement. Have you done ANY research on cascading comboboxes? The posted database has no code related to this requirement and is therefore useless in this discussion. Do the research and attempt design then when you encounter specific issue post a question.

[Susy]

to June7 :Why do you even bother to answer... it is not as easy as "cascading comboboxes". If you cannot do anything with the sample I provided, then what can you do, besides writing non constructive comments.

I know it is complicated, three or four comboboxes simultaneously and with no order... But someone out there might really know how to or have done something similar before.
My problem has been, until now, how to fill the .RowSource Property "After update" of each of the comboboxes after each combobox has been activated.

[June7]

Your narrative describes a need to "show in their lists the only options available" - this indicates cascading comboboxes are desired. We know how, we just don't want to do the work for you. We have provided guidance to get you on that path. Now we need to see some code with issues that need to be analyzed.

If cascading comboboxes is not what you need, then exactly what is the issue? If the records are not filtered as you desire, then exactly how do you want filter criteria applied? Why should order be a factor? Select parameters in any combination of comboboxes and code builds the filter string.

[Susy]

The comboboxes have to function in several directions... cascading, as the term implies, one after the other.

One user can star to filter
1. by state, and then by customer or gender,
2. by gender, and then by customer and then by state... and so on.

I uploaded a sample with only three comboboxes, but my project has more than 5 comboboxes... It is irritaring to have all the elements in a combobox even if after the first filter they were filtered out.

[June7]

Multi-directional cascading comboboxes is not something I have ever seen, probably because it is not practical. I have seen attempts at bi-directional dependency between 2 comboboxes and that failed miserably. Seems to me a defined hierarchy is necessary by nature of the concept.