I have a members form with a linked member contacts subform (child, spouse, sibling, parent). What I would like to do is: if the user selects that the contact is a spouse or child, have the address auto-fill with the same address as the member. If the contact is any other, the address would remain blank and have to be entered.

I tried to do an iif statement in the on enter property box where the address would pull from the table if the contact of spouse or child was selected, with no luck.

I then tried to do the same iif statement but pull the values from a query with the same result. (error message in both cases was "the object does not contain the automation object".

Is an iif statement in the on enter property the right way to go about this?

Any help would be appreciated!

umm no, that violates normalization rules. What you need to do is change your table structure. Full normalization would mean having a household table and address table and household member table. The Household table holds info about the hoiusehold itself. the address table the household address with a foreign key to Household record. The Member table lists the names of the household members, info about them specifically and a foreign key to the household table.

Normalizing your tables is always a good start.

If you decide to keep your table structure, use the After Update event on the control where you select the contact type. Put code there to set the value of the contact address.

ScottGem,

I see what you are saying; however, the contacts are not only within the household. And, the spouses will not always have the same address nor will the children. And, all these contacts get periodic mailings. I do need a separate address for each contact.

For example; I have a member and his contact list might include his parents, his children, his spouse, his uncle, and his sister and all of them may have different addresses.

I was defaulting the address to speed up data entry even though it is not 100% of the time. But if this is not normal is there a better way to approach this given the above info?

What would be the operator I use to set the value of the field? Thank you!

Create a data entry form with a main form bound to the Household table I described. and a subform bound to the Members table. On the subform have a combobox for Address. Use the Not In List event to popup a form where you can add a new address if its not there (Search UA on NotInList for more on how to do that).

Now, you enter the household, add a member, who is then linked to the household then add or enter their address. Data entry is simplfied and your tables are propelry related.