Combo box updating a combo box

[scheeps]

I would like to know how I can filter/update a combo box with the selection of another combo box.

I've got a combo box "Company" and depending on the selection, "Contact" needs to be updated with all the contacts belonging to the selected company.

Both the Company and Contact combo boxes Row Source's are from tables Company and Contacts, joined by Company_ID.

I know I need some code in the After UpDate event but can't figure out the code.

Hope you guys will be able to help.

 

[John Big Booty]

Have a look at Cascading Combo Boxes.

 

[scheeps]

Thanks for the quick reply and at least I know what to search for - Cascading Combo Boxes.

I'm using an ADP project and I suspect this is going to make my life harder. Where you will usually just have the following sql in your second combo's Row Source for example:

SELECT tblAttractions.AttactionAutoNo, tblAttractions.NameofAttraction, tblAttractions.AttractionType
FROM tblAttractions
WHERE (((tblAttractions.AttractionType)=[Forms]![FormView]![Type]));

and a .Requery in the parent combo, in ADP it is not that simple as you cannot pass [Forms]![FormView]![Type] back to SQL.

Any other help you can assist with maybe?

 

[MyOwnBoss]

I also use ADP for all my projects and understand what you are looking at.

First I do not fill the second combo untill I have made a selection in the first, then I can then open my record set for the second combo based on any key value.

Example: ABC Company is selected in Combo Box 1

then after update fill cb 2

50        rst(2).Open strCmbFill, CurrentProject.Connection, adOpenStatic, adLockReadOnly

          'Debug.Print rst(1).Fields("fcpopkey").Value & "," & rst(1).Fields("fcpoptext").Value & "," & rst(1).Fields("fcpopval").Value

60        rst(2).filter = "fcpopkey = " & "'prg.type'"

 

[scheeps]

Good to know that it is possible.

I've got my code as follow:

Private Sub cboCompany_AfterUpdate()
' Find the record that matches the control.
    Dim rst As Recordset
    Dim strCmbFill As String
    
    strCmbFill = "Select Contact_ID, Contact_Name from ODS.Contact"
    Set rst = CurrentDb.OpenRecordset(strCmbFill, dbOpenSnapshot)
   
    rst.Filter = "Company_ID = " & "'cboCompany.type'"

    rst.Close
    Set rst = Nothing
End Sub

Currently I get a "Object Variable or With block variable not set" error on Set rst = CurrentDb.OpenRecordset(strCmbFill, dbOpenSnapshot)

I've also got a few questions if you don't mind:
1. I don't understand in which part you'll actually update the Contact combo, seeing that the above is in the cboCompany AfterUpdate event.

2. What is the (2) in

rst(2).Open strCmbFill, CurrentProject.Connection, adOpenStatic, adLockReadOnly

3. I also don't understand the following part of your code

rst(2).filter = "fcpopkey = " & "'prg.type'"

What would 'prg.type' be?

Apologies for all the questions, Access is definitely not one of my strongest points.