MS ACCESS - How to filter a splitform's datasheet by a multiple combobox values

Question

I have a splitform with some txtboxes and 1 combobox. The combobox is related to the same form's query (qryPurchase) and it shows 3 columns of the query fields:

ID_purchase, Purchase_Type, Date_Purchase

In the query there other fields like Purchase_Item, Quantity and so on, but there are only 2 kind of Purchase_Type values, sell and borrow. Different Purchase_Item can have the same ID_purchase and Date_Purchase like an invoice.

example of the combobox list:

ID_purchase, Purchase_Type, Date_Purchase
1, sell, 08/01/2020
1, sell, 09/01/2020
1, borrow, 09/01/2020
2, sell, 10/01/2020
2, borrow, 11/01/2020

When i choose a row from the dropdown menu of the combobox, i want that it has to filter the splitform's datasheet below by the combobox's row values in order to get a datasheet filtered list of all ID_purchase and Purchase_Type and Date_Purchase that match with the combobox row values.

like in the list above, if i choose the first row, the datasheet below shows 2 records.

I tried to achive that like this:

Private Sub fltDocCombo_AfterUpdate()

Me.Form.Filter = "[ID_purchase] =" & Me.fltDocCombo.Column(0) _
& " AND [Purchase_Type] = " & "'" & Me.fltDocCombo.Column(1) & "'" _
& " AND [Date_Purchase] = " & "#" & Me.fltDocCombo.Column(2) & "#"

Me.Form.FilterOn = True
End Sub

Private Sub Form_Close()
Me.Form.Filter = ""
Me.Form.FilterOn = False
End Sub

Private Sub Form_Open(Cancel As Integer)
Me.Form.Filter = ""
Me.Form.FilterOn = False
End Sub

This sometimes works sometimes not, it seems that the Me.Form.Filter value is locked on the first ID_purchase of the list..

like in the example above:

ID_purchase, Purchase_Type, Date_Purchase
1, sell, 08/01/2020
1, sell, 09/01/2020
1, borrow, 09/01/2020
2, sell, 10/01/2020
2, borrow, 11/01/2020

If i try to choose the 2nd row, the filter applied is the first row values.. But not everytime..

Sometimes it seems that the filter of Date_Purchase doesn't filter nothing..

I thought to apply the filters sequantially like ID_purchase first, Purchase_Type second and Date the last one, but when it applies the second filter it cancel the first one and so on..

In this case i don't know how to add another filter to an existing one..

help..

Alex Alex · Accepted Answer · 2020-07-10T19:24:51

Solved..

the problem was the Date_Purchase Date format..

MS-ACCESS was installed for my local language and date format (dd/mm/yyyy) but in VBA the Date default format is always mm/dd/yyyy

so i modified the Me.Form.Filter value like this:

Me.Form.Filter = "[ID_turnin_temploan] =" & Me.fltDocCombo.Column(0) _
& " AND [Posizione] = " & "'" & Me.fltDocCombo.Column(1) & "'" _
& " AND [Data_turnin_temploan] = " & "#" & Format(Me.fltDocCombo.Column(2), "mm/dd/yyyy") & "#"

and now it works!

MS ACCESS - How to filter a splitform's datasheet by a multiple combobox values

1 Answers