I have created some code and can not seem to get it to work correctly.
If the Ref Workload field is <> 0 the code is suppose to return the Ref Workload value from the Employee Table where the Employee ID and Date of Work field matches the same as the currrent record.
Employee ID: Number field
Date of Work: Date/Time field
Ref Workload: Number field
Code:
Private Sub Employee_ID Change()
Dim RefWorkload As Integer
RefWorkload = Nz(DLookup("Ref_Workload", "Employee Table", "[Employee ID] = " & Forms![Employee Table]![Employee ID] & " AND [Date of Work] =#" & Forms![Employee Table]![Date of Work] & "#"), 0)
If Len(Me.Ref_Workload & "") <> 0 Then
Me.Ref_Workload = RefWorkload
End If
End Sub