Saving data from subform

[Shell]

I am sure this is a stupid question but it is completely escaping me.

Tables: Employee Data Feed
Service Date Changes

I have a form that uses "Service Date Changes" as its record source.
That form contains a linked subform which displays information from
"Employee Data Feed".

When my users enter data into the main form, that data saves
successfully. However, I also want some of the data that was pulled
into the subform to save on that same record. How do I do that??

Thanks so much!
Michelle

 

[Jeanette Cunningham]

Open the main form in design view.
Click once on the subform control so that you see just the black handles at
the side.

On the properties dialog, on the data tab, find both the Link Master Field
and the Link Child Field.
Click the small button at right to open the link dialog and choose the
primary key that relates the main form to the subform for both of the link
fields.
Save changes.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Shell]

Thanks so much for the reply Jeanette!

I had already done that piece and my form and subform are linked and
work correctly. I don't think I explained it well. Let me try again.

Table 1
Table 2

Main form uses Table 1, subform uses table 2. I want the data that was
displayed in my subform/table 2 to also be saved in Table 1 when the
user saves the main form. The reason for this is that Table 2 is
updated nightly and I want the historical record of what it was at the
time of change in Table 1. Does that make sense?

Happy New Year!

 

[John W. Vinson]

Thanks so much for the reply Jeanette!

I had already done that piece and my form and subform are linked and
work correctly. I don't think I explained it well. Let me try again.

Table 1
Table 2

Main form uses Table 1, subform uses table 2. I want the data that was
displayed in my subform/table 2 to also be saved in Table 1 when the
user saves the main form. The reason for this is that Table 2 is
updated nightly and I want the historical record of what it was at the
time of change in Table 1. Does that make sense?

Happy New Year!

Actually, no, it doesn't.

The record on the mainform is saved to Table1 the moment you set focus to the
subform. Normally a subform will display the "many" side of a one to many
relationship, so there could be hundreds of records from Table2 - which
obviously cannot be saved to a single record of Table1. And there's no
guarantee that your database will even be *open* when a change is made to
Table2.

You can maintain this kind of historical record, but your form-subform
approach is probably not the best way to do so!

What are these tables? What kind of information do they contain? How (if at
all) are they related? Do you want a new complete copy of all the data in
Table2 every day, or just selected records? If the latter, how are the records
to be selected?

 

[Shell]

Thanks John, I am in Human Resources and this database is used to
calculate people's various service dates. Say for example, someone is
hired on 1/1/09 and then takes a leave of absence from 3/1/09 to
4/1/09, we would need to change their service date to 2/1/09 to
account for the time they missed. There are several different dates
for different benefits with different rules so several date
calculations may or may not be done depending on the type and length
of leave. Because we are audited by the government on our plans, we
have to show cause for all changes to employee data related to their
benefits.

Table 1 is the table where all of the dates entered on the main form
are stored so it has the employee id, employee name, and lots of date
fields, and a few other things such as whether the leave was illness,
personal, termination, etc. This is important because it will show
our auditors what the start date was and how we calculated the new
date to prove we are doing the right thing.

Table 2 is a linked table that is automatically downloaded from our
HRMS system every night that contains employee information. This
table reads all of the dates from the system so that if someone is
making a change, they know what the current values are.

People may have several rows in table 1 if they have multiple leaves,
but they will always just have 1 row in table two because there is
only 1 employee record for them. Because we are updating our HRMS
system with these new dates, I need to store what they were at the
time of calculation to know what the starting point was to prove we
used the correct dates.

Does that make any more sense?

Thanks so much.
Michelle

 

[John W. Vinson]

Thanks John, I am in Human Resources and this database is used to
calculate people's various service dates. Say for example, someone is
hired on 1/1/09 and then takes a leave of absence from 3/1/09 to
4/1/09, we would need to change their service date to 2/1/09 to
account for the time they missed. There are several different dates
for different benefits with different rules so several date
calculations may or may not be done depending on the type and length
of leave. Because we are audited by the government on our plans, we
have to show cause for all changes to employee data related to their
benefits.

This seems misguided.

Surely the date that they started service was 1/1/09, regardless of how many
or how long their leaves might be!

Rather than updating their starting point, surely you should KEEP their actual
service date, and SUBTRACT the sum of all their leaves of absence???

 

[Shell]

It's standard practice, and we don't change the Hire Date, but if
someone was hired and works 5 years, quits, comes back 15 year later,
they shouldn't get a 20 year service award. If someone is hired, works
a month, and goes on a leave of absence for 4 years, they shouldn't be
100% vested in the retirement plan. Though, not really looking for a
debate in the practice, just looking for help in tracking it
properly

Happy New Year.

 

[John W. Vinson]

It's standard practice, and we don't change the Hire Date, but if
someone was hired and works 5 years, quits, comes back 15 year later,
they shouldn't get a 20 year service award. If someone is hired, works
a month, and goes on a leave of absence for 4 years, they shouldn't be
100% vested in the retirement plan. Though, not really looking for a
debate in the practice, just looking for help in tracking it
properly

Exactly.

You would subtract 15 years absence from the difference between hiredate and
today's date to get the time worked; or subtract four years absence from the
difference in the second case.

Storing a "fake" adjusted "not really a hire date" seems to be totally
backwards.

 

[Shell]

well, talk to PeopleSoft/Oracle about that, I didn't design the
system, I just have to work within it. Any help for me?

 

[John W. Vinson]

well, talk to PeopleSoft/Oracle about that, I didn't design the
system, I just have to work within it. Any help for me?

My apologies, then; my telepathy wasn't working and I didn't realize that you
didn't control the Access database, but were using a linked Oracle table.

You can force an update of the control on the mainform (assuming that it's
updateable - I don't have any idea how you would update the Oracle table if
you're importing the data from it) using code in the Subform's AfterUpdate
event, such as

Private Sub Form_AfterUpdate()
Parent!textboxname = Me!calculatedcontrolname
End Sub

This will overwrite whatever is in the control "textboxname" on the mainform
with the calculated value in the subform control named
"calculatedcontrolname".

 

[Shell]

Well, I can make the database anyway I want, but the end result always
needs to be a date to enter into the various fields in PeopleSoft. The
database has an ODBC connection to the SQL database that runs
PeopleSoft so that is how I get the live data. The database will not
insert back to Oracle, it will be manually entered.

The code you posted looks like it would be absolutely perfect except
that the subform won't be updated. It is information only. I just want
to store the dates that are on it.

Thanks again for your help!

 

[John W. Vinson]

The code you posted looks like it would be absolutely perfect except
that the subform won't be updated. It is information only. I just want
to store the dates that are on it.

In that case use either the AfterUpdate event of whichever control provides
the information needed for the calculation (there might be more than one), or
put a SendIt command button on the subform to do the calculation and send it
to the parent form.