Solved

Microsoft Access Form Field Names

Posted on 2010-08-13
16
445 Views
Last Modified: 2013-11-29
I'm sure that this is probably a simple question but I can't find the answer anywhere.

In all of the versions of Access that I've used, from 97 through and including 2007, sometimes, in order to get the form to recognize a given field, I'll have to include the table in which the field resides.

Sometimes I can enter simply
FieldName.
Sometimes I must enter
TableName.FieldName
or Access won't recognize the field.

Why is this?

Thanks
B
0
Comment
Question by:ButchDog
  • 6
  • 4
  • 2
  • +3
16 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33433922
ButchDog,
first of all, Forms does not have any FieldNames, only Tables have fields.
Forms use Controls (textbox, combo box.. et.c) to display information from "fields" of Table..

now, what is the problem?

0
 
LVL 84
ID: 33433946
Cap is right, but a Form which is bound to a table/query does expose the Fields of the underlying recordsource.

If you base your form on a SINGLE table, you don't have to preface that FieldName with a TableName.

If you base your form on a Query that may include more than one table, and if those tables have the same FieldNames, you'll have to qualify your FieldNames.
0
 
LVL 2

Accepted Solution

by:
diazluna earned 500 total points
ID: 33433951
It depends on the query that is used as the resource for the form.  If your query has multiple tables then you are going to need to prequalify the name of the field with the name of the table in order for MS Access to understand which table is it that you want to use.
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:ButchDog
ID: 33433971
Oh picky picky picky!
lol

I have a form.
On that form are various Controls; textboxes, combo boxes, etc.
Those controls need to be linked to fields in a table or query.
Some of them need to be formatted with just the field name.
Others have to have the table name preceding the field name with the two separated by a period.
I don't understand the difference but it's likely messing with a calculated field in a query.

I suspect that it may have something to do with relationships but I'm not sure.

That make sense?

Thanks
B

0
 

Author Comment

by:ButchDog
ID: 33433995
My form is based on a query.
Inside that query, under the field name, is a row that lists the table from which that field came.
As a result, it seems like I shouldn't have to preface the field name with the table name.
I have several, many, other fields in that same query, coming from the same table, that do not require that I preface the fieldname with the table name.

Also, the fields to which I refer exist in only one table and are NOT duplicated in other tables.

Thanks
B
0
 
LVL 2

Expert Comment

by:diazluna
ID: 33434006
What's the name of the field? What's the name of the TextBox (Drop down, etc.) that holds that field's data?
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 33434381
If your query contains fields from two tables, with identical names, then you will have to specify which version of that field you want.  Generally, there is no good reason to include two fields with identical names in a query, unless you are joining on that field, in which case you should only include one of the fields in the actual query results.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33439022
<I have a form.
On that form are various Controls; textboxes, combo boxes, etc.
Those controls need to be linked to fields in a table or query.>

...Then simply create your Form using the Form Wizard (with your query as the source).
Then all the Field assignments will be done automatically.
;-)

Why create a form with empty controls, ...and then go through all the trouble of assigning fields manually?
Just use the form wizard to create your form.

;-)

JeffCoachman
0
 

Author Comment

by:ButchDog
ID: 33458624
Hmmmm.
So far no good answer.

To clarify:
My form is already built and has many fields.  I'm just adding one calculated field so I cannot rebuild the form from scratch using the Wizard.
The form is controlled by an underlying query that has multiple tables linked in it.
99% of those fields do NOT need to be prefaced by the table name in which they reside.
Only a select few do and that is my dilemma.
The field in question is unique and there is no other field in any table with the same name.
This field is not used as part of a join and is not the index field.

Any thoughts?

Thanks
B
0
 
LVL 2

Expert Comment

by:diazluna
ID: 33458634
Again, what's the name of the field. Is it a reserved word?  You didn't answer my question.
0
 

Author Comment

by:ButchDog
ID: 33458678
Oh Sorry.

The field name is "ThisTripTotal"
It is a calculation of a "GrandTotal" field minus and an "AdditionalTrips" field.
The "AdditionalTrips" field is simply a number field.
The "GrandTotal" field is a calculated field and works perfectly.

Thanks
B
0
 
LVL 2

Expert Comment

by:diazluna
ID: 33458700
Ok. I'm also assuming the the text box name is also ThisTripTotal correct? If that's the case then change the name of the text box to txtThisTripTotal and let me know what happens.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 33458720
can you post the SQL of the query that is your recordsource?
0
 

Author Comment

by:ButchDog
ID: 33458780
If you're talking about ensuring that the control name and the control source are named differently, they already are.

The Name is "ThisTripTotalField".
The Control Name is "ThisTripTotal"
The "Label" immediately to the left of the field has a name of "ThisTripTotalLabel"
and the caption is "ThisTripTotal"

Does that help?
I'll try to post the SQL

0
 
LVL 84
ID: 33459888
Can you show the ControlSource of your Calculated Field?

There are many reason why Access may require you to preface the field with the Tablename, but in general it's good practice to do so regardless of whether you need to or not IF you're dealing with a query with multiple tables.
0
 

Author Comment

by:ButchDog
ID: 33582364
Sorry.
Still working on this.
Will answer questions asap.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question