filter linked subform using combo box
-
- BronzeLounger
- Posts: 1259
- Joined: 01 May 2016, 09:58
filter linked subform using combo box
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
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
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: filter linked subform using combo box
How does the combo box list the months? As January, February, ...? Or as January 2017, February 2017, ...? Or differently - if so, how?
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1259
- Joined: 01 May 2016, 09:58
Re: filter linked subform using combo box
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 :
i have used txtMydate just for test instead of the combo box
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
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: filter linked subform using combo box
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
... and tblDist_Items.[AssessedDate]=" & Me.txtMyDate & ""
to
... and Year(tblDist_Items.AssessedDate)=Year(Date()) AND Month(tblDist_Items.AssessedDate)=" & Me.cboMonthes
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1259
- Joined: 01 May 2016, 09:58
Re: filter linked subform using combo box
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
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
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: filter linked subform using combo box
You could call cboMonthes_AfterUpdate in the On Load event of the form.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1259
- Joined: 01 May 2016, 09:58
Re: filter linked subform using combo box
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
2- how you call an event from another place ( the way you suggested ) ?
Regards
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: filter linked subform using combo box
1. If it works for you, that's fine.
2. For example like this (this is for the main form):
2. For example like this (this is for the main form):
Code: Select all
Private Sub Form_Load()
Call cboMonthes_AfterUpdate
End Sub
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1259
- Joined: 01 May 2016, 09:58
-
- BronzeLounger
- Posts: 1259
- Joined: 01 May 2016, 09:58
Re: filter linked subform using combo box
HansV wrote: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 : 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 ?
You do not have the required permissions to view the files attached to this post.
Last edited by siamandm on 24 May 2017, 08:03, edited 1 time in total.
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: filter linked subform using combo box
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?
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1259
- Joined: 01 May 2016, 09:58
Re: filter linked subform using combo box
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 , i tried some ways to apply this filter but i got many errors so i deleted the query ...
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 , i tried some ways to apply this filter but i got many errors so i deleted the query ...
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: filter linked subform using combo box
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?
I see several combo boxes on frmBenTest. Which one should I look at?
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1259
- Joined: 01 May 2016, 09:58
Re: filter linked subform using combo box
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.
the subfrom is already filtered by the cboMonths... i want to add the list box selected items to the filter as well.
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: filter linked subform using combo box
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:
See the attached version.
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
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1259
- Joined: 01 May 2016, 09:58
Re: filter linked subform using combo box
perfect , that's what i needed
thanks alot
regards
thanks alot
regards
-
- BronzeLounger
- Posts: 1259
- Joined: 01 May 2016, 09:58
Re: filter linked subform using combo box
HansV wrote: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:
See the attached version.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
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
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: filter linked subform using combo box
Do you want to enter complete date in the text box?
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1259
- Joined: 01 May 2016, 09:58
Re: filter linked subform using combo box
yes please like 24/5/2017HansV wrote:Do you want to enter complete date in the text box?
regards
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: filter linked subform using combo box
Replace the lines
with (using txtDate as the name of the text box):
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
Code: Select all
If Not IsNull(Me.txtDate) Then
SQL = SQL & " AND tblDist_Items.AssessedDate=#" & Format(Me.txtDate, "yyyy-mm-dd") & "#"
End If
Best wishes,
Hans
Hans