By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,124 Members | 2,208 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,124 IT Pros & Developers. It's quick & easy.

Naming convention clarification

P: n/a
With all due respect to Stan Leszynski and Greg Reddick, I'm still wondering
how to name controls on forms in my Access MDB.

Correct me if I'm wrong...

If a textbox or other control on a form is *bound* to a table field named
"FullName", then it should *not* be named "txtFullName", but rather just
"FullName" - that is, the name of the textbox on a form should always be the
*same as the name of the table field* it's bound to. And because the table
field name should remain independent of its data type there's no need to use
txt, dtm, int or any other prefix.

sound about right?
Nov 12 '05 #1
Share this Question
Share on Google+
15 Replies


P: n/a
Unless you are working with a team and must follow team rules, find a system
that suits you.

For me, having the Name and ControlSource the same for bound controls works
fine, but unbound fields get the prefix.

The point of using a different Name from the ControlSource is that you can
then easily distinguish between the Control on the Form and the Field in its
RecordSource. For example, the control has a Visible property, but the Field
does not. The crucial aspect is to be consistent: if you assign a new value
to the Field (named differently than the control), you may not see the new
value in the control until the record is saved (at which point Access
updates the control).

Not many people seem to use prefixes for field names at the table level.
There is some point to it though: Text and Date/Time fields require
delimiters when you embed literals in a SQL string, and this crops up when
building filters for forms, WhereCondtions for OpenReport, the 3rd agument
for DLookup(), etc.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"deko" <no****@hotmail.com> wrote in message
news:OX*******************@newssvr25.news.prodigy. com...
With all due respect to Stan Leszynski and Greg Reddick, I'm still wondering how to name controls on forms in my Access MDB.

Correct me if I'm wrong...

If a textbox or other control on a form is *bound* to a table field named
"FullName", then it should *not* be named "txtFullName", but rather just
"FullName" - that is, the name of the textbox on a form should always be the *same as the name of the table field* it's bound to. And because the table field name should remain independent of its data type there's no need to use txt, dtm, int or any other prefix.

sound about right?

Nov 12 '05 #2

P: n/a
> The point of using a different Name from the ControlSource is that you can
then easily distinguish between the Control on the Form and the Field in its RecordSource. For example, the control has a Visible property, but the Field does not. The crucial aspect is to be consistent: if you assign a new value to the Field (named differently than the control), you may not see the new
value in the control until the record is saved (at which point Access
updates the control).


Exactly the insight I was looking for - now I think I know why I had to
resort to DoCmd.RunCommand acCmdSaveRecord in some cases.

Thanks.
Nov 12 '05 #3

P: n/a
> Not many people seem to use prefixes for field names at the table level.
There is some point to it though: Text and Date/Time fields require
delimiters when you embed literals in a SQL string, and this crops up when
building filters for forms, WhereCondtions for OpenReport, the 3rd agument
for DLookup(), etc.


A somewhat related question -

I understand that Date/Time fields require delimiters when used in an SQL
string. But what happens when the table field is formatted as "General
Date" (where the field includes the time), and I embed a literal in an SQL
string like #5/17/04# - will this cause problems because there is no time
in the literal?

In general, is it better to format table fields, or simply leave the table
format properly blank? I've formatted many of the textboxes in my forms as
date/time - should the format of the textbox match that of the table? does
it matter?

thanks again...
Nov 12 '05 #4

P: n/a
The Format property of the field in the table affects only how Access
displays the data. Internally, it stores it as a fractional number, where
the integer part represents the date, and the time the time of day (0.5 =
noon, 0.25 = 6am (quarter day), etc.)

Literals in a SQL statement should be entered as mm/dd/yyyy, regardless of
the user's local settings or the display formats requested.

If the date field in the table does have a time component (other than
midnight), it will not match the literal date unless the times are exactly
the same. To retrieve all records dated any time on May 17, use:
([MyDate] >= #5/17/2004#) AND ([MyDate] < #5/18/2004#)

If you are using Access in a country that uses a non-US date format, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"deko" <no****@hotmail.com> wrote in message
news:rn*******************@newssvr25.news.prodigy. com...
Not many people seem to use prefixes for field names at the table level.
There is some point to it though: Text and Date/Time fields require
delimiters when you embed literals in a SQL string, and this crops up when building filters for forms, WhereCondtions for OpenReport, the 3rd agument for DLookup(), etc.
A somewhat related question -

I understand that Date/Time fields require delimiters when used in an SQL
string. But what happens when the table field is formatted as "General
Date" (where the field includes the time), and I embed a literal in an SQL
string like #5/17/04# - will this cause problems because there is no time
in the literal?

In general, is it better to format table fields, or simply leave the table
format properly blank? I've formatted many of the textboxes in my forms

as date/time - should the format of the textbox match that of the table? does it matter?

thanks again...

Nov 12 '05 #5

P: n/a
After all the discussion between Allen and yourself, a recommendation that goes
back to the beginning when you are first designing your tables is to avoid using
the same field name in more than one table. This avoids the problem of having
two tables in a query with one or more duplicate field names and Access raising
the message that it can not determine which table is being referenced when a
duplicate field name is included in the query grid.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"deko" <no****@hotmail.com> wrote in message
news:OX*******************@newssvr25.news.prodigy. com...
With all due respect to Stan Leszynski and Greg Reddick, I'm still wondering
how to name controls on forms in my Access MDB.

Correct me if I'm wrong...

If a textbox or other control on a form is *bound* to a table field named
"FullName", then it should *not* be named "txtFullName", but rather just
"FullName" - that is, the name of the textbox on a form should always be the
*same as the name of the table field* it's bound to. And because the table
field name should remain independent of its data type there's no need to use
txt, dtm, int or any other prefix.

sound about right?

Nov 12 '05 #6

P: n/a
In that case disambiguate with the table name or alias the table and
disambiguate with the alias.
--
Terry Kreft
MVP Microsoft Access
"PC Datasheet" <sp**@nospam.spam> wrote in message
news:yU******************@newsread2.news.atl.earth link.net...
After all the discussion between Allen and yourself, a recommendation that goes back to the beginning when you are first designing your tables is to avoid using the same field name in more than one table. This avoids the problem of having two tables in a query with one or more duplicate field names and Access raising the message that it can not determine which table is being referenced when a duplicate field name is included in the query grid.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"deko" <no****@hotmail.com> wrote in message
news:OX*******************@newssvr25.news.prodigy. com...
With all due respect to Stan Leszynski and Greg Reddick, I'm still wondering how to name controls on forms in my Access MDB.

Correct me if I'm wrong...

If a textbox or other control on a form is *bound* to a table field named "FullName", then it should *not* be named "txtFullName", but rather just
"FullName" - that is, the name of the textbox on a form should always be the *same as the name of the table field* it's bound to. And because the table field name should remain independent of its data type there's no need to use txt, dtm, int or any other prefix.

sound about right?


Nov 12 '05 #7

P: n/a
Yes, that's what you have to do when there are duplicate field names. My point
is that you can avoid this in the first place with some forethought.

Steve
PC Datasheet
"Terry Kreft" <te*********@mps.co.uk> wrote in message
news:Yo********************@karoo.co.uk...
In that case disambiguate with the table name or alias the table and
disambiguate with the alias.
--
Terry Kreft
MVP Microsoft Access
"PC Datasheet" <sp**@nospam.spam> wrote in message
news:yU******************@newsread2.news.atl.earth link.net...
After all the discussion between Allen and yourself, a recommendation that

goes
back to the beginning when you are first designing your tables is to avoid

using
the same field name in more than one table. This avoids the problem of

having
two tables in a query with one or more duplicate field names and Access

raising
the message that it can not determine which table is being referenced when

a
duplicate field name is included in the query grid.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"deko" <no****@hotmail.com> wrote in message
news:OX*******************@newssvr25.news.prodigy. com...
With all due respect to Stan Leszynski and Greg Reddick, I'm still wondering how to name controls on forms in my Access MDB.

Correct me if I'm wrong...

If a textbox or other control on a form is *bound* to a table field named "FullName", then it should *not* be named "txtFullName", but rather just
"FullName" - that is, the name of the textbox on a form should always be the *same as the name of the table field* it's bound to. And because the table field name should remain independent of its data type there's no need to use txt, dtm, int or any other prefix.

sound about right?



Nov 12 '05 #8

P: n/a
"PC Datasheet" <sp**@nospam.spam> wrote in
news:yi*******************@newsread2.news.atl.eart hlink.net:
Yes, that's what you have to do when there are duplicate field
names. My point is that you can avoid this in the first place with
some forethought.


If the fields represent the same data, they should arguably have the
same name.

Thus, to me, it seems obvious that foreign keys should have the same
name as the field they are linked to. And, of course, it generally
doesn't cause a problem with queries because you generally don't
*need* both of them in your SELECT.

I also use the same field names for my time stamp fields, Created,
Updated and UpdatedBy. These are generally not relevant to my
queries, and are only used in my forms (for display), where I only
seldom have more than one table in a resultset (I have occasionally
used a 1:1 structure where I had two tables in the recordset for the
form -- in that case, it makes logical sense to give different names
to the time stamp fields in each table).

Having the same names in all the tables makes it very, very easy to
work with those fields, and there's little need for using those
fields in SQL, as they are audit fields and aren't going to come
into play in most joins.

Another point: if you're contemplating using the same names for
fields in different tables, this may be a clue that you have a
normalization problem, especially if there's more than one such
field. The exception would be groups of fields that have functions
that are not related to the data but to the operation of the data
structure (as with my time stamp fields).

I don't avoid name collisions myself, and just don't see problems
with it.

Indeed, the place where I *do* encounter the problem a lot is when
I'm doing self-joins, which in my current project I'm doing a lot
of. And there's no way to get around the name collisions in that
case.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #9

P: n/a
> If you are using Access in a country that uses a non-US date format, see:
International Date Formats in Access
proudly using US date format.
If the date field in the table does have a time component (other than
midnight), it will not match the literal date unless the times are exactly
the same. To retrieve all records dated any time on May 17, use:
([MyDate] >= #5/17/2004#) AND ([MyDate] < #5/18/2004#)


Now that's interesting. I like to time-stamp the modification date
of database records by setting the default value of the "Modified"
table field to Now(). The "Modified" value can then be used
in a query to search for records based on modification date.
So if Record "A" is created on 05/05/04 at 6:00 pm, and I search
for all records BETWEEN #05/05/04 And #05/15/04#, then I assume that Record
A *will* be in the query results because a default time of 12:00 AM is given
to the literals. I was thinking perhaps I needed to set the format property
on the table field to "Short Date" in cases like this, but as you say this
would have no effect since, internally, the dates are stored with a time
value.

Another scenario could be searching for records modified in the last X days,
e.g. "If Modified > (#05/15/2004# - 10)". If this is run at 6:00 pm, and we
are sticklers for precision, we will get false positives if records are
created in the morning hours of 05/10/2004. Is this correct?
Nov 12 '05 #10

P: n/a
The code:
If Modified > (#05/15/2004# - 10) Then
returns True for all records where the date is May 5 or later (including any
time on May 5).

The code:
If Modified < (#05/15/2004# - 10) Then
returns True for all records up to the first moment of May 5, but records
with any time on May 5 are not included.

While there are solutions such as using DateValue() to strip the time from
the date field, they prevent Access using the index to match the dates, so
"less than the next day" will be the most efficient code to execute.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"deko" <no****@hotmail.com> wrote in message
news:1J*******************@newssvr29.news.prodigy. com...
If the date field in the table does have a time component (other than
midnight), it will not match the literal date unless the times are exactly the same. To retrieve all records dated any time on May 17, use:
([MyDate] >= #5/17/2004#) AND ([MyDate] < #5/18/2004#)
Now that's interesting. I like to time-stamp the modification date
of database records by setting the default value of the "Modified"
table field to Now(). The "Modified" value can then be used
in a query to search for records based on modification date.
So if Record "A" is created on 05/05/04 at 6:00 pm, and I search
for all records BETWEEN #05/05/04 And #05/15/04#, then I assume that

Record A *will* be in the query results because a default time of 12:00 AM is given to the literals. I was thinking perhaps I needed to set the format property on the table field to "Short Date" in cases like this, but as you say this
would have no effect since, internally, the dates are stored with a time
value.

Another scenario could be searching for records modified in the last X days, e.g. "If Modified > (#05/15/2004# - 10)". If this is run at 6:00 pm, and we are sticklers for precision, we will get false positives if records are
created in the morning hours of 05/10/2004. Is this correct?

Nov 12 '05 #11

P: n/a
The problem with that approach is that it then becomes easier to fall into
the trap of defining data in the field names rather than just regarding the
field names as descriptors of the type of data the field contains.
--
Terry Kreft
MVP Microsoft Access
"PC Datasheet" <sp**@nospam.spam> wrote in message
news:yi*******************@newsread2.news.atl.eart hlink.net...
Yes, that's what you have to do when there are duplicate field names. My point is that you can avoid this in the first place with some forethought.

Steve
PC Datasheet
"Terry Kreft" <te*********@mps.co.uk> wrote in message
news:Yo********************@karoo.co.uk...
In that case disambiguate with the table name or alias the table and
disambiguate with the alias.
--
Terry Kreft
MVP Microsoft Access
"PC Datasheet" <sp**@nospam.spam> wrote in message
news:yU******************@newsread2.news.atl.earth link.net...
After all the discussion between Allen and yourself, a recommendation that
goes
back to the beginning when you are first designing your tables is to
avoid using
the same field name in more than one table. This avoids the problem of

having
two tables in a query with one or more duplicate field names and
Access raising
the message that it can not determine which table is being referenced
when a
duplicate field name is included in the query grid.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"deko" <no****@hotmail.com> wrote in message
news:OX*******************@newssvr25.news.prodigy. com...
> With all due respect to Stan Leszynski and Greg Reddick, I'm still

wondering
> how to name controls on forms in my Access MDB.
>
> Correct me if I'm wrong...
>
> If a textbox or other control on a form is *bound* to a table field

named
> "FullName", then it should *not* be named "txtFullName", but rather
just > "FullName" - that is, the name of the textbox on a form should

always be the
> *same as the name of the table field* it's bound to. And because
the table
> field name should remain independent of its data type there's no
need to use
> txt, dtm, int or any other prefix.
>
> sound about right?
>
>



Nov 12 '05 #12

P: n/a
In article <OX*******************@newssvr25.news.prodigy.com> ,
no****@hotmail.com says...
With all due respect to Stan Leszynski and Greg Reddick, I'm still wondering
how to name controls on forms in my Access MDB.

Correct me if I'm wrong...

If a textbox or other control on a form is *bound* to a table field named
"FullName", then it should *not* be named "txtFullName", but rather just
"FullName" - that is, the name of the textbox on a form should always be the
*same as the name of the table field* it's bound to. And because the table
field name should remain independent of its data type there's no need to use
txt, dtm, int or any other prefix.

sound about right?


100% incorrect

I never leave the control name the same as the bound field name. I
always give it a unique name. My text boxes are tbFieldName, labels are
lLabelText, and combo boxes are cbFieldName, for example.
Nov 12 '05 #13

P: n/a
Ima Lostsoul <me@myprovider.com> wrote in
news:MP************************@netnews.comcast.ne t:
I never leave the control name the same as the bound field name.
I always give it a unique name.


I don't bother with changing the name of the control unless:

1. I'm referring to it in code.

OR

2. I'm referring to it in the controlsource of another control
(which may or may not be on the same form).

In other words, I don't change things until such time as there's a
possibility for ambiguity.

One related issue: in certain contexts, Access 2000 (and perhaps
later versions of Access) have difficulty resolving references to
fields in the underlying recordsource (as opposed to references to a
control bound to the same field). This is the source of Error 2486
in certain contexts (see Google Groups on that error number and my
email address to see long discussions of it). It seems not to be a
problem with references within the same form, but with references
from other forms (parent or child).

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #14

P: n/a
In article <Xn**********************************@24.168.128.7 8>,
dX********@bway.net.invalid says...
Ima Lostsoul <me@myprovider.com> wrote in
news:MP************************@netnews.comcast.ne t:
I never leave the control name the same as the bound field name.
I always give it a unique name.


I don't bother with changing the name of the control unless:

1. I'm referring to it in code.

OR

2. I'm referring to it in the controlsource of another control
(which may or may not be on the same form).

In other words, I don't change things until such time as there's a
possibility for ambiguity.

One related issue: in certain contexts, Access 2000 (and perhaps
later versions of Access) have difficulty resolving references to
fields in the underlying recordsource (as opposed to references to a
control bound to the same field). This is the source of Error 2486
in certain contexts (see Google Groups on that error number and my
email address to see long discussions of it). It seems not to be a
problem with references within the same form, but with references
from other forms (parent or child).

Actually thats what I do often (use the object name is code or reference
it elsewhere). Makes things a lot easier in code when you name the
objects.
Nov 12 '05 #15

P: n/a
"Ima Lostsoul" <me@myprovider.com> wrote in message
news:MP************************@netnews.comcast.ne t...
In article <OX*******************@newssvr25.news.prodigy.com> ,
no****@hotmail.com says...
With all due respect to Stan Leszynski and Greg Reddick, I'm still wondering
how to name controls on forms in my Access MDB.

Correct me if I'm wrong...

If a textbox or other control on a form is *bound* to a table field named
"FullName", then it should *not* be named "txtFullName", but rather just
"FullName" - that is, the name of the textbox on a form should always be the
*same as the name of the table field* it's bound to. And because the table
field name should remain independent of its data type there's no need to use
txt, dtm, int or any other prefix.

sound about right?


100% incorrect

I never leave the control name the same as the bound field name. I
always give it a unique name. My text boxes are tbFieldName, labels are
lLabelText, and combo boxes are cbFieldName, for example.


Whereas I always have the control name the same as the field name and have never
had any problems with it. The only time I change them is when I need to use an
expression in the control to avoid the #Name issue.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Nov 12 '05 #16

This discussion thread is closed

Replies have been disabled for this discussion.