Get Data for Field by Dlookup

[Irshad Alam]

I have a table named - "EmpVacation" , the fields are EmpName, VacFrom,
VacTo, VacRemarks. When any employee go on vacation, I update this table.

There is a Form for posting daily attendance named "EmpAtdnF", which is
based on another table "EmpAtdnT". Every day I post data for attendance. The
form is a Tabular type Form and the field are : EmpName, CurDate, AttndStatus

My requirement :
I want that when I update the EmpName Field, It should do the following job
by the help of code :
A. Fill the next field value as Current Date
B. DLookuUp the Vacation Table, if the same name employee is on vacation
(means the current date falls inbetween the vacation period) then it post
AttndStatus.value as "OnVac" and if not the employee is not on vacation it
will post the value "Present"

Please advise what code should I write after Update Event of the EmpName
field of Form.

Regards.

Irshad

 

[Ken Snell [MVP]]

Assuming that the name of the control bound to the EmpName field is named
ctlEmpName (change all other names to match reality):

Private Sub ctlEmpName_AfterUpdate()
Dim lngCount As Long
Me.DateControlName.Value = Date()
lngCount = DCount("*", "VacationTableName", "EmpName='" & _
Me.ctlEmpName.Value & "' And #" & Format(Date(), _
"mm\/dd\/yyyy") & "# Between VacFrom And VacTo")
Me.VacationStatus.Value = IIf(lngCount = 0, "Present", "OnVac")
End Sub