Combo Box Question: One customer, multiple address

[Veli Izzet]

Hi all,

I have a Sales form where the customer is selected via a combo box from
customers query.

Some customers have more than one shipping address, and I want to be
able to choose the shipping address again by a combo box.

However, I am not able to choose from multiple adresses for the
PARTICULAR Customer.

The combo box I use brings all the addresses.

I think (if there is such a thing) the row source for the combo box must
be a dynamic query based on the customer ID on the form.

The combo box in English should be:
Lookup the addresses of the customer, bring them in a list, let me
choose, then store it in the "Shipping Address" control of the Sales table.

I need help...

Some details:

Tables: Customers, Adresses, Sales. (sales table has a field: Shipping
Address)

Relationships: Customers and Adresses; Customers and Sales

 

[Allen Browne]

If there is one default address, but you may need to be able to override it
occassionaly, look at the Orders form in the Northwind sample database.

If one customer has many addresses (relaed table), and you want the 2nd
combo to be restricted to the addresses for the customer in the 1st combo,
see:
Limit content of combo/list boxes
at:
http://www.mvps.org/access/forms/frm0028.htm

 

[Veli Izzet]

Thanks,

I checked the Nortwind, that is not what I want. It seems the second
option is what I want, I will check it now..

 

[Veli Izzet]

Allen,

There is a mention of stored query to be called later. What is a stored
query, and where is it stored?

 

[Allen Browne]

I believe the article means a query that is saved (on the Queries tab of the
database window.)

Personally I find it easier to create the query statement as as string and
assign the string to the RowSource of the combo.

 

[Veli Izzet]

Yes, that is true. There is one problem that I could not solve however:
The last SQL query stays in the Rowsource of the 2nd combo box. So when
I go to the form next time, without choosing the value from the first
combo box, that query shows on the second combo box.

Is there a way to erase it like when leaving the from?

 

[Allen Browne]

Unless the combo's bound column is zero-width, you could use the Enter event
of the 2nd combo to alter its RowSource:
Call cbxComb1_AfterUpdate