Combo box needs to change query when other combox changes

[MDS]

Process: After selecting the first combo box, the second combo box need to be
populated with a particular query based on info from the first.

1st Combo box: Type (there are 4 different types)
2nd Combo box: Query need to change based on Type (there are 4 queries).

 

[ruralguy via AccessMonster.com]

What you are describing is called Cascading Combo Boxes.
This link should help:
http://www.fontstuff.com/access/acctut10.htm

 

[Allen Browne]

See:
Limit content of combo/list boxes
at:
http://www.mvps.org/access/forms/frm0028.htm

 

[Ken Mitchell]

Hi, Allen & group.

I'm trying to make this procedure work for me, without much success.
There's something here that I'm missing.

I'm using a variant of the Northwinds database, and trying to get the Orders
Subform to allow me to select the supplier by name, and then select the
products that that supplier handles.

I've modified the Order Details Extended query to add the Suppliers table,
and linked it SupplierID to SupplierID.

I've created a Combo Box called "SupplierName", which brings up a list of
all the companies in the Suppliers table.

I created an AfterUpdate event procedure modeled on (OK, "copied from") Dev
Ashish's code in http://www.mvps.org/access/forms/frm0028.htm

Private Sub SupplierName_AfterUpdate()
Dim strSQL As String
strSQL = "Select " & Me!SupplierName
strSQL = strSQL & " from Suppliers"
Me!ProductID.RowSourceType = "Table/Query"
Me!ProductID.RowSource = strSQL
End Sub


I've modified the ProductID Combo Box to put the following into RowSource:
Select ProductID, ProductName from Products Where SupplierName =
Forms!NewOrderSubform!SupplierName;

When I do this, three things happen;
1. I get an error message "Run-time error '438': Object doesn't support
this property or method", and
2. When I click "Debug", the higlighted line is
"Me!ProductID.RowSourceType = "Table/Query""
3. Access fills in the SupplierID in the SupplierName field in the
"Suppliers" table, overwriting the SupplierName that was there.

This third item ought to tell me something, but I'm not sure what!

I suspect that I'm putting the SQL statement in "RowSource" in the wrong
place - but I'm not sure where it OUGHT to go.

The last couple of times I've been stumped, you've pointed me in the right
direction; I'm hoping that the "Third time is the charm".

Thanks.

 

[Allen Browne]

If you are trying to limit the ProductID combo so it shows only the products
that come from the SupplierID identified in your SupplierName combo, you
will need to so something like this:

strSql = "SELECT Products.ProductID, Products.ProductName FROM Products "
If Not IsNull(Me.SupplierName) Then
strSql = strSql & "WHERE (Products.SupplierID = " & Me.SupplierName & ")
"
End If
strSql = strSql & "ORDER BY Products.ProductName;"
Me.ProductID.RowSource = strSql

Note that this assumes the SupplierName actually does have the SupplierID.