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