Updating form field from another table record field

[Mado]

Hi all,

i have a one-to-many relationship ( tblemployee and tblvacations) an employee can take many vacations and for each vacation there is the following data ( starting date, extended date, leave reason, paid? , rejoining date ....)

what i need is the following:
when I open the main employees form, I want to check the latest leave record for that employee, and if the re-joining date is not filled then a field will show "Employee currently on leave" or else it will show the latest rejoining date for that employee..

 

[jzwp22]

I would create a max query to get the most recent vacation date entry for each employee. Something like this (you will have to substitute your own table and fieldnames:

SELECT tblVac.fkEmpID, Max(tblVac.startingdate) AS MaxOfstartingdate
FROM tblVac
GROUP BY tblVac.fkEmpID;


Then I would joint this to the vacation table in another query

SELECT qryMaxVac.fkEmpID, tblVac.startingdate, IIF(isnull( tblVac.rejoiningdate), "Employee currently on leave", tblVac.rejoiningdate) AS vacstatus
FROM qryMaxVac INNER JOIN tblVac ON (qryMaxVac.fkEmpID = tblVac.fkEmpID) AND (qryMaxVac.MaxOfstartingdate = tblVac.startingdate);


On your main form, I would use an unbound textbox control with the following control source (you will have to substitute your won form/formcontrol names):

=DLookUp("vacstatus","qryMaxVacstatus","fkEmpID=" & Forms!frmEmp.pkempid)

I hope this helps