Unable to Get value in the Form from Master Table, failed with DLookup Function

[gcbeldar]

Hello to All the Member,

I have created a simple database, which consists of two table; Master and Bills with a Form "Bills".


When I select TName Name from the Master Table in the Form Bills, rest of the Field from the Master Table to be filled in the form pertaining to the select TName.

Please find the attachment of database.

[Bob Fitz]

Why would you have two tables that are exactly the same

[ohmydatabase]

In VBA, when calling DLookup make sure all of your conditions are in quotations.
myValue = DLookup("Field Name","Table Name","Condition")

[andy49]

=DLookUp("[MinCharge]","Master","Tname='" & [TName] & "'")

the line above produces the correct value

[gcbeldar]

Thanks for Response;
=DLookUp("[MinCharge]","Master","TName=Forms![Bills]!TName")
for MinCharge Box in the form I have written the above expression assuming Mincharge from "Master" Table with Condition TName of Master Table value equal to the Value selected in Bills Form.
I am getting error "The Control as reference to itself"

[aytee111]

DLookup is not your issue - the table design is. As stated, why do you have two tables exactly the same? First, design the tables in a normalized way with each table having its own purpose and linked together through the relationships. Then, design a form where they select a "master" value and the form will filter the Bills table based on that value.

[ssanfu]

I am getting error "The Control as reference to itself"

This is because of the table structure design (as aytee111 said).

The control name on the form is "MinCharge" and the DLookup is referring to "MinCharge". Access is confused by having the same names - thus the circular reference.
You could use

=DLookUp("[Master].[MinCharge]","Master","Tname= '" & [TName] & "'")

.... but you really need to fix the underlying cause of the error.

(also note: "ID" is POOR name for a field and an Autonumber field should never be visible on a form/report.)


I stole the following from a post by orange - it is better than anything I could write:
---
"I recommend you create a narrative in simple terms about your proposed database and application. You don't start building a database by identifying a Form with 3 buttons. Now, that may very well be your initial user interface, but that is not the database. It is not as easy as many think to clearly describe WHAT your business is/will be. Consider the "things" involved(entities), the particular characteristics of each(attributes) that are important for your business and how these things relate to one another (relationships). After you have the narrative, you can use pencil and paper (or whiteboard or cardboard or ...) to develop and refine a data model. Create some test data and vet your model.
<snip>

<snip>
I see too many people, who have the latest HW and Access, jumping in head first think the software will build the database. They have multiple issues and can't access the data required for X and/or Y, and typically it is an issue of basic table and relationship design. Their next "rationalization" is " I've got too much invested to go back and correct the design...". Don't get yourself in that predicament.

You have to do the analysis, build a plan, set priorities......Since you have the business, you know the processes better than anyone, so analysis should be second nature-- and providing you are familiar with database concepts -- designing the database should be an iterative process and a good learning experience for you. The data model and testing/vetting will result in a blueprint for your database. Review, test, adjust, and repeat until you get the results you expect. People are here and willing to help."
---


I would suggest going back to the drawing board (white board, window, paper and pencil, cardboard,....) and designing the table structures.


------------------------------------------------------
And here is a little light reading that might help:


Normalization Terms and Concepts http://www.utteraccess.com/wiki/Norm...s_and_Concepts
Normalization http://www.utteraccess.com/wiki//Normalization


What is Normalization?
=======================
What Is Normalization, Part I: Why Normalization? http://rogersaccessblog.blogspot.com...on-part-i.html
What Is Normalization, Part II: Break it up. http://rogersaccessblog.blogspot.com...n-part-ii.html
What Is Normalization: Part III: Putting It Back Together http://rogersaccessblog.blogspot.com...-part-iii.html
What is Normalization: Part IV: More Relationships http://rogersaccessblog.blogspot.com...n-part-iv.html
What Is Normalization: Part V: Many-to-Many Relationships http://rogersaccessblog.blogspot.com...on-part-v.html


Entity-Relationship Diagramming
==============================
Entity-Relationship Diagramming: Part I http://rogersaccessblog.blogspot.com...ng-part-i.html
Entity-Relationship Diagramming: Part II http://rogersaccessblog.blogspot.com...g-part-ii.html
Entity-Relationship Diagramming: Part III http://rogersaccessblog.blogspot.com...ming-part.html
Entity-Relationship Diagramming: Part IV http://rogersaccessblog.blogspot.com...-i-ii-and.html



The Normal Forms
=========================
The Normal Forms: Introduction http://rogersaccessblog.blogspot.com...roduction.html
The Normal Forms: First Normal Form (1NF) http://rogersaccessblog.blogspot.com...-form-1nf.html
The Normal Forms: Second Normal Form (2NF) http://rogersaccessblog.blogspot.com...nd-normal-form.
The Normal Forms: Third Normal Form (3NF) http://rogersaccessblog.blogspot.com...rmal-form.html
The Normal Forms: In a Nutshell http://rogersaccessblog.blogspot.com...-nutshell.html

=====================================
GroverParkGeorge's PDF on Normalization http://www.gpcdata.com/downloads/nor...pgonaccess.pdf

=======================================
Access Basics Tutorial by Crystal (Strive4Peace) http://www.utteraccess.com/wiki/Access_Basics_Tutorial


===============================================
Autonumbers--What they are NOT and What They Are http://www.utteraccess.com/wiki/Autonumbers
Microsoft Access Tables: Primary Key Tips and Techniques http://www.fmsinc.com/free/newtips/primarykey.asp
Use Autonumbers properly http://access.mvps.org/access/general/gen0025.htm

===============================================

"Old Programmer's Rule" is this: If you can't do it on paper then you can't do it in Access. Until you can draw out your tables AND rules for how they change for each function you want to perform, you are going nowhere fast.

===============================================

[gcbeldar]

Thanks to all, Problem Solved. Changed the Names in Bills Table.
Help me to How to "refresh all" after change/Update TName field in Bills form.

[gcbeldar]

Thanks to All, Problem solved