Reference currently selected record value in RowSource SQL stateme

[Guest]

I have a Person and an Address table. The relevant fields are:

Person.ID
Person.defaultAddress
Address.ID
Address.PersonID
Address.category

These two tables have a one-to-many relationship between Person.ID and
Address.PersonID. I have it set up like this so that each Person record can
store multiple address records.

I have a form based on the Person table. I would like to create a combo box
entitled "Default Address" that does the following:

A) populates the selection list (via a RowSource statement?) with the
Address.category values for the currently selected Person record,

Example:

Person #30 has two addresses stored in the Address table, as follows:

ID, PersonID, category, ...
3, 30, Home, ...
4, 30, Work, ...

On my Person form, the combo box would list "Home" and "Work" as possible
selections.

B) stores the Address.ID for the selected Address.category value in the
Person.defaultAddress field.

Example:

When I select "Home" in the combo box for Person #30, the value "3" is
stored in the Person.defaultAddress field.

Any ideas how I can do this? Is this even possible?

Thank you in advance for your assistance.

 

[Guest]

Hi Daniel,

I believe this should be fairly straightforward. To start with, place a
combo on your form bound to the Person.defaultAddress field. The trick will
be in the rowsource of the combo. Select the Address table, then click just
to the right of it to open the query generator. Select the Address.ID field,
and the Address.Category field, and finally the Address.PersonID. For the
criteria of the Address.PersonID field, enter the following:

Forms!YourFormName!YourPersonIDControlName

But replace the second part with your actual form name, and the third with
the name of the control on your form that contains the person's ID.

This will automatically limit the drop-down selections to the appropriate
person.

You can deselect the checkbox for the Address.PersonID field, since there is
no need to display it on the dropdown. Then, just set your drop-down to
display two columns, set your desired column widths, and you should be in
business. Post back if you run into any problems.

HTH, Ted Allen