Easy Search Criteria

Easy Search Criteria

More years ago than I care to remember, I created this Search Form. It was one of my first Adventures into MS Access VBA. Later, when I was looking for a subject which I could use as a way of introducing people into the world of VBA, it was this Search Form that came to mind. The reason being was that the Search Form was the result of iteration after iteration of learning. The process of building this Search Form raised the level of my VBA skills from If Then Statements, Select Case Statements, to using Object Oriented Programming Techniques. Then an OP posted a question which was basically a humongous cumbersome SQL Statement used for searching. As I tendered my help and advice to help the OP shape this SQL Statement into something useful, I realised that it offered an excellent vehicle as an educational tool for others. I never realised my teaching adventure would lead me to create 3 YouTube playlists!

Easy Search Criteria 1

Easy Search Criteria 1

Video 1 (5:03)

This is a demo of how easy it is to add new Search Fields to this Search Form.

LAST VIDEO! – This is actually the last video! The next two video’s lead to this video. I put this video at the front to whet your appetite so to speak, so you can see where it’s all heading!

Easy Search Criteria 1 - Bullet Points (a)

00:13 this isn’t the same form, this is something completely different!
00:30 what’s special about this form is the ability to quickly and easily add another textbox and Option Group
00:43 open the form in design view
00:48 copy & paste the text box an option group
01:12 copy “City”
01:25 open the property sheet and paste “City” into the “Tag Property”
01:38 do the same with the Option Group
02:06 enter search criteria “22” into City

Video 1 (5:03)

Nifty Access YouTube Thumb Nail
Play Video

Easy Search Criteria 1 - Bullet Points (b)

02:24 let’s examine the SQL Statement return by the message box
02:44 you can see that the fields “Surname” and “City” are selected in the SQL Statement
03:00 notice the different positions of the Asterisks
03:20 now let’s add another field “State”
04:25 this is a demo of an “Easy Search Form” utilising object oriented practices

… …

Easy Search Criteria 2

Easy Search Criteria 2

Video 1 (8:42)

In this video I demonstrate how to turn a set of functions into an Object Orientated approach.

Easy Search Criteria 2 - Bullet Points (a)

00:05 in an If Statement we asked the question, is it an empty String is it NULL? For each and every text box!
00:28 instead of interrogating every TextBox individually, we can use an Object Orientated method, and look at Each Control in turn, using just One Function!
00:38 insert a new Function template from the Insert / Insert File Command
00:50 this allows you to utilise consistent Error Handling Code without any effort!
01:20 insert the code stub for a for next loop, again utilising the Insert / Insert File Command
01:40 limit the controls checked to just text boxes
02:40 the object is to Run 2 different SQL statements depending on whether the TextBox’s contain text or not
02:56 message box showing the two different SQL Statements required
03:18 the new method uses the ability of MS Access to loop through a set of Controls and test them each in turn, with a for next loop
03:30 how do we know how many Controls we need to check? We count them
04:20 we test each Control with an If Statement like this — “If Ctrl = “” Or IsNull(Ctrl) Then

Video 1 (8:42)

Nifty Access YouTube Thumb Nail
Play Video

Easy Search Criteria 2 - Bullet Points (b)

04:25 we have to return the Controls name like this MsgBox  & Ctrl.Name because the control portion “Ctrl” is an object and can’t be passed into the message box because the message box will only accept a string. You extract the string name from the object “Ctrl”
05:15 if we count all the Controls and compare the number with the number that have text in, this will enable us to decide whether to use the where clause or not
06:43 we cannot just call the Function, we have to place show the answer the Function gives (true or false) in a message box
07:52 we have replaced the Old method of interrogating individual Controls with the new method of interrogating the Controls in the Collection
08:05 walkthrough of how the function works

… …

Easy Search Criteria 3

Easy Search Criteria 3

Video 1 (13:23)

Once you identify a pattern in your code, once you notice that you have several functions that essentially do the same thing, then it’s a good idea, (and satisfying) to Turn your multiple Functions into one! It is one of the main goals of any developer! This is an excellent example of what to look for and how to implement it in VBA.

Now you might like to go back to video 1, and look at it again, as it’s actually THE LAST VIDEO! It shows you what all this coding has achieved!

Easy Search Criteria 3 - Bullet Points (a)

00:12  recap of the new function
00:15 For Each Ctrl – “for each control” – consider the form a container holding ALL of the Controls. This statement allows you to take each control in turn, examine it, move on to the next control…
00:30 this technique is very useful especially when you have lots of controls. You can add more controls without having to change any code!
00:43 again we have a repeating pattern, a good indication that there is an opportunity to create an object orientated approach
00:55 if you see a pattern, then it is likely you will be able to use some sort of loop code structure instead
01:00 it looks like all the bits we need to form a loop exist
01:34 the Textbox and the Option group operate together. We need some way of making them a matched pair
02:22 we can match them by adding information in the tag property
02:40 now we have a way of associating the two controls together
03:18 the only piece of information that was missing is the field name but now have it because not only are we using it to match the two controls together we use it to identify the pair of controls that relate to the field in the underlying record set
03:35 Do a Video on Inserting Code from the Text File Store
04:14 I pulled together all the bits on I need to start this new function the function will iterate through ALL the text boxes on the form we want to find the contents of the tag property of each textbox
04:23 the tag should contain the name of the field you want to query with the SQL statement
04:26 take the field name extracted and match it against the option group. The option selected determines the criteria for the SQL statement
04:44 the “For Each Loop” inspects each control in turn so we can look at each controls tag property with VBA code like this:- Ctrl.Tag

Video 1 (13:23)

Nifty Access YouTube Thumb Nail
Play Video

Easy Search Criteria 3 - Bullet Points (b)

05:30 it found “Surname” but did not find anything in the tag properties of the other two text boxes
05:50 in this section the tag properties of the other two text boxes are updated
06:15 good description here of how the Option Group can be used to provide the integer 1 through 5
06:52 develop the new function “fOptSelected” (function option selected)
07:24 feed the field name extracted from the Tag property into the function “fOptSelected” as a parameter. The function then uses the field name to find the option selected
07:40 in this function restrict the objects tested to option groups
08:09 extract the value selected by the option group and pass it back into the function “fOptSelected”
09:00 back in the other function “fXXXXX” we use the field name extracted from the textbox tag property and feed it into the function “fOptSelected” to obtain the option selected
09:50 we’ve got all the information we need to allow us to build a part of the SQL string Where Clause
10:30 to prove that we can obtain all the information required we use a message box demonstrate this
11:05 using the function developed earlier “fSearchFld” along with the newly extracted information we can now build the SQL string with one function in one place instead of using three functions.
12:13 rename the function, remove the message box and return the result to the function. Have a general tidy-up of the code
12:49 now we can remove the three functions and replace the 3 with just one function.
13:00 it works!

…. ….

More Useful Stuff HERE:-

Leave a comment

two + 15 =

This website uses third-party software - WordPress Add-Ins to be exact. I don't know what any individual add-in does, but I'm sure that many of them collect information about you. So be aware, if you continue using this site, then you are likely to be sharing your information. I don't know how to disable this sharing for any, or all of the plugins for individual users. So I can't stop the sharing of information. If this worries you then please do not use this site... If you continue to use this site I will assume that you are happy with it.

Do you need a hand in the right direction?

You are in the right place.