How to track contract dates

[lushh]

hi guys. i am just a newbie here. i am currently working on an employee
database using MS access 2003. i wanted to track all the contract dates of a
certain employee. is that possible?

whenever the user would enter a new contract date for a certain employee, it
will not overwrite the previous contract dates, hence it would add up to the
list of the contract dates for that certain employee...

what could be the appropriate VBA command or function for that? i've tried to
search for this function, but unfortuntely i wasn't able to find what i've
been looking for. i would definitely appreciate all the help you guys can
give. thank you so much.

 

[Guest]

There is more than one issue here. First, you need the correct database
design. Since you will have multiple contract dates for an employee, you
need two tables - One to store Employee info and the other to store contract
data for employees. This is a normal one to many relationship.

Each of your employee records needs to have a unique field and the records
in the contract dates table needs to have a field to store the employee's
unique field value so you know which contract date records belong to which
employees. The most common way to do that is with an Autonumber field in the
employee table. You could use an employee number, but I don't recommend it.
For example, where I am, we hire contract labor. They do not get a direct
hire ID number, but a contractor number. Many are later hired as direct
employees. When that happens, the get a direct employee number. So if we
used Employee number as the identifier, we would not be able to trackt their
data as one continuous employee.

Now, as to entering the data. You will want a form/subform construct where
the Employee table is the record source for the form and the contract dates
table is the record source for the subform. Use the Link master/child fields
properties of the subform control to keel the contract table data in sync
with the form data.

 

[lushh via AccessMonster.com]

thank you for the reply. in our case, since we are a human resource
outsourcing company, we deploy contractual employees to different companies..
we do maintain a single employee id in our company. if you want to see what i
have already accomplished here is the link to my access file:
http://www.gigafiles.co.uk/files/636/HRIS/human resource info system_2006-09-27.zip

There is more than one issue here. First, you need the correct database
design. Since you will have multiple contract dates for an employee, you
need two tables - One to store Employee info and the other to store contract
data for employees. This is a normal one to many relationship.

Each of your employee records needs to have a unique field and the records
in the contract dates table needs to have a field to store the employee's
unique field value so you know which contract date records belong to which
employees. The most common way to do that is with an Autonumber field in the
employee table. You could use an employee number, but I don't recommend it.
For example, where I am, we hire contract labor. They do not get a direct
hire ID number, but a contractor number. Many are later hired as direct
employees. When that happens, the get a direct employee number. So if we
used Employee number as the identifier, we would not be able to trackt their
data as one continuous employee.

Now, as to entering the data. You will want a form/subform construct where
the Employee table is the record source for the form and the contract dates
table is the record source for the subform. Use the Link master/child fields
properties of the subform control to keel the contract table data in sync
with the form data.

hi guys. i am just a newbie here. i am currently working on an employee
database using MS access 2003. i wanted to track all the contract dates of a

[quoted text clipped - 8 lines]

been looking for. i would definitely appreciate all the help you guys can
give. thank you so much.

 

[lushh via AccessMonster.com]

thank you for the reply. in our case, since we are a human resource
outsourcing company, we deploy contractual employees to different companies..
we do maintain a single employee id in our company. if you want to see what i
have already accomplished here is the link to my access file:
http://www.gigafiles.co.uk/files/636/HRIS/human resource info system_2006-09-27.zip

There is more than one issue here. First, you need the correct database
design. Since you will have multiple contract dates for an employee, you
need two tables - One to store Employee info and the other to store contract
data for employees. This is a normal one to many relationship.

Each of your employee records needs to have a unique field and the records
in the contract dates table needs to have a field to store the employee's
unique field value so you know which contract date records belong to which
employees. The most common way to do that is with an Autonumber field in the
employee table. You could use an employee number, but I don't recommend it.
For example, where I am, we hire contract labor. They do not get a direct
hire ID number, but a contractor number. Many are later hired as direct
employees. When that happens, the get a direct employee number. So if we
used Employee number as the identifier, we would not be able to trackt their
data as one continuous employee.

Now, as to entering the data. You will want a form/subform construct where
the Employee table is the record source for the form and the contract dates
table is the record source for the subform. Use the Link master/child fields
properties of the subform control to keel the contract table data in sync
with the form data.

hi guys. i am just a newbie here. i am currently working on an employee
database using MS access 2003. i wanted to track all the contract dates of a

[quoted text clipped - 8 lines]

been looking for. i would definitely appreciate all the help you guys can
give. thank you so much.

 

[Guest]

Having worked in your industry for the last 15 years, I understand exactly
what you are doing. I can't, however, download anything. Sorry, but is it
just too risky these days. I hope you understand.

Even though you maintain a consistent employee id, the contract date issue
is the same. You need a table of assignments which, of course, includes
begin date, expected end date, and actual end date. This table needs to be
related to both the employee and the client.

thank you for the reply. in our case, since we are a human resource
outsourcing company, we deploy contractual employees to different companies..
we do maintain a single employee id in our company. if you want to see what i
have already accomplished here is the link to my access file:
http://www.gigafiles.co.uk/files/636/HRIS/human resource info system_2006-09-27.zip

There is more than one issue here. First, you need the correct database
design. Since you will have multiple contract dates for an employee, you
need two tables - One to store Employee info and the other to store contract
data for employees. This is a normal one to many relationship.

Each of your employee records needs to have a unique field and the records
in the contract dates table needs to have a field to store the employee's
unique field value so you know which contract date records belong to which
employees. The most common way to do that is with an Autonumber field in the
employee table. You could use an employee number, but I don't recommend it.
For example, where I am, we hire contract labor. They do not get a direct
hire ID number, but a contractor number. Many are later hired as direct
employees. When that happens, the get a direct employee number. So if we
used Employee number as the identifier, we would not be able to trackt their
data as one continuous employee.

Now, as to entering the data. You will want a form/subform construct where
the Employee table is the record source for the form and the contract dates
table is the record source for the subform. Use the Link master/child fields
properties of the subform control to keel the contract table data in sync
with the form data.

hi guys. i am just a newbie here. i am currently working on an employee
database using MS access 2003. i wanted to track all the contract dates of a

[quoted text clipped - 8 lines]

been looking for. i would definitely appreciate all the help you guys can
give. thank you so much.

 

[lushh via AccessMonster.com]

ok. i do understand.. =) but it will be much better if you can see the actual
file, because it's quite complicated. there are companies, positions, dealers
and outlets. i already did constuct tables. the relationships are complete.
all that i lack is the actual code.. =)

Having worked in your industry for the last 15 years, I understand exactly
what you are doing. I can't, however, download anything. Sorry, but is it
just too risky these days. I hope you understand.

Even though you maintain a consistent employee id, the contract date issue
is the same. You need a table of assignments which, of course, includes
begin date, expected end date, and actual end date. This table needs to be
related to both the employee and the client.

thank you for the reply. in our case, since we are a human resource
outsourcing company, we deploy contractual employees to different companies..

[quoted text clipped - 31 lines]

 

[Guest]

If you have a form that is bound to the employee table with a subform bound
to the contract dates table, it really doesn't take that much in coding other
than data validation and calculations you may need to do.

ok. i do understand.. =) but it will be much better if you can see the actual
file, because it's quite complicated. there are companies, positions, dealers
and outlets. i already did constuct tables. the relationships are complete.
all that i lack is the actual code.. =)

Having worked in your industry for the last 15 years, I understand exactly
what you are doing. I can't, however, download anything. Sorry, but is it
just too risky these days. I hope you understand.

Even though you maintain a consistent employee id, the contract date issue
is the same. You need a table of assignments which, of course, includes
begin date, expected end date, and actual end date. This table needs to be
related to both the employee and the client.

thank you for the reply. in our case, since we are a human resource
outsourcing company, we deploy contractual employees to different companies..

[quoted text clipped - 31 lines]

been looking for. i would definitely appreciate all the help you guys can
give. thank you so much.

 

[lushh via AccessMonster.com]

oh ok.. are there any sites that has sample codes for this? because i am
really new to VBA and MS access...

If you have a form that is bound to the employee table with a subform bound
to the contract dates table, it really doesn't take that much in coding other
than data validation and calculations you may need to do.

ok. i do understand.. =) but it will be much better if you can see the actual
file, because it's quite complicated. there are companies, positions, dealers

[quoted text clipped - 15 lines]

 

[Guest]

There are a lot of resources.

Here are a few sites to see:

http://www.mvps.org/access/
http://allenbrowne.com/tips.html
http://www.lebans.com/

Now, to start you will need a form. The Record Source of the form should be
based on a query that has the fields you need to display information about
the employee for the purpose of entering the contract dates.
You will want to use a Combo Box that is not bound to any field in the
Record Source for the purpose of looking up the employees. Combo boxes have
a row source property. The row source property is similar in nature to the
record source property of a form. It should be a query that has the minimum
number of columns to display the employee name and the employee id. If the
employee id is the primary key of the employee table, it should be the bound
column of the combo. The employee name should be only for display purposes.
On the sites I listed above, you should be able to get details on how to use
a combo to do searches. If not, we can cover that later. First, let's get
the form working.

On your form you will need a subform control. Do not confuse a subform
control with a subform. The subfrom is only another form object. The
subform control is a control object that you put on a form. This allows you
to embed one form into another so you can display information related to the
data on the main form. The subform will have its own record source. In this
case, it will be the contract dates table or a query based on the table.

The subform control has a Source Object property. This is the name of the
subform. It also has a Link Child Fields property and a Link Master Fields
property. These properties are used to relate the employee record to the
contract dates records. In the Link Child Fields, you put the name of the
field (or fields) in the contract date record that relates to the employee
record. In your case, it will probably be the employee id or, if you are
using autonumber primary keys, the foreign key field in the contract date
record that is the primary key value of the employee it belongs to. The same
is true for the Link Master Fields property. It should be the name of the
field in the employee table that relates to the contract date records.

Hopefully, this will get you started. Feel free to post back when you have
more questions. These newsgroups are a really good source of information.

Good Luck.

oh ok.. are there any sites that has sample codes for this? because i am
really new to VBA and MS access...

If you have a form that is bound to the employee table with a subform bound
to the contract dates table, it really doesn't take that much in coding other
than data validation and calculations you may need to do.

ok. i do understand.. =) but it will be much better if you can see the actual
file, because it's quite complicated. there are companies, positions, dealers

[quoted text clipped - 15 lines]

been looking for. i would definitely appreciate all the help you guys can
give. thank you so much.

 

[lushh via AccessMonster.com]

thank you so much.. you don't know how much it will help me.. the deadline
for this project is on october 20 and i am working on it for 3 months now.
i'm just a fresh grad with a limited knowledge about programming and database,
so it will definitely be a big help. thanks a lot Klatuu... God bless.. wish
i could repay you someday... i'll just keep you posted.. good day...

There are a lot of resources.

Here are a few sites to see:

http://www.mvps.org/access/
http://allenbrowne.com/tips.html
http://www.lebans.com/

Now, to start you will need a form. The Record Source of the form should be
based on a query that has the fields you need to display information about
the employee for the purpose of entering the contract dates.
You will want to use a Combo Box that is not bound to any field in the
Record Source for the purpose of looking up the employees. Combo boxes have
a row source property. The row source property is similar in nature to the
record source property of a form. It should be a query that has the minimum
number of columns to display the employee name and the employee id. If the
employee id is the primary key of the employee table, it should be the bound
column of the combo. The employee name should be only for display purposes.
On the sites I listed above, you should be able to get details on how to use
a combo to do searches. If not, we can cover that later. First, let's get
the form working.

On your form you will need a subform control. Do not confuse a subform
control with a subform. The subfrom is only another form object. The
subform control is a control object that you put on a form. This allows you
to embed one form into another so you can display information related to the
data on the main form. The subform will have its own record source. In this
case, it will be the contract dates table or a query based on the table.

The subform control has a Source Object property. This is the name of the
subform. It also has a Link Child Fields property and a Link Master Fields
property. These properties are used to relate the employee record to the
contract dates records. In the Link Child Fields, you put the name of the
field (or fields) in the contract date record that relates to the employee
record. In your case, it will probably be the employee id or, if you are
using autonumber primary keys, the foreign key field in the contract date
record that is the primary key value of the employee it belongs to. The same
is true for the Link Master Fields property. It should be the name of the
field in the employee table that relates to the contract date records.

Hopefully, this will get you started. Feel free to post back when you have
more questions. These newsgroups are a really good source of information.

Good Luck.

oh ok.. are there any sites that has sample codes for this? because i am
really new to VBA and MS access...

[quoted text clipped - 8 lines]

 

[lushh via AccessMonster.com]

hi klatuu... i already did the subform and it looked like this:

http://i114.photobucket.com/albums/n258/lushh_16/subform.jpg

I just want to know where can i add a data entry for the contract dates... i
tried to type in one of the fields, but it won't allow me to...

hope to hear from you again. thank you so much...

thank you so much.. you don't know how much it will help me.. the deadline
for this project is on october 20 and i am working on it for 3 months now.
i'm just a fresh grad with a limited knowledge about programming and database,
so it will definitely be a big help. thanks a lot Klatuu... God bless.. wish
i could repay you someday... i'll just keep you posted.. good day...

There are a lot of resources.

[quoted text clipped - 46 lines]