Solved

Microsoft Access Form Field Names

Posted on 2010-08-13
16
434 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 119

Expert Comment

by:Rey Obrero
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
 

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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now