Change of Form Recipient to set dates

[matthewnsarah07]

I am using a database to collate and send staff holiday requests to their Line Manager.

At the moment when the member staff selects their name their Line Managers name is automatically added as an AfterUpdate events based on the table StaffNames.

When they submit the form the To: field is populated from the LineManager field.

This works fine until the manager is off for any reason and a deputy is in charge - is it possible to set something up whereby if the system date (shown in field RequestDate) falls within a given range then the Line Manager name is changed to the Deputys name.

I would do it manually but theres no guarantee I would be around to change things back.

Thanks for your help

 

[khawar]

Give detail of your tables how are the managers and their deputys saved

 

[khawar]

and also how are you saving their leave record

 

[matthewnsarah07]

Manager detail are saved in a table TeamManagers - this contains a unique id field [TMID] which links one to many to a table Staff Names.

The staff are then linked to their leave requests via their unique ID called [LeaveRecordID] - Staff are in table StaffNames and Leave is in LeaveRecords

The deputies are just in the staffnames table with everyone else

Any advice on where to list them

 

[khawar]

Is there some indication that in case a manager is on leave who will be his deputy
If possible upload some sample database

 

[matthewnsarah07]

I've been thinking about this problem.
If I added the field [Deputy] on the TMNames table next to the correct manager and then added a Yes/No type field called say [UseDeputy]

Is it possible to write some code or add something to the form that basically adds the Deputy name to the Line Manger field when the UseDeputy box is ticked or 'Yes' etc. - Could I do this?

 

[khawar]

I think you should use a single table for employees but add a field "AssistsTo"
like reports to in northwind sample database
in case the manager is absent then that employee can easily be picked who is assisting that specific manager