Combox Filters!!!!!!!!!!!

[Old Man Devin]

I mean it filters and returns nothing, when you click on the drop down arrow on the combo box all PostCodes are listed in the drop down. When I type SW for SW London and then press enter it filters but with no results.

And yes the filter box does show orange and that it has been filtered.

I think that can only mean the wildcard isn't working (if there definitely are entries beginning with "SW"), so I suggest you take it out for now.

The only other way of trying it that comes to mind is to replace the '=' with 'like' for the lines are of the form:

" and [PostCode] Like '" & _ 
Replace(Me.cboPostCode.Text, "'", "''") & "*'"

Sometimes when it comes to string comparisons, Access prefers Like, so that may do something.

 

[Neilster]

Yep tried the 'LIKE' and the results are the same unfortunately - no results. Even if I put the full PostCode in, it still filters with no results.?

 

[Old Man Devin]

Does it definitely work without the wildcards and typing an exact postcode into the box?

One other but more limited approach to take could be to have lines like:

" and Left([PostCode],2) Like '" & _  Replace(Me.cboPostCode.Text, "'", "''") & "'"

And then only EVER enter the 2 first characters. This should probably be run from a different box so you don't lose the ability to search for full postcodes as well. That's quite a clunky/hacky way of doing it, but should be possible in principle if you need this feature!

 

[Neilster]

Works a treat thank you so much

The only thing I changed was ,2 and replaced it with ,4 so that I can search on the 1st 4 characters of a post code. but works a treat!!

And is it a standard rule that once you have filtered what you want - if say Neil, Pipeline, City, PostCode and if I wanted to the do Neil, New, City, PostCode - do I have to clear all filters 1st or can it be possible to change each individual combo box without have to clear all filters again.?

But other than that, thank you so much for all your help.

 

[Neilster]

But the only problem as you mentioned is that you can only filter if only you enter the number of characters that are set in the VBA code, so in effect I can only ever enter SW11 which will work, however if I enter SW2 it will not work.

 

[Old Man Devin]

do I have to clear all filters 1st or can it be possible to change each individual combo box without have to clear all filters again.?

If you want to be able to edit a filter you've set up, then you need to reestablish the complete filter after editing anything. So you need to write a Public Sub in a new module that builds the filters, adding to the filter the contents of ALL the boxes you want to filter by (unless they have nothing in them). Then you call this Public sub in the after update of all of those boxes.

So it will be of the form:

If Not IsNull(Me.NameFilterBox) Then
   If Me.Form.Filter="" Then
      Me.Form.Filter="Name ='" & Me.NameFilterBox & "'"
   Else
      Me.Form.Filter = Me.Form.Filter & " and Name = '" & Me.NameFilterBox & "'"
   End If
End If 

If Not IsNull(Me.LocationFilterBox) Then
   If Me.Form.Filter="" Then
      Me.Form.Filter="Location ='" & Me.LocationFilterBox & "'"
   Else
      Me.Form.Filter = Me.Form.Filter & " and Location = '" & Me.LocationFilterBox & "'"
   End If
End If

' etc....

Note how every time I need to check to see if the filter is nothing first, because if it is, when I don't need to have the 'and' part. This means that if NameFilterBox was blank and thus nothing that been added to the filter by the time it checks location filter box, it still constructs the filter correctly. Hopefully that makes sense.

As for the problem with things like SW2, yes that is just an inbuilt weakness of this method for codes with non-standard lengths, such a postcode. However, since the postcode will have a space, you could try searching for "SW2 " with a space on the end, so you have still entered 4 characters for it to look for. With any luck that will get around the problem.

 

[Neilster]

Sometimes it work with a space most of the time. The reason behind the method is when agents call say London we give them a postal area to call so that other agents won't clash or duplicate on calls.

I'm not sure how to perform a Public Sub in a new module with above code as it won't let me?

 

[Old Man Devin]

Sometimes it work with a space most of the time. The reason behind the method is when agents call say London we give them a postal area to call so that other agents won't clash or duplicate on calls.

I'm not sure how to perform a Public Sub in a new module with above code as it won't let me?

In the VBA editor, click Insert, then module. This starts a new public module. Then with the module selected click Insert and Procedure, then a menu comes up from which pick public sub. Then that sub.

To run it from your form, just write the name of the public sub on a line for the relevant event in that form. Something like:

Private Sub NameBoxFilter_AfterUpdate
   ApplyFilter
End Sub

Where 'ApplyFilter' was the name you gave the public sub in your new module. It doesn't matter what the module is called btw.

As for that space thing, I'm curious as to why it wouldn't work sometimes. Is there anything special about the ones it doesn't work for?

 

[Neilster]

In fact I've changed it to 3 characters in the VBA code because it doesn't work anymore for some reason when adding a space to a postcode that has 2-3 characters, sometimes you have to log out of the whole database and go back in and it might work once?

When the form after procedure pops up it has nothing in it to choose?

 

[Old Man Devin]

In fact I've changed it to 3 characters in the VBA code because it doesn't work anymore for some reason when adding a space to a postcode that has 2-3 characters, sometimes you have to log out of the whole database and go back in and it might work once?

When the form after procedure pops up it has nothing in it to choose?

This is because after the first time the filter is never turned off or cleared, so that when you start replacing the filter, it actually just adds the new criteria to the existing one? (Opposite problem to the one you had at the very beginning!)

 

[Neilster]

Hi Old Man Devin,

I a have followed your procedure as below :-

If Not IsNull(Me.cboOPOwner) Then
If Me.Form.Filter = "" Then
Me.Form.Filter = "OPOwner ='" & Me.cboOPOwner & "'"
Else
Me.Form.Filter = Me.Form.Filter & " and OPOwner = '" & Me.cboOPOwner & "'"
End If
End If

If Not IsNull(Me.TblGlobal) Then
If Me.Form.Filter = "" Then
Me.Form.Filter = "TblGlobal ='" & Me.TblGlobal & "'"
Else
Me.Form.Filter = Me.Form.Filter & " and TblGlobal = '" & Me.TblGlobal & "'"
End If
End If

And then comes up with compile error - invalid outside procedure.

I have set a class module and in the after update on the particular form I have put in ApplyFilter.

I must be doing something stupidly wrong here?? :banghead::banghead:

 

[Neilster]

Oh it doesn't like the Me. part in this line - If Not IsNull(Me.cboOPOwner) Then

 

[Old Man Devin]

Oh it doesn't like the Me. part in this line - If Not IsNull(Me.cboOPOwner) Then

So that was the line causing the compile error? Such an innocent looking part! Well possibly replacing 'Me.' with 'Me!' will make a different. Apart from that you need to make sure that there is something called cboOPOwner for it to look at in the place the code the is run from.

Ah perhaps that is the problem: you have created a new class module to hold this code if I understand you correctly. To use Me style references it needs to the in the correct form class object. So in the VBA editor all the objects with the forms' names are the form class objects, so try having the code be in there.

Or you could replace 'Me' with 'Forms![FormName]' if it's kept in a separate module.

 

[Neilster]

OK, What I can do is this - In the VBA editor, click Insert, then module. This starts a new public module. (What I can't) do is this - """"Then with the module selected click Insert and Procedure, then a menu comes up from which pick public sub. Then that sub."""

What i did do is manually put a module in creating - public sub after_update ()?? the on a form added ApplyFilter and the it ran and didn't like ApplyFilter.

Hope this makes sense? i'm nearly there with it.

 

[Neilster]

OK this is what it looks like so far.

Public Sub ApplyFilters()

If Not IsNull(Me.cboOPOwner) Then
If Me.Form.Filter = "" Then
Me.Form.Filter = "OPOwner ='" & Me.cboOPOwner & "'"
Else
Me.Form.Filter = Me.Form.Filter & " and OPOwner = '" & Me.cboOPOwner & "'"
End If
End If

If Not IsNull(Me.TblGlobal) Then
If Me.Form.Filter = "" Then
Me.Form.Filter = "TblGlobal ='" & Me.TblGlobal & "'"
Else
Me.Form.Filter = Me.Form.Filter & " and TblGlobal = '" & Me.TblGlobal & "'"
End If
End If

End Sub

the module I have named ApplyFilters, which on the after update event looks like this.

Private Sub cboOPOwner_AfterUpdate()

ApplyFilters

End Sub

I tried the ! and it don't like too much

 

[Old Man Devin]

I think you still need to change the Me calls to Forms![FormName]! calls. Because you've said 'Me' in a module that is set apart from the actual form, Access doesn't know what 'Me' is because public modules can interact with any form. So you need to be specific.

OR just have the whole apply filter be in a public sub declared in the code of that form. Then it will assume Me refers to the form it is written on, and should be more happy with using it.

 

[Old Man Devin]

OK, What I can do is this - In the VBA editor, click Insert, then module. This starts a new public module. (What I can't) do is this - """"Then with the module selected click Insert and Procedure, then a menu comes up from which pick public sub. Then that sub."""

The insert procedure bit is in the same place as the insert module. The insert menu should have Module, Procedure, or Class Module as the options. When you pick procedure it asks if you want it to be public or private, and if it should be a sub (just a set of code that runs) or a function (code that creates information and gives it to other pieces of code).

 

[DemonDNF]

I'm also having problems getting a combobox filter happening. I am attaching what the forms look like, the VB code and the debug result.

Layout looks like this:

Main form
    Inventory Form
        InventoryDetail form

Private Sub lblPackage_Click()
  Form_InventoryDetail!PackageID.SetFocus
  Form_InventoryDetail.Filter = Form_InventoryDetail.Filter & _
                                " AND PackageID = '" & _
                     Replace(Form_InventoryDetail!PackageID.Text, "'", "''") & "'"
  Form_InventoryDetail.Refresh
End Sub

I'd like the option to filter detail records on the continous form by selecting a record, then clicking on the column heading. In this case I wanted only the Axial record after clicking Package.

PackageID is a SQL to Package table. I tried Requery, didn't work either.

Thanks!

Robert

 

[DemonDNF]

SOLVED!

After reading countless threads, trying a gazillion combinations, SQL...WHERE would filter properly, hardcoded FILTER on the form would not, so I knew I was on to something.

It was a combination of syntax (needed brackets) and setting filter flag. Why I have to set the flag is beyond me, it was already on in the form.

Private Sub lblPackage_Click()
  Form_InventoryDetail.Form.Filter = "[PackageID] = " & Form_InventoryDetail!PackageID
  Form_InventoryDetail.Form.FilterOn = True
End Sub

Selecting a record and clicking the heading now filters the details.

Robert

 

[Neilster]

Hi Fella,

I have followed your advise code regarding Public filters and have set up a module as advised, however when I put the code in..

If Not IsNull(Me.NameFilterBox) Then
If Me.Form.Filter="" Then
Me.Form.Filter="Name ='" & Me.NameFilterBox & "'"
Else
Me.Form.Filter = Me.Form.Filter & " and Name = '" & Me.NameFilterBox & "'"
End If
End If

If Not IsNull(Me.LocationFilterBox) Then
If Me.Form.Filter="" Then
Me.Form.Filter="Location ='" & Me.LocationFilterBox & "'"
Else
Me.Form.Filter = Me.Form.Filter & " and Location = '" & Me.LocationFilterBox & "'"
End If
End If

After highlighting Me. It come up with a compile error saying :- Invalid use of Me keyword

I was wondering if you could cast some light on this?

Cheers