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..
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:and now it works!