473,729 Members | 2,148 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Search Form: multiple controls (Access 2003)

9 New Member
Hi all,

I'm not very conversant with the vocabulary of Access, so please ask for clarification if necessary...

I am trying to build a search form that allows combinations of search terms. For example, let's say I have three controls on my form; year, keyword, location. Entering a valid value for all three gives expected results from the dataset. However, I'd like to interpret a blank field in the search form as ignoring that constraint. The user should be able to enter values into any combination of form fields, e.g., entering only a year returns all records associated with that year, irrespective of any values in the other two fields. Entering a value in one of the other two fields refines the search result.

I've seen forms like this one:
http://www.mvps.org/access/forms/frm0045.htm

This looks like a great utility, but I can't get it to work in my db... launching the form populates the list of objects control, but clicking any button or object name yields a 'Variable not defined' error. I don't know enough about VB/A to troubleshoot this.

Other solutions I've seen around look very close, but require more understanding of the scripts than I have currently. Here is the query I'm starting with:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblCards01.serialNumber, tblCards01.barcode, tblCards01.cardTitle, tblCards01.cardKeywords, tblCards01.cardYear, tblCards01.locCity, tblCards01.locState, tblCards01.cardStory, tblCards01.luOrientation, tblCards01.luDimensions
  2. FROM tblCards01
  3. WHERE (((tblCards01.cardKeywords) Like "*" & Forms!frmQryKeyword!qryKeyword & "*") And ((tblCards01.luOrientation)=(Forms!frmQryKeyword!qryOrientation)) And ((tblCards01.cardYear)=(Forms!frmQryKeyword!qryYear)));
  4.  
Clearly this is a simple Logical AND setup, so I understand it requires a value to search on for each criterion. Not entering a value is interpreted literally - the query is looking for null values in the field.

So, I'm wondering 1) if there's something relatively simple I'm missing in setting up the query to interpret no value as meaning 'ignore this parameter', 2) is my approach all wrong, 3) does anyone know of a way to get the linked form to work, or of a competing utility that will provide similar functionality.

I do some scripting in other languages, but have precious little experience w/ VB. I'm willing to do some homework here, but I've got lots of other irons in the fire, and don't want to take a couple of courses to solve this issue. Having said that, I do appreciate any insights even if they include 'take a class' :)

Thanks!

-Scott
Feb 18 '07 #1
9 16097
christinamasalha
16 New Member
Dear Scott,

i used this code to create a search form on an application of mine, and it works great..... it will find the results to any number of criteria. All you need to do is customize the code to fit your application.


PLease fins below the article for the search from (i have copied the whole article for you so you can better understand the code before using it):
Expand|Select|Wrap|Line Numbers
  1. Experts, please don't answer this Q, this is being posted as a general tutorial question, but please feel free to add your comments and suggest any additions!
  2.  
  3. Q. We want to create a form for users to look up records in our table. Our table is called Customers and looks like this:
  4. CusID (Autonumber field)
  5. LastName (Text field)
  6. FirstName (Text field)
  7. Age (Number)
  8. Address (Text Field)
  9. Status (Text field, and it will only ever contain "Active", "Inactive" or "Prospect")
  10.  
  11. A. We have a form called SearchForm. It has 5 controls:
  12. * Two combo boxes called cboFindFirstName and cboFindLastName. These show all the unique first names from the customers table, and their RowSource properties are set to SQL like this: SELECT DISTINCT FirstName FROM Customers. We want the search form to find all records matching what We choose from these combo boxes.
  13.  
  14. * A text box called txtFindAge. We want to find everyone older than the number we put in here.
  15.  
  16. * An text box called txtFindAddress. We want to find addresses that contain any text we put in here. So if we put in "19 East", we want it to find "19 East Road" and "19 East Avenue"
  17.  
  18. * A multiselect list box called lstFindStatus which shows "Active", "Inactive" or "Prospect". We want to find records matching all the statuses we pick from this box. So if we choose both Active and Inactive, we want to find both active and inactive customers.
  19. If we leave any of these controls blank or we don't choose anything, we don't want it to search on that field.
  20.  
  21. One way of doing this is described here.
  22.  
  23. In order to achieve the above, we have to create a filtering string. We have a command button on the form, "Find records", called cmdFindRecords.
  24.  
  25. We need to put code in the OnClick event of this button. To do this, click on the command button in design view, and then click View->Properties on the menus. This brings up the Properties window. Click on the Event page, and click in the "On Click" row. A build button with "..." on it appears. Click on that button, and choose Code Builder.
  26.  
  27. A window with:
  28. Private Sub cmdFindRecords_Click()
  29.  
  30. End Sub
  31. appears. All our code goes in between those two lines.
  32.  
  33. First of all, we create a string variable to hold our filtering string, and set it to an empty string. We also need a variable to represent the items we've selected from the list box:
  34. ---
  35. Dim strFilter As String
  36. Dim varItem As Variant
  37.  
  38. strFilter=""
  39. ---
  40.  
  41. Now we check to see if the combo boxes are empty. If they aren't, we add them to the filter string:
  42. ---
  43. If Not IsNull(Me!cboFindFirstName) Then strFilter = strFilter & "[FirstName]=" Chr(34) & Me!cboFindFirstName & Chr(34) & " AND "
  44. If Not IsNull(Me!cboFindLastName) Then strFilter = strFilter & "[LastName]=" Chr(34) & Me!cboFindLastName & Chr(34) & " AND "
  45. ---
  46. Using Me!cboFindFirstName and Me!cboFindLastName gives us the values we've chosen in those combo boxes. We first check to see if they are empty by using the IsNull function (which gives us back True if they are). Because FirstName and LastName are text fields, we put quote characters around them in the string (this is what Chr(34) is). We could use apostrophes (') but we might have names like O'Sullivan, which would cause the filter string to be wrong so we use quotes instead.
  47.  
  48. Now we check if anything has been entered into the txtAge text box.
  49. ---
  50. If Not IsNull(Me!txtFindAge) Then strFilter = strFilter & "[Age]>" & Me!txtFindAge & " AND "
  51. ---
  52. Because Age is a number field, we don't need to put apostrophes or quote marks around the value.
  53.  
  54. The next thing to check is the address. We want to match lines containing what we've typed not exact matches, so we have to use the Like operator which lets us use wildcards:
  55. ---
  56. If Not IsNull(Me!txtFindAddress) Then strFilter = strFilter & "[Address] Like " Chr(34) & "*" & Me!txtFindAddress & "*" & Chr(34) & " AND "
  57. ---
  58.  
  59. The final thing to check is the multiselect list box. This is trickier because the user might have chosen more than one item. So we have to loop through the list box's items to see which ones have been selected:
  60. ---
  61. if Me!lstFindStatus.ItemsSelected.Count>0 Then
  62.    strFilter = strFilter & "("
  63.    For Each varItem In Me!lstFindStatus.ItemsSelected
  64.       strFilter = strFilter & "[Status]=" & Chr(34) & Me!txtFindStatus.ItemData(varItem) & Chr(34) & " OR "
  65.    Next
  66.    strFilter = Left$(strFilter, Len(strFilter)-4) & ") AND "
  67. End If
  68. ---
  69.  
  70. We begin the above code checking to see if anything has been selected, We then loop through each selected item and add it to the filtering string. We use OR here rather than AND because we want to find records that have a status of say, Active OR have a status of Inactive. After the loop finishes, we have a stray OR at the end, so we take that off and put a close bracket on the end.
  71.  
  72. Finally, we have to check to see if our filter string has anything in it at all (because someone might have clicked on Find Records but not typed anything in or chosen anything from the combos and listbox. If they've chosen or typed anything, our filter string will have a stray AND, so we have to take that off.
  73. ---
  74. If strFilter<>"" Then strFilter = Left$(strFilter,Len(strFilter)-5)
  75. ---
  76. So if the user has chosen "Smith" in the last name combo, "Joe" in the first name combo, typed "20" in the age box, "Main" in the address box and chosen "Active" and "Inactive" from the list box, our filter string looks like this:
  77. [FirstName]="Joe" AND [LastName]="Smith" AND [Age]>20 AND [Address] Like "*Main*" AND ([Status]="Active" OR [Status]="Inactive")
  78.  
  79. Now that we have our filtering string, what do we do with it ?
  80.  
  81. We can use it to open another form which shows all of the records from the Customers table. For example:
  82. ---
  83. DoCmd.OpenForm "frmCustomerDetails", , ,strFilter
  84. ---
  85. This opens frmCustomerDetails (which is based on the Customers table and would normally shows all customers) and applies the filter to only show records matching our criteria.
  86.  
  87. Or instead, we might have a subform inside our search form which shows all the customer details in a tabular format (a continuous or datasheet subform). This subform might be called frmSubCustomerDetails, and is held inside a subform control called subCustomers. We can use our filter string to set a  filter for the subform:
  88. ---
  89. If strFilter="" Then
  90.    Me!subCustomers.Form.FilterOn = False
  91. Else
  92.    Me!subCustomers.Form.Filter = strFilter
  93.    Me!subCustomers.Form.FilterOn = True
  94. End If
  95. ---
  96. This checks to see if our filter is empty. If it is, we just switch any existing filter on our subform off. Otherwise, we set the subform's filter to our string, and then switch the filter on.
  97.  
  98. Our completed code looks like this:
  99. ===================================
  100.  
  101. Private Sub cmdFindRecords_Click()
  102. Dim strFilter As String
  103. Dim varItem As Variant
  104.  
  105. strFilter=""
  106.  
  107. If Not IsNull(Me!cboFindFirstName) Then strFilter = strFilter & "[FirstName]=" Chr(34) & Me!cboFindFirstName & Chr(34) & " AND "
  108. If Not IsNull(Me!cboFindLastName) Then strFilter = strFilter & "[LastName]=" Chr(34) & Me!cboFindLastName & Chr(34) & " AND "
  109. If Not IsNull(Me!txtFindAge) Then strFilter = strFilter & "[Age]>" & Me!txtFindAge & " AND "
  110. If Not IsNull(Me!txtFindAddress) Then strFilter = strFilter & "[Address] Like " Chr(34) & "*" & Me!txtFindAddress & "*" & Chr(34) & " AND "
  111. if Me!lstFindStatus.ItemsSelected.Count>0 Then
  112.    strFilter = strFilter & "("
  113.    For Each varItem In Me!lstFindStatus.ItemsSelected
  114.       strFilter = strFilter & "[Status]=" & Chr(34) & Me!txtFindStatus.ItemData(varItem) & Chr(34) & " OR "
  115.    Next
  116.    strFilter = Left$(strFilter, Len(strFilter)-4) & ") AND "
  117. End If
  118.  
  119. If strFilter<>"" Then strFilter = Left$(strFilter,Len(strFilter)-5)
  120. DoCmd.OpenForm "frmCustomerDetails", , ,strFilter
  121. End Sub


Hope this helped,
Christina
Feb 18 '07 #2
MMcCarthy
14,534 Recognized Expert Moderator MVP
Hi Scott

Check out this tutorial on Example Filtering on a Form

Mary
Feb 18 '07 #3
lightning
9 New Member
Wow!

Thanks to both of you for great replies. I'm going to spend some time digesting these so I can find the right solution for me.

Christina - I've taken the liberty of beginning to convert that article to a PDF for easy reference. If you'd like it, let me know and I'll post it somewhere for you. It's only got basic formatting for the moment, but I can do some doctoring if you like.

Mary - I presume that thread you noted combines a query result with filtering? I'm not sure how to approach that method since I'm looking for a way to avoid a hierarchy in my query. Essentially, I need to leave out any search criteria that are not explicitly called for. Am I misunderstandin g the nature of that thread?
Feb 18 '07 #4
MMcCarthy
14,534 Recognized Expert Moderator MVP
Mary - I presume that thread you noted combines a query result with filtering? I'm not sure how to approach that method since I'm looking for a way to avoid a hierarchy in my query. Essentially, I need to leave out any search criteria that are not explicitly called for. Am I misunderstandin g the nature of that thread?
This kind of filtering usually work on the recordsource of the opening form.

Presumably you want your results in a form or datasheet. All you do is set up the form or datasheet to return all records without criteria. Then use the tutorial to set filtering criteria and open your form based on this criteria.

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "FormName", , , "Criteria"
Mary
Feb 18 '07 #5
lightning
9 New Member
Presumably you want your results in a form or datasheet. All you do is set up the form or datasheet to return all records without criteria. Then use the tutorial to set filtering criteria and open your form based on this criteria.
Gotcha.

Ok, I'm working through this approach, and have started with just a single search for a keyword. I have done this with a simple query, but am having trouble with this part of the script:

Expand|Select|Wrap|Line Numbers
  1. If Not IsNull(Me!txtFindTitle) Then strFilter = strFilter & "[cardTitle]=" Chr(34) & Me!txtFindTitle & Chr(34) & " AND "
  2.  
The error I get says "Compile Error. Expected: end of statement" and the Chr(34) is highlighted. Does this have something to do with the VB version, or did I just type something incorrectly?
Feb 18 '07 #6
MMcCarthy
14,534 Recognized Expert Moderator MVP
Gotcha.

Ok, I'm working through this approach, and have started with just a single search for a keyword. I have done this with a simple query, but am having trouble with this part of the script:

Expand|Select|Wrap|Line Numbers
  1. If Not IsNull(Me!txtFindTitle) Then strFilter = strFilter & "[cardTitle]=" Chr(34) & Me!txtFindTitle & Chr(34) & " AND "
  2.  
The error I get says "Compile Error. Expected: end of statement" and the Chr(34) is highlighted. Does this have something to do with the VB version, or did I just type something incorrectly?
You didn't use the & before the first Chr(34). Just use the single quotes, it's easier:

Expand|Select|Wrap|Line Numbers
  1. If Not IsNull(Me!txtFindTitle) Then 
  2.   strFilter = strFilter & "[cardTitle]='" & Me!txtFindTitle & "' AND "
  3.  
Also don't forget to remove the last "AND" in the build.

Mary
Feb 18 '07 #7
lightning
9 New Member
You didn't use the & before the first Chr(34).
Thanks again, Mary. I added &, but also declared a variable for Chr$(34) which seems to make everything happy.

For reference, here's what I ended up with:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdFindRecords_Click()
  2.  
  3. Dim strFilter As String
  4. Dim strQt As String
  5.  
  6. strFilter = ""
  7. strQt = Chr$(34)
  8.  
  9. If Not IsNull(Me!txtFindTitle) Then strFilter = strFilter & "[cardTitle]=" & strQt & "*" & Me!txtFindTitle & "*" & strQt & " AND "
  10. If Not IsNull(Me!txtFindKeyword) Then strFilter = strFilter & "[cardKeywords]Like" & strQt & "*" & Me!txtFindKeyword & "*" & strQt & " AND "
  11. If Not IsNull(Me!cboOrientation) Then strFilter = strFilter & "[luOrientation]=" & strQt & Me!cboOrientation & strQt & " AND "
  12. If Not IsNull(Me!cboFindYear) Then strFilter = strFilter & "[cardYear]=" & Me!cboFindYear
  13.  
  14. 'End If
  15.  
  16. If strFilter <> "" Then strFilter = Left$(strFilter, Len(strFilter) - 5)
  17. DoCmd.OpenForm "frmSearchResults", , , strFilter
  18.  
  19. End Sub
  20.  
This search allows for keywords (from a memo field), title search, and selection based on distinct entries (image orientation and year of publication). The purpose of this db is to cull images for printing from a template, so I needed a variety of selection options, but also to restrict possible combinations that are incompatible (two orientations, for example, because each orientation uses a different template). Possible enhancements to this are allowing multiple years, and adding more keywords - right now, you can only enter a single search term in each of the title and keyword boxes.

Thanks very much for all the help!

My next step is to export the results to a tab-delimited text file, which I'll ask about in a new thread.

-A very appreciative n00b (Scott)
Feb 18 '07 #8
MMcCarthy
14,534 Recognized Expert Moderator MVP
Thanks for posting the final solution.

When you get into dealing with multiple values you can use multi select listboxes and access them for the build in a for...loop as per this example.

Mary
Feb 18 '07 #9
NeoPa
32,569 Recognized Expert Moderator MVP
A hijack post has been split across to Search Form. Interested parties can follow the progress there.
Aug 6 '10 #10

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

Similar topics

1
2701
by: BillK | last post by:
Hi - I am looking for a solution to a clients requirement. They want to distribute a database of info on CD, which user can search on selected fields and results will be displayed. The data and database MUST be encrypted and be displayed on-screen in a format that cannot be 'Copy/Pasted'. Apart from the search results displayed on-screen, user must NOT be able to extract multiple records to use for Spamming. Database about 1/2 million...
10
1886
by: Ed | last post by:
Hoping someone an assist me urgently. I would truly appreciate and help. I have a form and prices are based on 'price break'. 1.The price break fields work fine, but I cannot for the life of me add more than one to a page. 2. Also need to add the two results to a 'total' field. (See code attached). Many thanks in advance.
25
10255
by: Lyn | last post by:
Hi, I am working on a genealogy form. The only table (so far) lists everybody in the family, one record per person. Each record has an autonum ID. The parent form (frmMainForm) displays the data in each record, which includes the ID of the father and the mother (who also have records in the table). One record per form. I have a Tab Control in the form, and in one of the tabs I have a subform (sfmSiblings) in which I wish to list...
4
3174
by: Kathy | last post by:
What is the standard technique for handling the fields in the following scenario on a continuous form? Multiple Divisions. Each Division has multiple Buildings. Each Building has a Supervisor. Tables: (abbreviated) TblDivision DivisionID
16
7233
by: TD | last post by:
This is the code under a command button - Dim ctl As Control For Each ctl In Me.Controls If ctl.BackColor <> RGB(255, 255, 255) Then ctl.BackColor = RGB(255, 255, 255) End If Next ctl
3
2184
by: DotNetNewbie | last post by:
I am reading the book Teach Yourself Microsoft Visual Basic .Net 2003 in 21 Days. I am having trouble getting one of the exercises to work at the end of day 4. Exercises: 1. Create a new multiple-form application that displays two forms when it starts, each with a text box. Add code to each text box’s TextChanged event (this is the
2
7249
by: Homey! | last post by:
Hello all I am new to Access. I have imported data from an old FoxPro 2.x database. This is probably the most basic function but I cant get a search box to work. I need to search for company name and cant figure it out in access. Tony (homey)
1
11697
by: atl10spro | last post by:
Hello Everyone, I am new to MS Access and although I have created several different databases I lack the VB knowledge to code a search function. I am turning to your expertise for assistance. I am using MS Access 2003. This is what I am looking for: A text field for the user to enter the search string or keyword.
8
36135
by: hoofbeats95 | last post by:
I don't think this should be this complicated, but I can't figure it out. I've worked with C# for several years now, but in a web environment, not with windows form. I have a form with a query button on it. If the query returns multiple results, a new window is opened with a grid containing the results. When the user double clicks on the desired row in the grid, I want the first form to populate with the correct data. I don't know how...
0
8911
marktang
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...
0
8760
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9280
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8140
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
4525
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...
0
4795
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3238
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
2
2673
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2162
bsmnconsultancy
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...

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.