Nifty Tips

Nifty Video

Using an excellent new feature of Elementor Page Builder I am now able to show you clips from my MS Access Videos.

Insert VBA Code Snippets

A useful feature of MS Access is the ability to insert VBA Code Snippets from the insert file menu option.

Basically you keep all your Code Snippets in text files in a folder on your computer, that’s it! It’s also possible to create, edit and update the files on the go, as you work, creating your VBA code. In this video I demonstrate how to do it.

Play Video

SQL Insert Statement

Formatting SQL statements can be a pain. It is often a source of errors and difficult to discover where the error is. The solution is quite simple as with any coding, “divide and conquer” in this video I show you an easy way to format your SQL statements so that you removed any chance of errors creeping in!

Play Video

Global Variables 1

Everyone will tell you you shouldn’t use global variables. There are good reasons for this but I won’t go into those. I will just show you in this video what you can do instead!

Play Video

Auto Complete with Ctrl Space

Handy tip here you can use Control (Ctrl) Space to speed up your coding! Control space calls up autocorrect autofill and enters instructions automatically…

Play Video

Bring Your Code to the Top

By bringing the latest code you are working onto the top of your module, you are quickly and easily able to pick up where you left off, after an extended absence from your project!

Play Video

Compact On Close

Compact on close is one of those things which you may or may not want! I use it a lot myself but occasionally find it can cause issues.

Play Video

Take out the Tables

You can remove most of the references to the table name when your SQL Statement is based on a single table. This trick depends on NOT just selecting the table name, but also the DOT “.” following it. You need to select the dot (.) to prevent your action removing the actual reference to the table in the FROM Clause.

Play Video

Find Form Name - In VBA Window

I can’t quite remember a particular Form Name. I don’t want to come out of the VBA window and look at the list of forms, so I use this Nifty little tip…

Play Video

Display Data from a Combo Box in a Text Box

You can display any information from a Combobox in a Textbox with this Nifty tip here. Excellent for displaying “Addresses” straight from a Combobox on your form. Makes for a very Tidy interface…

Play Video

Remove Layout View

 Get rid of Layout View! — I find layout of you particularly annoying. I like the old-fashioned way of being able to drag your controls around easily. Sometimes MS Access is just too helpful!

Play Video

Combo Boxes are numbered from 0 (Zero)

One of the Gotcha’s when coding in VBA, and many other coding environments is that columns are often numbered from “0” up, not “1” up.

Play Video

Escape from Debug Routine

The way to escape from the debug routine is to press “Run” >>> “Reset” this shuts it down.

Play Video

Ellipsis? What is it?

Ellipsis is 3 dots (…) – In MS Access it usually denotes opening another menu or information box.

Play Video

Star * as Query Criteria

Although I am generally complaining about the automatic things MS Access does which can mess up my Code, I quite like it when it helps me build the Search Criteria in a query builder grid… a* >>> becomes >>> Like “a*” —– now that’s Nifty!

Play Video

Expression Builder to Set Textbox Control Source

You can use the expression builder to put some useful code in the control source of a text box. The text box in this example displays information from a combo box

Play Video

Combo Box - Hide Columns

The combobox column width property can be used to hide any of the columns. This is useful if you want access to the information but don’t want to see it in a particular combobox.

Play Video

7874 Error - Microsoft Access cannot find the Object

I deliberately caused this error in my Beginning VBA – Video Course, and thought I would do a Nifty Tip about it.

Play Video

Command Buttons - Convert Two in to One

If you have two Command Buttons, one “to do” something and the other “to undo” (often “lock” and “unlock”) you can tidy up your Form by converting them into one button by following these instructions

Play Video

Query Criteria - From a Text Box

Criteria for a Query can be provided by a Textbox. In this Video I demonstrate how you can wire this up. The video is from a set of videos introducing VBA. The set of videos is HERE:- VBA Beginner 

Play Video

Combo Box Row Source - Table as a

Wiring up a Combobox. Controlling what is displayed. An explanation of the choices, and reasons for those choices when setting up a Combo-Box…

Play Video

Access's "Automatic Help" can cause YOU Problems!

One of MS Access’s “Annoying Features” is its helpfulness! Sometimes it can be JUST too helpful! This helpfulness takes the form of inserting characters in your SQL statements, your strings, just where you don’t really want them. It’s important you know about this “Feature of MS Access” so that you are aware, and realise that it might not be you making a mistake, it might well be MS Access itself!

Play Video

Tag - Use the Tag Property to Group Controls

One of the biggest leaps forward I made in my MS Access programming was when I discovered the possibility of Looping through a set of Controls! I just can’t explain how liberating perfecting this technique is. You can do fantastic things with MS Access. It’s also a very gratifying way of programming. You feel as if you are actually communicating with MS Access, working together with it, not fighting it! In this example I show you how to use the Tag property of a Control to group them within your form; so that you can operate on controls as a set. For instance, you could have two or three different sets of Controls. I did a complete Video Blog on this technique many years ago now! It is here:- “Lock, Unlock Controls” — it’s one of my early examples so you must excuse the ancientness of it…

Play Video

Set the Control Source of a Text Box to a Function

Did you know that you can put a Function in the Control Source of a Text Box? This tip shows you a quick peek at it. The whole video is useful in itself as it shows you how you can fill a textbox with the results from a Function()

Play Video

How to Get the Text Version of a Query

Your initial Ventures into the world of SQL Statements can be quite daunting. How on earth can anybody write these long complicated SQL strings? Well I’ll tell you a secret, I don’t! And even better, you don’t have to either! If I had to sit down and write an SQL Statement from scratch, I’d be hard pressed! I would definitely have to look it up on the interweb! The reason is, I don’t have to! It’s very easy to create SQL Statements using the Microsoft Access “Query Builder Grid” it’s the second best thing in MS Access after Subforms..

Play Video

Sub Form Window? What is it?

When you drag a subform onto another form, a new invisible control is created around your subform. And guess what, MS Access gives it a default name which is the same name as the form contained within it! It is this intermediary control, the control between the main form and subform, between the parent form and the child form, which controls the interaction between the two forms. This video gives a brief introduction to it. There’s more about so from subreport controls in my blog here:-

Play Video

Add to "Insert File"

In this video clip I demonstrate how easy it is to add and modify code in the “File Insert” section of “MS Access”… It’s one of the little used features of MS Access. It’s so helpful and can speed up your development process no end. If you are not currently using it I highly recommend you get conversant with it, start adding your favourite code snippets in there. I find the biggest problem is “Categorising my Code Snippets” I never know whether to categorise it by objects like, Form, Report, Module — or by what the code does! If you’ve got any ideas, then I’m all ears!

Play Video