?
Solved

Microsoft Access Form Field Names

Posted on 2010-08-13
16
Medium Priority
?
467 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 85
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 2000 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

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 48

Expert Comment

by:Dale Fye
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 48

Expert Comment

by:Dale Fye
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 85
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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
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…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Suggested Courses

764 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