Easy Search Criteria

Easy Search Criteria

Intro HERE

… …

Easy Search Criteria 1

Easy Search Criteria 1

Video 1 (5:03)

X xxxxx

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)

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)

X xxxxx

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)

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)

X xxxxx

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)

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!

… …