IIf in control source (2003)

Topic

New Reply

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.

Reply | Quote

Replies

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

Address: IIf(IsNull([SID],[TAddress],[LAddress])

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.

Reply | Quote