Filter a contunuous form

[houstonswamp]

Hello there,

I'm trying to filter a continuous form based on the value of a combo-box.

I attached the following code to the combo box after update:

Private Sub Combo1_AfterUpdate()

Me.Filter = "[fkKlantenID] = [Forms]![Verkoopoverzicht]![Combo1]"
Me.FilterOn = True

End Sub

It seems to work, but only once. Is there a possibility to change the value and it filters again and again?

Thanks in advance,

Houston

 

[MajP]

if not isnull(me.combo1) then
  Me.Filter = "[fkKlantenID] = " & Me.combo1
end if

 

[houstonswamp]

Re: Filter a continuous form

Hello Majp,

I have some additional questions if you don't mind.
When I restart MS Access, I found out that the filter button in the top menu of Access was turned off. Can this be overruled?

Next to this I added another field, a weeknumber. On my form it is a copy of a date, notated as a week 'ww'. How can I let the filter find this value? Or do I need to add another column in the table with specificaly week number?

I would like to avoid a 'filter' button, so when the fields are empty it should show everything, when only one filter filled only filter on that criteria. A year must be added as well, can this all be done in the same script?

I'm using the following code now:

Private Sub Knop67_Click()

If Not IsNull(Me.Combo1) Then
  Me.Filter = "[fkKlantenID] = " & Me.Combo1
  Me.Filter = "[Leverdatum] = " & Me.txtWeek
End If

End Sub

Thanks in advance.

Houston.

 

[MajP]

Next to this I added another field, a weeknumber. On my form it is a copy of a date, notated as a week 'ww'. How can I let the filter find this value? Or do I need to add another column in the table with specificaly week number?

I interpret this to mean you want to enter a value of 25 in txtWeek and filter dates that fall into the 25th week. If that is the case do not add a field to the table but a calculated column to your underlying query.

A year must be added as well

I would do the same as above and add a year to the query.

So something like

SELECT 
  LeverDatum, 
  Format([LeverDatum],"ww") AS LeverWeek, 
  Format([LeverDatum],"yyyy") AS LeverYear,
  OtherFields
  ....
FROM SomeTable

Now you can filter on leverWeek and LeverYear simply.

Do you want your filter to be an AND or OR. If you have 2 or more controls filled in do you want to and the filters or "or" the filters?

 

[houstonswamp]

Hello MajP,

Eventually I have 3 comboboxes on top of a continuous form.
I filter it using the criteria field under the tables in query design. For this I'm using the following expression:

[Formulieren]![Verkoopoverzicht]![cboKlant]

I also added to the combobox:

Private Sub cboKlant_AfterUpdate()
    Me.Requery
End Sub

I works fine, but gives no results when the comboboxes have no value.
I tried several options and 'if isnull' expressions, but I cannot find the right one to apply on each table in the query.

Thanks a lot.

Houston