Nifty Tips

Nifty Video Tips

Using an excellent new feature of Elementor Page Builder I am now able to show you time indexed clips from my MS Access Videos. This way you can quickly and easily see a video demonstration of just a few minutes, a Demo that gives you a leg up in your MS Access learning. My goal is to make you indispensable to your company regarding MS Access databases.

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.

Nifty Access YouTube Thumb Nail
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 remove any chance of errors creeping in!

Nifty Access YouTube Thumb Nail
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!

Nifty Access YouTube Thumb Nail
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…

Nifty Access YouTube Thumb Nail
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!

Nifty Access YouTube Thumb Nail
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.

Nifty Access YouTube Thumb Nail
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.

Nifty Access YouTube Thumb Nail
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…

Nifty Access YouTube Thumb Nail
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…

Nifty Access YouTube Thumb Nail
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!

Nifty Access YouTube Thumb Nail
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.

Nifty Access YouTube Thumb Nail
Play Video

Escape from Debug Routine

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

Nifty Access YouTube Thumb Nail
Play Video

Ellipsis? What is it?

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

Nifty Access YouTube Thumb Nail
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!

Nifty Access YouTube Thumb Nail
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

Nifty Access YouTube Thumb Nail
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.

Nifty Access YouTube Thumb Nail
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.

Nifty Access YouTube Thumb Nail
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

Nifty Access YouTube Thumb Nail
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 

Nifty Access YouTube Thumb Nail
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…

Nifty Access YouTube Thumb Nail
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!

Nifty Access YouTube Thumb Nail
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…

Nifty Access YouTube Thumb Nail
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()

Nifty Access YouTube Thumb Nail
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..

Nifty Access YouTube Thumb Nail
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:-

Nifty Access YouTube Thumb Nail
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!

Nifty Access YouTube Thumb Nail
Play Video

ApplyFilter to Sub-Form

In this video I run through what you probably already know and that’s how to apply a filter to a form. However I take it one stage further and show you how to easily write some VBA code based on the filter you created so that you can operate the filter from pressing on a label or a command button on your form.

I demonstrate the problem you will have with the text delimiters that’s the quotation marks, and the solution.

Nifty Access YouTube Thumb Nail
Play Video

More Useful Stuff HERE:-

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.