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.
10 2163 NeoPa 32,568
Recognized Expert Moderator MVP
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.
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
Oh dear...I haven't got it sussed how to insert the quotes properly! (Originally posted by..is blank)
Sorry.
Denburt 1,356
Recognized Expert Top Contributor
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 32,568
Recognized Expert Moderator MVP
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 : - [ quote ] ... [ /quote ] - This gives the results you had.
- [ quote=name ] ... [ /quote ] - This shows the name of the person you're intending to quote.
[ 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 32,568
Recognized Expert Moderator MVP
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.
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 32,568
Recognized Expert Moderator MVP
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.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Alan |
last post by:
I'm just about to start a project that needs to combine the results of a SQL
Server query with the results of an Index Server query. The basic idea is
that the user enters/selects a bunch of search criteria on a form. Most of
the criteria selected by the user will be used to select records from the
database - standard WHERE clause stuff - but the user can also enter
free-text that should be searched for in associated uploaded documents. The...
|
by: N. Graves |
last post by:
Hi,
I want to have a Search Dialog box that has several text box and
fields to build a search and display the results in a form. I can do
everything that I need to if I us a report but I would like to have
the search from data displayed in a form.
The structure that I have for this was take from the Asset Manger from
MS. Anyway I open a report that in turn opens up a search form that
allow me to select data. Then you continue by...
|
by: Jules |
last post by:
Hi:
I have an Access 97 Search form set up - a couple of combo boxes, a
couple of text fields and a command button. I want the command button
to run an SQL script and then open the results form. I can get the
button to open the other form but I can't get it to run the module
with the subroutine in it. This is my first sorti into programming
Access so be kind - I have some SQL and ASP experience but these
modules are a problem for me.
|
by: misschristalee |
last post by:
I'm having a brain blockage day....
Scenario:
Search Form with 6 text boxes
Query has same six fields
Each has this IIF: IIf(IsNull(!!),"",!!)
with each dictating the correct text box of course.
SQL dictates... If text box 1 isNull do nothing or do this OR if text
|
by: tdmailbox |
last post by:
I have a form with a child form. In the child form there is a list of
names that can grow quite large. On the parent form I want to display
the first name from the child form. I set up a test box that is
populated with the code
=subfrm_media_review_sec_party.Form!first_name & " " &
subfrm_media_review_sec_party.Form!last_name
It works except that when I flip through the names it populates the
parent form with the name of what ever...
| |
by: Steph |
last post by:
Hi.
I'm very new to MS Access and have been presented with an Access database
of contacts by my employer.
I am trying to redesign the main form of the database so that a button
entitled 'search' may be clicked on by the user and the user can then
search all records by postcode. I want to do this to prevent duplicate
data entry.
|
by: visionstate |
last post by:
Hi there,
I'm fairly new to access and the way it works and I have been building
a relatively simple database.
It is basically a database where staff members can search for what
training they (or others) have completed by staff name, staff group or
training name.
I created this using a form which had 2 text boxes and a combo box.
These were used to search a query and the results were displayed in a
sub form within the main form.
The...
|
by: Chris |
last post by:
I need a search form to perform. I have a Form and a subform. The
subform is based off a query and the main form is unbound. The query
runs perfect, however, I have a command button in the main form with
the code:
Private Sub Command12_Click()
Me.Refresh
End Sub
That command button is not working and gives me the prompt, "Error
|
by: mforema |
last post by:
Hi Everyone,
I want to search records by typing in multiple keywords. I currently have a search form. It has a combo box, text box, Search command button, and a subform. The combo box lists the names of the fields found in my subform. The search form is supposed to allow a user to choose which field he/she wants to search by and then type a keyword(s) in the text box. The subform should display the filtered results.
My problem occurs...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |