Re-Filtering on a current form

[Redbeard]

Hi, I am a newbie using Access 2003. I am trying to apply a filter to a form and then re-filtering that forms records again. Basically I have my main form and when I wish to filter I click a button and a pop-up box comes up with several lists. I select what I want from the lists and click the button on the pop-up to perform the filter. It filters my main form, however if I want to filter again to narrow my search then I do the same thing and it reset my main form and does the filter again. So If I do a filter for "apples" and then do one for "red" I want the second filter to pull all the "red, apples" not everything that is red. Here is the code I am working with.

 Private Sub Search_Click()
Dim strOr As String
Dim strDoc As String
Dim varSelect As Variant
Dim ctl As Control
 
strDoc = "frmDescription"
    
    Set ctl = Forms!frmPhotoKeywordSearch.Controls("list1")
        For Each varSelect In ctl.ItemsSelected
        strOr = strOr & "[Photo Keywords] Like '*" & ctl.ItemData(varSelect) & "*' OR "
        Next varSelect
    Set ctl = Forms!frmPhotoKeywordSearch.Controls("list2")
        For Each varSelect In ctl.ItemsSelected
        strOr = strOr & "[Photo Keywords] Like '*" & ctl.ItemData(varSelect) & "*' OR "
        Next varSelect
    Set ctl = Forms!frmPhotoKeywordSearch.Controls("list3")
        For Each varSelect In ctl.ItemsSelected
        strOr = strOr & "[Photo Keywords] Like '*" & ctl.ItemData(varSelect) & "*' OR "
        Next varSelect
    Set ctl = Forms!frmPhotoKeywordSearch.Controls("list4")
        For Each varSelect In ctl.ItemsSelected
        strOr = strOr & "[Photo Keywords] Like '*" & ctl.ItemData(varSelect) & "*' OR "
        Next varSelect
    Set ctl = Forms!frmPhotoKeywordSearch.Controls("list5")
        For Each varSelect In ctl.ItemsSelected
        strOr = strOr & "[Photo Keywords] Like '*" & ctl.ItemData(varSelect) & "*' OR "
        Next varSelect
    Set ctl = Forms!frmPhotoKeywordSearch.Controls("list6")
        For Each varSelect In ctl.ItemsSelected
        strOr = strOr & "[Photo Keywords] Like '*" & ctl.ItemData(varSelect) & "*' OR "
        Next varSelect
    Set ctl = Forms!frmPhotoKeywordSearch.Controls("list7")
        For Each varSelect In ctl.ItemsSelected
        strOr = strOr & "[Photo Keywords] Like '*" & ctl.ItemData(varSelect) & "*' OR "
        Next varSelect
    '   lose the last ' OR '
    strOr = Left(strOr, Len(strOr) - 4)
    
DoCmd.OpenForm strDoc, acNormal, , strOr
DoCmd.Close acForm, "frmPhotoKeywordSearch"
    
End Sub

[Jim Doherty]

Have a quick look at the zipped db attachment I've raised here based on your post. I am not convinced I understand how you are working this data as you have a number of lists all are targetting the same single column of data to provide the keywords so it is pretty much guesswork but the functional loop of controls might interest you in terms of method rather than having lots of lines to refer to the lists explicitly.

If you can be more explanatory on your method of working that data then we might be able to help

Regards

Jim :)

[Redbeard]

Ok, here is a bit more explanation. I have 7 lists each with different terms in them. The terms relate to each other that is why there are 7 lists instead of 1 big one (easier to manage). You can select as many terms from each list and it will look for them in a textbox on my main for. The textbox on the main form only holds terms that are in the 7 lists. I actually have 3 button, this one does "or" another does "and' and the final one does "not". So if I select "apples" out of list 1 and "red" out of list 2 and hit the "or" button, I will get all records containing "red" and all the ones containing "apple". If I hit the "and" button it will give me only the records containing "red" and "apple". And the “not” button gives me all records that don’t contain “red’ or “apple”. This works great for me. However, I would like to be able to do is, filter for "apples" and "red" hitting the "and" button. Then filter for "Macintosh" using the "not" button, which would give me.... red apples that were not Macintosh. Inside I currently get everything that is not Macintosh, because it filters the original form and not the filtered one. (I am using fruit as a simple example, my terms are much more complex) Any thoughts.

[Jim Doherty]

Ok, here is a bit more explanation. I have 7 lists each with different terms in them. The terms relate to each other that is why there are 7 lists instead of 1 big one (easier to manage). You can select as many terms from each list and it will look for them in a textbox on my main for. The textbox on the main form only holds terms that are in the 7 lists. I actually have 3 button, this one does "or" another does "and' and the final one does "not". So if I select "apples" out of list 1 and "red" out of list 2 and hit the "or" button, I will get all records containing "red" and all the ones containing "apple". If I hit the "and" button it will give me only the records containing "red" and "apple". And the “not” button gives me all records that don’t contain “red’ or “apple”. This works great for me. However, I would like to be able to do is, filter for "apples" and "red" hitting the "and" button. Then filter for "Macintosh" using the "not" button, which would give me.... red apples that were not Macintosh. Inside I currently get everything that is not Macintosh, because it filters the original form and not the filtered one. (I am using fruit as a simple example, my terms are much more complex) Any thoughts.

In order to lay something out it was puzzling me about the 7 lists all effect targetting the one column of data which I assume had a kind of look up value in the one column so if 'apples' was a data value on its own in the 'Photo keywords' column lets say 20 rows down and 'Red' was fifteen rows down how would you know which value came first if you were wanting to use the search criteria of 'Red' and then 'Apples'

Are you able to provide a cut down version of what you have there as an attachment. Its difficult guessing the layout and whilst I appreciate the second explanation seeing a layout and a small working model is much more convenient and helpful.

Regards

Jim :)

[Redbeard]

Hi Jim

Sorry but doing a model is beyond me, it tock me 3 weeks and some very helpful people in "the scripts" to help me get this together in the first place. I would send you my whole database but it is to big and has sensitive info in it. Anyways I think that you are missing the point. The only part of the code that I am trying to rework is the part that tells it to reset the form for the next filter. That would be

DoCmd.OpenForm strDoc, acNormal, , strOr

I need the code to use the main form for the filter but not to reset the form so that multiple filters can be done to narrow down results. That is what I am looking for.

Thanks

[Jim Doherty]

Hi Jim

Sorry but doing a model is beyond me, it tock me 3 weeks and some very helpful people in "the scripts" to help me get this together in the first place. I would send you my whole database but it is to big and has sensitive info in it. Anyways I think that you are missing the point. The only part of the code that I am trying to rework is the part that tells it to reset the form for the next filter. That would be

DoCmd.OpenForm strDoc, acNormal, , strOr

I need the code to use the main form for the filter but not to reset the form so that multiple filters can be done to narrow down results. That is what I am looking for.

Thanks

Hi,

I don't need your data I was merely wishing to understand the overall principle design of this is in order to help you. You mentioned various "and/or" and "not" buttons and my assumption is that they have code behind each one of them, that will/might affect the filtration AS it is being built on the fly. One cannot code something in isolation and expect it to fit in seemlessly with existing code when one has not seen any potential conflicts that might occur due to existing design.

How the filtration is currently built, assigned and retained and or omitted currently is what interests me from your a design/code perspective because it will dictate the 'finality' of the logic in what you want this filtration to include/omit as part of any SQL syntax.

If I have missed the point then I apologise for not understanding you but in many ways I don't believe I have either. The posted code shows you only working on ONE column namely Photo Keywords. When we provide OR logic as part of a WHERE clause in SQL we can add as many OR's to the comparison as we wish to when working off one column like that.

The problem comes with any subsequent list when we use the same column and stipulate that the comparison on that column must be an AND comparison. So Red AND Apples when presented in any SQL filtration syntax from a 'two' list selection working only the ONE column could potentially read

SELECT * FROM [YourTableName] WHERE [Photo Keywords]="Apples" OR [Photo Keywords] = "Oranges" OR [Photo Keywords] = "Pears" AND [Photo Keywords]="Red" AND [Photo Keywords]= "Orange" AND [Photo Keywords]="Yellow" AND [Photo Keywords] <> "Macintosh"

This SQL if executed would provide completely wrong results if anything at all because the comparison syntax is simply not able to determine a 'Red Apple' The computer can only do what it is told and in these circumstances would never have sufficient logic provided to it to make a connection between the word 'Red' and the word 'Apple' intelligently. To do this properly you would need two fields one called Colour and the other called Fruit and make comparisons then between the two fields.

The final variable storing the sql is as you displayed STROR used in the DoCmd.Openform statement. What the variable STROR contains is the all important element. It can only contain imformation fed to it. If that information is spurious then so will be any resultant dataset

I am sorry if this does not assist you to the point you would like to be at right now let us see if anyone else has a clearer picture of this than I have

Regards
Jim :)

[Redbeard]

Hi Jim

Most of what you said in your last post went right over my head, but I am not really schooled in this sort of thing. I have just picked it up as I go so the theory does not always make sense to me. I have managed to ripe the code out of my database and put it into a simple one. I will post it as an attachment for you to look at, but how do I do that? I can't seem to find a way to add it to this post... is there a button or something. Let me know and I will post it for you to look at.

Thanks for all you help so far.

[Jim Doherty]

Hi Jim

Most of what you said in your last post went right over my head, but I am not really schooled in this sort of thing. I have just picked it up as I go so the theory does not always make sense to me. I have managed to ripe the code out of my database and put it into a simple one. I will post it as an attachment for you to look at, but how do I do that? I can't seem to find a way to add it to this post... is there a button or something. Let me know and I will post it for you to look at.

Thanks for all you help so far.

OK thats really humble of you to say so... too many people including myself at times fail to reconcile themselves to that in favour of savouring themselves as right a lot of the time. So lets deal with this and see if we cannot crack this properly once we get a look at the thing. When you make a post you have a certain amount of time to go back in and edit your post (this provides for mistakes that you may have made. When you go back into edit you will see 'Attachments' click that and you can then upload your file. if you right click on your file before sending please send it aa a compressed zip folder. I can then extract it this end

Regards

Jim :)

[Jim Doherty]

OK thats really humble of you to say so... too many people including myself at times fail to reconcile themselves to that in favour of savouring themselves as right a lot of the time. So lets deal with this and see if we cannot crack this properly once we get a look at the thing. When you make a post you have a certain amount of time to go back in and edit your post (this provides for mistakes that you may have made. When you go back into edit you will see 'Attachments' click that and you can then upload your file. if you right click on your file before sending please send it aa a compressed zip folder. I can then extract it this end

Regards

Jim :)

Redbeard check your PM messages I have just sent you one look top of screen

Jim

[Redbeard]

Open the attachment. It should load up to the main form. There are 24 records as test. If you click on open keywords list it will open a list that will allow you to add or create new entry in each record or a new record. If you close that and open photo keyword search you can search by selecting item and using the buttons. Give it a try.

[Redbeard]

Here is the File,take a look!