Building Search Criteria

Building Search Criteria

A couple of years ago I produced this set of videos describing how you can create a powerful search form for your database. I’ve recently upgraded the videos, added some more information and taken out some stuff to shorten the videos into a much quicker and simpler explanation. Total watch time now is about 25 minutes if you run the video at double speed. Now that’s not too much of your precious to assimilate the information that these excellent videos provide. And a Bonus! I’m currently available to help you with any enquiries, just press the email button:- “Help with Building the Search Form”  BELOW and I will get back to you, usually within 24 hours…

… …

Search Form from Scratch!

Building a Search Form from Scratch!

Video 11 (4:34)​

This Advanced but simple to use Search Form is unbelievably easy to create following the comprehensive Video Instructions.

In less than 50 minutes you can build yourself a Search Form for your MS Access database Project.  There is a sample file containing this database which is available for a small Fee.

PRESS “Buy Now” (THE BUTTON…)

Prerequisites:-  You will need a little knowledge of VBA. You will need to be reasonably happy using the Query Builder Grid.

Add this incredible utility to your MS Access database and establish yourself as the “Go To” person in your organisation for Database Improvements!

Video 11 (4:34)​

Video number 11, this is the last video of the series showing you how to build this excellent Search Form. I put a copy of it here at the beginning to whet your appetite! I want to show you what you can achieve by following the instructions in the videos 1 to 11 below…

Total viewing time “Less than 50 minutes”

Need any help understanding anything, then drop me a line. Press the Green Button:-

“Help with Building the Search Form”

Don’t have 50 minutes to spare?

Then there’s a Quick and Easy Option. See my Ready to GO – Product here:-

A “Search Form” you can drop into your database and be with using within 4 minutes!

…         ..         ..            ..        ..          ..           ..     ..  ..         … 

… …

Making a Start!

Search Form - Set out the Basics

Video 1 (3:47)

Now you’ve seen where this set of training videos is taking you let’s go through a few preliminaries.

You may want to Skip Ahead, then SEE THIS PAGE where there’s a full list of all the bullet points from the videos.

You can pick a place where you’d like to start.

These comprehensive Video Instructions demonstrate how to add this incredible Search utility to your database.

With it, you can establish yourself as the “Go To” person in your organisation for database improvements!

Nifty Access Drop in “Nifty Components” will quickly elevate you to “Power User Level!” 

Video 1 (3:47)​

Search Criteria 1 - Bullet Points:-

00:28 Basic command button calling a message box
00:36 Always have the messagebox transfer something so you can see it’s working
00:50 Have a module containing the constants, things like project name, your telephone number
01:22 Make sample text available by adding it to your Module, and commenting it out
01:27 Shorten the SQL Statement by removing the superfluous Table Name
01:40 Select the table name and the Dot (tblMain. NOT tblMain)
02:05 Demo of how to insert a “Function Template”
02:55 Shows the use of the constant “conAppName”
03:15 Always bring your latest code to the top of the module
03:30 Append the Function “fSQLX” to the message box statement with an ampersand

… …

Build an SQL Statement (a String)

Build an SQL Statement

Video 2 (5:28)

In this video I go through the process of converting the SQL version of an MS Access Query in to Code suitable for building the SQL query up with VBA.

If you’ve got this far, then you are well on your way to becoming an MS Access Power User!” 

Video 2 (5:28)

Search Criteria 2 - Bullet Points:-

01:15 Simplify the look of the code

01:25 Make patterns where you can

03:10 Build an SQL Statement (a String)

03:20 An SQL Statement is a string version of a Query

… …

How to Step Through your Code

Stepping Through your Code

Video 3 (7:41)

In this video I continue the development of the SQL Statements in VBA code. I demonstrate how to add a Breakpoint in your code, and how step through your code with the “F8” key to find the Error.

For a more in-depth look, have a look at the “Search Criteria 3 – Bullet Points” which are also available in the YouTube Video description; if you want to watch it on YouTube. 

Video 3 (7:41)

Search Criteria 3 - Bullet Points:-

00:22 How to insert a template file
00:40 Bring your most recent code to the top of the Module
00:53 Don’t add them at the bottom
00:57 The code window gets stuck forcing you to Type right at the bottom
01:20 keeping the code at the top allows you to quickly identify where you got to with your work
04:04 Too many spaces is not necessarily a bad thing in SQL Statements
04:30 Use an ampersand “&” for concatenation of strings
04:47 Type Mismatch Error Number 13
05:08 Adding a breakpoint to your code
05:14 Code execution stops at the breakpoint
05:20 Code execution position highlighted in yellow
05:28 Press F8 to allow the Code to pick one more step
06:04 Line where the error occurs highlighted in yellow
06:07 The error causes the code to skip to the Error_Handler:
06:15 The error code is checked against the Error_Handler: Select Case for a match
06:19 There is no predefined error handler to catch the error so it goes to Case Else
06:23 Message box displays the error description and the error number
06:46 Clear the breakpoint
06:50 Cycle through the code and find where it breaks
06:52 Reason for error identified, trying to multiply two strings together
07:18 These damn “Quotation Marks”

… …

Simplify Your Code!

Make your code as Simple as Possible

Video 4 (6:45)

“Quotes”, “Speech Marks” sometimes called “Double Quotes” in SQL Statements are one of the biggest causes of problems and errors. In this video I demonstrate how to build your code without them!

I demonstrate how to insert your own Code from your own Code Repository, using built-in functions of MS Access, a feature often overlooked.

Video 4 (6:45)

Search Criteria 4 - Bullet Points:-

00:15 Remove the multiplication sign and make it into a string
00:18 Make your code as simple as possible right from the beginning
03:00 I’m breaking the naming convention Here!
03:15 Use character 34 Chr(34) to replace the speech mark – quote mark
03:40 Explanation of how quotes and Chr(34) will appear correctly in your “text” string
04:20 Display the string variable in a message box see see what it actually contains
06:05 How to rename a function with “Replace”

… …

Reuse your Code Blocks

Reuse your Code Blocks where possible

Video 5 (1:36)

Demonstration of how to Copy a function, to reuse it. Use find a replace to rename it. Struggling for anything useful to say about this short video!

Video 5 (1:36)

Search Criteria 5 - Bullet Points:-

00:27 Make a copy of a function
00:40 Rename it to avoid clashes
00:46 Note Replace set to “Current Procedure”

… …

Break Code Up into Components

Break things up into Logical Components

Video 6 (3:37)

Keeping your work at the top of the module has two advantages. It is easier to work on. and if you come back to your Code after a long break, you will immediately know the last thing you were doing.

If you run into an unexpected error, don’t forget you also have “Undo” within the MS Access Code Window, which can be very handy!

As you get more experience with VBA code, you will come to realise you are better off creating smaller functions and subroutines. It is much easier to debug your Code if you have smaller single purpose Functions/Subs rather than large ungainly multiple use, multiple function, Monsters!

The same principle applies to the components within the Function/Subroutine… Long Strings, which you are forced to break up with continuation characters are completely unnecessary! They can be created just as easily by dividing them up into smaller strings and then concatenating those together.

Again, I think it’s partly to do with the “look mum” see what I can do, attitude of some programmers, as if it’s some sort of skill to be able to decipher and read long complex strings. There are many advantages to dividing long strings up into smaller strings, then rebuild them with a concatenation where required.

I hope to demonstrate the advantage of this method in this set of 11 videos

Video 6 (3:37)

Search Criteria 6 - Bullet Points:-

01:42 If you make a mistake,  you can go back a step With “Undo”
02:10 Don’t forget to break things up into logical components

… …

Breaking it down again for Emphasis

Breaking the Code down again for Emphasis!

Video 7 (1:59)

This is basically a repeat of the previous video, Video Number 6 so I don’t have a lot to say about this one!

Video 7 (1:59)

Search Criteria 7 - Bullet Points:-

00:26 Find and Replace
00:30 Make sure you’ve got “Current Procedure” selected
00:40 Don’t forget to break your Code into smaller units
01:10 Demonstration on a “Compile Error”

… …

Pull the Components back Together

Pull the components back Together

Video 8 (5:55)

This video takes String Building to the next level! As I explained in the comments against the video number 6, you should endeavour to build your code as the smallest set of components you can. Then pull those small components together in small functions, then use several functions, instead of one massive utility function!

This method is demonstrated nicely here where you have a function to return the “WHERE” clause, then another function to return the “AND” Statements for the three different text boxes.

What do you won’t see until later is, that by taking this approach the three functions, which represent the three text boxes are turned into one Single Object Oriented function later!

Video 8 (5:55)

Search Criteria 8 - Bullet Points:-

00:30  Build a SQL Statement by concatenating the functions together
01:15 We’ve got an “AND” on the end that needs removing
01:57 Get the length of a string
02:22 Get the leftmost characters of a string
02:40 Now we have removed the “AND”
02:54 Grab the context contents of the message box with “Ctrl Copy”
03:00 Place the message box contents in a Query
03:15 Create a real Query
03:20 Create a Query in the Query Designer Grid
03:29 MS Access adds “Like” automatically
03:38 How to get the text version of a Query
04:03 Look at the SQL view again, (the text version of the Query)
04:40 Compare the VBA generated SQL Statement
04:55 Against the SQL Statement produced with the Query Builder Grid
05:04 Identify the errors and correct them in the VBA Code

… …

VBA created SQL Statement

Compare VBA SQL Statement to Query SQL

Video 9 (6:05)

Debugging SQL Statements is not the same as debugging VBA code. It’s very similar, but there are some subtle differences. The main difference is that your SQL Statement will always (nearly always) contain some “Quote Marks”, “Double Quotes”, “Speech Marks”, whatever you want to call them!

I’ve noticed that most programmers whilst debugging there code, force what they want to examine into the “Immediate Window” and study it there. I’ve always found it better to extract the code into a message box. The message box then pops up when you’re actually using your Form, you can see immediately what’s going on.

It’s also very easy to copy the SQL Statement out of the Message Box, then you can paste it directly into a Query and see if it works! I can’t think of anything simpler! You can see this method of debugging your VBA and SQL Statements in this video, Video number 9 … HERE:- At Time Index 05:00

Video 9 (6:05)

Search Criteria 9 - Bullet Points:-

00:09 General adjustments to the SQL string
00:15 Add the bracket to the WHERE Clause
00:25 Add a space to the “SELECT FROM” Statement
00:52 Need to remove the space from the Surname String
01:17 Remove one of the speech marks from the Star
01:28 Do this by removing the ASCII code Chr(34)
01:57 Change the star “strSTAR” to a left star “strSTARL”
02:05 Create a right star “strSTARR”
02:25 Don’t forget to amend your code to the new String Variables
02:28 Update the Default Value of the text boxes to reflect the table contents
03:30 Copy the contents of the message box with the key combination “Ctrl C”
03:43 Compare the VBA generated SQL Statement against the Actual SQL Statement
04:10 Tidy up a bit by removing redundant comments
04:35 Compile error due to missing Ampersand “&”
04:42 Add the missing Ending Bracket
05:00 Copy the contents of the message box with the key combination “Ctrl C”
05:02 Put the VBA created SQL Statement straight in a Query and test it
05:10 There you go! The query runs a treat, returning the expected Records
05:12 Extract the text version of the Query
05:18 Paste it into the form module and compare it with the sample SQL Statement
05:26 A couple of minor problems with extra Spaces – easily resolved!
05:36 MS Access SQL Statements usually run OK with Superfluous Spaces
05:44 Solved the extra space on the End by removing 5 characters instead of 4!

… …

General Tidy Up!

General Tidy Up!

Video 10 (0:52)

Just a short Pause & Tidy Up.

I don’t have a lot to say about this one either!

Video 10 (0:52)

Search Criteria 10 - Bullet Points:-

NO BULLET POINTS!

… …

Subforms - Access Best Feature!

Subforms - One of MS Access's best Features

Video 11 (4:34)

In this video we create a subform to go on the main form. In the subform we’re going to display a “Datasheet” of the records we’ve selected with the SQL Statement. Subforms are one of MS Access best features and provide some incredible functionality, very easily straight out of the box as it were.

I also demonstrate the Code for accessing a Subform’s RecordSource from a Main Form with VBA code. This is a very basic use of the subform/subreport Control. Simply housing a form on your main form.

The actual subform/subreport Control is a very powerful Object and gives you the ability to link the records displayed in the subform to the records on the main form. This isn’t demonstrated in this set of video’s. You can see this in another explanation here:-

Video 11 (4:34)

Search Criteria 11 - Bullet Points:-

00:24 How to create a subform –  Select the Table you want to base the Form on
00:26 How to create a form based on a table
00:26 Select the Create Tab
00:27 Click on the button marked “Form” in the Forms section
00:29 Open the newly created Form in design view
00:30 How to change the form to datasheet view
00:32 Open the Forms property sheet
00:34 Select the “Format” tab and change the Forms “Default View” to Datasheet
00:45 Save the Form with a name identifying it as a Subform “sFrmMain”
00:50 Open the original Form “Form1” in “design view”
00:55 How to Drag a subform on to a Main Form
01:00 Drag the Subform “sFrmMain” onto the Form “Form1”
01:11 The subform displays all the Records
01:17 Now we need to  modify the newly added subforms “RecordSource” with VBA code
01:19 Select the subform/subreport Control which houses the subform
01:23 This is a Subform/Subreport Control (Subform Window)
01:30 the source object of the subform subreport control is the Subform “sFrmMain”
01:48 This is it’s RecordSource
01:50 Subform “sFrmMain” has a RecordSource
01:54 Open Subform “sFrmMain” in design view
01:56 Open the Subforms property sheet
01:57 The Subform “sFrmMain” RecordSource is set to “tblMain”
02:05 We need to modify the subform RecordSource with VBA code
02:15 The subform is housed in the subform/subreport Control I call it a “subFrmWinFORMNAME”
02:18 This is the Sub Form Window (Subform/Subreport Control)
02:21 The default name for the subform/subreport Control is the name of the Form it houses
02:22 Access gives the Subform/Subreport Control a Confusing Name
02:25 The default name in this case is:- Subform “sFrmMain”
02:37 I like to think of it as a window, and name it thus:- “subFrmWinFORMNAME”
02:38 Immediately change its name to something sensible!
02:43 I named it:- “subFrmWinFrmMain” I should have named it “subFrmWinSfrmMain”
03:11 VBA code to modify the subform RecordSource
03:15 Set the subform RecordSource  with VBA code
03:25 Me.subFrmWinFrmMain.Form.RecordSource
03:50 Demo of the RecordSource of the sub-form being set with VBA code
04:00 Demo testing different search criteria
04:10 Test to see what happens when there are no matching records
04:26 Possible improvement! Add a reset button

… …