Filter a contunuous form (1 Viewer)

houstonswamp · Mar 13, 2019

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:

Code:

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 · Mar 13, 2019

Code:

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

houstonswamp · Mar 13, 2019

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:

Code:

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 · Mar 13, 2019

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

Code:

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 · Mar 14, 2019

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:

Code:

[Formulieren]![Verkoopoverzicht]![cboKlant]

I also added to the combobox:

Code:

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

Filter a contunuous form (1 Viewer)

houstonswamp

New member

MajP

You've got your good things, and you've got mine.

houstonswamp

New member

MajP

You've got your good things, and you've got mine.

houstonswamp

New member

Similar threads

Users who are viewing this thread