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

[Neilster]

Good day!

I have at present 2 combo box's that filter, now....... the 1st combo box is a drop down value list that will filter the OPOwner (persons name) for example there are 3000 records and 5 OPOwners, I can for now filter my name in cboOPOwner combo box.

The 2nd box (drop down list) is Status which would be "New";"Open";Pipeline";"Lead" ect...

If I filter my name it brings up all records related to my name and the when I want to filter status it will bring up all records relating to say 'Pipeline' however it will bring everyone's ;Pipeline and not just mine.

What I want is to filter my name in the OPOwner combo box and once all records are filtered in my name then in the status combo box filter what ever I want to look at. example what I want to filter - 'Filter Neil' then 'Filter Pipeline, or filter Lead or New'.

This is the code I have in both combo's....


Private Sub cboOPOwner_AfterUpdate()


If Nz(Me.cboOPOwner.Text) = "" Then
Me.Form.Filter = ""
Me.FilterOn = False

ElseIf Me.cboOPOwner.ListIndex <> -1 Then
Me.Form.Filter = "[OPOwner] = '" & _
Replace(Me.cboOPOwner.Text, "'", "''") & "'"
Me.FilterOn = True

Else
Me.Form.Filter = "[OPOwner] Like '*" & _
Replace(Me.cboOPOwner.Text, "'", "''") & "*'"
Me.FilterOn = True

End If

Me.cboOPOwner.SetFocus
Me.cboOPOwner.SelStart = Len(Me.cboOPOwner.Text)

End Sub

*****And then***************

Private Sub cboStatus_AfterUpdate()

If Nz(Me.cboStatus.Text) = "" Then
Me.Form.Filter = ""
Me.FilterOn = False


ElseIf Me.cboStatus.ListIndex <> -1 Then
Me.Form.Filter = "[Status] = '" & _
Replace(Me.cboStatus.Text, "'", "''") & "'"
Me.FilterOn = True


Else
Me.Form.Filter = "[Status] Like '*" & _
Replace(Me.cboStatus.Text, "'", "''") & "*'"
Me.FilterOn = True

End If


Me.cboStatus.SetFocus
Me.cboStatus.SelStart = Len(Me.cboStatus.Text)

End Sub

Please can someone help as I have looked for months on forums and YouTube!

:banghead::banghead::banghead::banghead:

 

[Old Man Devin]

The issue is that your are overwriting the filter when you apply it on the status. Instead you want to build on top of this. So instead of lines like:

Me.Form.Filter = "[Status] = '" & _
                     Replace(Me.cboStatus.Text, "'", "''") & "'"

Use:

Me.Form.Filter = Me.Form.Filter & "and [Status] = '" & _
                     Replace(Me.cboStatus.Text, "'", "''") & "'"

This way the filter string combines with the previously established one (via the 'and' I have added... I think that's the right place for it...) and allows you to filter via both variable at the same time. Should do what you need!

 

[Neilster]

THANK YOU SOOOOOOOOOOOOOOOO MUCH!!!!

You have saved my life!!! it works a treat.

 

[Neilster]

I forgot to ask, would this be the same principle if I had a combo box for say 'City' and then 'Postcode' ??

Cheers

 

[Old Man Devin]

No problem, glad to help!

Yes the same principle can be used for any combination of any number of criteria. I use it all the time for setting up complicated filters with loads of different inputs. As long as you make sure that all criteria after the first one have the 'and' in them, and are being added to the old filter rather than overwriting it, all will be well.

 

[Neilster]

2 things I wanted to ask and I will leave you alone Old Man Devin.

In my PostCode combo I want to type in a PostCod that searches post code, however I want to just type in the first part say like 'S' or 'SW' or 'M16' at the moment it will just filter one post code, can I add a wild card to it??

And lastly

When you design a form Access has at the bottom navigation buttons for the record number and a search box next to it so that if your going through and say your on recored 235 and you quit the database and then go back in, you type that number in the search box and it will take you to that record.

What I've done is create my own nav buttons with a record count which is fine, however I can't seem to create a search box so I can enter the record number so it jumps to that record, if that makes sense.

Here is my code.

Private Sub Form_Current()

If Me.NewRecord Then
Me.lblRecordCount.Caption = _
"Record " & Me.CurrentRecord & " of " & Me.Recordset.RecordCount + 1
Else
Me.lblRecordCount.Caption = _
"Record " & Me.CurrentRecord & " of " & Me.Recordset.RecordCount
End If

End Sub

Thanks for all your help

 

[Old Man Devin]

1) Yes you can add a wildcard. You probably want "*" in this case, since it just means anything. So if you looked for M16* if should return everything with M16 as the beginning go the postcode.

2) I don't see why you want to create your own system for this when there is a built in one? Assuming all the records have a unique ID, it is easier to get it to jump to those. All you need to do is add another filter condition on the After Update of the search box, adding something like:
"and OwnerID = " & [Me.OwnerIDSearch] & "
note that I have assumed the ID is a number, and that for numbers you don't need as many apostrophes in the string to have it work properly. OwnerIDSearch should be the name of the search box.

Hope this helps!

 

[Neilster]

Cool thanks where do I put the "*" in my code.

Private Sub cboPostCode_AfterUpdate()

If Nz(Me.cboPostCode.Text) = "" Then
Me.Form.Filter = ""
Me.FilterOn = False


ElseIf Me.cboPostCode.ListIndex <> -1 Then
Me.Form.Filter = Me.Form.Filter & "and [PostCode] = '" & _
Replace(Me.cboPostCode.Text, "'", "''") & "'"
Me.FilterOn = True


Else
Me.Form.Filter = Me.Form.Filter & "and [PostCode] = '" & _
Replace(Me.cboPostCode.Text, "'", "''") & "'"
Me.FilterOn = True

End If


Me.cboPostCode.SetFocus
Me.cboPostCode.SelStart = Len(Me.cboPostCode.Text)

End Sub

And thanks for OwnerID option.

 

[Old Man Devin]

I think you can just edit the
& "'"
at the end of the filter strings to:
& "*'"
And that will do it. Although actually I'm struggling to remember if that's right, so perhaps just try it out, and if it doesn't work try:
& * & "'"
instead.

 

[Neilster]

Would this work only with a text box or combo box, I have tried both & "*"
& * & "*" and it's not working.

 

[Old Man Devin]

Does it just come up with no results? If so then it might be looking for a postcode that literally has a star in it.

I looked it up and putting:
& "*'"
instead of
& "'"
at the end of your filter strings is the proper way to do it. I'm fairly sure you can do this regardless of where or how the postcode is actually displayed.

Were there any error messages when you tried to do it?

 

[jdraw]

Neilster,

In this code

...& "and [PostCode] = '" & _

I recommend you add a space before the and

eg ...& " and [PostCode] = '" & _

You need the space in the rendered SQL.

 

[Neilster]

Would it have anything to do with what I should put in Row Source and Row Source type? in Data on the property sheet.

 

[Neilster]

And it come up with 'compile error'

 

[Old Man Devin]

Would it have anything to do with what I should put in Row Source and Row Source type? in Data on the property sheet.

It will depend on this. The data for the sheet needs to actually have all the parameters you'll be looking for obviously.

A compile error means there is a mistake in the way the code is written, rather than a mistake with the concepts used. So it is probably just a apostrophe in the wrong place or something; compilers are very unforgiving of such things.


I've put the * entries in the code myself, in what I believe to be the right places. To see if it compiles, replace your current code with it, then click on 'Debug' at the top of the VBA editor and choose 'Compile [DatabaseName]'. I have also included jdraw's point about the extra space being needed, which was my mistake.

Also, are you sure the code you have before adding the * bits was compiling okay?

Private Sub cboPostCode_AfterUpdate()

If Nz(Me.cboPostCode.Text) = "" Then
    Me.Form.Filter = ""
    Me.FilterOn = False
 
  ElseIf Me.cboPostCode.ListIndex <> -1 Then
    Me.Form.Filter = Me.Form.Filter & " and [PostCode] = '" & _
                     Replace(Me.cboPostCode.Text, "'", "''") & "*'"
    Me.FilterOn = True
  
  Else
    Me.Form.Filter = Me.Form.Filter & " and [PostCode] = '" & _
                     Replace(Me.cboPostCode.Text, "'", "''") & "*'"
    Me.FilterOn = True

  End If
  
  Me.cboPostCode.SetFocus
  Me.cboPostCode.SelStart = Len(Me.cboPostCode.Text)
  
End Sub

 

[Neilster]

Cool! OK,

Using a space or not between the and hadn't made no difference before, however I have now put in apostrophe and it does filter, but shows not results?

Before when adding * it wouldn't run and came up with a compile error suggesting I write Text. = "M16" or syntext error.

In the Row Source I have this in place:-

SELECTTblCloseCommunicatons.PostCodeFROMTblCloseCommunicatons;


I wonder if that is what i'm doing wrong??

 

[Old Man Devin]

No idea if it makes any differences, but try adding spaces to the row source SQL so it is like (also note you had a random space in the middle of 'Communicatons', which itself appears to be a misspelling!):

SELECT TblCloseCommunicatons.PostCode FROM TblCloseCommunicatons;

Never tried it without spaces so don't know if it causes errors, but maybe.

So it does compile now but it just doesn't come up with what you expect in the end?

 

[Neilster]

I've tried the spaces in Row Source and it still does the same when I filter OPOwner, Status, City then PostCcode it filters nothing.

So yes it does compile with no error's, just no results. All other combo box's work fine.

So I am at a loss now as it should work.

 

[Old Man Devin]

Do you mean it doesn't filter at all or it filters it but returns nothing in the results?

Does the orange 'Filtered' icon appear at the bottom of the screen after running it?

 

[Neilster]

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.