By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,191 Members | 1,064 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,191 IT Pros & Developers. It's quick & easy.

Search results subform - display the criteria used

P: 41
I have an unbound form with text and combo boxes for entering search criteria . When Search button is clicked it builds a sql statement and assigns it to the recordsource of a subform.
The subform has in the footer a textbox that has ="found " & (Count([RecipeName])) & " records matching search criteria" as its controlsource.
I want to add to this by including what search criteria has been used to get these results. I don't want it to display anything for the text/combo boxes that have been left blank.
I will then shift it to the header section so I can print the subform with the results and criteria used.
I am a self-taught access newbie (can you tell..lol). You can see the code that makes up the sql in my previous post.
Thanks for any help.
Mar 20 '07 #1
Share this Question
Share on Google+
10 Replies


NeoPa
Expert Mod 15k+
P: 31,494
I have an unbound form with text and combo boxes for entering search criteria . When Search button is clicked it builds a sql statement and assigns it to the recordsource of a subform.
The subform has in the footer a textbox that has ="found " & (Count([RecipeName])) & " records matching search criteria" as its controlsource.
So far, so good. All making sense up to here.
I want to add to this by including what search criteria has been used to get these results. I don't want it to display anything for the text/combo boxes that have been left blank.
Two sentences which leave me wondering what you're talking about.
I will then shift it to the header section so I can print the subform with the results and criteria used.
This may or may not be possible depending on what it is (Obviously a Footer comes after the data and a Header before, so we'll have to see when we have a better understanding of what you're about.)
You can see the code that makes up the sql in my previous post.
How, without a link?
Please add a link if you think the code is important. It wouldn't hurt anyway I suppose, but if your code is working as you require, I can't see that it's going to add much to this.
Mar 21 '07 #2

P: 41
Hi NeoPa, thanks for the reply.
Two sentences which leave me wondering what you're talking about.
Hehe..you and me both when it comes to access I confuse myself all the time!
I will then shift it to the header section so I can print the subform with the results and criteria used.
Sorry I guess this part is irrelevant really, I was trying to explain why I was trying to do what I was trying to do.
I seem to have solved it, I'm building the criteria expression up in plain english when I build the sql, this will become my heading for the search results. I have changed from using a subform for the results, to opening the form on its own instead (for various irrelevant reasons). Thought I'd post my code in case it helps anyone else:
Expand|Select|Wrap|Line Numbers
  1. Private Sub CommandSearch_Click()
  2.  
  3. Dim strWhere As String
  4. Dim lngLen As Long
  5. Dim lngLen2 As Long
  6. Dim strDesc As String
  7.  
  8. strDesc = "Recipes "
  9.  
  10. If Not IsNull(Me.FiltRecName) Then
  11. strWhere = strWhere & "([RecipeName] like ""*" & Me.FiltRecName & "*"") And "
  12. strDesc = strDesc & "with name containing '" & (Me.FiltRecName) & "' and "
  13. End If
  14.  
  15. If Not IsNull(Me.FiltCat) Then
  16. strWhere = strWhere & "([FoodCategory] = """ & Me.FiltCat & """) And "
  17. strDesc = strDesc & "in " & (Me.FiltCat) & " category and "
  18. End If
  19.  
  20. If Not IsNull(Me.FiltCost) Then
  21. strWhere = strWhere & "([SumOfIngredCost] < " & Me.FiltCost & ") And "
  22. strDesc = strDesc & "costing less than " & (Format(Me.FiltCost, "currency")) & " and "
  23. End If
  24.  
  25. If Not IsNull(Me.FiltIng1) Then
  26. strWhere = strWhere & "([RecipeID]in (SELECT [RecipeID] FROM tblRecipeIngredients WHERE[Ingredient] = """ & Me.FiltIng1 & """)) And "
  27. strDesc = strDesc & "which include " & (Me.FiltIng1) & " and "
  28. End If
  29.  
  30. If Not IsNull(Me.FiltIng2) Then
  31. strWhere = strWhere & "([RecipeID] not in (SELECT [RecipeID] FROM tblRecipeIngredients WHERE[Ingredient] = """ & Me.FiltIng2 & """)) And "
  32. strDesc = strDesc & "which do not include " & (Me.FiltIng2) & " and "
  33. End If
  34.  
  35. strDesc = strDesc & ":"
  36.  
  37. 'Apply the filter
  38. lngLen = Len(strWhere) - 5 'Without trailing " AND "
  39. lngLen2 = Len(strDesc) - 5
  40. If lngLen <= 0 Then
  41. MsgBox "No criteria."
  42. Else
  43. Me!msg1.Visible = False 'search instructions message
  44. Me!msg2.Visible = True 'Searching..Please wait message
  45.  
  46. DoCmd.OpenForm "frmResults", , , , acFormReadOnly, acHidden
  47. Forms!frmResults.RecordSource = "SELECT DISTINCT Query2.RecipeID, Query2.RecipeName, Query2.FoodCategory, Query2.SumOfIngredCost FROM Query2 WHERE " & (Left(strWhere, lngLen))
  48. Forms!frmResults!Description = (Left(strDesc, lngLen2))
  49. Forms!frmResults.Visible = True
  50.  
  51. Me!msg2.Visible = False
  52. Me!msg1.Visible = True
  53. End If
  54.  
  55. strWhere = ""
  56.  
  57. End Sub
Thanks again,
Jenny
Mar 21 '07 #3

P: 41
Oh dear...I haven't got it sussed how to insert the quotes properly! (Originally posted by..is blank)
Sorry.
Mar 21 '07 #4

Denburt
Expert 100+
P: 1,356
Looks good Jenny just an idea something you may want to consider is using a combo box that list the field names that are in a query then you can add or remove them on the fly (in the query) and you can choose the field you want to search by. After the first combo box is updated it builds the sql for the second Combo box which is the one that I search by (I usually place one above the other). This allows me to have 3-4 options to search by and the code is dynamic so I can throw it into almost any database and with minimal changes I am up and running.

Expand|Select|Wrap|Line Numbers
  1. Private Sub FieldList0_AfterUpdate()
  2. If IsNull(Me.ActiveControl) Or Trim(Me.ActiveControl) = "" Then Exit Sub
  3. MySearch Me, Me.ActiveControl, "Search0"
  4. End Sub
  5.  
Expand|Select|Wrap|Line Numbers
  1. Public Function MySearch(frm As Form, FList As String, SrchBx As String)
  2. 'On Error GoTo Err
  3. Dim QDef As QueryDef
  4. Dim myDB As Database
  5. Dim myField As Field
  6. Dim ctl As Control
  7. Set ctl = frm(SrchBx)
  8. Set myDB = CurrentDb
  9.  
  10. Set QDef = myDB.QueryDefs("frm.ActiveControl.RowSource")
  11. Set myField = QDef.Fields(FList)
  12. frm(SrchBx) = ""
  13. frm(SrchBx).RowSource = "Select " & myField.SourceField & " From " & myField.SourceTable & " Group By " & myField.SourceField
  14. frm(SrchBx).Requery
  15.  
  16. Set myField = Nothing
  17. Set myDB = Nothing
  18. Set QDef = Nothing
  19.  
  20. End Function
  21.  
The second portion or the actual search is still in a somewhat developemental stage but it is currently working in about 5 db's right now if you are interested let me know hopefully this gave you some things to think about.
Mar 21 '07 #5

NeoPa
Expert Mod 15k+
P: 31,494
Firstly, don't miss Denburt's post just because it's not the latest one in the list. It's worth a read.
Oh dear...I haven't got it sussed how to insert the quotes properly! (Originally posted by..is blank)
Sorry.
No problem. The syntax for quoting (Ignore the spaces I've included so it DOESN'T work) is one of :
  1. [ quote ] ... [ /quote ] - This gives the results you had.
  2. [ quote=name ] ... [ /quote ] - This shows the name of the person you're intending to quote.
Mar 22 '07 #6

P: 41
[ quote=name ] ... [ /quote ] - This shows the name of the person you're intending to quote.
Thanks for this NeoPa..I wouldn't have figured it out!

Thank-you too Denburt for your search code. I can only hope that one day I am able to understand enough to write/use code like you experts do. I can sort of see what it does, but I'm not sure how it would work with what I want to do..
This allows me to have 3-4 options to search by
Can it search by any combination of the options, or only one? I want to search by name and/or category and/or cost etc.
I am happy that the code I already have works, but I am also keen to learn the best way to do things.
Mar 22 '07 #7

NeoPa
Expert Mod 15k+
P: 31,494
Thanks for this NeoPa..I wouldn't have figured it out!
No problem. The trick's the same as for using the wizards.
Get the wizard or the system somehow to create it for you automatically (in this case click on the reply button next to a post), then see what it creates for you. That's how I started in VBA and the old Excel macro language. I got it to do the basics for me (by recording a macro of what I needed), then built on from there, after examining the code it created for me automatically. It's an idea I've used many times and it works well.
Mar 23 '07 #8

P: 41
Ah now I see the difference between the reply and post reply button under it..personally I prefer when the replies in the forums don't repeat all the posts in the thread but thats me.
And using the wizard as an example is a great idea, thats how I started my database - by creating one with the wizard, looking at how it was set up then figuring out how to do it myself. Time consuming but worth it. And of course I have done a lot of googling and searching the forum ;). Thanks again for all the help.
Jenny
Mar 23 '07 #9

NeoPa
Expert Mod 15k+
P: 31,494
I agree about repeating all the posts. Hopefully the new layout, when it comes, will make a simple post easier to do. I tend only to quote a post when it is NOT the most recent, or I may sometimes quote a part of a post to highlight what my response refers to. Otherwise, when I know it follows on from the post I'm replying to, I clear away the quote part.
Mar 23 '07 #10

Denburt
Expert 100+
P: 1,356
Thank-you too Denburt for your search code. I can only hope that one day I am able to understand enough to write/use code like you experts do. I can sort of see what it does, but I'm not sure how it would work with what I want to do..
You are quite welcome, your code doesn't look bad and is very similar to something I recently wrote for the same reason and even looks somewhat similar. :)

Can it search by any combination of the options, or only one? I want to search by name and/or category and/or cost etc.
Yes I can put multiples say 3-4 ingredients and pull them all at once. I can put in an ingredient per say and say a serving size etc. If I put two dates it will pull between those dates, I need to add a message for that in case they put three dates right now it skips the third one. [evil laugh]

As I said it was written recently so I have only tested it out on several databases and it does have a couple of kinks to work out. I am currently changing it over to a local table storing the criteria, source table information, and source field info. The reason for this is so I can have a subform in a tab control with as many bits of criteria I want included and as many as I want excluded (using a hidden check box) thus saving the search for the next use complete with delete button of course.

I am happy that the code I already have works, but I am also keen to learn the best way to do things.
I am quite glad your code works as well and I am glad you like learning I learn new things every day and I love it. I also enjoy the dynamics of dynamic coding. :)
Mar 23 '07 #11

Post your reply

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