basing TWO combo boxes on another combo box in a form.

[Guest]

I have been able to base ONE combo box on another combo box using an article
at the Office online website. But I have THREE combos I need to 'join' (if
thats right word).

Customer combo box
Delivery Address combo box
Customer Contact combo box

I have managed to limit the delivery address combo list to only the customer
selected, but cannot seem to use the same code in the third box.

Any help with this would be much appreciated.

 

[BruceM]

You should post the code you are using, along with a bit more information
about the table structure. Are Delivery Address and Contact in the Customer
table, or are they in related tables, or what exactly? Also, it may be
helpful to know which specific article on the Office website is the basis of
your approach.

 

[Guest]

BruceM,

Thanks for the reply. I got the article from
http://office.microsoft.com/en-gb/access/HA011730581033.aspx

My structure is:-

tblCustomers
tblCustomers Delivery Addresses
tblCustomers Contacts

 

[Guest]

Sorry the code is :-

Private Sub cus_AfterUpdate()
Me.del = Null
Me.del.Requery
Me.del = Me.del.ItemData(0)
End Sub


Private Sub Form_Current()
Me.del.Requery
End Sub

Private Sub Form_Load()
Me.cus = Me.cus.ItemData(0)
Call cus_AfterUpdate
End Sub

 

[Guest]

The prefered approach for me to filter one combo based on another, is to
filter the RowSource on the second combo by the selection of the first one.

The RowSource of combo 2 will have

Select FieldNAme From TableNAme Where FieldName =
Forms![FormName]![Combo1Name]

On the After Update event of combo one I run the code
Me.[Combo2Name].Requery

To refresh the list
***********************************
In this method Combo 3 will have that type of SQL as it's RowSource to
include the filter of two combo's

Select FieldName From TableName Where FieldName1 =
Forms![FormName]![Combo1Name] And FieldName2 = Forms![FormName]![Combo2Name]

On the AfterUpdate event of Combo2 run the code
Me.[Combo3Name].Requery
***********************************

 

[BruceM]

I will leave you to Ofer Cohen's part of this thread, as it is the approach
I would have taken. Here are a couple of links that may help you to
understand the process. The article seems to take the same general
approach, but it gets there by a roundabout route.

http://www.mvps.org/access/forms/frm0028.htm
http://www.databasedev.co.uk/filter_combo_boxes.html

 

[Guest]

GOT IT!!

Thank you very much to Ofer Cohen and BruceM.

The prefered approach for me to filter one combo based on another, is to
filter the RowSource on the second combo by the selection of the first one.

The RowSource of combo 2 will have

Select FieldNAme From TableNAme Where FieldName =
Forms![FormName]![Combo1Name]

On the After Update event of combo one I run the code
Me.[Combo2Name].Requery

To refresh the list
***********************************
In this method Combo 3 will have that type of SQL as it's RowSource to
include the filter of two combo's

Select FieldName From TableName Where FieldName1 =
Forms![FormName]![Combo1Name] And FieldName2 = Forms![FormName]![Combo2Name]

On the AfterUpdate event of Combo2 run the code
Me.[Combo3Name].Requery
***********************************


--
Good Luck
BS"D

I have been able to base ONE combo box on another combo box using an article
at the Office online website. But I have THREE combos I need to 'join' (if
thats right word).

Customer combo box
Delivery Address combo box
Customer Contact combo box

I have managed to limit the delivery address combo list to only the customer
selected, but cannot seem to use the same code in the third box.

Any help with this would be much appreciated.