filter linked subform using combo box

[siamandm]

Hi

i have a main form which contains and ID and unbound a combo box which has a list for all months name called cboMonthes
and i have a sub form which shows records for the current ID, one of the field is Date

now i want to filter the sub form for the current ID based on the month selected in the combo box
how i do this using vba

regards

[HansV]

How does the combo box list the months? As January, February, ...? Or as January 2017, February 2017, ...? Or differently - if so, how?

[siamandm]

thanks Hans
i have a table called tblMonths used as rowsource for the combo box
the table is like below:
ID MonthName
1 January
2 February
.
.
.

im trying to use after update event for the combo box as blow :

Code: Select all

Private Sub cboMonthes_AfterUpdate()
Dim SQL As String

SQL = "SELECT tblDist_Items.ItemID, tblDist_Items.ItemName_ID, tblDist_Items.ItemQty, tblDist_Items.BenefID_FK, tblItemCat.ItemCategory, tblDist_Items.DateReceived, tblDist_Items.AssessedDate, tblDist_Items.Status " _
& " FROM (tblItemCat RIGHT JOIN tblItemList ON tblItemCat.ItemCatID = tblItemList.ItemCat_ID_FK) RIGHT JOIN tblDist_Items " _
& " ON tblItemList.ItemNameID = tblDist_Items.ItemName_ID " _
& " Where BenefID =tblDist_Items.BenefID_FK and tblDist_Items.[AssessedDate]=" & Me.txtMyDate & ""

Me.Dist_items.Form.RecordSource = SQL
Me.Dist_items.Form.Requery


End Sub

i have used txtMydate just for test instead of the combo box

[HansV]

Assuming that you only want to look at months in the current year, change

... and tblDist_Items.[AssessedDate]=" & Me.txtMyDate & ""

to

... and Year(tblDist_Items.AssessedDate)=Year(Date()) AND Month(tblDist_Items.AssessedDate)=" & Me.cboMonthes

[siamandm]

yes , thats it ...working fine....
another question

i made a default value for the cboMonths = 5 ,

also i have added onload event to the main form me.subfrm.Form.requery in order to filter my data according to the combo box criteria but its not working ?!!
why is that ?


regards

[HansV]

You could call cboMonthes_AfterUpdate in the On Load event of the form.

[siamandm]

1- i have used me.Form.Requery inside the sub form on Load event and seems to be working ! , is this correct way ?
2- how you call an event from another place ( the way you suggested ) ?

Regards

[HansV]

1. If it works for you, that's fine.

2. For example like this (this is for the main form):

Code: Select all

Private Sub Form_Load()
    Call cboMonthes_AfterUpdate
End Sub

[siamandm]

thanks alot

[siamandm]

1. If it works for you, that's fine.

2. For example like this (this is for the main form):

Code: Select all

Private Sub Form_Load()
    Call cboMonthes_AfterUpdate
End Sub

1- im getting this error below :

Capture.PNG

2-
is this possible to call two events from two separate controls

for example i have a combo box and list box used to filter data ?

[HansV]

Yes, but please be more specific - do you want to filter the form using either the combo box or the list box, or using a combination of them? And do they filter on the same field, or on different fields?

[siamandm]

they are filtering different fields

what i need to do , i have a list box ( i want by default 2 or 3 items to be selected) and combo box ... on load i want to filter my sub folder based on both of them.
and later one after on load, when i change the selected item in the list or the combo box.

in this attached file i have form frmBenTest which i have the controls ,

NewDistDB-v031.zip

i tried some ways to apply this filter but i got many errors so i deleted the query ...

[HansV]

I assume that lstItm is the list box. If you want the user to be able to select more than one item, you must set the Multi Select property of the list box to Simple or Extended in the Other tab of the Property Sheet.

I see several combo boxes on frmBenTest. Which one should I look at?

[siamandm]

cboMonthes and lstitm ,
the subfrom is already filtered by the cboMonths... i want to add the list box selected items to the filter as well.

[HansV]

This is the central routine that is called from the On Load event of the form and the After Update events of the list box and combo box:

Code: Select all

Private Sub FilterSubForm()
    Dim SQL As String
    Dim itm As Variant
    Dim strWhere As String

    SQL = "SELECT tblDist_Items.ItemID, tblDist_Items.ItemName_ID, tblDist_Items.ItemQty, " & _
        "tblDist_Items.BenefID_FK, tblItemCat.ItemCategory, tblDist_Items.DateReceived, " & _
        "tblDist_Items.AssessedDate, tblDist_Items.Status " & _
        "FROM (tblItemCat RIGHT JOIN tblItemList ON tblItemCat.ItemCatID = tblItemList.ItemCat_ID_FK) " & _
        "RIGHT JOIN tblDist_Items ON tblItemList.ItemNameID = tblDist_Items.ItemName_ID " & _
        "WHERE BenefID_FK=tblDist_Items.BenefID_FK AND tblDist_Items.Status=1"

    If Not IsNull(Me.cboMonthes) Then
        SQL = SQL & " AND Year(tblDist_Items.AssessedDate)=Year(Date()) " & _
            "AND Month(tblDist_Items.AssessedDate)=" & Me.cboMonthes
    End If

    If Me.lstItm.ItemsSelected.Count > 0 Then
        For Each itm In Me.lstItm.ItemsSelected
            strWhere = strWhere & "," & Me.lstItm.ItemData(itm)
        Next itm
        SQL = SQL & " AND tblDist_Items.ItemName_ID in (" & Mid(strWhere, 2) & ")"
    End If

    Me.Dist_items.Form.RecordSource = SQL
End Sub

See the attached version.

NewDistDB-v031.zip

[siamandm]

perfect , that's what i needed
thanks alot
regards

[siamandm]

This is the central routine that is called from the On Load event of the form and the After Update events of the list box and combo box:

Code: Select all

Private Sub FilterSubForm()
    Dim SQL As String
    Dim itm As Variant
    Dim strWhere As String

    SQL = "SELECT tblDist_Items.ItemID, tblDist_Items.ItemName_ID, tblDist_Items.ItemQty, " & _
        "tblDist_Items.BenefID_FK, tblItemCat.ItemCategory, tblDist_Items.DateReceived, " & _
        "tblDist_Items.AssessedDate, tblDist_Items.Status " & _
        "FROM (tblItemCat RIGHT JOIN tblItemList ON tblItemCat.ItemCatID = tblItemList.ItemCat_ID_FK) " & _
        "RIGHT JOIN tblDist_Items ON tblItemList.ItemNameID = tblDist_Items.ItemName_ID " & _
        "WHERE BenefID_FK=tblDist_Items.BenefID_FK AND tblDist_Items.Status=1"

    If Not IsNull(Me.cboMonthes) Then
        SQL = SQL & " AND Year(tblDist_Items.AssessedDate)=Year(Date()) " & _
            "AND Month(tblDist_Items.AssessedDate)=" & Me.cboMonthes
    End If

    If Me.lstItm.ItemsSelected.Count > 0 Then
        For Each itm In Me.lstItm.ItemsSelected
            strWhere = strWhere & "," & Me.lstItm.ItemData(itm)
        Next itm
        SQL = SQL & " AND tblDist_Items.ItemName_ID in (" & Mid(strWhere, 2) & ")"
    End If

    Me.Dist_items.Form.RecordSource = SQL
End Sub

See the attached version.

NewDistDB-v031.zip

if i change the combo box to a text box date, when i change the date i want to re run this query again , what should i do please
i tried after update event and after change : using Me.subform.Form.Requery
didnt work !

please help.

regards

[HansV]

Do you want to enter complete date in the text box?

[siamandm]

Do you want to enter complete date in the text box?

yes please like 24/5/2017


regards

[HansV]

Replace the lines

Code: Select all

    If Not IsNull(Me.cboMonthes) Then
        SQL = SQL & " AND Year(tblDist_Items.AssessedDate)=Year(Date()) " & _
            "AND Month(tblDist_Items.AssessedDate)=" & Me.cboMonthes
    End If

with (using txtDate as the name of the text box):

Code: Select all

    If Not IsNull(Me.txtDate) Then
        SQL = SQL & " AND tblDist_Items.AssessedDate=#" & Format(Me.txtDate, "yyyy-mm-dd") & "#"
    End If