Help filling in form from 2 different tables

[Tadbit]

I have two tables, CustomerData and EmployeeData. CustomerData has all the
address, phn, name, empid# of last employee to help em and custid (primary
key) and employeedata has name addr phn and empid (primarykey) I have a combo
box that pulls up custdata (by Custid sorting by Lastname & ", " & Firstname)
I wanna do an Afterupdate event to fill out the following textboxes (based on
the Custid it fills out the Lastname, Firstname, Address, Phnnumber) and then
based on the Empid of the last Employee that helped em it will fill out the
textboxes witht he first and last name of the employee from EmployeeData
table.

I linked the two tables together with customerdata on-to-many employeedata

I'm new to access and for the life of me i cant seem to change the value of
the textboxes to apporiate information from the tables. Any help would be
appreciated.

 

[John W. Vinson]

I have two tables, CustomerData and EmployeeData. CustomerData has all the
address, phn, name, empid# of last employee to help em and custid (primary
key) and employeedata has name addr phn and empid (primarykey) I have a combo
box that pulls up custdata (by Custid sorting by Lastname & ", " & Firstname)
I wanna do an Afterupdate event to fill out the following textboxes (based on
the Custid it fills out the Lastname, Firstname, Address, Phnnumber) and then
based on the Empid of the last Employee that helped em it will fill out the
textboxes witht he first and last name of the employee from EmployeeData
table.

I linked the two tables together with customerdata on-to-many employeedata

I'm new to access and for the life of me i cant seem to change the value of
the textboxes to apporiate information from the tables. Any help would be
appreciated.

STOP.

If you're trying to *copy* data from one table to another table... don't!

In addition, it would seem that you are making a mistake by storing the
employee's personal information in the customer table. Normally you would use
*three* tables: a table of CustomerData (custID as its primary key), a table
of Employees (emplID as its primary key), and a table of customer
interactions, with fields for CustID (who was being helped), EmplID (who
helped them), and fields for the date of the interaction and any other
information you want to record about the interaction. This would let you keep
a history of interactions, from which the most recent interaction could be
readily obtained.

So... why are you trying to store employee data redundantly in the customer
table? And do you really want to keep overwriting data? The identity of the
employee is *not* a characteristic attribute of a customer!

 

[Tadbit via AccessMonster.com]

Here is the scenario: I have a Table Customer data (stores all about the cust)
and another table with all current and past employees (checkbox states
weither they are still employeed for purposes of keeping employees in the
data since they are going be linked with the customers they helped) and i
have 2 forms to add new cust and add new employees.

Im working on a form that will be used to as a workorder type that employees
will type in remarks of what they helped the customers with and autostamp
date & time and which employee did the helping. My 4th form (currently what
the question was intended for) i want to choose the customer from a Combobox
and it will fill in name, address phn number of the cust as well as a subform
on it that upon loading the customer info will display a list of service
dates sorted decending by date but will also show the employee that helped
them.

I have a semi working way of doing it but i was looking for an easier way
since all i really need is the custid and empid and just load the data from
their respectable tables. Right now im loading up the cust info via Combobox
and then using VB to me.address = me.combo.column(X) and so forth to load the
cust into the data. This is resulting in saving the cust info and employee
info into the workorder table resulting in redunant data. I know there is an
eaiser less "spacious" area to achive this but i cant figure it out.

I have two tables, CustomerData and EmployeeData. CustomerData has all the
address, phn, name, empid# of last employee to help em and custid (primary

[quoted text clipped - 11 lines]

the textboxes to apporiate information from the tables. Any help would be
appreciated.

STOP.

If you're trying to *copy* data from one table to another table... don't!

In addition, it would seem that you are making a mistake by storing the
employee's personal information in the customer table. Normally you would use
*three* tables: a table of CustomerData (custID as its primary key), a table
of Employees (emplID as its primary key), and a table of customer
interactions, with fields for CustID (who was being helped), EmplID (who
helped them), and fields for the date of the interaction and any other
information you want to record about the interaction. This would let you keep
a history of interactions, from which the most recent interaction could be
readily obtained.

So... why are you trying to store employee data redundantly in the customer
table? And do you really want to keep overwriting data? The identity of the
employee is *not* a characteristic attribute of a customer!

 

[John W. Vinson]

Right now im loading up the cust info via Combobox
and then using VB to me.address = me.combo.column(X) and so forth to load the
cust into the data. This is resulting in saving the cust info and employee
info into the workorder table resulting in redunant data. I know there is an
eaiser less "spacious" area to achive this but i cant figure it out.

Just store the CustID and the WorkerID.

That's ALL YOU NEED in the workorder table. You don't need the names! You are
using a relational database - use it relationally! If you want to see the
names in conjunction with the data in the workorder table, you can *create a
query joining the workorder table* to the customer table by CustID, and
joining it to the employee table by EmplID; you will then have all the fields
available.

If you just want to see them on a form, rather than having code in the
afterupdate to "push" data from Column(x), you can use textboxes on the form
with a control source

=combobox.Column(x)

to "pull" it into view. You don't need any code nor a table field in the
workorder table in order to do so.