Form Filtering

[migi48]

Hi!

I just want to ask if it is posible to make a multiple search/filter of records in a form?

For example, I have a form(frmApplications) that has a record source connected to a table (tblApplications). Then I want to view and edit records which App_Num are equal with "5299672017819116", "5299671118593117", "5299671606423116", and so on. Filter feature of MS ACCESS only allows me to view and edit filtered records one by one. Is there a way I can view and edit all these filtered records of App_Nums in one form(hopefully in a Continuous Form)?

Thank you so much :)

[MikeTheBike]

Hi

The short answer is yes, but we would need to know how you intend to impliment the filter for the required App_Num's.

For instance, you could have a multi-select list box and a button to apply the filter, which would build a filter criteria from the selected items in code and then apply it to the form.

This would of course require some VBA knowlege!

There are other ways to do this, perhaps you could indicate how you see it operating.


MTB

[NeoPa]

First let me say that you have posted two very similar threads at the same time (How do I Open a Form with Multiple Items Filtered). As there is considerable overlap I suggest this is a self-defeating approach. I will delete the other question for now while the main concepts of the filtering issue are dealt with in here. If, at that point, you feel you'd like to ask about applying those concepts to opening a separate form pre-filtered to your choices, then you should do that then, from a position of better understanding.

As far as filtering within the same form goes, there are two fundamental issues you need to deal with :

1. How to provide an interface that enables multiple items to be input.
2. How to filter the records based on that list.

MTB has already offered some thoughts on A, which seem sensible given a finite list from which to choose values. If that is not the case then a simpler TextBox control allowing the entry of a list of items separated by a character such as a comma (,) would be a viable alternative.

For filtering within and between forms you can find all you need at Example Filtering on a Form. It even has an example database to download and experiment with, and explains how various types of data should be formatted in the filter string.

[migi48]

Hi guys! Thanks for replying! First of all, I admit that I'm new at access and VBA coding so please forgive me. :D

@MikeTheBike - I actually visualize the form to have multiple textboxes where I could input different App_Num's I want to find. Then with a click of a button, I want to see only the records which I have input. (doesn't really matter if it should be on the same form or not) But I want it editable and not in a query(since I believe queries are only for viewing). :)

@NeoPa - Sorry for the other thread. I repost this question simply because I thought I found the other one more difficult to understand. Thank you for directing me to an article. I would try to read and understand that and maybe that could answer my question. :)

[NeoPa]

No need to apologise really. The questions were different enough that it was hard for me (and I have a lot of experience in the forums) to know if it was a problem or not. It would be unreasonable to expect you to appreciate it quite so well.

Anyway, you may like to consider some other aspects of having multiple TextBox controls to allow entry of this information. Do you have a finite size for the list? If not then how many controls do you design?

When you've designed it, you will only have to put all the values together into a comma (,) separated string anyway to include in your filter (using the In() facility).

It's not an impossible approach, but it may make your life more complicated than necessary. Ultimately how you handle it is your choice of course :-)

[migi48]

Hi NeoPa!

I have read your article on Filtering on a form. However it only filters/finds one account at a time. I would want my form to display multiple accounts(just to relate with your article). Considering that I would input multiple account codes(unique codes) in multiple textboxes(Let's say 5 textboxes). And after clicking a button, the form would show me the 5 accounts which I could edit. :D

[migi48]

@NeoPa

I see. So I can just input 5 values in one textbox separating them with commas(,)? How can I separate these (in code) values when I need to filter them in the module?

[NeoPa]

Migi48:
How can I separate these (in code) values when I need to filter them in the module?

You don't need to. That's the point. The filter should look like :

[FieldName] In(1,6,9,45,9923)

That is exactly what you would expect the operator to enter into your TextBox. If it's string values required then it sould look like :

[FieldName] In('Bob','John','Valerie','Alex')

This is easily accomplished by :

strFilter = "[FieldName] In('" & Replace(Me.txtX, ",", "','") & "')"

[migi48]

I tried your code. But I always end up a syntax error on strFilter. :(

Option Compare Database
 
Private Sub btnFilter_Click()
    Dim strFilter As String, strOldFilter As String
 
    strOldFilter = Me.Filter
 
    If Me!txtFindCardNumber > "" Then _
        strFilter = "[Card_Number] In('" & Replace(Me.txtFindCardNumber, ",", "','") & "')"
 
    If strFilter > "" Then strFilter = Mid(strFilter, 6)
    If strFilter <> strOldFilter Then
        Me.Filter = strFilter
        Me.FilterOn = (strFilter > "")
    End If
 
End Sub

[migi48]

Hi NeoPa! I got the filter working for me :) Unfortunately, only one record can be shown at a time. I want my strFilter to look like these:

 strFilter = "[Card_Number] In('5299672017819116','5299671118593117','5299671606423116','5299671934335115')" 

This is the the actual code I have right now.

 If Me!txtFindCardNumber > "" Then _
        strFilter = "[Card_Number] In('" & Replace(Me.txtFindCardNumber, ",", "','") & "')" 

The Card_Number's would come from a textbox (txtFindCardNumber) only separated by commas(,) I think there is something wrong with this line that's why it only gives me the first Card_Number which I have input in the textbox (txtFindCardNumber). Please tell me what's wrong with it. Thanks! :D

[migi48]

Hi NeoPa! :D

I have finally done it! :D Thank you so much for your HELP and PATIENCE! :D I just included a space after the comma(,) from the code you gave me :D Here's my final code. I hope this thread could help a lot of newbies like me :D

 Private Sub btnFilter_Click()
    Dim strFilter As String
 
    If Me!txtFindCardNumber > "" Then _
        strFilter = "[Card_Number] In('" & Replace(Me.txtFindCardNumber, ", ", "', '") & "')"
 
    If strFilter > "" Then
        Me.Filter = strFilter
        Me.FilterOn = (strFilter > "")
    End If
 
End Sub
 

CHEERS!
migi48

[NeoPa]

Migi48:
I tried your code. But I always end up a syntax error on strFilter. :(

Line #11 is extraneous here. It's only required if you have multiple potential strings - all starting with " AND ...".

[NeoPa]

Migi48:
Please tell me what's wrong with it. Thanks! :D

I see nothing wrong with the code here. What data is passed in Me.txtFindCardNumber?

Migi48:
I just included a space after the comma(,) from the code you gave me :D

Not a good plan. This may get past the problem of the data but doesn't fix the data. The operator is entering the data incorrectly (a space after the comma is not correct - and explains why it was failing previously). Your code now only works with incorrectly entered data.

Try instead :

Private Sub btnFilter_Click()
    Dim strFilter As String
 
    With Me
        If .txtFindCardNumber > "" Then
            strFilter = Replace(.txtFindCardNumber, ", ", ",")
            strFilter = "[Card_Number] In('" & Replace(strFilter, ",", "','") & "')"
        End If
 
        .Filter = strFilter
        .FilterOn = (strFilter > "")
    End With
End Sub

This will allow the operator to make that particular mistake without penalising them for entering the data correctly. Only the comma (,) should be entered between valid items.

PS. Setting .Filter should not be dependent on whether a value has been entered - otherwise clearing the filter would have no effect. See my code for how it should be done properly.

[migi48]

@NeoPa

I did what you suggested and it's now working pefectly. :D
Thank you so much for correcting me and explaining how it should be done. :D More power to expert programmers like you! :D

Private Sub btnFilter_Click() 
    Dim strFilter As String 
 
    With Me 
        If .txtFindCardNumber > "" Then 
            strFilter = Replace(.txtFindCardNumber, ", ", ",") 
            strFilter = "[Card_Number] In('" & Replace(strFilter, ",", "','") & "')" 
        End If 
 
        .Filter = strFilter 
        .FilterOn = (strFilter > "") 
    End With 
End Sub 
 
 

[NeoPa]

Always pleased to help. It's why I do it - because I enjoy it :-)