Use a Naming Convention that is simple
and
easy to follow.
It is very important to use a naming convention when developing applications. This will reduce confusion and make it easy for the developer to write vba code and work with objects. Take time to think about the names of fields, objects, modules, variables, and external files. This is one area I feel novice developers need to tackle early in their education. Too often I see code such as…
Private Sub Command0_Click()
DoCmd.Close
End Sub
What is Command0? If instead I use a naming convention my code would look like:
Private Sub cmdExit_Click()
DoCmd.Close
End Sub
It is much easier to identify cmdExit as a command button named exit.
Below I have shared the naming convention I use for developing Microsoft Access applications. Whether or not you use mine, someone else’s, or create your own is not important. The fact that you use a naming convention is. You will find most seasoned Access developers follow the Leszynski/Reddick Guidelines for Microsoft Access to some degree. (check out RVBA Naming Conventions as well)
Here are some tips when it comes to naming fields, objects, variables, and procedures in Microsoft Access.
- Capitalized the first character of each word
- Use only alphanumeric characters (letters and number) and the underscore (_)
- Don’t use spaces or special characters - such as: @ , ! . - +
- Do not use Number (0-9) as the first letter
- Do not use reserved words
Do NOT prefix your filed names.
A user creating there own ad-hoc query against your custom application should not have to ask “What is this ‘blnFXP’ Field?”. Users and developers (including you) should be able to easily identify the fields in your design structure.
For example:
strLastName – the str prefix tells me the data type is string (actually called Text in Microsoft Access’s table design interface but string in VBA code). I can already see the confusion on the faces of end users and novice developers.
I follow Leszynski/Reddick Guidelines, but not with field names. By doing so, it makes it less confusing for users. Also, you won't find many other database applications that follow this method (Oracle, SQL Server, mySQL). It is easier to interact with these applications if the prefixes are not present (especially if you upsize your access application at some pont).
My Suggestions:
Data Type |
Naming Convention |
Example |
Notes |
Text (String) |
Just Name the field without the prefix |
FirstName
LastName
StreetAddress |
|
Currency |
Use Amt or Amount at the end of the Field Name |
PriceAmt
TaxAmmount
UnitPriceAmt |
|
Date/Time |
Use the word Date or Time at the end of the field name |
EntryDate
BirthDate
ProccesTime |
It is easy to identify the Date/Time data type. |
AutoNumber |
Always use the word ID at the end of the field name |
ContactID
TranactionID
LocationID |
I can easily identify my Primary Key field is an AutoNumber data type (or Long if a foreign key). |
Yes/No (Boolean) |
Use _YN at the end of the field name |
Inactive_YN
DeleteFlag_YN
Deceased_YN |
The bln (Boolean) prefix confuses users because the Data Type is called Yes/No in access table design. |
Memo |
I reserve the words Notes and Comments for my memo fields |
ContactNotes
VendorComments
OrderNotes |
Avoid memo fields as much as possible in your application. They can corrupt and data would be lost. |
I mostly use only 2 of the Numeric data types |
Double |
|
|
This filed allows for decimals |
Long Integer |
|
|
No Decimals |
Byte
Integer
Single
Decimal |
My suggestion is to avoid these data types unless you have a specific purpose and are an experienced developer. Improperly used, these data types can cause problems and headaches (such as numeric overflows and rounding) |
OLE Object
Hyperlink
Lookup Wizard... |
ugh – that pretty much some it up. Database Bloat, record corruption, decreased performance. Avoid these, there are better solutions. |
Use prefixes for database objects.
You may be thinking “Ok, hold on, you just said not to use prefixes, already you are contradicting yourself!” I will clarify – I said not to use prefixes for Field Names, I did not mention database objects such as tables, queries, forms, reports, etc. Yes prefixes are very important – I just don’t like to see them in field names.
My Suggestions:
I have come up with some of my own prefixes, mostly for easy identification and to group objects together within the database window.
Prefix |
Object |
Example |
Notes |
tbl |
Table |
tblCustomer |
|
tlkp |
Table (lookup) |
tlkpShipper |
|
tsys |
Table (system) |
tsysPath |
There are custom system tables in my applications that I do not want a normal user to edit. An example would be the Path to the back-end on the network file server. |
usys |
Table (system – hidden) |
usysVersion |
This is the same as the tsys with one difference, the table can only be viewed if system objects are visible. To view system tables select Tools > Options from the Access menu bar, then check the system objects checkbox from the View tab. This is an excellent way to hide tables from those nosey users who like to poke around. An example of this type of table is one when I store the current version number and date of revision. |
temp |
Table (Temporary) |
tempSalesData |
I use this prefix for tables where I place data into a temporary location in order to perform advanced calculations or during an import/export process. |
qry |
Query |
qryContact |
|
q |
Action Queries (delete, update, append, make table) |
qExport01
qExport02
qExport03
qExport04 |
Most of my action queries are SQL statements I concatenate in my VBA code. Usually when I keep action queries in my application, they are used for a specific process which generally consists of more than just 1 query. I might have a set of action queries that builds a temporary table for export. Let’s say I need to run a delete query, 2 append queries, and then an update query. In this case I would name each query qExport followed by a number which designated the order they run. If I used the qdel, qapp, qupd prefixes, I would be looking though all my queries in order to find the next one to execute. |
frm |
Form |
|
|
ftlkp |
Form (lookup table) |
|
Yes – ftlkp – I realize most people do not keep the t in the prefix, but I hate how all my lookup forms appear before my regular forms when sorted alphabetically. I want them sorted like my tables (tbl then tlkp). |
fdlg |
Form (dialog) |
|
|
fmnu |
Form (menu) |
|
|
fmsg |
Form (message) |
|
|
fsub |
Form (subform) |
|
|
rpt |
Report |
|
|
rsub |
Report (subreport) |
|
|
_ |
Any Object |
_tblStudents |
I use _ for anything currently in development. I may be working on a new form or report, but not quite finished with it. This makes it easy to identify objects in development from those in production. Once I have tested the new object and ready to deploy into production, I remove the _. |
Z |
Any Object |
xfrmContact |
Z is used for a object that will need to be deleted at some point. Many developers use old at the beginning or end of their object name. I like using Z – I will delete the object at some point. |
1 |
Any Object |
1Accounts |
I use 1 when I am importing data |
Use prefixes for unbound form and report controls objects. Do not prefix the controls if they are bound to a field.
By a bound control I mean a control where the control source property is set to a field in the objects record source. Such as a textbox bound to the LastName field of the Form’s record source: tblContacts.
You should get in the habit of naming ALL your controls. Names like textboox34 and command19 helps no one. As you start writing your VBA code, changing there later will create more work for you, because you code will not automatically update.
Explanation:
Remember my earlier tip “Do not prefix your filed names.”? Now I can easy identify what controls are bound to date in my vba code.
- Me.LastName would tell me that I am dealing with the Last Name field of a table. Changing the value in vba code would change the date in the underlying table.
- Me.txtLastName would tell me that this is a textbox, but the date is not bound. Here changing the value would not effect the underlying table (at least not directly).
Again, this is merely a preference. Within my vba code, I personally fell it is more important to know whether or not the control is bound to a recordset then to know what the control type is (textbox, combobox, checkbox, etc). I can always determine the control type by using the ControlType property in vba. The same can be said for determining whether or not the control is bound or unbound, but I am going with what I need the majority of the time. |
