Help with tracking of contract dates 4

[lushh]

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. if you want to see the ms access file, here's the link:

http://www.gigafiles.co.uk/files/636/HRIS/human resource info system_2006-09-27.zip

 

[rjoubert]

Since there can be more than one contract date per employee, you need another table to track the contract dates.

I'm assuming you already have an employee table...

Employee Table
Employee ID

 

[rjoubert]

Sorry, hit the submit a bit too soon...allow me to continue...

Employee Table
Employee ID Employee Name
1 Joe Schmo

EmployeeContracts Table
Employee ID ContractDate
1 1/1/2006
1 2/3/2006
1 3/23/2006

 

[lushh]

hi.. thanks for the replies... yup. i already have an employee table and contract tables.. the relationships are also fine. all that i lack is the code..

 

[Pampers]

Well, I don't think you need (much) code. You need a mainform with a subform (master/child), where the link master/child property in the subform are set. Mainform populated by the Employee-table, subform by the Contacts-table.

Pampers
Keeping it simple can be complicated

 

[lushh]

oh ok... sorry i am only new to programming.. and i am not really familiar with your advice. how can i set the (master/child) link.. i am really sorry for the inconvenience...

 

[lushh]

hi. i have already followed your advice. the form now looks like this:

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

how can the data entry be done? where should i type the data that needs to be tracked like the contract dates? thanks a lot. hope to hear from you again.

 

[WildHare]

If you right-click the SUBFORM to select it, and then select PROPERTIES on the Design toolbar, you'll see the property sheet for the embedded subform. Check out the Link Master/Link Child properties. They should be set to the Employee Number, which is your foreign key in the contract dates table. (I hope...)

If you have an explicit One-to-Many Relationship already set in your database, this linking would have happened automatically when you imbedded the subform in your main form.



Don't be sexist - Broads hate that.

 

[lushh]

hi.. i already tried to do that but still it won't work...

here is the query for the record source of the subform:

SELECT tblPosition.PositionName, tblContract.StartDateContract, tblContract.EndDateContract, tblCompany.CompanyName, tblDealer.DealerName, tblOutlet.OutletName
FROM (tblOutlet INNER JOIN (tblDealer INNER JOIN (tblCompany INNER JOIN tblPosition ON tblCompany.CompanyIDPK = tblPosition.CompanyIDFK) ON tblDealer.DealerIDPK = tblPosition.DealerIDFK) ON tblOutlet.OutletIDPK = tblPosition.OutletIDFK) INNER JOIN tblContract ON tblPosition.PositionIDPK = tblContract.PositionIDFK
WHERE (((tblContract.EmployeeIDFK)=[frmEmployee2].[EmployeeIDPK]));

but it's not working...

here is the link to the actual ms access file, in case you need a reference:

http://www.gigafiles.co.uk/files/636/human resource info system_2006-10-11.zip

the name of the form with the subform is frmEmployee2. thanks a lot and sorry for the inconvenience...

 

[PHV]

but it's not working...
Any chance you could elaborate ?
what happens ? error message ? computer crash ? unexprcted behaviour ? ...

Anyway the correct syntax is:
WHERE (((tblContract.EmployeeIDFK)=[!]Forms]![/!][frmEmployee2]![EmployeeIDPK]));

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lushh]

it's not working an enter parameter value appears... and it asks for frmEmployee2.EmployeeIDPK... and i can't type in the fields related to the subform...

 

[PHV]

Sorry for the typo, the criteria for tblContract.EmployeeIDFK should be:
[Forms]![frmEmployee2]![EmployeeIDPK]

provided frmEmployee2 is the open mainform.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lushh]

it still won't work.. is there something wrong with the child/master link? here is the link to the actual file...

http://www.gigafiles.co.uk/files/636/human resource info system_2006-10-11.zip

thanks a lot and sorry for the inconvenience...

 

[Pampers]

Hi Lushh,
Could you sent me an email (my address is in my profile) so I could mail back your database?


Pampers
Keeping it simple can be complicated

 

[lushh]

hi pampers.. i already sent you an email.. thanks a lot... =)

 

[Pampers]

Modified your database a bit. Take a look at how I setup the Employee-table and the Contract-table, and the relationship between them (1-n). Also look at the comboboxes and how to use them in a (sub)form, you store actually the ID's of the tables, not the text-values.

Pampers
Keeping it simple can be complicated

 

[lushh]

yup, thanks a lot.. it's really nice of you.. i was just wondering if the reports in the database will still work since you revised the relationships? and regarding the employee id, we really have a standard employee id here, is it possible for the subform to work if the employee id is not an auto number?