By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,154 Members | 2,313 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,154 IT Pros & Developers. It's quick & easy.

Help with tracking of contracts

P: 15
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 SQL statement or 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.

i tried to create a form and subform with this query


Expand|Select|Wrap|Line Numbers
  1. 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 won't work..

if you want to check the actual ms access file, here is the link:

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

. the name of the form with the subform is frmEmployee2.

thanks a lot and sorry for the inconovenience...
Oct 16 '06 #1
Share this Question
Share on Google+
1 Reply


PEB
Expert 100+
P: 1,418
PEB
So this is possible using before update or after update event procedure when the date is updated...

You need to run an append query that inserts the new row in the table...

If the current row is with the needed information, so you have to backup the previous date in the database and you need to use the before update event procedure using oldvalue property of your datecontract control...

The query should be

Docmd.runsql "INSERT INTO Yourcontracttables(ourfield1, Your field2, your contractdate) Values (field1,field2,'"+Mecontractdate.oldvalue+"');"

Best regards
Oct 21 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.