Naming protocol

[Perfac]

My programming partner and me had a discussion. Will anybody kindly inform me of your reason why you would name a field or label on the form just a little different in the Property Sheet under "Other". We started a few years ago off by being paranoid to never name any field the same than another in the whole system. Our system now is getting close to 2000 objects, could be 10 000 field names, no two the same. If our Protocol looks like below, I will appreciate advice if it can improve. We use suffixes with every name, maybe unnecessary?

1. No reserved words(not possible because of suffix), no spaces in names(follow advice, even if spaces work sometimes). All other common rules.
2. If we create a table i.e. t01Country, and it is the 214nd table in our system, we allocate 214 as a suffix to every field. If the field name "Capital" was allocated in the 82nd table created, the field name will be Capital082. Now it will be Capital 214.
3. If the same field needs to be used more than once in the same query we will rename, Capital214a, Capital214b, Capital214c.
4. Today I am not sure why we decided that text fields should be renamed, but a few tutorials from internet did advice us that way.
5. Field names in tables get the best word to indicate what the field is used for, of course.
6. If we rename a text field named Capital214 in the Property Sheet we add a lower case t in front. tCapital214.
7. In the case of labels we only change the name if the label is used in VBA, which very seldom happens. Lower case l in front lCapital214.

After a couple of years we kind of settled on this recently, most of our development will change as we work on objects, especially creating new ones. I am aware of the guaranteed pain changing old ones.

As a default the name in the Property sheet on the form is the same than the name in the table. I have never experienced a clash or anything, but if we do not have to rename it, it will improve our protocol. Every drop help filling the ocean.

If someone had bad experience because of the same field name being used multiple times, even in separate tables, I would like to share your experience.

[ranman256]

Ive never needed 'versions' in table names, nor fields.
tCapital is fine.
tCountry is fine.
why would you have 214 capitals?

[June7]

Same field name in multiple tables can be confusing in queries. Requires table/query name prefix. Such as leaving all the autonumber PK fields named ID. Not a big deal once query is built properly - once and done. But can be an aggravation during development phase.

I also wonder about the indexing of table and field names. t01Country implies there are t02Country, t03Country, etc up to 214!!!????. This would not be optimized design.

[Perfac]

I would never need the same field name 214 times. In my example Capital may only appeared twice. What same field name did you repeat the most time? Did you ever rename a field from the table or query, on the form? If you did, I want to learn of any reason. Following advice from tutorials from the beginning we renamed 10 000 fields, did we waste some serious time, as we created every form? All our rules are recorded, we will change our rule never renaming a field in the Property Sheet. We always did that from our start, but we now think it was time wasted. Table names have no suffix, field names do.Our naming protocol include to start tables names with;
a. t01 for all tables storing general information not following below. I.e. t01Country
b. t02 All transactions stored. I.e. t02Salesinvoice etc.
c. t03 Salaries and wages.
d. t04 Stationary and documents created in Access. Even though we create most Stationary in Word and link it through a button in Access.
e. t05 Tables needed to host print forms, and other utilities.
f. t07 Financial statements
g. t09 Budget.
h. x A table in our system with name starting lower case x is always where an "Append Query" appended it from another object. We use that a lot, to reduce expressions making things "heavy". This tables are still tables, and the word part will have he same field names than the original object it came from, but the suffix will be different.

I am curious and it may save us a lot of time if we never need to rename field on forms. We note these things in a separate little database we name PERFAC SETUP. The name of our development is PERFAC. We also note reasons why decisions are made, because experience is that you may come at a time where you are not aware of the reason you should or should not follow a procedure, and then take a wrong turn. Surely all veterans connect with that.

[Perfac]

Hi June. No, no second country(t02Country). I am happy with our protocol of naming tables. I doubt about renaming fields on forms. When programming VBA do you know of a reason why a clash can occur. Surely just because the "Name property" is there, there is a reason for it.

[June7]

I always name control different from field it is bound to, such as tbxQty, cbxEmp, etc. Controls can have properties that fields don't, such as Text. I suppose VBA eventually resolves confusion based on property referenced but different naming removes ambiguity.

[Perfac]

I became disabled at 53 in 2016, healed by the end of 2018. Really only started then 2016 to train myself on Access. Wife died and 3 of 4 children emigrated to the other side of the planet so I am married to Access and spent serious time on it in 4 years, since selling the business. I am proud of our system, but even some of the basics I don't know yet. I do not understand when you write "Controls can have properties that fields don't, such as Text." Fields do store text? I have used a lot of VBA, my thought now is Me!fieldname refers to the field in the query or table linked to the form. Me.fieldname refers to the field placed on the form, which is the control. I have not yet understood a difference between a field placed on a form, and a control, it will be great if you teach me something here. My assumption it is the same thing.

I also assume when using Me.fieldname. Let us say the field name is Capital214, and in the Properties renamed tCapital214. Whether I use Me!Capital214 or Me!tCapital214 it will work. Do I have it wrong?

[June7]

Text and Value are properties. Text property is only accessible when control has focus. Field would not have Text property. Value is default property of field and data controls.

Data controls such as textbox, combobox, listbox, etc can be bound or unbound. Bound means ControlSource property is set to a field of the form or report RecordSource. Unbound means there is no field in the ControlSource or there is an expression such as =Qty * Price.

Input to a bound control passes data directly to field.

Often control and field can be interchangeable in code, not always.

[ssanfu]

A long while ago, I took over maintenance of a dB that had a unique naming protocol.
The tables had a 3 word name with the underscore between the words. Didn't matter what the word were as long as they pertained to the contents of the table(s).
For example the employee table was named "Employee_Name_Def".
For many to many (junction table), the third word would be "Link" - ex. "Company_Carrier_Link"

Employee_Name_Def	Company_Carrier_Link	Hire_Class_Def
end_pk	ccl_pk	hcd_pk
end_ssn	ccl_cpdfk	hcd_class
end_last	ccl_icdfk	hcd_description
end_first		hcd_abbrev

Each of the fields in a table is prefixed with the first letter of each of the table names.

Looking at a query in SQL view, it is really easy to tell which table a field is in. And no field name is duplicated.
Just one of the more interesting naming protocols