Need Help...

I have two tables TblEmp and TblEmpService with a common field EmpID.
TblEmp has the fields: EmpID, EmpName and Site. A form is attached to this
table to enter new or edit existing employee.
TblEmpService fields are : EmpID, EmpName, ServiceType,Site..etc
I made a form based on TblEmpService in which all the fields is there from
tblEmpService.
What I want is that if I put Employee ID in the EmpID text Box the Employee
Name should come in the EmpName text Box from the TblEmp. Also this Emplyee
ID should store in the TblEmpService.
Thanks & Regards,

hi Nad,

On 07.03.2010 13:51, Nad wrote:
I have two tables TblEmp and TblEmpService with a common field EmpID.
TblEmp has the fields: EmpID, EmpName and Site. A form is attached to this
table to enter new or edit existing employee.
TblEmpService fields are : EmpID, EmpName, ServiceType,Site..etc
Normally you don't need to store the name and the site redundantly.
I would recommend a table structure like this:

Employee:
ID AutoNumber, Primary Key, Not Null
Name Text(255), Not Null

ServiceType:
ID AutoNumber, Primary Key, Not Null
Name Text(255), Not Null

Site:
ID AutoNumber, Primary Key, Not Null
Name Text(255), Not Null

Service:
ID AutoNumber, Primary Key, Not Null
idEmployee Number(Long), Not Null
iderviceType Number(Long), Not Null
idSite Number(Long), Not Null
etc..

The primary key is a combined key over idEmployee, idServiceType and
idSite.

Build the foreign keys according to the field names in the service table.

What I want is that if I put Employee ID in the EmpID text Box the Employee
Name should come in the EmpName text Box from the TblEmp. Also this Emplyee
ID should store in the TblEmpService.
Build a new form based on the service table. Place a ComboBox for each
foreign key field. Bound it on that corresponding idTableName field.
Change the RowSource of these ComboBoxes to the corresponding table. Set
the Bound Field to 1, the Column Count to 2 and Column Widths to 0;5.


mfG
-- stefan --

On Sun, 7 Mar 2010 04:51:01 -0800, Nad wrote:

I have two tables TblEmp and TblEmpService with a common field EmpID.
TblEmp has the fields: EmpID, EmpName and Site. A form is attached to this
table to enter new or edit existing employee.
TblEmpService fields are : EmpID, EmpName, ServiceType,Site..etc
I made a form based on TblEmpService in which all the fields is there from
tblEmpService.
What I want is that if I put Employee ID in the EmpID text Box the Employee
Name should come in the EmpName text Box from the TblEmp. Also this Emplyee
ID should store in the TblEmpService.
Thanks & Regards,

You're mistaking how relational databases work. The employee name should exist
in TblEmp - and *NOPLACE ELSE*. Relational databases use the "Grandmother's
Pantry Principle": "A place - ONE place! - for everything, everything in its
place." You would store the name once (preferably as two fields LastName and
FirstName, perhaps middlename, Title and Suffix fields as well), and then use
Forms and Queries to tie them together. Table datasheets should *not* be used
for viewing or editing data, they're very limited.

See some of the tutorials he

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/acc...resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

--

John W. Vinson [MVP]

"John W. Vinson" wrote in message
...
On Sun, 7 Mar 2010 04:51:01 -0800, Nad
wrote:

I have two tables TblEmp and TblEmpService with a common field EmpID.
TblEmp has the fields: EmpID, EmpName and Site. A form is attached to this
table to enter new or edit existing employee.
TblEmpService fields are : EmpID, EmpName, ServiceType,Site..etc
I made a form based on TblEmpService in which all the fields is there
from
tblEmpService.
What I want is that if I put Employee ID in the EmpID text Box the
Employee
Name should come in the EmpName text Box from the TblEmp. Also this
Emplyee
ID should store in the TblEmpService.
Thanks & Regards,

You're mistaking how relational databases work. The employee name should
exist
in TblEmp - and *NOPLACE ELSE*. Relational databases use the
"Grandmother's
Pantry Principle": "A place - ONE place! - for everything, everything in
its
place." You would store the name once (preferably as two fields LastName
and
FirstName, perhaps middlename, Title and Suffix fields as well), and then
use
Forms and Queries to tie them together. Table datasheets should *not* be
used
for viewing or editing data, they're very limited.

See some of the tutorials he

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/acc...resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

--

John W. Vinson [MVP]

;:;jb:;

"Nad" a écrit dans le message de groupe de
discussion : ...
I have two tables TblEmp and TblEmpService with a common field EmpID.
TblEmp has the fields: EmpID, EmpName and Site. A form is attached to this
table to enter new or edit existing employee.
TblEmpService fields are : EmpID, EmpName, ServiceType,Site..etc
I made a form based on TblEmpService in which all the fields is there
from
tblEmpService.
What I want is that if I put Employee ID in the EmpID text Box the
Employee
Name should come in the EmpName text Box from the TblEmp. Also this
Emplyee
ID should store in the TblEmpService.
Thanks & Regards,