Search Criteria YouTube Bullet Points

YouTube Bullet Points

Total watch time now is about 25 minutes if you (Run the Video’s at Double Speed) Now that’s not too much of your precious TIME 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 on this Webpage and I will get back to you, usually within 24 hours…

Find Your Level!


Search Criteria 1 Nifty Access




Search Criteria 2 Nifty Access




Search Criteria 3 Nifty Access




Search Criteria 4 Nifty Access




Search Criteria 5 Nifty Access




Search Criteria 6 Nifty Access




Search Criteria 7 Nifty Access




Search Criteria 8 Nifty Access




Search Criteria 9 Nifty Access




Search Criteria 10 Nifty Access




Search Criteria 11 Nifty Access




Total Time


47 mins

Search Criteria 1 Nifty Access

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

Search Criteria 2 Nifty Access

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

Search Criteria 3 Nifty Access

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”

Search Criteria 4 Nifty Access

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”

Search Criteria 5 Nifty Access

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

Search Criteria 6 Nifty Access

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

Search Criteria 7 Nifty Access

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”

Search Criteria 8 Nifty Access

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

Search Criteria 9 Nifty Access

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!

Search Criteria 10 Nifty Access

NO Bullet Points!!!

Search Criteria 11 Nifty Access

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

… …

More Useful Stuff HERE:-

Leave a comment

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.