Tip The Ten Commandments of Access (1 Viewer)

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 03:57
Joined
Feb 28, 2001
Messages
26,946
Acceesbility:

If you expect nothing else for that key, then you are OK.

The problem we often see from new forum members is that these folks want to generate an Autonumber that includes some visual meaning. Which means they want a synthetic key that contains at least an element of natural (non-synthetic) data. Those are the folks for whom my admonition has the most meaning.
 

Acceesbility

Registered User.
Local time
Today, 01:57
Joined
Jan 4, 2017
Messages
32
Doc_Man Wrote:

The problem we often see from new forum members is that these folks want to generate an Autonumber that includes some visual meaning. Which means they want a synthetic key that contains at least an element of natural (non-synthetic) data. Those are the folks for whom my admonition has the most meaning.
AutoNumber -I just see it as a unique record reference number.
I'd find it difficult to think of it in any other way. :eek:

:D

Galaxiom Wrote:
Quit is absolutely essential. Set to Nothing simply destroys the pointer to the Word session.
Can you define "Pointer" for me?
Is that just a shortcut?

:)
 
Last edited:

constableparks

Registered User.
Local time
Today, 03:57
Joined
Jul 6, 2017
Messages
53
Acceesbility:

If you expect nothing else for that key, then you are OK.

The problem we often see from new forum members is that these folks want to generate an Autonumber that includes some visual meaning. Which means they want a synthetic key that contains at least an element of natural (non-synthetic) data. Those are the folks for whom my admonition has the most meaning.

I see this stated often when talking about table design. But it is never explained WHY this is a bad idea. Can someone elaborate on what kind of a problem this introduces? For example: If I have a table tblSalesOrder and the primary key is SalesOrderNumber which is an Auntonumber, what kinds of problems would I likely run into?

Thanks!
 

CJ_London

Super Moderator
Staff member
Local time
Today, 08:57
Joined
Feb 19, 2013
Messages
16,521
you can use it as a sales order number providing

a) you don't mind having gaps in the incremental numbers which will occur if you go to create a record then change your mind
b) you don't expect to look at the autonumber for the last record created and know that that is the number of records created
c) don't mind sometimes a new record has an autonumber value less that existing ones in the database (can occur) so you don't rely on the field to determine an order of input.

Many people don't mind, but accountants don't like to see gaps in say invoice numbers - and on that subject what do you do with credit notes? They'll be in the same table, but convention requires them to be in a different number range.
 

merlin777

Registered User.
Local time
Today, 01:57
Joined
Sep 3, 2011
Messages
193
OK, so I'm a newbie and note well the second commandment.

Just out of curiosity, if lookup fields are so bad, why are they there and what are the alternatives?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 08:57
Joined
Feb 19, 2013
Messages
16,521
they are not bad, just have their place. In most situations they will simply confuse - particularly newbies because they don't understand what is actually happening.

I'm an experienced developer and will use them if appropriate for simple things which do not change - so I might use them with a value list for such things as days of the week, months of the year.

but if you use them to lookup say a list of customers and store the ID, what do you call your field? ID or CustomerName? The first is correct in that is what you are actually storing but the second is what you see.

But no matter, you create your form and access helpfully populates the related label - so you go for customername for your field name so you don't have to change it on your form.

Now you want to search on customername - so you search on the field called customername, but it will generate a data mismatch error because you are search for 'ABC' but your field is actually numeric

Or you want to sort the data alphabetically - but the data is numeric so will sort on the ID, not the name.

And does it save you any time? Not really except for the most basic and simple forms - and that time saved is quickly lost when you forget that customer name is not really the customer name.

The alternative is not to use them in tables, but only in forms - you can quickly change a textbox to a combobox on a form.

These links provided a fuller explanation

http://access.mvps.org/access/lookupfields.htm
https://bytes.com/topic/access/answers/204202-need-alternative-lookup-fields
 

JPFred

Registered User.
Local time
Today, 04:57
Joined
Oct 29, 2017
Messages
47
Thow normally not in the right section, I felt, that as all newbies visit this section first this is the best place to address the above.

The Ten Commandments of Access

And it came to pass that the cries and lamentations of the Access newbies were heard on high by the gods of the Database, and their hearts were moved to pity for their followers. And they opened their mouths and spake, saying: "Nevermore shall the young and innocent wander witless on their journeys!
We shall provide guidance to them, yea, and to all who wish to seek the paths of wisdom." And they caused these commandments to be written and placed before the eyes of those seeking enlightenment.
So heed the words of those who have come before you, and keep these commandments in thine heart as thou dost create thy Database application. If thou shalt only follow these commandments thy burden shall be made light and thy path shall be made straight.
  1. Thou shalt design normalized tables and understand thy fields and relationships before thou dost begin.
  2. Thou shalt never allow thy users to see or edit tables directly, but only through forms and thou shalt abhor the use of "Lookup Fields" which art the creation of the Evil One.
  3. Thou shalt choose a naming convention and abide by its wisdom and never allow spaces in thy names.
  4. Thou shalt write comments in your procedures and explain each variable.
  5. Thou shalt understand error handling and use it faithfully in all thy procedures.
  6. Thou shalt split thy databases.
  7. Thou shalt not use Autonumber if the field is meant to have meaning for thy users.
  8. Thou shalt not copy and paste other people's code without at least attempting to understand what it does.
  9. Thou shalt not use "SendKeys", "Smart Codes" or "GoTo" (unless the GoTo be part of an OnError process) for these will lead you from the path of righteousness.
  10. Thou shalt back-up thy database faithfully, working not on thy Production Database, but on the Prototype Copy, as it is right and good to do.
Thus spake the gods of the Database, and blessed be their names! And Blessed, too, are those who contribute to the Access Newsgroup - giving freely of themselves to serve those who hunger and thirst for knowledge and understanding!

Well met! Spoke like a true application designer.
 

Jeffr.Lipton

Registered User.
Local time
Today, 01:57
Joined
Sep 14, 2018
Messages
31
A more general rule could be avoid special characters when naming objects.

With the (hopefully obvious) exception of underscore. (I personally like sLots_Of_Tiny_Words to sLotsOfTinyWords -- if there is an advantage to the latter, other than length, please let me know.)
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 03:57
Joined
Feb 28, 2001
Messages
26,946
Jeffr.Liption:

The disadvantage comes if you ever have to upconvert your database to some back end other than Native Access. In that case, not every potential backend accepts underscores or treats them uniformly.

As long as your DB stays "pure" access, underscores in names work OK and don't require you to enclose the name in [] - cf. having spaces in names, which always require []. IIRC, some non-Access BE SQL engines treat underscores as though they were spaces.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 08:57
Joined
Feb 19, 2013
Messages
16,521
Another factor is if you have a field called 'some field' and create a form or report, the control will be called 'some field' but any functions associated with it will be called 'some_field'.
 

isladogs

MVP / VIP
Local time
Today, 08:57
Joined
Jan 14, 2017
Messages
18,164
Another point might be readability for others.
IMO MyCamelCaseField is easier to read than My_Camel_Case_Field ...but you may disagree.

Also there is a 64 character limit on object names so lots of unnecessary underscores could conceivably cause an issue
 

CJ_London

Super Moderator
Staff member
Local time
Today, 08:57
Joined
Feb 19, 2013
Messages
16,521
I like rule 8 - so many times that is exactly what happens. There was an OP (who shall remain nameless) who built their entire app that way - a typical thread might have in excess of 50 posts trying to make it fit. I came to think of their app as a kind of 'Frankenstein's Monster' :)
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 19:57
Joined
Jan 20, 2009
Messages
12,847
With the (hopefully obvious) exception of underscore. (I personally like sLots_Of_Tiny_Words to sLotsOfTinyWords -- if there is an advantage to the latter, other than length, please let me know.)

We mostly read using the top half of characters. Visually, an underscore is virtually a space making it harder to discern the object names from the key words.

Besides, lots of tiny words make terrible object names no matter how they are typed. A couple of SubstantialWords makes a far more readable name. Surely nobody would ever include a preposition (eg 'of') in a name. :eek: :confused:

The underscore already has a role in VBA separating the procedure name from the object name.
Code:
Private Sub SomeControl_AfterUpdate
 

Minty

AWF VIP
Local time
Today, 08:57
Joined
Jul 26, 2013
Messages
10,346
I've also seen examples where two underscores have appeared due to fat finger trouble
e.g. Split__Name
If you aren't observant it's a really easy one to miss, and will cause you heaps of trouble!

And even worse - names ending in an underscore !

MyControl1_

Which then turns into

MyControl1__AfterUpdate
:eek:
 

Jeffr.Lipton

Registered User.
Local time
Today, 01:57
Joined
Sep 14, 2018
Messages
31
Another point might be readability for others.
IMO MyCamelCaseField is easier to read than My_Camel_Case_Field ...but you may disagree.

I personally find the version with underscores easier to read, but I think it may be a matter of personal preference (albeit a weak one).

[L]ots of tiny words make terrible object names no matter how they are typed. A couple of SubstantialWords makes a far more readable name. Surely nobody would ever include a preposition (eg 'of') in a name. :eek: :confused:

The underscore already has a role in VBA separating the procedure name from the object name.
Code:
Private Sub SomeControl_AfterUpdate

That was NOT a real world example. strPath_Ext or strFile_Name are more likely examples.

Your point about the role of underscore is well taken.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 08:57
Joined
Jul 9, 2003
Messages
16,224
I find that often, the underscore is hidden, making it look like a space. I find this disconcerting!

Sent from my SM-G925F using Tapatalk
 
Last edited:

Agnister

Registered User.
Local time
Today, 19:57
Joined
Jul 2, 2011
Messages
21
I love it oh "Wise Man". I shall abide by your commandments
 

adewale4favour

Registered User.
Local time
Today, 01:57
Joined
Aug 9, 2019
Messages
55
Hey Guys, these commandments are with the tune of the SCRIPTURES, so they are instructive.

got some preachers here. pretty good.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 08:57
Joined
Jul 9, 2003
Messages
16,224
When I read the title I thought about the ten commandments of the Bible hehe...

If you break one of the Bibles Ten Commandments, then you only have God to deal with. If you break one of the MS Access Ten Commandments, then you have, Colin, Pat, Richard, me and a host of other people to deal with!
 

Users who are viewing this thread

Top Bottom