Results 1 to 2 of 2
Thread: IIf in control source (2003)
2006-07-18, 17:16 #1
- Join Date
- Jun 2002
- Michigan City, Indiana, USA
- Thanked 0 Times in 0 Posts
IIf in control source (2003)
I have a form called frmTutorInfo which contains SSN, Name, and address of our tutors. Some of these tutors are students and some are not. Those who are students would already have an address in another table called tblPermData. I would like to either use the address from tblPermData if their SSN is in there or create something that automatically updates the tblTutorData address with the tblPermData address if there is a difference.
I tried the following expression: =IIf([txtSSN]=tblPermData!SID,tblPermData!LAddress,tblTutorData !TAddress) but am getting the #Name? error. What I'm trying to say is if the SSN on the form has a matching SSN in tblPermData, then use the address from tblPermData, otherwise, use the address from tblTutorData.
Is there a way to maybe have a macro run on load of the form that would check to see if the two addresses match and update them if they don't? Then I could forget my IIF statement.
Thanks for any help you can give.
2006-07-18, 17:27 #2
- Join Date
- Mar 2002
- Thanked 29 Times in 29 Posts
Re: IIf in control source (2003)
Create a query based on tblTutorData (I assume that's currently the record source of the form) and on tblPermData.
Join them on SSN vs SID.
Double click the join line and select the option to include ALL records from tblTutorData.
Add the fields you need.
Create a calculated column
Save this query and use it as record source for the form.
Set the control source of the address text box to the Address field you added.