Search results subform - display the criteria used

[KiwiGenie]

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.

[NeoPa]

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.

[KiwiGenie]

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:

Private Sub CommandSearch_Click()
 
Dim strWhere As String
Dim lngLen As Long
Dim lngLen2 As Long
Dim strDesc As String
 
strDesc = "Recipes "
 
If Not IsNull(Me.FiltRecName) Then
strWhere = strWhere & "([RecipeName] like ""*" & Me.FiltRecName & "*"") And "
strDesc = strDesc & "with name containing '" & (Me.FiltRecName) & "' and "
End If
 
If Not IsNull(Me.FiltCat) Then
strWhere = strWhere & "([FoodCategory] = """ & Me.FiltCat & """) And "
strDesc = strDesc & "in " & (Me.FiltCat) & " category and "
End If
 
If Not IsNull(Me.FiltCost) Then
strWhere = strWhere & "([SumOfIngredCost] < " & Me.FiltCost & ") And "
strDesc = strDesc & "costing less than " & (Format(Me.FiltCost, "currency")) & " and "
End If
 
If Not IsNull(Me.FiltIng1) Then
strWhere = strWhere & "([RecipeID]in (SELECT [RecipeID] FROM tblRecipeIngredients WHERE[Ingredient] = """ & Me.FiltIng1 & """)) And "
strDesc = strDesc & "which include " & (Me.FiltIng1) & " and "
End If
 
If Not IsNull(Me.FiltIng2) Then
strWhere = strWhere & "([RecipeID] not in (SELECT [RecipeID] FROM tblRecipeIngredients WHERE[Ingredient] = """ & Me.FiltIng2 & """)) And "
strDesc = strDesc & "which do not include " & (Me.FiltIng2) & " and "
End If
 
strDesc = strDesc & ":"
 
'Apply the filter
lngLen = Len(strWhere) - 5 'Without trailing " AND "
lngLen2 = Len(strDesc) - 5
If lngLen <= 0 Then
MsgBox "No criteria."
Else
Me!msg1.Visible = False 'search instructions message
Me!msg2.Visible = True 'Searching..Please wait message
 
DoCmd.OpenForm "frmResults", , , , acFormReadOnly, acHidden
Forms!frmResults.RecordSource = "SELECT DISTINCT Query2.RecipeID, Query2.RecipeName, Query2.FoodCategory, Query2.SumOfIngredCost FROM Query2 WHERE " & (Left(strWhere, lngLen))
Forms!frmResults!Description = (Left(strDesc, lngLen2))
Forms!frmResults.Visible = True
 
Me!msg2.Visible = False
Me!msg1.Visible = True
End If
 
strWhere = ""
 
End Sub

Thanks again,
Jenny

[KiwiGenie]

Oh dear...I haven't got it sussed how to insert the quotes properly! (Originally posted by..is blank)
Sorry.

[Denburt]

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.

Private Sub FieldList0_AfterUpdate()
If IsNull(Me.ActiveControl) Or Trim(Me.ActiveControl) = "" Then Exit Sub
MySearch Me, Me.ActiveControl, "Search0"
End Sub
 

Public Function MySearch(frm As Form, FList As String, SrchBx As String)
'On Error GoTo Err
Dim QDef As QueryDef
Dim myDB As Database
Dim myField As Field
Dim ctl As Control
Set ctl = frm(SrchBx)
Set myDB = CurrentDb
 
Set QDef = myDB.QueryDefs("frm.ActiveControl.RowSource")
Set myField = QDef.Fields(FList)
frm(SrchBx) = ""
frm(SrchBx).RowSource = "Select " & myField.SourceField & " From " & myField.SourceTable & " Group By " & myField.SourceField
frm(SrchBx).Requery
 
Set myField = Nothing
Set myDB = Nothing
Set QDef = Nothing
 
End Function
 

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.

[NeoPa]

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.

[KiwiGenie]

[ 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.

[NeoPa]

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.

[KiwiGenie]

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

[NeoPa]

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.

[Denburt]

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. :)