dlookup formula in Form

[Steve Stad]

I need a dlookup formula for a field in a form that will provide an org
number for a given Org_Name. I need to know where in the form to put the
dlookup, e.g., macro, code, what event, and syntax. They are all text
fields.

Tbl_Control has Control_OrgName and Control_OrgNumber.
The Master table has Emp_OrgName and Emp_OrgNumber.

I think it translates to something like ... dlookup Emp_OrgName in
TBL_Control where Emp_OrgName = Control_OrgName

 

[Arvin Meyer [MVP]]

A simple combo box can provide that data in a form. What are you trying to
do?

 

[Steve Stad]

Arvin,

I was trying to automatically populate a field named Org Number on a form
based on the input of Org Name on same form. OrgName and OrgNumber are
linked in two tables so I thought I could use a dlookup somewhere in code,
macro, form properties to do so.

Steve

 

[John W. Vinson]

Arvin,

I was trying to automatically populate a field named Org Number on a form
based on the input of Org Name on same form. OrgName and OrgNumber are
linked in two tables so I thought I could use a dlookup somewhere in code,
macro, form properties to do so.

You can, but ordinarily you would not WANT to do so. Storing the same
information in two different tables is redundant, which is a bad thing because
it's redundantly redundant!

The pairing of OrgName and OrgNumber should exist in the table of
organizations, and *noplace else*; other tables should just contain the
OrgNumber, and you would use a Combo Box, or a Query linking the two tables,
or (in restricted circumstances since it's slow and inefficient) a DLookUp.

What's this form's Recordsource? Are you in fact trying to copy both the
orgname and the orgnumber from the combo's rowsource into the form's
recordsource? If so, why?

 

[Steve Stad]

John,

I see what you mean. It may be a design issue. The Record Source for the
Form is a query of a Master Employee table. The Master Employee table
contains Org Name and Org Number and a Control Org Table also contains Org
Name and Org Number too. Are you saying the optimal design is to use a query
to link the Employee tbl to the Org Table by Org Name or Number? Would the
Org name or number populate automatically if one of the other fields was
primary key and linked in the relationship window? I currently have a combo
box for the Org name in form and was trying to use dlookup to populate the
Org number so the user would not have to populate the number if it could be
linked and entered automatically - but dlookup is getting 'Run time error
3075' syntax error (missing operator) in query or expression.

 

[John W. Vinson]

John,

I see what you mean. It may be a design issue. The Record Source for the
Form is a query of a Master Employee table. The Master Employee table
contains Org Name and Org Number and a Control Org Table also contains Org
Name and Org Number too. Are you saying the optimal design is to use a query
to link the Employee tbl to the Org Table by Org Name or Number? Would the
Org name or number populate automatically if one of the other fields was
primary key and linked in the relationship window? I currently have a combo
box for the Org name in form and was trying to use dlookup to populate the
Org number so the user would not have to populate the number if it could be
linked and entered automatically - but dlookup is getting 'Run time error
3075' syntax error (missing operator) in query or expression.

Yes, I would say it IS a design error. If you have an OrgName (don't use
blanks in fieldnames!) in the Org Table and also in the Employee Table,
there'd be nothing to prevent you from having it be "Doctors Without Borders"
in the Org Table and "Playboy Club" in the Employee table for the same org
number.

Relational databases use the "Grandmother's Pantry Principle": "A place - ONE
place! - for everything, everything in its place". I'd suggest having the
OrgNumber be the primary key of Organizations (or tblOrganizations or OrgTable
or whatever you call it, I dislike storing descriptive tags in object names,
many folks disagree with me); OrgName should exist ONLY in that table. You
would display the org name on Forms either by using a Query linking the two
tables; or a Combo Box storing the OrgNumber while displaying the OrgName; or
there are more elaborate techniques.

Note that users looking at the employee table will see only the OrgNumber.
*THIS IS CORRECT*. Tables are not designed for data display or editing, but
for data storage! Interaction with data should take place using Forms, which
have a rich toolset to display and manage the data. Tables don't!