After Update event to populate second field

[JudyB]

I am working on a database that has a main form and three subforms (all in
tabs). In the 1st subform, I have a field that I would like to automatically
duplicate in a field on the 2nd tab. I have tried to use the "After Update"
event to get the job done, but I have not had any luck. This is my first
database and I'm sure my coding is off. Can anyone help?

 

[Evi]

You omitted to include your coding, Judy
Evi

 

[JudyB]

Sorry...Code I have been trying to use is -
Me.[Service Record subform].Form![Department Name] = [Department Service
subform].Form![Department Name]
I am trying to get the data entered in the Department Name field of the
Service Record subform to automatically populate the Dapartment Name in the
Department Service subform.
Thanks for any help. Also can you recommend a good Access book for
beginners? I purchased "Access 2007 for Dummies" but it hasn't helped much.
I am visual so examples help.

 

[John W. Vinson]

Sorry...Code I have been trying to use is -
Me.[Service Record subform].Form![Department Name] = [Department Service
subform].Form![Department Name]
I am trying to get the data entered in the Department Name field of the
Service Record subform to automatically populate the Dapartment Name in the
Department Service subform.

The Department Name should almost certainly NOT be stored in the subform's
table. What are the Recordsources of the mainform and subform? What are the
Master and Child Link Fields? If the mainform contains information about the
department, then the subform should contain ONLY a link to that table; it's
not necessary nor appropriate to store the department name redundantly.

 

[JudyB]

Hi John,
Thanks for responding. It sounds like I may be in over my head. I'll tell
you what I have and what I am trying to do. My tables along w/fields are:
Employees - (First Name, Last Name, Dept Seniority, Job Seniority, & Hire
Date); Service Record - (Dept. Name, job Title Name, Date In, & Date Out);
Department Service - (Dept. Name & Service Time);
Job Title Service - (Job Title Service & Service Time)
I created a form. "Employees" is the main form. "Service Record" is my
subform shown in a tab. I have two other tabs - Department Service and Job
Title Service.
I am trying to track an employee's service time within each department and
each job title. I was using the Department Service and Job Title Service
tabs as a means to keep a running total of time in each Department/Job Title.
Can you steer me in the right direction to accomplish this? Thanks again!
--
JudyB

Sorry...Code I have been trying to use is -
Me.[Service Record subform].Form![Department Name] = [Department Service
subform].Form![Department Name]
I am trying to get the data entered in the Department Name field of the
Service Record subform to automatically populate the Dapartment Name in the
Department Service subform.

The Department Name should almost certainly NOT be stored in the subform's
table. What are the Recordsources of the mainform and subform? What are the
Master and Child Link Fields? If the mainform contains information about the
department, then the subform should contain ONLY a link to that table; it's
not necessary nor appropriate to store the department name redundantly.

 

[John W. Vinson]

Hi John,
Thanks for responding. It sounds like I may be in over my head. I'll tell
you what I have and what I am trying to do. My tables along w/fields are:
Employees - (First Name, Last Name, Dept Seniority, Job Seniority, & Hire
Date);

This table should certainly have a Primary Key, probably a numeric EmployeeID.
You cannot count on names to be unique. I'd also recommend avoiding blanks in
fieldnames - e.g. FirstName instead of First Name.

Service Record - (Dept. Name, job Title Name, Date In, & Date Out);

Is this a service record *for an employee*"?? If so, you need some way to tell
WHICH empployee, such as the employeeID.

Department Service - (Dept. Name & Service Time);

Is this a table of unique departments, or - again - service for the employee?
How is this table related (logically, based on the data in it) to the Service
Record table?

Job Title Service - (Job Title Service & Service Time)

Same questions.

I created a form. "Employees" is the main form. "Service Record" is my
subform shown in a tab. I have two other tabs - Department Service and Job
Title Service.

It really sounds like you started your design with the Forms. That's
backwards. You need to get the Tables right FIRST, and then create forms as
windows onto the tables!

I am trying to track an employee's service time within each department and
each job title. I was using the Department Service and Job Title Service
tabs as a means to keep a running total of time in each Department/Job Title.
Can you steer me in the right direction to accomplish this? Thanks again!

If you're using the "TABS" as a way to keep track... that's your first
problem. Forms *do not contain any data*. Forms are just tools, windows that
let you see and edit data which is stored in tables, and only in tables.

It's not clear to me what "service time" is, but if it is (or can be)
calculated from the date in and date out, then it should not be stored AT ALL,
in ANY table; it can instead by calculated on the fly in a Query.

My GUESS - and it's purely a guess, since I don't know your business - is that
you need four tables (for this portion of the application anyway):

Departments <just a lookup table for departments>
DepartmentID
DepartmentName

Jobs
JobID
JobDescription

Employees
EmployeeID <Primary Key; maybe autonumber, maybe tax ID, a unique identifier>
LastName
FirstName
HireDate
<other biographical info as needed>

JobHistory
JobHistoryID <autonumber primary key>
EmployeeID <whose history is this>
DepartmentID <which department were they in at this time>
JobID <what job were they doing at this time>
StartDate <date/time>
EndDate <date/time, NULL if they're still in the job>


Time in service, seniority, etc. can probably all be calculated dynamically
from the data in the job history table.

 

[JudyB]

Thanks John! I did set up the tables first and do have a Primary Key
(EmployeeID) in the Employee table. I will make adjustments to leave out the
blanks in fieldnames. I also have the EmployeeID in the Service Record
table. I was trying to make my message short so I left that out.

The Department Service and Job Title Service I had also setup as tables. I
was wanting to use these tables to calculate an employees time in each
Department and each Job Title. For example: John Doe worked in HR Dept for
52 weeks, promoted to a different department for 25 weeks, and then returns
to HR Dept where he works for another 52 weeks. I was wanting the database
to tell me that John Doe has a total service time in the HR Dept of 104
weeks.

I see what you are talking about by not using tables at all, but to
calculate on the fly in a Query. I will give that a try. I will have to
read up on how to accomplish that task. As you can tell...I am green!

Again...Thanks so much!

 

[John W. Vinson]

Thanks John! I did set up the tables first and do have a Primary Key
(EmployeeID) in the Employee table. I will make adjustments to leave out the
blanks in fieldnames. I also have the EmployeeID in the Service Record
table. I was trying to make my message short so I left that out.

The Department Service and Job Title Service I had also setup as tables. I
was wanting to use these tables to calculate an employees time in each
Department and each Job Title. For example: John Doe worked in HR Dept for
52 weeks, promoted to a different department for 25 weeks, and then returns
to HR Dept where he works for another 52 weeks. I was wanting the database
to tell me that John Doe has a total service time in the HR Dept of 104
weeks.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

I see what you are talking about by not using tables at all, but to
calculate on the fly in a Query. I will give that a try. I will have to
read up on how to accomplish that task. As you can tell...I am green!

Take a look at the DateDiff function in the VBA help (type ctrl-G to open the
VBA editor and then search for DateDiff).