I have a form Medical Leave Table, which allows a user to update information for employees which are on medical leave. The user requested the form also track the employees available FMLA each year. I created a querry which sums the FMLA used for each employee, then another querry which calculates the remaining FMLA for that employee. I need the Remaining FMLA to be displayed on the Medical Leave Table, however, when use the control source option to select the information from the query containing the information, it returns the #Name? error. I used the builder to select the feild from the query, so it cant be the way it is spelled not matching the way it is spelled on the query. I built a relationship between the query and the Table: Medical Leave on which the Form: Medical Leave Table is based thinking perhaps the reason I was getting the error was related to the form not being able to disquish which feild was correct for that record. All of my relationships in the database are based on the Employee ID.
I have attempted to create a DLookup function, and a DSum function. I would prefer the calcuation be done in the query because I need to reset the available amount each year, and if the calculation is in the query, I can set the query the crosstab runs from to have a date criteria. I understand this could also be done within in the form most likely, however, the user doesnt have any control over the design of the form, but can change the dates in the query if needed.
In short, I really just need to know how to effectively display the related record from the query on the form. Both are based on the Employee ID, and the relationship has been built in the relation design, though Im not sure it was necessary since the query runs from the table to which I built the relationship...
Not sure I am making much sense at this point, Im pretty frustrated by it. LoL
Any help is much appreciated, thanks!