Automatically populate a field using a field on another form

[SikhSioux]

I wonder if someone can help me:
I have a customer enquiry form (frmCustEnquiry) within which is a combo box field, the combo box stores the customers department ID (DeptId). The email address for the department sometimes changes, the changes are stored in a table (tblDeptEmailAddress) which contains the fields DeptId, deptEmailAddress and dateEmailAddressIntroduced. After all the fields in the frmCustEnquiry form are completed a new form (frmEmailCust) opens which allows the user to email a response to the customer. I need to include a field in form frmEmailCust which uses the DeptId in the frmCustEnquiry form and the tblDeptEmailAddress table to display the latest email address for the department. I can then use this to send an email reply to the customers department

Thanks

 

[SikhSioux]

I forgot to mention what I've tried so far:

I created a totals query which shows the deptEmailAddress where the DeptId is equal to the value of the DeptId in the frmCustEnquiry form and the dateEmailAddressIntroduced field has its maximum value. I then successfully tested this query. I then created a form based on this query which displayed the deptEmailAddress field. I successfully tested this form i.e. the email address displayed successfuly on the form. I then included this form as a subform on the frmEmailCust form, for some reason the email address no longer displayed!

You maybe wondering why the email address always changes, well sometimes a new customer might not be able to access the departments group mailbox, in which case they would use there own email address until they were given access