473,320 Members | 2,133 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Re-Filtering on a current form

112 100+
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.

Expand|Select|Wrap|Line Numbers
  1.  Private Sub Search_Click()
  2. Dim strOr As String
  3. Dim strDoc As String
  4. Dim varSelect As Variant
  5. Dim ctl As Control
  6.  
  7. strDoc = "frmDescription"
  8.  
  9.     Set ctl = Forms!frmPhotoKeywordSearch.Controls("list1")
  10.         For Each varSelect In ctl.ItemsSelected
  11.         strOr = strOr & "[Photo Keywords] Like '*" & ctl.ItemData(varSelect) & "*' OR "
  12.         Next varSelect
  13.     Set ctl = Forms!frmPhotoKeywordSearch.Controls("list2")
  14.         For Each varSelect In ctl.ItemsSelected
  15.         strOr = strOr & "[Photo Keywords] Like '*" & ctl.ItemData(varSelect) & "*' OR "
  16.         Next varSelect
  17.     Set ctl = Forms!frmPhotoKeywordSearch.Controls("list3")
  18.         For Each varSelect In ctl.ItemsSelected
  19.         strOr = strOr & "[Photo Keywords] Like '*" & ctl.ItemData(varSelect) & "*' OR "
  20.         Next varSelect
  21.     Set ctl = Forms!frmPhotoKeywordSearch.Controls("list4")
  22.         For Each varSelect In ctl.ItemsSelected
  23.         strOr = strOr & "[Photo Keywords] Like '*" & ctl.ItemData(varSelect) & "*' OR "
  24.         Next varSelect
  25.     Set ctl = Forms!frmPhotoKeywordSearch.Controls("list5")
  26.         For Each varSelect In ctl.ItemsSelected
  27.         strOr = strOr & "[Photo Keywords] Like '*" & ctl.ItemData(varSelect) & "*' OR "
  28.         Next varSelect
  29.     Set ctl = Forms!frmPhotoKeywordSearch.Controls("list6")
  30.         For Each varSelect In ctl.ItemsSelected
  31.         strOr = strOr & "[Photo Keywords] Like '*" & ctl.ItemData(varSelect) & "*' OR "
  32.         Next varSelect
  33.     Set ctl = Forms!frmPhotoKeywordSearch.Controls("list7")
  34.         For Each varSelect In ctl.ItemsSelected
  35.         strOr = strOr & "[Photo Keywords] Like '*" & ctl.ItemData(varSelect) & "*' OR "
  36.         Next varSelect
  37.     '   lose the last ' OR '
  38.     strOr = Left(strOr, Len(strOr) - 4)
  39.  
  40. DoCmd.OpenForm strDoc, acNormal, , strOr
  41. DoCmd.Close acForm, "frmPhotoKeywordSearch"
  42.  
  43. End Sub 
Feb 7 '08 #1
10 1759
Jim Doherty
897 Expert 512MB
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 :)
Attached Files
File Type: zip redbeard.zip (24.7 KB, 158 views)
Feb 7 '08 #2
Redbeard
112 100+
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.
Feb 7 '08 #3
Jim Doherty
897 Expert 512MB
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 :)
Feb 7 '08 #4
Redbeard
112 100+
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

Expand|Select|Wrap|Line Numbers
  1. 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
Feb 8 '08 #5
Jim Doherty
897 Expert 512MB
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

Expand|Select|Wrap|Line Numbers
  1. 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

Expand|Select|Wrap|Line Numbers
  1. 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 :)
Feb 9 '08 #6
Redbeard
112 100+
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.
Feb 9 '08 #7
Jim Doherty
897 Expert 512MB
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 :)
Feb 9 '08 #8
Jim Doherty
897 Expert 512MB
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
Feb 9 '08 #9
Redbeard
112 100+
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.
Feb 9 '08 #10
Redbeard
112 100+
Here is the File,take a look!
Attached Files
File Type: zip Photo Key Word.zip (73.9 KB, 86 views)
Feb 11 '08 #11

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Nel | last post by:
I have a question related to the "security" issues posed by Globals ON. It is good programming technique IMO to initialise variables, even if it's just $foo = 0; $bar = ""; Surely it would...
4
by: Craig Bailey | last post by:
Anyone recommend a good script editor for Mac OS X? Just finished a 4-day PHP class in front of a Windows machine, and liked the editor we used. Don't recall the name, but it gave line numbers as...
1
by: Chris | last post by:
Sorry to post so much code all at once but I'm banging my head against the wall trying to get this to work! Does anyone have any idea where I'm going wrong? Thanks in advance and sorry again...
4
by: Alan Walkington | last post by:
Folks: How can I get an /exec'ed/ process to run in the background on an XP box? I have a monitor-like process which I am starting as 'exec("something.exe");' and, of course the exec function...
1
by: John Ryan | last post by:
What PHP code would I use to check if submitted sites to my directory actually exist?? I want to use something that can return the server code to me, ie HTTP 300 OK, or whatever. Can I do this with...
10
by: James | last post by:
What is the best method for creating a Web Page that uses both PHP and HTML ? <HTML> BLA BLA BLA BLA BLA
8
by: Lothar Scholz | last post by:
Because PHP5 does not include the mysql extension any more is there a chance that we will see more Providers offering webspace with Firebird or Postgres Databases ? What is your opinion ? I must...
1
by: joost | last post by:
Hello, I'm kind of new to mySQL but more used to Sybase/PHP What is illegal about this query or can i not use combined query's in mySQL? DELETE FROM manufacturers WHERE manufacturers_id ...
3
by: presspley | last post by:
I have bought the book on advanced dreamweaver and PHP recently. I have installed MySQL and PHP server but am getting an error on the $GET statement show below. It says there is a problem with...
1
by: Brian | last post by:
I have an array like this: $events = array( array( '2003-07-01', 'Event Title 1', '1' //ID Number (not unique) ), array( '2003-07-02',
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.