setting Combo Box values depending on other control values

[amcg]

I'm designing a form with five combo boxes on it. These combo boxes each contain parts 1-5 of an Address respectively. When a user chooses an option from the first box, how do I restrict the options further down the form to addresses matching that first line?

As an example, here's what I'm using as the Row Source for Combo box 2 which contains the second part of the address:

Select distinct [Address 2] from dbo_Addresses where dbo_Addresses.[Address 1] = Forms![New Address]![Address 1]


Ideally this would be further refined so that when the user chooses an option from the first box the other 4 boxes should instantly display the rest of the Address that was chosen in the first box...

Can anybody help with this?

 

[Guest_imported]

In the AfterUpdate Event for each combobox, you can refine the information however you want.
Dim strSQL as String,strSQL2 as String, strSQL3 as String, strSQL4 as String, strSQL5 as String
strSQL = "Select distinct [Address 2] from dbo_Addresses where dbo_Addresses.[Address 1] = '" & Forms![New Address]![Address 1]& "'"
Me.Combo2.RowSource = strSQL
strSQL2 = "Select distinct [City] from dbo_Addresses where dbo_Addresses.[City]= '" & Forms![New Address]![City] & "'"
Me.Combo3.RowSource = strSQL2
Just follow this for each combobox.
Paul